Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1
Description
The ON ERROR / ON EMPTY / DEFAULT ... ON ERROR|EMPTY clauses of JSON_TABLE columns are all silently ignored: when a JSON-to-target-type conversion fails, regardless of the specified handling, the type's zero value is returned (INT→0, DATE→'0000-00-00'):
ERROR ON ERROR: no error raised, returns 0 (MySQL raises ER 3156)
NULL ON ERROR: returns 0 instead of NULL
DEFAULT '-1' ON ERROR: returns 0 instead of -1
ERROR ON EMPTY: returns NULL instead of raising an error when the path does not exist
The combined form ERROR ON EMPTY ERROR ON ERROR is equally ineffective
SELECT * FROM JSON_TABLE('{"a":"str"}', '$' |
COLUMNS(c INT PATH '$.a' ERROR ON ERROR)) jt; |
-- MariaDB: c = 0 (should raise ER 3156; MySQL tested and raises it) |
-- MySQL: ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column c at row 1 |
|
|
SELECT * FROM JSON_TABLE('{"a":"str"}', '$' |
COLUMNS(c INT PATH '$.a' NULL ON ERROR)) jt; |
-- MariaDB: c = 0 (should return NULL) |
|
|
SELECT * FROM JSON_TABLE('{"a":"str"}', '$' |
COLUMNS(c INT PATH '$.a' DEFAULT '-1' ON ERROR)) jt; |
-- MariaDB: c = 0 (should return -1) |
|
|
SELECT * FROM JSON_TABLE('{}', '$' |
COLUMNS(c INT PATH '$.a' ERROR ON EMPTY)) jt; |
-- MariaDB: c = NULL (should raise an error)
|
|
|
SELECT * FROM JSON_TABLE('{"a":"notadate"}', '$' |
COLUMNS(c DATE PATH '$.a' ERROR ON ERROR)) jt; |
-- MariaDB: c = '0000-00-00' (should raise an error) |
|