Description:
A query using DISTINCTROW produces missing rows when a constant projection is used in combination with COUNT and JSON_SET.
Specifically, the following expression always evaluates to 1:
IFNULL(COUNT(0), 0)
Replacing it with literal 1 causes row loss when combined with DISTINCTROW, GROUP BY, and JSON_SET.
This violates SQL semantics because DISTINCT should operate on the fully evaluated projection list.
JSON_SET produces structured JSON outputs per row, which must be fully considered during DISTINCT comparison. The presence of a constant projection triggers incorrect deduplication.
How to repeat:
DROP TABLE IF EXISTS t9;
CREATE TABLE t9
(
c0 INT NULL
);
INSERT INTO t9 VALUES (0), (NULL);
– Query 1 (baseline)
SELECT DISTINCTROW
IFNULL(COUNT(0), 0),
JSON_SET(’{}’, ‘$.k’, c0)
FROM t9
GROUP BY c0;
– Query 2 (equivalent rewrite)
SELECT DISTINCTROW
1,
JSON_SET(’{}’, ‘$.k’, c0)
FROM t9
GROUP BY c0;
Expected result:
1 | {“k”: 0}
1 | {“k”: null}
Actual result:
Query 1:
1 | {“k”: 0}
1 | {“k”: null}
Query 2:
1 | {“k”: 0}
One row is missing.
Suggested fix:
Ensure DISTINCT is applied after full evaluation of all select expressions.
Constant projections must not trigger premature deduplication.
JSON_SET results must be fully considered in DISTINCT comparison.
Description: A query using DISTINCTROW produces missing rows when a constant projection is used in combination with COUNT and JSON_SET. Specifically, the following expression always evaluates to 1: IFNULL(COUNT(0), 0) Replacing it with literal 1 causes row loss when combined with DISTINCTROW, GROUP BY, and JSON_SET. This violates SQL semantics because DISTINCT should operate on the fully evaluated projection list. JSON_SET produces structured JSON outputs per row, which must be fully considered during DISTINCT comparison. The presence of a constant projection triggers incorrect deduplication. How to repeat: DROP TABLE IF EXISTS t9; CREATE TABLE t9 ( c0 INT NULL ); INSERT INTO t9 VALUES (0), (NULL); – Query 1 (baseline) SELECT DISTINCTROW IFNULL(COUNT(0), 0), JSON_SET(’{}’, ‘$.k’, c0) FROM t9 GROUP BY c0; – Query 2 (equivalent rewrite) SELECT DISTINCTROW 1, JSON_SET(’{}’, ‘$.k’, c0) FROM t9 GROUP BY c0; Expected result: 1 | {“k”: 0} 1 | {“k”: null} Actual result: Query 1: 1 | {“k”: 0} 1 | {“k”: null} Query 2: 1 | {“k”: 0} One row is missing. Suggested fix: Ensure DISTINCT is applied after full evaluation of all select expressions. Constant projections must not trigger premature deduplication. JSON_SET results must be fully considered in DISTINCT comparison.