Wrong result (missing rows) with semijoin=on, firstmatch=on, IN subquery, constant InnoDB table

[Elena Stepanova]

The following query

SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );

on test data returns 1 row instead of expected 4 rows.
t2 is an InnoDB table with 1 row.

bzr version-info
revision-id: <email address hidden>
date: 2012-01-02 20:06:36 -0800
build-date: 2012-01-06 02:26:41 +0400
revno: 3376
branch-nick: maria-5.3

Reproducible on 5.5, but there the scenario additionally requires join_cache_level=0, as otherwise it hits a plan with flat BNL. On 5.3 revno 3376 it is reproducible with the default join_cache_level=2.

EXPLAIN with InnoDB table (wrong result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
1 PRIMARY t1 eq_ref a a 4 test.t2.b 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`c` = `test`.`t2`.`c`) and (`test`.`t1`.`a` = `test`.`t2`.`b`))

EXPLAIN with MyISAM table (correct result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t1 const a a 4 const 1 100.00 Using index
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,1 AS `b`,1 AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where 1

Minimal optimizer_switch: semijoin=on,firstmatch=on (current defaults)
Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
INSERT INTO t1 VALUES (1),(2),(3),(4);

# t2 needs to be InnoDB
CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,1);

SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );

End of test case

Expected result:
a b c
1 1 1
2 1 1
3 1 1
4 1 1

Actual result:
a b c
1 1 1

[Elena Stepanova (elenst)]

This bug has also been filed in JIRA as MDEV-72

[Sergey Petrunia (sergefp)]

For the record: there is no constant innodb table here. constant tables are marked as such by EXPLAIN.