The following query:

SELECT * FROM t3
WHERE t3.a IN (
SELECT t5.a FROM t2, t4, t5 WHERE ( t2.c = t5.a ) AND ( t2.b = t5.b )
);

returns 45 rows when executed with LooseScan, even though t3 contains just 15 rows. In addition, the query plan always contains FirstMatch regardless of whether the firstmatch switch is on or off.

Explain:

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

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

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

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=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-23 15:51:47 +0300
build-date: 2011-08-26 16:50:13 +0300
revno: 3166
branch-nick: maria-5.3

test case:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( b int, c int, KEY (b)) ;
INSERT INTO t2 VALUES (1,0),(1,0),(9,0),(1,0),(5,0);

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( a int);
INSERT INTO t3 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

DROP TABLE IF EXISTS t4;
CREATE TABLE t4 ( a int);
INSERT INTO t4 VALUES (0),(0),(0);

DROP TABLE IF EXISTS t5;
CREATE TABLE t5 ( b int, a int , KEY (a,b)) ;
INSERT INTO t5 VALUES (7,0),(9,0);

SELECT * FROM t3
WHERE t3.a IN (
SELECT t5.a FROM t2, t4, t5 WHERE ( t2.c = t5.a ) AND ( t2.b = t5.b )
);