Description:
A query using DISTINCTROW returns different results when a semantically equivalent expression is replaced by a constant.
Specifically, the following expression is always equal to 0:
IFNULL(SUM(c0) - SUM(c0), 0)
However, replacing this expression with literal 0 leads to row loss when combined with DISTINCTROW, GROUP BY, and JSON_ARRAYAGG.
This violates SQL semantics because DISTINCT must be applied after full evaluation of the projection list.
JSON_ARRAYAGG produces distinct aggregated values per group, which must be 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 t6;
CREATE TABLE t6
(
c0 INT NULL
);
INSERT INTO t6 VALUES (0), (NULL);
– Query 1
SELECT DISTINCTROW
IFNULL(SUM(c0) - SUM(c0), 0),
JSON_ARRAYAGG(c0)
FROM t6
GROUP BY c0;
– Query 2 (equivalent rewrite)
SELECT DISTINCTROW
0,
JSON_ARRAYAGG(c0)
FROM t6
GROUP BY c0;
Expected result:
0 | [null]
0 | [0]
Actual result:
Query 1:
0 | [null]
0 | [0]
Query 2:
0 | [null]
One row is missing.
Suggested fix:
Ensure that DISTINCT is applied after full evaluation of all select expressions.
Constant projections must not trigger premature deduplication.
Aggregation results (including JSON_ARRAYAGG) must be fully considered during DISTINCT comparison.
