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

    • 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"}', '$' 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

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