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

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

                Dates

                Created:
                Updated:
                Resolved: