[MDEV-30962] JSON_OBJECT function generates "malformed" JSON with ZEROFILL type Created: 2023-03-29  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.4, 10.11.2, 10.7.8, 10.8.7, 10.9.5, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Minor
Reporter: Juan Ferrer Toribio Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-32692 JSON_OBJECT creates invalid json for ... Closed

 Description   

When using the JSON_OBJECT function with ZEROFILL values, "malformed" JSON strings are generated, this occurs because it puts one or more 0s before the number. The JSON MariaDB own's functions fail to parse the string. Ej:

CREATE OR REPLACE TEMPORARY TABLE tTest(
 
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 
  zeroInt INT(2) UNSIGNED ZEROFILL
 
);
 
 
 
INSERT INTO tTest SET zeroInt = 1;
 
 
 
SELECT JSON_OBJECT(
 
    'validProp1', 1,
 
    'zeroInt', zeroInt,
 
    'validProp2', 2
 
  ) INTO @json
 
  FROM tTest;
 
 
 
SELECT
 
  @json,
 
  JSON_VALUE(@json, '$.validProp1') validProp1,
 
  JSON_VALUE(@json, '$.zeroInt') zeroInt,
 
  JSON_VALUE(@json, '$.validProp2') validProp2;

Wich results in:

+--------------------------------------------------+----------+-------+----------+
 
| @json                                            |validProp1|zeroInt|validProp2|
 
+--------------------------------------------------+----------+-------+----------+
 
| {"validProp1": 1, "zeroInt": 01, "validProp2": 2}|1         |0      |          |
 
+--------------------------------------------------+----------+-------+----------+

The same JSON string also fails to parse with native Javascript JSON.parse() function.

JSON.parse('{"validProp1": 1, "zeroInt": 01, "validProp2": 2}')
 
 
 
> Uncaught SyntaxError: JSON.parse: expected ',' or '}' after property value in object at line 1 column 31 of the JSON data...



 Comments   
Comment by Alice Sherepa [ 2023-04-04 ]

Thank you for the report! I repeated as described on 10.4-10.11:

MariaDB [test]> create table t (a int(5) zerofill);
 
Query OK, 0 rows affected (0.034 sec)
 
 
 
MariaDB [test]> insert into t select 1;
 
Query OK, 1 row affected (0.004 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select JSON_OBJECT("id", 1, "a", a, "b",'b') into @j from t;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [test]> select @j,json_valid(@j),JSON_VALUE(@j, '$.a'), JSON_VALUE(@j , '$.b') from t;
 
+---------------------------------+----------------+-----------------------+------------------------+
 
| @j                              | json_valid(@j) | JSON_VALUE(@j, '$.a') | JSON_VALUE(@j , '$.b') |
 
+---------------------------------+----------------+-----------------------+------------------------+
 
| {"id": 1, "a": 00001, "b": "b"} |              0 | 0                     | NULL                   |
 
+---------------------------------+----------------+-----------------------+------------------------+
 
1 row in set (0.001 sec)

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