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

JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails

    XMLWordPrintable

Details

    Description

      This works and expectedly returns NULL without any errors or warnings (as the implicit behavior is NULL ON ERROR):

      bb-10.6-mdev17399-hf 8611fb30b

      MariaDB [test]> SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH '$.*')) AS jt;
      +------+
      | f    |
      +------+
      | NULL |
      +------+
      1 row in set (0.001 sec)
      

      However, this fails, even though there isn't, and cannot be, an incorrect value as it should already be NULL-ed by JSON_TABLE:

      MariaDB [test]> CREATE TABLE t AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH '$.*')) AS jt;
      ERROR 1292 (22007): Incorrect date value: '1' for column ``.`(temporary)`.`f` at row 1
      

      Same happens even with an explicit NULL ON ERROR:

      MariaDB [test]> CREATE TABLE t AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH '$.*' NULL ON ERROR)) AS jt;
      ERROR 1292 (22007): Incorrect date value: '1' for column ``.`(temporary)`.`f` at row 1
      

      In MySQL 8.0 it works:

      MySQL 8.0.23

      MySQL [test]> CREATE TABLE t AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH '$.*' )) AS jt;
      Query OK, 1 row affected (0.130 sec)
      Records: 1  Duplicates: 0  Warnings: 0
      

      Attachments

        Issue Links

          Activity

            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.