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

JSON_TABLE: If value extracted for regular column is array, the first element is returned instead of error

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • N/A
    • JSON
    • None

    Description

      bb-10.5-hf 6dfb3fab2

      		 
      MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt;
      		 
      +------+
      		 
      | v    |
      		 
      +------+
      		 
      | foo  |
      		 
      +------+
      		 
      1 row in set (0.002 sec)
      		 
       
      		 
      MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt;
      		 
      +------+
      		 
      | v    |
      		 
      +------+
      		 
      | foo  |
      		 
      +------+
      		 
      1 row in set (0.002 sec)

      The value extracted from JSON text above is an array:

      MariaDB [test]> select json_extract('{"a":"foo","b":"bar"}','$.*');
      		 
      +---------------------------------------------+
      		 
      | json_extract('{"a":"foo","b":"bar"}','$.*') |
      		 
      +---------------------------------------------+
      		 
      | ["foo", "bar"]                              |
      		 
      +---------------------------------------------+
      		 
      1 row in set (0.001 sec)

      Based on MySQL documentation (and less obviously on the standard), only scalar values are allowed, and an error should be returned upon an array, like this:

      MySQL 8.0.19

      		 
      MySQL [test]>  select *  from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt1 limit 2;
      		 
      +------+
      		 
      | v    |
      		 
      +------+
      		 
      | NULL |
      		 
      +------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MySQL [test]>  select *  from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt1;
      		 
      +------+
      		 
      | v    |
      		 
      +------+
      		 
      | 100  |
      		 
      +------+
      		 
      1 row in set (0.001 sec)

      Attachments

        Issue Links

          relates to

          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 -
            Description {code:sql|title=bb-10.5-hf 6dfb3fab2}
            MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt;
            +------+
            | v |
            +------+
            | foo |
            +------+
            1 row in set (0.002 sec)

            MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt;
            +------+
            | v |
            +------+
            | foo |
            +------+
            1 row in set (0.002 sec)
            {code}

            The value extracted from JSON text above is an array:
            {code:sql}
            MariaDB [test]> select json_extract('{"a":"foo","b":"bar"}','$.*');
            +---------------------------------------------+
            | json_extract('{"a":"foo","b":"bar"}','$.*') |
            +---------------------------------------------+
            | ["foo", "bar"] |
            +---------------------------------------------+
            1 row in set (0.001 sec)
            {code}

            According to MySQL specification (and less obviously from the standard), only scalar values are allowed, and an error should be returned upon an array, like this:

            {code:sql|title=MySQL 8.0.19}
            MySQL [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt1 limit 2;
            +------+
            | v |
            +------+
            | NULL |
            +------+
            1 row in set (0.000 sec)

            MySQL [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt1;
            +------+
            | v |
            +------+
            | 100 |
            +------+
            1 row in set (0.001 sec)
            {code}             		{code:sql|title=bb-10.5-hf 6dfb3fab2}
            MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt;
            +------+
            | v |
            +------+
            | foo |
            +------+
            1 row in set (0.002 sec)

            MariaDB [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt;
            +------+
            | v |
            +------+
            | foo |
            +------+
            1 row in set (0.002 sec)
            {code}

            The value extracted from JSON text above is an array:
            {code:sql}
            MariaDB [test]> select json_extract('{"a":"foo","b":"bar"}','$.*');
            +---------------------------------------------+
            | json_extract('{"a":"foo","b":"bar"}','$.*') |
            +---------------------------------------------+
            | ["foo", "bar"] |
            +---------------------------------------------+
            1 row in set (0.001 sec)
            {code}

            Based on MySQL documentation (and less obviously on the standard), only scalar values are allowed, and an error should be returned upon an array, like this:

            {code:sql|title=MySQL 8.0.19}
            MySQL [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*')) as jt1 limit 2;
            +------+
            | v |
            +------+
            | NULL |
            +------+
            1 row in set (0.000 sec)

            MySQL [test]> select * from json_table('{"a":"foo","b":"bar"}', '$' columns (v varchar(20) path '$.*' default '100' on error)) as jt1;
            +------+
            | v |
            +------+
            | 100 |
            +------+
            1 row in set (0.001 sec)
            {code}
            holyfoot Alexey Botchkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            holyfoot Alexey Botchkov added a comment -

            fixed to work as Oracle.
            I wonder though if it's better to just disallow wildcards in the column's paths?

            holyfoot Alexey Botchkov added a comment - fixed to work as Oracle. I wonder though if it's better to just disallow wildcards in the column's paths?
            holyfoot Alexey Botchkov made changes -
            issue.field.resolutiondate 2020-06-05 09:52:23.0 2020-06-05 09:52:23.312
            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 [ 107396 ] MariaDB v4 [ 157643 ]

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.