Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.7(EOL), 10.8(EOL), 10.9(EOL)
-
Unexpected results
-
The comparison of two JSON objects with JSON_EQUALS, JSON_NORMALIZE, or ordering, could result in unpredictable results where there was an object with the same object key listed more than once; the normalization has been made consistent
Description
There is a bug in the processing of JSON_NORMALIZE() due to the fact that the sort algorithm isn't stable (it doesn't guarantee that duplicates in the input remain in input order). For instance:
SET @j = '{"x": 1, "y": 2, "z": 3, "b": 0, "c": 5, "b":3, "a": 4, "b": 1, "a": 3, "a": 2, "a": 1, "b": 2}'; |
SELECT @k := JSON_NORMALIZE(@j); |
SELECT JSON_EXTRACT(@j, '$.b'), JSON_EXTRACT(@k, '$.b'); |
Results in:
+---------------------------------------------------------------------------------------------------------------------------+
|
| @k |
|
+---------------------------------------------------------------------------------------------------------------------------+
|
| {"a":4.0E0,"a":1.0E0,"a":2.0E0,"a":3.0E0,"b":1.0E0,"b":2.0E0,"b":3.0E0,"b":0.0E0,"c":5.0E0,"x":1.0E0,"y":2.0E0,"z":3.0E0} |
|
+---------------------------------------------------------------------------------------------------------------------------+
|
+-------------------------+-------------------------+
|
| JSON_EXTRACT(@j, '$.b') | JSON_EXTRACT(@k, '$.b') |
|
+-------------------------+-------------------------+
|
| 0 | 1.0E0 |
|
+-------------------------+-------------------------+
|
Given that duplicate key values are inaccessible via path expressions, I expected JSON_NORMALIZE() to remove duplicate keys so that
{"a": 1, "a": 2}and
{"a": 1}would produce the same JSON_NORMALIZE() output (and thus also be JSON_EQUAL()). If this isn't desirable for some reason, the sort must at least be stable so that the extracted values don't jump around.
Attachments
Issue Links
- relates to
-
MDEV-16375 Add JSON_NORMALIZE function to normalize JSON values
-
- Closed
-
-
-
- Open
-
