[MDEV-22297] JSON_TABLE: ON ERROR clause is used for empty values if ON EMPTY is not specified explicitly Created: 2020-04-18  Updated: 2020-04-29  Resolved: 2020-04-29

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: N/A
Fix Version/s: N/A

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   

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)



 Comments   
Comment by Alexey Botchkov [ 2020-04-27 ]

I did it that way intentionally - this is how the Oracle server behaves.
So not sure if this needs to be changed.

Comment by Elena Stepanova [ 2020-04-27 ]

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?

Comment by Sergei Golubchik [ 2020-04-27 ]

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.

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