pingcap / pingcap/tidb

Wrong result for IN expression when the left operand is a != ANY comparison over an empty set

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

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```
CREATE TABLE t7 (c1 INT);
INSERT t7 () VALUES (1);
SELECT * FROM t7 WHERE ((((EXISTS(SELECT 0)) != ANY (SELECT 0 WHERE 0)) IN (0)));
-- empty set
SELECT (EXISTS(SELECT 0)) != ANY (SELECT 0 WHERE 0);
-- 0
SELECT SUM(count) FROM (SELECT (((((EXISTS(SELECT 0)) != ANY (SELECT 0 WHERE 0)) IN (0)))) IS TRUE AS count FROM t7) AS ta_norec;
-- 1

```

### 2. What did you expect to see? (Required)
The first query should return one row with the value 1.
The second query should return 1.
Reasoning:
(EXISTS(SELECT 0)) evaluates to TRUE (or 1).
(SELECT 0 WHERE 0) returns an empty set.
According to RESULT, != ANY () evaluates to FALSE (or 0).
Therefore, the expression simplifies to (0 IN (0)), which is TRUE.
The WHERE clause should be satisfied, and the single row from table t7 should be returned.

### 3. What did you see instead (Required)
mysql> SELECT * FROM t7 WHERE ((((EXISTS(SELECT 0)) != ANY (SELECT 0 WHERE 0)) IN (0)));
Empty set (0.00 sec)

mysql> SELECT SUM(count) FROM (SELECT (((((EXISTS(SELECT 0)) != ANY (SELECT 0 WHERE 0)) IN (0)))) IS TRUE AS count FROM t7) AS ta_norec;
+------------+
| SUM(count) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)

### 4. What is your TiDB version? (Required)

Release Version: v8.5.0-20251010-ec6b882
Edition: Community
Git Commit Hash: https://github.com/pingcap/tidb/commit/ec6b882d188f77c199907ba26843a872b8c5fd6d
Git Branch: heads/refs/tags/v8.5.0-20251010-ec6b882
UTC Build Time: 2025-10-10 10:21:51
GoVersion: go1.23.3
Race Enabled: false
Check Table Before Drop: false
Store: unistore

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.