pingcap / pingcap/tidb

Incorrect Query Results When OR Condition with CASE Expression Interacts with HAVING Clause

Open
#64,789 0 comments 0 reactions 0 assignees View on GitHub
affects-8.5 contribution severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

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

```sql
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 set
```

### 2. 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.
```sql
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 set
```

### 3. What did you see instead
```sql
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: e4e814fdc0afe9c3a6e5e96f129d83df802ab820
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 |

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.