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

JSON_TABLE: EXISTS PATH value is converted to other types incorrectly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: N/A
    • Component/s: Data types, JSON
    • Labels:
      None

      Description

      bb-10.5-hf 6dfb3fab2

      MariaDB [test]> select * from json_table('{"a":"foo"}', '$' columns (f char(1) exists path '$.a')) as jt;
      +------+
      | f    |
      +------+
      |      |
      +------+
      1 row in set (0.002 sec)
      

      MySQL 8.0.19

      MySQL [test]> select * from json_table('{"a":"foo"}', '$' columns (f char(1) exists path '$.a')) as jt;
      +------+
      | f    |
      +------+
      | 1    |
      +------+
      1 row in set (0.001 sec)
      

      I couldn't find specification of EXISTS PATH in the standard, but MySQL documentation explicitly says "type can be any valid MySQL data type" (even if it should be integer). And integer normally casts to char well, so it shouldn't be a problem.

      Float is also wrong:

      =bb-10.5-hf 6dfb3fab2

      MariaDB [test]> select * from json_table('{"a":"foo"}', '$' columns (f float exists path '$.a')) as jt;
      +------+
      | f    |
      +------+
      |    0 |
      +------+
      1 row in set (0.002 sec)
      

      MySQL 8.0.19

      MySQL [test]> select * from json_table('{"a":"foo"}', '$' columns (f float exists path '$.a')) as jt;
      +------+
      | f    |
      +------+
      |    1 |
      +------+
      1 row in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: