Bug #121529 CASE WHEN TRUE changes the result of a DATETIME comparison with subquery

• Submitted: 16 Oct 7:40
• Reporter: jinxin gui
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.0, 26.10.0-er
• OS: Linux
• CPU Architecture: Any
• Tags: case ... when, datetime, INDEX

[16 Oct 7:40] jinxin gui

Description:
An indexed DATETIME column is compared with a numeric expression containing a scalar COUNT(*) subquery. The direct predicate returns one row. Wrapping the identical predicate in both branches of CASE WHEN TRUE returns two rows. The CASE expression should not change which rows satisfy the predicate.

How to repeat:
Run in an empty schema:
CREATE TABLE t (a DATETIME, INDEX (a));
CREATE TABLE c (x INT);

INSERT INTO t VALUES ('2009-09-22 12:00:00'), ('2009-09-23 12:00:00');
INSERT INTO c VALUES (1), (2), (3);

SELECT a FROM t WHERE a >= CAST((SELECT COUNT(*) FROM c) AS SIGNED) + 20090920;

SELECT a FROM t WHERE CASE WHEN TRUE THEN a >= CAST((SELECT COUNT(*) FROM c) AS SIGNED) + 20090920 ELSE a >= CAST((SELECT COUNT(*) FROM c) AS SIGNED) + 20090920 END;

Actual result:
The first query returns only 2009-09-23 12:00:00. The second returns both 2009-09-22 12:00:00 and 2009-09-23 12:00:00.
Expected result:
Both queries should return two rows. COUNT(*) is 3, so the numeric expression is 20090923. MySQL documents that an integer value produced by a subquery is compared with a DATETIME value numerically rather than converted to a temporal constant. Under that comparison, both rows qualify.