Description:
When applying DISTINCT to results produced by JSON_ARRAY, MySQL incorrectly eliminates rows containing JSON null values.
Specifically, JSON_ARRAY(NULL) produces a valid JSON value [null], which is semantically distinct from other JSON values such as [0] and [1]. However, when DISTINCT is applied, the row corresponding to [null] is incorrectly removed.
This suggests that MySQL either misinterprets JSON null as SQL NULL or performs incorrect comparison of JSON values during duplicate elimination.
How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
c0 DECIMAL NULL
);
INSERT INTO t1 (c0) VALUES (0);
INSERT INTO t1 (c0) VALUES (1);
INSERT INTO t1 (c0) VALUES (NULL);
-- Query 1 (baseline)
SELECT JSON_ARRAY(t1.c0)
FROM t1
GROUP BY t1.c0;
-- Query 2 (with DISTINCT)
SELECT DISTINCT JSON_ARRAY(t1.c0)
FROM t1
GROUP BY t1.c0;
Expected result:
Query 1:
[0]
[1]
[null]
Query 2 (DISTINCT should preserve all rows):
[0]
[1]
[null]
Actual result:
Query 1:
[0]
[1]
[null]
Query 2:
[0]
[1]
Suggested fix:
Ensure that JSON values are compared based on their full semantic content during DISTINCT processing.
In particular:
• JSON null ([null]) must not be treated as SQL NULL
• JSON values must not be collapsed or normalized in a way that loses semantic distinctions
• DISTINCT comparison logic should correctly differentiate JSON values such as [0], [1], and [null]
The issue may stem from incorrect handling of JSON null or improper internal comparison (e.g., hashing or canonicalization) of JSON values.
Description: When applying DISTINCT to results produced by JSON_ARRAY, MySQL incorrectly eliminates rows containing JSON null values. Specifically, JSON_ARRAY(NULL) produces a valid JSON value [null], which is semantically distinct from other JSON values such as [0] and [1]. However, when DISTINCT is applied, the row corresponding to [null] is incorrectly removed. This suggests that MySQL either misinterprets JSON null as SQL NULL or performs incorrect comparison of JSON values during duplicate elimination. How to repeat: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( c0 DECIMAL NULL ); INSERT INTO t1 (c0) VALUES (0); INSERT INTO t1 (c0) VALUES (1); INSERT INTO t1 (c0) VALUES (NULL); -- Query 1 (baseline) SELECT JSON_ARRAY(t1.c0) FROM t1 GROUP BY t1.c0; -- Query 2 (with DISTINCT) SELECT DISTINCT JSON_ARRAY(t1.c0) FROM t1 GROUP BY t1.c0; Expected result: Query 1: [0] [1] [null] Query 2 (DISTINCT should preserve all rows): [0] [1] [null] Actual result: Query 1: [0] [1] [null] Query 2: [0] [1] Suggested fix: Ensure that JSON values are compared based on their full semantic content during DISTINCT processing. In particular: • JSON null ([null]) must not be treated as SQL NULL • JSON values must not be collapsed or normalized in a way that loses semantic distinctions • DISTINCT comparison logic should correctly differentiate JSON values such as [0], [1], and [null] The issue may stem from incorrect handling of JSON null or improper internal comparison (e.g., hashing or canonicalization) of JSON values.