EXISTS / NOT EXISTS on DATE_FORMAT produces incorrect result
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Hi, I found a bug in TiDB 8.5.6. When a query contains both `EXISTS (SELECT ... WHERE DATE_FORMAT(...) IS NULL)` and `NOT EXISTS` with the exact same condition, the condition should be a logical contradiction resulting in an empty set. However, TiDB v8.5.6 returns a row instead, likely due to incorrect optimization or evaluation of the correlated DATE_FORMAT expression.
### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE t0(c0 INTEGER);
CREATE TABLE t1(c0 INTEGER, c1 INTEGER);
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1 VALUES (0, 0);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL) AND NOT EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL);
+----+----+
| c0 | c1 |
+----+----+
| 0 | 0 |
+----+----+
SELECT DATE_FORMAT(0, 0) IS NULL;
+---------------------------+
| DATE_FORMAT(0, 0) IS NULL |
+---------------------------+
| 0 |
+---------------------------+
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE FALSE) AND NOT EXISTS (SELECT 1 FROM t0 WHERE FALSE);
Empty set (0.00 sec)
```
### 2. What did you expect to see? (Required)
```sql
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL) AND NOT EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL);
Empty set (0.00 sec)
```
### 3. What did you see instead (Required)
```sql
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL) AND NOT EXISTS (SELECT 1 FROM t0 WHERE DATE_FORMAT(t1.c0, t1.c1) IS NULL);
+----+----+
| c0 | c1 |
+----+----+
| 0 | 0 |
+----+----+
```
### 4. What is your TiDB version? (Required)
| Release Version: v8.5.6
Edition: Community
Git Commit Hash: https://github.com/pingcap/tidb/commit/ae18096e023780bb56bfce33698abec0d4640d0a
Git Branch: HEAD
UTC Build Time: 2026-04-14 07:15:36
GoVersion: go1.25.8
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
Contributor guide
Assessment
This issue has not been assessed yet.