Description:
When using the EXISTS operator with a WHERE clause, MySQL is returning incorrect results. Specifically, in a query where the EXISTS operator is combined with an OR condition, the result should be empty when no matching rows exist. However, the query is incorrectly returning a result, despite the condition logically evaluating to FALSE.
How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t1` (
`c1` int,
`c2` int
);
INSERT INTO `t1` VALUES (1,2);
Execute the Following Queries:
Query 1: Without WHERE Clause Filtering
select
((exists (
select
ref_1.c2 as c_1
from
t1 as ref_1
where ((-87) <> (subq_0.c_0)) ))
or false) as w_1
from
(t1 as ref_0
left outer join (select
45 as c_0,
ref_1.c1 as c_1
from
t1 as ref_1
) as subq_0
on (( (ref_0.c2) = (subq_0.c_1)) ))
Result:
+------+
| w_1 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
Query 2: With WHERE Clause Filtering
SELECT
((EXISTS (
SELECT
ref_1.c2 AS c_1
FROM
t1 AS ref_1
WHERE ((-87) <> (subq_0.c_0)) ))
OR FALSE) AS w_1
FROM
(t1 AS ref_0
LEFT OUTER JOIN (SELECT
45 AS c_0,
ref_1.c1 AS c_1
FROM
t1 AS ref_1
) AS subq_0
ON ((ref_0.c2) = (subq_0.c_1)) )
WHERE ((EXISTS (
SELECT
ref_1.c2 AS c_1
FROM
t1 AS ref_1
WHERE ((-87) <> (subq_0.c_0)) ))
OR FALSE);
Expected Result:
Empty set (0.00 sec)
Since the EXISTS subquery in the WHERE clause evaluates to FALSE (in Query 1), the query should return an empty set.
Actual Result:
+------+
| w_1 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql version:
github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174
version: 9.1.0
os version:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
Description: When using the EXISTS operator with a WHERE clause, MySQL is returning incorrect results. Specifically, in a query where the EXISTS operator is combined with an OR condition, the result should be empty when no matching rows exist. However, the query is incorrectly returning a result, despite the condition logically evaluating to FALSE. How to repeat: Create Test Table and Insert Sample Data: CREATE TABLE `t1` ( `c1` int, `c2` int ); INSERT INTO `t1` VALUES (1,2); Execute the Following Queries: Query 1: Without WHERE Clause Filtering select ((exists ( select ref_1.c2 as c_1 from t1 as ref_1 where ((-87) <> (subq_0.c_0)) )) or false) as w_1 from (t1 as ref_0 left outer join (select 45 as c_0, ref_1.c1 as c_1 from t1 as ref_1 ) as subq_0 on (( (ref_0.c2) = (subq_0.c_1)) )) Result: +------+ | w_1 | +------+ | 0 | +------+ 1 row in set (0.00 sec) Query 2: With WHERE Clause Filtering SELECT ((EXISTS ( SELECT ref_1.c2 AS c_1 FROM t1 AS ref_1 WHERE ((-87) <> (subq_0.c_0)) )) OR FALSE) AS w_1 FROM (t1 AS ref_0 LEFT OUTER JOIN (SELECT 45 AS c_0, ref_1.c1 AS c_1 FROM t1 AS ref_1 ) AS subq_0 ON ((ref_0.c2) = (subq_0.c_1)) ) WHERE ((EXISTS ( SELECT ref_1.c2 AS c_1 FROM t1 AS ref_1 WHERE ((-87) <> (subq_0.c_0)) )) OR FALSE); Expected Result: Empty set (0.00 sec) Since the EXISTS subquery in the WHERE clause evaluates to FALSE (in Query 1), the query should return an empty set. Actual Result: +------+ | w_1 | +------+ | 0 | +------+ 1 row in set (0.00 sec) mysql version: github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174 version: 9.1.0 os version: Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux