Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
N/A
-
None
Description
|
bb-10.5-hf 6dfb3fab2 |
MariaDB [test]> select * from json_table('{"a":"foo"}', '$' columns (f varchar(16) path '$.x' default '100' on error)) as jt; |
+------+ |
| f |
|
+------+ |
| 100 |
|
+------+ |
1 row in set (0.001 sec) |
|
MySQL 8.0.19 |
MySQL [test]> select * from json_table('{"a":"foo"}', '$' columns (f varchar(16) path '$.x' default '100' on error)) as jt; |
+------+ |
| f |
|
+------+ |
| NULL | |
+------+ |
1 row in set (0.001 sec) |
MySQL behavior appears to be correct. According to the standard, default behavior for ON EMPTY and ON ERROR is NULL. Nothing suggests that ON ERROR can be used instead of ON EMPTY.
If both ON EMPTY and ON ERROR are specified, then ON EMPTY is picked up correctly:
|
bb-10.5-hf 6dfb3fab2 |
MariaDB [test]> select * from json_table('{"a":"foo"}', '$' columns (f varchar(16) path '$.x' default '100' on error default '0' on empty)) as jt; |
+------+ |
| f |
|
+------+ |
| 0 |
|
+------+ |
1 row in set (0.002 sec) |
Attachments
Issue Links
- relates to
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
Activity
To my understanding, that's not what the standard says, though. I am not sure that we should follow Oracle and not the standard in case of a contradiction.
Or maybe I am misinterpreting the standard.
serg, bar, any opinions?
elenst, you are right. The standard (SQL:2016, 7.11 <JSON table>, Syntax rules, 1)e) clearly says
iii) If JTRCD does not contain <JSON table column empty behavior>, then the implicit <JSON table column empty behavior> of JTRCD is NULL ON EMPTY.
and we should do it this way.
I did it that way intentionally - this is how the Oracle server behaves.
So not sure if this needs to be changed.