MySQL Bugs: #121490: Wrapping a correlated scalar subquery in CASE changes its result when the outer aggregate has no matching rows

Bug #121490	Wrapping a correlated scalar subquery in CASE changes its result when the outer aggregate has no matching rows
Submitted:	12 Oct 16:39	Modified:	14 Oct 8:11
Reporter:	jinxin gui	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	26.7.0, 26.10.0-er	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Aggregate Function, subquery

Description:
Wrapping a correlated COUNT(*) subquery in CASE WHEN TRUE THEN ... ELSE ... END changes its result when the outer aggregate query has no matching rows. The direct subquery returns NULL, while the CASE expression returns 0.

Both branches of the CASE expression are identical, so the two queries should return the same result. The difference reproduces with one table and one row.

How to repeat:
Run the following in an empty schema:

CREATE TABLE t (a INT, b INT, UNIQUE (a), INDEX (b));
INSERT INTO t VALUES (1, 1);

SELECT MAX(b) AS x, (SELECT COUNT(*) FROM t AS s WHERE s.b <= t.b) AS y FROM t WHERE a = 230;

SELECT MAX(b) AS x, CASE WHEN TRUE THEN (SELECT COUNT(*) FROM t AS s WHERE s.b <= t.b) ELSE (SELECT COUNT(*) FROM t AS s WHERE s.b <= t.b) END AS y FROM t WHERE a = 230;

Actual result:

The first query returns (NULL, NULL). The second query returns (NULL, 0).

Expected result:

Both queries should return the same (x, y) row. The CASE condition is always true, and both branches contain the same subquery as the first query.

Thank you for the bug report.
Verified as described.