Description:
A query using DISTINCTROW returns different results when a semantically equivalent expression is replaced by a constant.
Specifically, the following expression always evaluates to 0:
COALESCE(SUM(c0), 0)
Replacing it with literal 0 causes missing rows when used together with DISTINCTROW, GROUP BY, and JSON_OBJECT.
This violates SQL semantics because DISTINCT must be applied after full evaluation of all select expressions.
JSON_OBJECT produces structured key-value outputs per row, which must be fully considered during DISTINCT comparison. The presence of a constant projection appears to trigger incorrect deduplication.
This suggests a potential optimizer issue where constant folding interacts incorrectly with DISTINCTROW.
How to repeat:
DROP TABLE IF EXISTS t8;
CREATE TABLE t8
(
c0 INT NULL
);
INSERT INTO t8 VALUES (0), (NULL);
– Query 1 (baseline)
SELECT DISTINCTROW
COALESCE(SUM(c0), 0),
JSON_OBJECT(‘k’, c0)
FROM t8
GROUP BY c0;
– Query 2 (equivalent rewrite)
SELECT DISTINCTROW
0,
JSON_OBJECT(‘k’, c0)
FROM t8
GROUP BY c0;
Expected result:
0 | {“k”: 0}
0 | {“k”: null}
Actual result:
Query 1:
0 | {“k”: 0}
0 | {“k”: null}
Query 2:
0 | {“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.
All JSON outputs (including JSON_OBJECT) must be fully considered in DISTINCT comparison.
Private comment:
This issue is part of the same class affecting DISTINCTROW + constant projections with JSON functions. Similar behavior may occur with JSON_ARRAY, JSON_SET, and JSON_OBJECTAGG.
Description: A query using DISTINCTROW returns different results when a semantically equivalent expression is replaced by a constant. Specifically, the following expression always evaluates to 0: COALESCE(SUM(c0), 0) Replacing it with literal 0 causes missing rows when used together with DISTINCTROW, GROUP BY, and JSON_OBJECT. This violates SQL semantics because DISTINCT must be applied after full evaluation of all select expressions. JSON_OBJECT produces structured key-value outputs per row, which must be fully considered during DISTINCT comparison. The presence of a constant projection appears to trigger incorrect deduplication. This suggests a potential optimizer issue where constant folding interacts incorrectly with DISTINCTROW. How to repeat: DROP TABLE IF EXISTS t8; CREATE TABLE t8 ( c0 INT NULL ); INSERT INTO t8 VALUES (0), (NULL); – Query 1 (baseline) SELECT DISTINCTROW COALESCE(SUM(c0), 0), JSON_OBJECT(‘k’, c0) FROM t8 GROUP BY c0; – Query 2 (equivalent rewrite) SELECT DISTINCTROW 0, JSON_OBJECT(‘k’, c0) FROM t8 GROUP BY c0; Expected result: 0 | {“k”: 0} 0 | {“k”: null} Actual result: Query 1: 0 | {“k”: 0} 0 | {“k”: null} Query 2: 0 | {“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. All JSON outputs (including JSON_OBJECT) must be fully considered in DISTINCT comparison. Private comment: This issue is part of the same class affecting DISTINCTROW + constant projections with JSON functions. Similar behavior may occur with JSON_ARRAY, JSON_SET, and JSON_OBJECTAGG.