Description:
A query using DISTINCTROW produces inconsistent results when a semantically equivalent expression is replaced with a constant.
Specifically:
IFNULL(SUM(NULL), 0) ≡ 0
However, rewriting this expression as the literal 0 leads to missing rows when used together with DISTINCTROW, GROUP BY, and JSON_OBJECTAGG.
This suggests that the optimizer performs incorrect deduplication when a constant projection is present alongside aggregation, failing to account for differences in aggregated results.
How to repeat:
DROP TABLE IF EXISTS t4;
CREATE TABLE t4
(
c0 MEDIUMINT(104) UNSIGNED ZEROFILL NULL
);
INSERT INTO t4 (c0) VALUES (0);
INSERT INTO t4 (c0) VALUES (NULL);
-- Query 1
SELECT DISTINCTROW
IFNULL(SUM(NULL), 0),
JSON_OBJECTAGG('A12', t4.c0)
FROM t4
GROUP BY t4.c0;
-- Query 2 (equivalent rewrite)
SELECT DISTINCTROW
0,
JSON_OBJECTAGG('A12', t4.c0)
FROM t4
GROUP BY t4.c0;
Expected result:
Both queries should return:
0 | {"A12": null}
0 | {"A12": 0}
Actual result:
Query 1:
0 | {"A12": null}
0 | {"A12": 0}
Query 2:
0 | {"A12": null}
One row is missing.
Suggested fix:
Defer the execution of DISTINCTROW until all select-list expressions have been completely evaluated.
Avoid treating constant projections as a basis for early duplicate elimination.
Ensure that all computed aggregation outputs, particularly JSON_OBJECTAGG, are properly considered during DISTINCT comparison.
Description: A query using DISTINCTROW produces inconsistent results when a semantically equivalent expression is replaced with a constant. Specifically: IFNULL(SUM(NULL), 0) ≡ 0 However, rewriting this expression as the literal 0 leads to missing rows when used together with DISTINCTROW, GROUP BY, and JSON_OBJECTAGG. This suggests that the optimizer performs incorrect deduplication when a constant projection is present alongside aggregation, failing to account for differences in aggregated results. How to repeat: DROP TABLE IF EXISTS t4; CREATE TABLE t4 ( c0 MEDIUMINT(104) UNSIGNED ZEROFILL NULL ); INSERT INTO t4 (c0) VALUES (0); INSERT INTO t4 (c0) VALUES (NULL); -- Query 1 SELECT DISTINCTROW IFNULL(SUM(NULL), 0), JSON_OBJECTAGG('A12', t4.c0) FROM t4 GROUP BY t4.c0; -- Query 2 (equivalent rewrite) SELECT DISTINCTROW 0, JSON_OBJECTAGG('A12', t4.c0) FROM t4 GROUP BY t4.c0; Expected result: Both queries should return: 0 | {"A12": null} 0 | {"A12": 0} Actual result: Query 1: 0 | {"A12": null} 0 | {"A12": 0} Query 2: 0 | {"A12": null} One row is missing. Suggested fix: Defer the execution of DISTINCTROW until all select-list expressions have been completely evaluated. Avoid treating constant projections as a basis for early duplicate elimination. Ensure that all computed aggregation outputs, particularly JSON_OBJECTAGG, are properly considered during DISTINCT comparison.