pingcap / pingcap/tidb

EXISTS / NOT EXISTS on DATE_FORMAT produces incorrect result

Open
#67,942 2 comments 0 reactions 0 assignees View on GitHub
affects-8.5 contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 severity/critical sig/planner type/bug
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

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.