Wrong result for IN expression when the left operand is a != ANY comparison over an empty set
- 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
Assessment
This issue has not been assessed yet.