[MDEV-31477] Inconsistent handling while fetching values in json Created: 2023-06-14  Updated: 2023-09-12

Status: Stalled
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 11.2
Fix Version/s: 10.4, 11.2

Type: Bug Priority: Major
Reporter: Ramesh Sivaraman Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-30145 JSON_TABLE: allow to retrieve the key... Closed

 Description   

The JSON_KEY_VALUE() (in 11.2) , JSON_QUERY() and JSON_VALUE() function does not print a warning about syntax error if variable is combined with string.
JSON_EXTRACT() does. So the behaviour is non consistent.

11.1.0-dbg>set @json = '{"a":1, "b":2, "c" : 3}';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
11.1.0-dbg>select @json;
 
+-------------------------+
 
| @json                   |
 
+-------------------------+
 
| {"a":1, "b":2, "c" : 3} |
 
+-------------------------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>select  JSON_KEY_VALUE('{"key1": @json}', '$.key1');
 
+---------------------------------------------+
 
| JSON_KEY_VALUE('{"key1": @json}', '$.key1') |
 
+---------------------------------------------+
 
| NULL                                        |
 
+---------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
11.1.0-dbg>

Expected syntax error message

11.1.0-dbg>select  JSON_KEY_VALUE('{"key1": @json}', '$.key1');
 
ERROR 4038 (HY000): Syntax error in JSON text in argument 1 to function 'JSON_KEY_VALUE' at position x
 
11.1.0-dbg>

JSON_KEY_VALUE function should also print syntax error if the curly braces are not properly closed

11.1.0-dbg>SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b":2}', '$.key1');
 
+------------------------------------------------------+
 
| JSON_KEY_VALUE('{"key1":{"a":1, "b":2}', '$.key1')   |
 
+------------------------------------------------------+
 
| [{"key": "a", "value": 1}, {"key": "b", "value": 2}] |
 
+------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>

in 10.4:

MariaDB [test]> select json_extract('

{"key1": @json}

', '$.key1');
-------------------------------------------

json_extract(' {"key1": @json}

', '$.key1')

-------------------------------------------

NULL

-------------------------------------------
1 row in set, 1 warning (0.001 sec)

Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10

 Comments   
Comment by Sergei Golubchik [ 2023-06-16 ]

should it be an error? for JSON_EXTRACT it's a warning

MariaDB [test]> select json_extract('{"key1": @json}', '$.key1');
 
+-------------------------------------------+
 
| json_extract('{"key1": @json}', '$.key1') |
 
+-------------------------------------------+
 
| NULL                                      |
 
+-------------------------------------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10

for JSON_VALUE it's just NULL

MariaDB [test]> select json_value('{"key1": @json}', '$.key1');
 
+-----------------------------------------+
 
| json_value('{"key1": @json}', '$.key1') |
 
+-----------------------------------------+
 
| NULL                                    |
 
+-----------------------------------------+
 
1 row in set (0.000 sec)

Comment by Rucha Deodhar [ 2023-06-16 ]

It should be a warning, behaviour should be uniform. There was a bug in Json_path_extractor::extract() function. Will fix it in the lowest version

Comment by Rucha Deodhar [ 2023-06-20 ]

Patch:
https://github.com/MariaDB/server/tree/bb-10.4-MDEV-31477
https://github.com/MariaDB/server/tree/bb-11.2-MDEV-31477-json_key_value
https://github.com/MariaDB/server/tree/bb-10.5-MDEV-31477

Comment by Alexey Botchkov [ 2023-09-12 ]

Patch looks mostly fine.
One modification recommended in the comment.
here:
https://github.com/MariaDB/server/tree/bb-11.2-MDEV-31477-json_key_value

Generated at Thu Feb 08 10:24:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.