Description:
When using NULLIF in a WHERE clause, MySQL incorrectly includes a row in the result set even though the condition should evaluate to FALSE.
The expected behavior is that if NULLIF(-33.26, ref_1.c1) IS NULL evaluates to FALSE, the row should be filtered out. However, MySQL still returns the row, which seems to be incorrect.
How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t1` (
`c1` double
);
INSERT INTO `t1` VALUES (0);
query1:
select
((nullif(-33.26, ref_1.c1)) is null) as w1
from
t1 as ref_1;
Result:
+----+
| w1 |
+----+
| 0 |
+----+
1 row in set (0.00 sec)
This correctly shows that NULLIF(-33.26, 0) IS NULL evaluates to FALSE (0).
query2:
select
((nullif(-33.26, ref_1.c1)) is null) as w1
from
t1 as ref_1
where ((nullif(-33.26, ref_1.c1)) is null);
Expected Result:
No rows should be returned because the condition evaluates to FALSE.
Actual Result:
+----+
| w1 |
+----+
| 0 |
+----+
1 row in set (0.00 sec)
This result is incorrect because the row should have been filtered out due to the WHERE condition evaluating to FALSE.
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
