[MDEV-25148] Unexpected success and result set upon erroneous JSON_VALUE call Created: 2021-03-15  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

10.2 ba7d86a6

 
MariaDB [test]> select json_value('{"a":[1,2]}', '$.a[*]');
 
+-------------------------------------+
 
| json_value('{"a":[1,2]}', '$.a[*]') |
 
+-------------------------------------+
 
| 1                                   |
 
+-------------------------------------+
 
1 row in set (0.000 sec)

So, the provided path refers to two values, of which the function picks up one and returns it. It is a wrong result, the expected result is an error. The standard is very specific about it:

JSON_VALUE expects that the SQL/JSON path expression will return one SQL/JSON item; the ON EMPTY clause can be used to handle missing data (no SQL/JSON items) gracefully. More than one SQL/JSON item is an error.

MySQL returns NULL, which implies an error:

MySQL 8.0.23

 
MySQL [test]> select json_value('{"a":[1,2]}', '$.a[*]');
 
+-------------------------------------+
 
| json_value('{"a":[1,2]}', '$.a[*]') |
 
+-------------------------------------+
 
| NULL                                |
 
+-------------------------------------+
 
1 row in set (0.000 sec)


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