Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25149

JSON_TABLE: Inconsistency in implicit data type conversion



    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON
    • Labels:


      This was a part of code review comments / changes. The example is taken from the review comments as is:

      select * from
        columns (
          id for ordinality,
          intcol int path '$.a' default '1234' on empty default '5678' on error
      ) as tt;

      At that point, intcol value in the first row was 0, indicating that the value was retrieved, and an implicit string => number conversion was performed. The question was raised on what grounds this conversion occurs, and why there is no warning about it; eventually the result was changed to the default-on-error, which it is now:

      bb-10.6-mdev17399-psergey2 8b533cc1d5

      | id   | intcol |
      |    1 |   5678 |
      |    2 |    123 |
      |    3 |   5678 |
      |    4 |   5678 |
      4 rows in set (0.001 sec)

      Personally, I am fine with this decision; but to my understanding, the implicit conversion is a general MariaDB approach applied in a variety of cases, so this change may contradict the "official line". Here is the basic example:

      MariaDB [test]> select "asd" = 0;
      | "asd" = 0 |
      |         1 |
      1 row in set, 1 warning (0.000 sec)
      MariaDB [test]> show warnings;
      | Level   | Code | Message                                 |
      | Warning | 1292 | Truncated incorrect DOUBLE value: 'asd' |
      1 row in set (0.000 sec)

      I suggest to make sure that Sergei Golubchik is okay with the chosen JSON_TABLE behavior, otherwise there is a big chance that later the result will be re-considered and changed for the sake of consistency. In this case it's better to do it right from the beginning than change the behavior later.

      Of course the review note about a missing warning was also valid and should be taken into account if the logic changes back to returning the converted value.


          Issue Links



              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              0 Vote for this issue
              4 Start watching this issue



                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.