|
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.
|