Bug #120142 Incorrect result with DISTINCTROW and JSON_OBJECTAGG when replacing COALESCE(SUM(NULL),0) with constant

• Submitted: 25 Mar 3:09
• Modified: 25 Mar 19:06
• Reporter: Wang Ojiken
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any
• Tags: distinct, GROUP BY, json_objectagg, Optimizer, wrong result

[25 Mar 3:09] Wang Ojiken

Description:
A query using DISTINCTROW returns different results when a semantically equivalent expression is replaced by a constant.

Specifically:

COALESCE(SUM(NULL), 0) ≡ 0

However, replacing this expression with literal 0 causes row loss when combined with DISTINCTROW, GROUP BY, and JSON_OBJECTAGG.

This indicates that the optimizer performs incorrect deduplication when a constant projection is present together with aggregation.

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 
       COALESCE(SUM(NULL), 0),
       JSON_OBJECTAGG('k79', t4.c0)
FROM t4
GROUP BY t4.c0;

-- Query 2 (equivalent rewrite)
SELECT DISTINCTROW 
       0,
       JSON_OBJECTAGG('k79', t4.c0)
FROM t4
GROUP BY t4.c0;

Expected result

Both queries should return:

0 | {"k79": null}
0 | {"k79": 0}

Actual result

Query 1:
0 | {"k79": null}
0 | {"k79": 0}

Query 2:
0 | {"k79": null}
One row is missing.

Suggested fix:
Ensure that:
	•	DISTINCT is applied after full evaluation of all select expressions
	•	Constant projections do not trigger premature deduplication
	•	Aggregation results (especially JSON_OBJECTAGG) are fully considered in DISTINCT comparison

[25 Mar 19:06] Roy Lyseng

Thank you for the bug report.
Verified as described.
Problem is repeatable also without the deprecated zerofill and display width features.

[25 Mar 20:29] Jean-François Gagné

For completeness, I think it is interesting to add that Bug#120149 was marked as a duplicate of this one.