[MDEV-25255] JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails Created: 2021-03-25  Updated: 2021-04-21  Resolved: 2021-03-28

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, JSON
Affects Version/s: N/A
Fix Version/s: 10.6.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed

 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


Generated at Thu Feb 08 09:36:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.