Wrong result: "t.a = e.b OR e.b <=> t.a" with e.b NULL from a const table matches rows where the NOT NULL indexed column t.a is 0

e is empty and joined with ON e.b IS NULL on a NOT NULL column, so the plan marks it "Impossible ON condition" and e.b is NULL in every result row. The condition t.a = e.b OR e.b <=> t.a is then NULL OR FALSE for every row, because t.a is never NULL. The query nevertheless returns the two rows with t.a = 0.

The warning after EXPLAIN EXTENDED shows the condition rewritten to t.a = NULL or NULL <=> t.a, and the plan reads t by ref access on key a with ref=const. It looks as if the NULL constant is stored into the key buffer of the NOT NULL column, where it becomes 0.

The result is correct (empty) with IGNORE INDEX, with only e.b <=> t.a, with only t.a = e.b, and when t.a is nullable. With a VARCHAR NOT NULL column the rows with '' are returned. MySQL 26.7.1 returns the correct empty result.

How to reproduce

CREATE TABLE t (a INT NOT NULL, KEY (a));

INSERT INTO t VALUES (0), (0), (1);

CREATE TABLE e (b INT NOT NULL PRIMARY KEY);

SELECT t.a, e.b FROM t LEFT JOIN e ON e.b IS NULL;

-- (0,NULL), (0,NULL), (1,NULL)

SELECT t.a, e.b FROM t LEFT JOIN e ON e.b IS NULL WHERE t.a = e.b OR e.b <=> t.a;

-- expected: Empty set

-- actual:   (0,NULL), (0,NULL)

SELECT t.a, e.b FROM t IGNORE INDEX (a) LEFT JOIN e ON e.b IS NULL WHERE t.a = e.b OR e.b <=> t.a;

-- Empty set   correct

SELECT t.a, e.b FROM t LEFT JOIN e ON e.b IS NULL WHERE e.b <=> t.a;

-- Empty set   correct

EXPLAIN EXTENDED SELECT t.a, e.b FROM t LEFT JOIN e ON e.b IS NULL WHERE t.a = e.b OR e.b <=> t.a;

-- e  const  PRIMARY  Impossible ON condition

-- t  ref    a        ref=const  Using index

SHOW WARNINGS;

-- ... where `t`.`a` = NULL or NULL <=> `t`.`a`