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

JSON_TABLE: EXISTS PATH value is converted to other types incorrectly

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • N/A
    • Data types, JSON
    • 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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22293 JSON_TABLE: Values for regular columns are truncated for no reason

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17399 Add support for JSON_TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Fix Version/s 10.5 [ 23123 ]
            Assignee Alexey Botchkov [ holyfoot ]
            Description {code:sql|title=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)
            {code}
            {code:sql|title=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)
            {code}

            I couldn't find specification of {{EXISTS PATH}} in the standard, but [MySQL documentation|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html] 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.
            elenst Elena Stepanova made changes -
            Summary JSON_TABLE: EXISTS PATH value is converted to CHAR incorrectly JSON_TABLE: EXISTS PATH value is converted to other types incorrectly
            elenst Elena Stepanova made changes -
            Description {code:sql|title=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)
            {code}
            {code:sql|title=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)
            {code}

            I couldn't find specification of {{EXISTS PATH}} in the standard, but [MySQL documentation|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html] 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.             		{code:sql|title=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)
            {code}
            {code:sql|title=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)
            {code}

            I couldn't find specification of {{EXISTS PATH}} in the standard, but [MySQL documentation|https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html] 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:
            {code:sql|title==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)
            {code}

            {code:sql|title=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)
            {code}
            elenst Elena Stepanova added a comment -

            I've just realized that it's most likely the same problem as MDEV-22293, and the values are just truncated incorrectly rather than wrongly converted.

            elenst Elena Stepanova added a comment - I've just realized that it's most likely the same problem as MDEV-22293 , and the values are just truncated incorrectly rather than wrongly converted.
            elenst Elena Stepanova made changes -
            holyfoot Alexey Botchkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            holyfoot Alexey Botchkov made changes -
            issue.field.resolutiondate 2020-04-27 12:40:10.0 2020-04-27 12:40:10.647
            holyfoot Alexey Botchkov made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 107394 ] MariaDB v4 [ 157641 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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