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

JSON_TABLE: ON ERROR clause is used for empty values if ON EMPTY is not specified explicitly

    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"}', '$' columns (f varchar(16) path '$.x' default '100' on error)) as jt;
      +------+
      | f    |
      +------+
      | 100  |
      +------+
      1 row in set (0.001 sec)
      

      MySQL 8.0.19

      MySQL [test]> select * from json_table('{"a":"foo"}', '$' columns (f varchar(16) path '$.x' default '100' on error)) as jt;
      +------+
      | f    |
      +------+
      | NULL |
      +------+
      1 row in set (0.001 sec)
      

      MySQL behavior appears to be correct. According to the standard, default behavior for ON EMPTY and ON ERROR is NULL. Nothing suggests that ON ERROR can be used instead of ON EMPTY.

      If both ON EMPTY and ON ERROR are specified, then ON EMPTY is picked up correctly:

      bb-10.5-hf 6dfb3fab2

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

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: