Description:
mysql> select a, b from t1;
+------+------+
| a | b |
+------+------+
| 1 | 0 |
| 2 | 0 |
+------+------+
2 rows in set (0.00 sec)
-- correct
mysql> select nullif( (0 != 5), a / b) from t1;
+--------------------------+
| nullif( (0 != 5), a / b) |
+--------------------------+
| 1 |
| 1 |
+--------------------------+
2 rows in set (0.00 sec)
-- correct
mysql> select nullif( (0 != 5), a / b) IS NULL from t1;
+----------------------------------+
| nullif( (0 != 5), a / b) IS NULL |
+----------------------------------+
| 0 |
| 0 |
+----------------------------------+
2 rows in set (0.00 sec)
-- correct
mysql> select * from t1 where nullif( (0 != 5), a / b);
+------+------+
| a | b |
+------+------+
| 1 | 0 |
| 2 | 0 |
+------+------+
2 rows in set (0.00 sec)
-- incorrect
mysql> select * from t1 where nullif( (0 != 5), a / b) IS NULL;
+------+------+
| a | b |
+------+------+
| 1 | 0 |
| 2 | 0 |
+------+------+
2 rows in set (0.00 sec)
How to repeat:
drop table if exists t1;
create table t1 (
a int,
b int
);
insert into t1 values (1,0), (2,0);
select nullif( (0 != 5), a / b) from t1;
select nullif( (0 != 5), a / b) IS NULL from t1;
select * from t1 where nullif( (0 != 5), a / b);
-- incorrect
select * from t1 where nullif( (0 != 5), a / b) IS NULL;
Suggested fix:
NULLIF result should be the same when the result is checked again
