MySQL Bugs: #120149: Unexpected row loss with JSON_ARRAY when replacing aggregation with constant in DISTINCTROW query

Bug #120149	Unexpected row loss with JSON_ARRAY when replacing aggregation with constant in DISTINCTROW query
Submitted:	25 Mar 7:49	Modified:	25 Mar 19:36
Reporter:	Wang Ojiken	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	9.6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	distinct, GROUP BY, json_array, Optimizer, wrong result

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:
IFNULL(SUM(c0), 0)

However, replacing it with literal 0 leads to missing rows when combined with DISTINCTROW, GROUP BY, and JSON_ARRAY.

This violates SQL semantics because DISTINCT must be applied after full evaluation of all select expressions.

JSON_ARRAY produces structured values 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 t7;
CREATE TABLE t7
(
c0 INT NULL
);

INSERT INTO t7 VALUES (0), (NULL);

– Query 1 (baseline)
SELECT DISTINCTROW
IFNULL(SUM(c0), 0),
JSON_ARRAY(c0)
FROM t7
GROUP BY t7.c0;

– Query 2 (equivalent rewrite)
SELECT DISTINCTROW
0,
JSON_ARRAY(c0)
FROM t7
GROUP BY t7.c0;

Expected result:
0 | [0]
0 | [null]

Actual result:
Query 1:
0 | [0]
0 | [null]

Query 2:
0 | [0]

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.
All JSON aggregation or construction results (including JSON_ARRAY) must be fully considered in DISTINCT comparison.

Thank you for the bug report.
Verified as duplicate of bug#120142.

I think it is useful to add that this also affects 8.0.45 and 8.4.8, see repro below.

I am also curious how much this is related to Bug#110556.

./use test <<< '
DROP TABLE IF EXISTS t7;
CREATE TABLE t7(c0 INT NULL);

INSERT INTO t7 VALUES (0), (NULL);

SELECT version();
SELECT DISTINCTROW IFNULL(SUM(c0), 0), JSON_ARRAY(c0) FROM t7 GROUP BY t7.c0;
SELECT DISTINCTROW 0, JSON_ARRAY(c0) FROM t7 GROUP BY t7.c0;'

version()
8.0.45
IFNULL(SUM(c0), 0)	JSON_ARRAY(c0)
0	[0]
0	[null]
0	JSON_ARRAY(c0)
0	[0]

version()
8.4.8
IFNULL(SUM(c0), 0)	JSON_ARRAY(c0)
0	[0]
0	[null]
0	JSON_ARRAY(c0)
0	[0]

version()
9.6.0
IFNULL(SUM(c0), 0)	JSON_ARRAY(c0)
0	[0]
0	[null]
0	JSON_ARRAY(c0)
0	[0]