An index on an INET6, INET4 or UUID column changes the query result: a <=> 'x' misses the NULL row when 'x' is not a valid value

CREATE TABLE t1 (a INET6, KEY (a));

INSERT INTO t1 VALUES (NULL), ('::1'), ('::2');

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE a <=> 'x', c, 1)

if ($c != 1)

{

  --die COUNT(*) for a <=> 'x' is $c and not 1

}

DROP TABLE t1;

Leads to:

mysqltest: At line 6: COUNT(*) for a <=> 'x' is 0 and not 1

The index changes the query result. Without it (IGNORE INDEX (a)), the query finds the row with NULL, and SELECT CAST(NULL AS INET6) <=> 'x' gives 1. EXPLAIN shows "Impossible WHERE noticed after reading const tables". a <=> 'x' OR a = '::1' gives 1 and not 2. An INET4 column with b <=> '::1', a UUID column with c <=> 'x', and a prepared statement a <=> ? give the same. a = 'x', a join ON t1.a <=> t3.v, and a NOT NULL column give the right result. Optimized builds give the same result.

From the code it seems that Field_fbt::get_mm_leaf() in sql/sql_type_fixedbin.h stores 'x' in the field. The store fails, sets the field to NULL and returns 1, and for = and <=> the function then returns an impossible range. For <=> the value compares as NULL: Arg_comparator::compare_e_native() in sql/item_cmpfunc.cc gives 1 when both values fail to convert, so the rows with NULL match.