[MDEV-25255] JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails - Jira

XML

Word

Printable

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

relates to

MDEV-17399 Add support for JSON_TABLE

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.