Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.8, 10.3.15, 10.4.4
-
None
Description
Tests were done on these versions, but more versions may be affected:
- 10.3.8 (server version)
- 10.3.15 (Docker image)
- 10.4.4 (Docker image)
We have found some issues with JSON_VALID and JSON_MERGE functions that occur when the keys of the JSON objects start with a double quote.
For a JSON object to be valid, the double quote needs to be escaped with a backslash because the key is a string. Given the backslash is a reserved character in SQL, we need to add an additional one for the escaping, hence the double backslash seen in the following test sets.
JSON_VALID Test Set
select JSON_VALID('{"admin\\"": null}'), '{"admin\\"": null}' |
UNION
|
select JSON_VALID('{"\\"admin": null}'), '{"\\"admin": null}' |
UNION
|
select JSON_VALID('{"\\"": null}'), '{"\\"": null}'; |
Results
| JSON_VALID() | json |
|---|---|
| TRUE | {"admin\"": null} |
| FALSE | {"\"admin": null} |
| FALSE | {"\"": null} |
Expected Result
| JSON_VALID() | json |
|---|---|
| TRUE | {"admin\"": null} |
| TRUE | {"\"admin": null} |
| TRUE | {"\"": null} |
JSON_MERGE Test Set
create table Tags( |
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, |
tj JSON
|
);
|
|
|
insert into Tags(tj) values ('{"admin":null}'); |
insert into Tags(tj) values ('{"\\"":null}'); |
insert into Tags(tj) values ('{"admin":null, "\\"":null}'); |
insert into Tags(tj) values ('{"\\"":null, "admin":null}'); |
insert into Tags(tj) values ('{"admin":null, "\\"":null, "admin2":null }'); |
insert into Tags(tj) values ('{"admin":null, "admin2":null ,"\\"":null }'); |
insert into Tags(tj) values ('{"abc\\"":null}'); |
insert into Tags(tj) values ('{"admin":null, "abc\\"":null}'); |
insert into Tags(tj) values ('{"abc\\"":null, "admin":null}'); |
|
|
select tj, JSON_VALID(tj), |
JSON_MERGE(tj,'{"admin":null}'), |
JSON_MERGE('{"admin":null}', tj), |
|
|
JSON_MERGE(tj,'{"\\"":null}'), |
JSON_MERGE('{"\\"":null}', tj), |
|
|
JSON_MERGE(tj,'{"admin": null, "\\"":null}'), |
JSON_MERGE('{"admin": null, "\\"":null}', tj), |
|
|
JSON_MERGE(tj,'{"\\"":null, "admin":null}'), |
JSON_MERGE('{"\\"":null, "admin":null}', tj), |
|
|
JSON_MERGE(tj,'{"admin":null, "\\"":null, "admin2":null }'), |
JSON_MERGE('{"admin":null, "\\"":null, "admin2":null }', tj), |
|
|
JSON_MERGE(tj,'{"admin":null, "admin2":null ,"\\"":null }'), |
JSON_MERGE('{"admin":null, "admin2":null ,"\\"":null }', tj), |
|
|
JSON_MERGE(tj,'{"abc\\"":null}'), |
JSON_MERGE('{"abc\\"":null}', tj), |
|
|
JSON_MERGE(tj,'{"admin":null, "abc\\"":null}'), |
JSON_MERGE('{"admin":null, "abc\\"":null}', tj), |
|
|
JSON_MERGE(tj,'{"abc\\"":null, "admin":null}'), |
JSON_MERGE('{"abc\\"":null, "admin":null}', tj) |
FROM Tags; |
Issues found
1. Order of JSON_MERGE parameters affects the result.
Check differences between:
JSON_MERGE(tj,'{"admin":null}') |
JSON_MERGE('{"admin":null}', tj) |
2. Number of JSON keys affects the result.
Check differences between:
JSON_MERGE('{"admin": null, "\\"":null}', tj) |
JSON_MERGE('{"\\"":null}', tj) |
3. Order of JSON keys within a JSON Object affects the result.
Check differences between:
JSON_MERGE('{"admin": null, "\\"":null}', tj) |
JSON_MERGE('{"\\"":null, "admin":null}', tj) |
Expected Results
JSON_MERGE should produce a valid output regardless of the order and size of parameters (assuming the parameters have JSON_VALID as 1).
Note: We suspect the issue in JSON_MERGE is related with the first issue with JSON_VALID.