Description:
When applying DISTINCT to results produced by JSON_SET, MySQL incorrectly removes rows that contain JSON null values.
Specifically, JSON_SET('{}', '$.k', NULL) produces a valid JSON value {"k": null}, which is semantically distinct from {"k": 0}. However, when DISTINCT is applied, the row corresponding to {"k": null} is missing from the result.
This indicates that MySQL either treats JSON null as SQL NULL or incorrectly compares JSON values during DISTINCT evaluation, leading to loss of semantically distinct results.
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 (NULL);
-- Query 1 (baseline)
SELECT JSON_SET('{}', '$.k', t1.c0)
FROM t1
GROUP BY t1.c0;
-- Query 2 (with DISTINCT)
SELECT DISTINCT JSON_SET('{}', '$.k', t1.c0)
FROM t1
GROUP BY t1.c0;
Expected result:
Query 1:
{"k": 0}
{"k": null}
Query 2:
{"k": 0}
{"k": null}
Actual result:
Query 1:
{"k": 0}
{"k": null}
Query 2:
{"k": 0}
Suggested fix:
As was submitted earlier, I am currently attempting to assist MySQL in identifying similar types of bugs.
Description: When applying DISTINCT to results produced by JSON_SET, MySQL incorrectly removes rows that contain JSON null values. Specifically, JSON_SET('{}', '$.k', NULL) produces a valid JSON value {"k": null}, which is semantically distinct from {"k": 0}. However, when DISTINCT is applied, the row corresponding to {"k": null} is missing from the result. This indicates that MySQL either treats JSON null as SQL NULL or incorrectly compares JSON values during DISTINCT evaluation, leading to loss of semantically distinct results. 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 (NULL); -- Query 1 (baseline) SELECT JSON_SET('{}', '$.k', t1.c0) FROM t1 GROUP BY t1.c0; -- Query 2 (with DISTINCT) SELECT DISTINCT JSON_SET('{}', '$.k', t1.c0) FROM t1 GROUP BY t1.c0; Expected result: Query 1: {"k": 0} {"k": null} Query 2: {"k": 0} {"k": null} Actual result: Query 1: {"k": 0} {"k": null} Query 2: {"k": 0} Suggested fix: As was submitted earlier, I am currently attempting to assist MySQL in identifying similar types of bugs.