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

JSON_TABLE: Inconsistency in implicit data type conversion

    XMLWordPrintable

    Details

    • 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:
      None

      Description

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

      select * from
      json_table(
        '[{"a":"asd"}, 
          {"a":123},
          {"a":[]},
          {"a":{}}
         ]',
        '$[*]' 
        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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: