t2.b = t1.a AND t1.a <=> t2.b matches NULL with NULL when t2.b has a key; the ref access is built from <=>

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (NULL), (1);

CREATE TABLE t2 (b INT, KEY (b));

INSERT INTO t2 VALUES (NULL), (NULL), (1);

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 JOIN t2 ON t2.b = t1.a WHERE t1.a <=> t2.b, c, 1)

if ($c != 1)

{

  --die COUNT(*) for t2.b = t1.a AND t1.a <=> t2.b is $c and not 1

}

DROP TABLE t1, t2;

Leads to:

mysqltest: At line 8: COUNT(*) for t2.b = t1.a AND t1.a <=> t2.b is 3 and not 1

Without the key (IGNORE INDEX (b)), the result is 1. t2.b = t1.a alone gives 1, and t2.b <=> t1.a alone gives 3. ON t2.b = t1.a AND t2.b <=> t1.a, ON t2.b <=> t1.a WHERE t1.a = t2.b, a LEFT JOIN, a key on two columns, DATETIME columns and a prepared statement give the same wrong count. Optimized builds give the same result.

+--------+----------+--------+

| MySQL  | COUNT(*) | Engine |

+--------+----------+--------+

| 5.5.62 | 1        | MyISAM |

| 5.6.51 | 3        | MyISAM |

| 5.7.44 | 3        | InnoDB |

| 8.0.36 | 3        | InnoDB |

| 9.1.0  | 3        | InnoDB |

+--------+----------+--------+

From the code it seems that both conditions give a KEYUSE for t2.b with the value t1.a: one from = with null_rejecting set, and one from <=> without it (add_key_field() in sql/sql_select.cc). sort_keyuse() does not order these two, and create_ref_for_key() takes the first one, here the one from <=>. The ref access then does not skip a NULL value (cp_buffer_from_ref()), and finds the rows with NULL. test_if_ref() removes t2.b = t1.a from the condition, as the ref access uses the same value. EXPLAIN FORMAT=JSON shows the condition t1.a <=> t1.a on t1, and no condition on t2.