Description:
A join whose condition contains both "a.c = b.c" and "a.c <=> b.c" returns a row pairing
NULL with NULL when the inner table is read by an index lookup. NULL = NULL is not true,
so the conjunction is not true and no row should be returned.
With IGNORE INDEX the result is correct (empty). EXPLAIN FORMAT=TREE shows that the "="
condition is used only as the index lookup key, and the remaining filter is
"(a.c <=> a.c)", so nothing rejects the NULL key.
The same problem appears when the outer side is a materialized derived table (lookup on
its automatic key), so a user-defined index is not required.
How to repeat:
CREATE TABLE t(c INT, KEY(c));
INSERT INTO t VALUES (NULL);
SELECT * FROM t a JOIN t b ON a.c = b.c;
-- empty (correct)
SELECT * FROM t a JOIN t b ON a.c = b.c AND a.c <=> b.c;
-- expected: empty
-- actual: NULL NULL
SELECT * FROM t a IGNORE INDEX (c) JOIN t b IGNORE INDEX (c) ON a.c = b.c AND a.c <=> b.c;
-- empty (correct)
EXPLAIN FORMAT=TREE SELECT * FROM t a JOIN t b ON a.c = b.c AND a.c <=> b.c;
-- -> Nested loop inner join
-- -> Filter: (a.c <=> a.c)
-- -> Covering index scan on a using c
-- -> Covering index lookup on b using c (c = a.c)
