Wrong result with EXISTS and NULLs in maria-5.3 , maria-5.3-mwl89 regardless of @@optimizer_switch

[Philip Stoev]

Repeatable in maria-5.3 and maria-5.3-mwl90 regardless of the switch. Not reproducible on maria-5.2 and mysql-5.5

The following query:

SELECT * FROM t1 WHERE EXISTS ( SELECT f1 FROM t2 WHERE t1.f1 = t2.f1 );

Returns rows even though the tables contain only NULL values and for no rows the equality t1.f1 = t2.f1 is TRUE. The problem goes away if the tables have 1 row or if IGNORE INDEX is used to prevent the index on f1 from being used.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL f1 4 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref f1 f1 4 test.t1.f1 2 Using index

test case:

CREATE TABLE t1 ( f1 varchar(1) , KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (NULL),(NULL);

CREATE TABLE t2 ( f1 varchar(1) , KEY (f1)) ;
INSERT IGNORE INTO t2 VALUES (NULL),(NULL);

SELECT *
FROM t1
WHERE EXISTS (
        SELECT f1
        FROM t2
        WHERE t1.f1 = t2.f1
);