Description:
When using a WHERE clause with a condition like (col1 = col2), MySQL fails to filter the rows correctly.
Specifically, when performing a RIGHT OUTER JOIN and checking the condition (ref_1.c1) = (subq_0.c_0) where subq_0.c_0 is NULL, the result should be filtered out because NULL = ref_1.c1 should evaluate to NULL, and therefore not return rows.However, MySQL does not filter out rows as expected.
How to repeat:
Create Test Table and Insert Sample Data:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int
) ;
INSERT INTO `t1` VALUES (1);
Execute the Following Query:
select
subq_0.c_0 as c_0,
ref_1.c1 as c_1
from
((select
1 as c_0
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
from
t1 as ref_0
where false) as subq_0
right outer join t1 as ref_1
on (true))
where
((ref_1.c1) = (subq_0.c_0));
Expected Result:
Empty set (0.00 sec)
Actual Result:
+-----+------+
| c_0 | c_1 |
+-----+------+
| NULL | 1 |
+-----+------+
1 row in set (0.01 sec)
Here, NULL = 1 should evaluate to NULL, and thus the row should be filtered out. However, it still appears in the result.
Description: When using a WHERE clause with a condition like (col1 = col2), MySQL fails to filter the rows correctly. Specifically, when performing a RIGHT OUTER JOIN and checking the condition (ref_1.c1) = (subq_0.c_0) where subq_0.c_0 is NULL, the result should be filtered out because NULL = ref_1.c1 should evaluate to NULL, and therefore not return rows.However, MySQL does not filter out rows as expected. How to repeat: Create Test Table and Insert Sample Data: DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `c1` int ) ; INSERT INTO `t1` VALUES (1); Execute the Following Query: select subq_0.c_0 as c_0, ref_1.c1 as c_1 from ((select 1 as c_0 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 from t1 as ref_0 where false) as subq_0 right outer join t1 as ref_1 on (true)) where ((ref_1.c1) = (subq_0.c_0)); Expected Result: Empty set (0.00 sec) Actual Result: +-----+------+ | c_0 | c_1 | +-----+------+ | NULL | 1 | +-----+------+ 1 row in set (0.01 sec) Here, NULL = 1 should evaluate to NULL, and thus the row should be filtered out. However, it still appears in the result.