Description:
Hi, MySQL developers. I found a bug.
When a WHERE clause contains mutually exclusive conditions together with functions like COALESCE, IFNULL, LEAST, or GREATEST, MySQL incorrectly returns rows that do not satisfy the equality condition.
How to repeat:
CREATE TABLE t0(c0 DECIMAL);
CREATE TABLE t1(c0 DECIMAL);
INSERT INTO t0(c0) VALUES(0);
INSERT INTO t1(c0) VALUES(0);
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND NOT COALESCE(t0.c0); -- {0|0}, expect {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND NOT IFNULL(t0.c0, 1); -- {0|0}, expect {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND LEAST(t0.c0, 1) = 0; -- {0|0}, expect {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND GREATEST(t0.c0, 0) = 0; -- {0|0}, expect {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND NOT COALESCE(t0.c0); -- {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND NOT IFNULL(t0.c0, 1); -- {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND LEAST(t0.c0, 1) = 0; -- {}
SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND GREATEST(t0.c0, 0) = 0; -- {}
Description: Hi, MySQL developers. I found a bug. When a WHERE clause contains mutually exclusive conditions together with functions like COALESCE, IFNULL, LEAST, or GREATEST, MySQL incorrectly returns rows that do not satisfy the equality condition. How to repeat: CREATE TABLE t0(c0 DECIMAL); CREATE TABLE t1(c0 DECIMAL); INSERT INTO t0(c0) VALUES(0); INSERT INTO t1(c0) VALUES(0); SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND NOT COALESCE(t0.c0); -- {0|0}, expect {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND NOT IFNULL(t0.c0, 1); -- {0|0}, expect {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND LEAST(t0.c0, 1) = 0; -- {0|0}, expect {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 WHERE t0.c0 = 0.1 AND GREATEST(t0.c0, 0) = 0; -- {0|0}, expect {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND NOT COALESCE(t0.c0); -- {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND NOT IFNULL(t0.c0, 1); -- {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND LEAST(t0.c0, 1) = 0; -- {} SELECT * FROM t0 JOIN t1 ON t0.c0 != 0.1 AND t0.c0 = 0.1 AND GREATEST(t0.c0, 0) = 0; -- {}