Wrong result (extra rows) with loosescan and IN subquery

[Elena Stepanova]

The following query

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

on the test data returns 47 rows if it's run with loosescan=ON, and 19 rows otherwise. The latter is correct.

bzr version-info
revision-id: <email address hidden>
date: 2012-01-22 12:54:30 -0800
build-date: 2012-01-23 06:15:25 +0400
revno: 3392
branch-nick: maria-5.3

Also reproducible on 5.5.

EXPLAIN with loosescan=ON (wrong result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
1 PRIMARY t2 ALL c NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00 Using index; LooseScan
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`d` AS `d` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`d` = `test`.`t2`.`d`) and (`test`.`t1`.`b` = `test`.`t2`.`c`) and (`test`.`t1`.`b` = `test`.`t2`.`c`))

EXPLAIN with loosescan=OFF (correct result):

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
1 PRIMARY t2 ALL c NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref b b 5 test.t2.c 2 100.00 Using index; Start temporary
1 PRIMARY t1 ALL b NULL NULL NULL 10 80.00 Using where; End temporary; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`d` AS `d` from `test`.`t2` semi join (`test`.`t1`) join `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`d` = `test`.`t2`.`d`) and (`test`.`t1`.`b` = `test`.`t2`.`c`) and (`test`.`t1`.`b` = `test`.`t2`.`c`))

Minimal optimizer_switch: semijoin=on,loosescan=on
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:

SET optimizer_switch = 'semijoin=on,loosescan=on';

CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
INSERT INTO t1 VALUES
  (1,2),(2,1),(3,3),(4,2),(5,5),
  (6,3),(7,1),(8,4),(9,3),(10,2);

CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
INSERT INTO t2 VALUES
  (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);

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