Incorrect Query Results When OR Condition with CASE Expression Interacts with HAVING Clause #64789
Description
Bug Report
A bug exists in TiDB where a query combining an OR condition containing a CASE expression with a HAVING clause returns incorrect results. Specifically, when the WHERE clause contains true OR (CASE ... END), it causes the HAVING clause to be improperly evaluated.
1. Minimal reproduce step
CREATE TABLE t0(c0 TEXT);
CREATE TABLE t1(c0 TEXT);
INSERT INTO t0 VALUES ('s\n');
INSERT INTO t1 VALUES ('s ');
SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 WHERE true OR (CASE false WHEN true THEN FIND_IN_SET(t1.c0, t1.c0) ELSE t1.c0 END) HAVING t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| s
| s |
+------+------+
SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 WHERE true HAVING t0.c0 <= t1.c0;
empty set2. What did you expect to see?
Both queries should return an empty set because the JOIN condition t0.c0 > t1.c0 and HAVING condition t0.c0 <= t1.c0 are contradictory.
SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 WHERE true OR (CASE false WHEN true THEN FIND_IN_SET(t1.c0, t1.c0) ELSE t1.c0 END) HAVING t0.c0 <= t1.c0;
empty set3. What did you see instead
SELECT * FROM t0 JOIN t1 ON t0.c0 > t1.c0 WHERE true OR (CASE false WHEN true THEN FIND_IN_SET(t1.c0, t1.c0) ELSE t1.c0 END) HAVING t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| s
| s |
+------+------+4. What is your TiDB version?
| Release Version: v8.5.4
Edition: Community
Git Commit Hash: e4e814f
Git Branch: HEAD
UTC Build Time: 2025-11-26 15:53:39
GoVersion: go1.23.12
Race Enabled: false
Check Table Before Drop: false
Store: tikv |