[MDEV-31478] Inconsistent handling of zero-padded numbers in json Created: 2023-06-14  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 11.0, 11.1, 11.2

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

Issue Links:
Duplicate
is duplicated by MDEV-31464 JSON_ARRAY_INTERSECT function prints ... Closed

 Description   

Actual Result

11.1.0-dbg>SELECT * FROM JSON_TABLE(JSON_KEY_VALUE('{"key1":{"a":01}}', '$.key1'), '$[*]' COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt;
 
Empty set (0.000 sec)
 
 
 
11.1.0-dbg>

Expected Result 

11.1.0-dbg>SELECT * FROM JSON_TABLE(JSON_KEY_VALUE('{"key1":{"a":01}}', '$.key1'), '$[*]' COLUMNS (k VARCHAR(11) PATH '$.key', v VARCHAR(5) PATH '$.value', id FOR ORDINALITY)) AS jt;
 
+------+------+------+
 
| k    | v    | id   |
 
+------+------+------+
 
| a    | 1    |    1 |
 
+------+------+------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>



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

what's the correct behavior for numbers with leading zeros?

MariaDB [test]> select JSON_EXTRACT('{"key1":{"a":01}}', '$.key1');
 
+---------------------------------------------+
 
| JSON_EXTRACT('{"key1":{"a":01}}', '$.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 15

Comment by Ramesh Sivaraman [ 2023-06-16 ]

Some JSON functions print positive integer and skip leading zeros.

11.1.0-dbg>SELECT JSON_OBJECT("id", 01, "name", "Monty");
 
+----------------------------------------+
 
| JSON_OBJECT("id", 01, "name", "Monty") |
 
+----------------------------------------+
 
| {"id": 1, "name": "Monty"}             |
 
+----------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>SELECT json_array(0, 01);
 
+-------------------+
 
| json_array(0, 01) |
 
+-------------------+
 
| [0, 1]            |
 
+-------------------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>

And some prints only leading zero in the result set. A ticket has already been created to fix this issue MDEV-31464

11.1.0-dbg>select json_value('{"key1":010}', '$.key1');
 
+--------------------------------------+
 
| json_value('{"key1":010}', '$.key1') |
 
+--------------------------------------+
 
| 0                                    |
 
+--------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
11.1.0-dbg>

Comment by Sergei Golubchik [ 2023-06-16 ]

then it's not a JSON_KEY_VALUE issue, let's rename it to "inconsistent handling of zero-padded numbers in json" and fix all functions here. And may be not in 11.2 only

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