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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: N/A
    • Component/s: JSON
    • Labels:
      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

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: