Description:
mysql> SELECT t0.c0, t0.c1 FROM t0 WHERE (t0.c0) NOT IN (COALESCE(NULL, 0.5465051074331168));
+------+-------------+
| c0 | c1 |
+------+-------------+
| 1 | 0.758446682 |
+------+-------------+
1 row in set (0.000 sec)
mysql> SELECT t0.c0, t0.c1 FROM t0 WHERE (NOT ((t0.c0) NOT IN (COALESCE(NULL, 0.5465051074331168))));
+------+-------------+
| c0 | c1 |
+------+-------------+
| 1 | 0.758446682 |
+------+-------------+
1 row in set (0.000 sec)
How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
`c0` tinyint DEFAULT NULL COMMENT 'asdf',
`c1` decimal(9,9) NOT NULL COMMENT 'asdf',
PRIMARY KEY (`c1`),
KEY `i0` (`c1`) USING BTREE,
KEY `i1` (`c0`)
);
INSERT INTO `t0` VALUES(1,0.758446682);
SELECT t0.c0, t0.c1 FROM t0 WHERE (t0.c0) NOT IN (COALESCE(NULL, 0.5465051074331168));
SELECT t0.c0, t0.c1 FROM t0 WHERE (NOT ((t0.c0) NOT IN (COALESCE(NULL, 0.5465051074331168))));
Suggested fix:
After removing the COALESCE function, the result becomes correct — so it’s probably the issue, right?
