Wrong result with semijoin + loosescan + comma join

[Philip Stoev]

The following query

SELECT * FROM t3 WHERE (f12) IN ( SELECT alias2.f12 FROM t1 AS alias1 , t2 AS alias2 , t1 WHERE alias1.f13 = 24 );

returns more rows than are in t3 when executed with semijoin + loosescan.

explain:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias1 const PRIMARY PRIMARY 4 const 1 100.00 Using index
1 PRIMARY alias2 index f12 f12 7 NULL 1 100.00 Using index; LooseScan
1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 100.00 Using index; FirstMatch(alias2); Using join buffer (flat, BNL join)
1 PRIMARY t3 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (flat, BNL join)

explain extended:

select `test`.`t3`.`f12` AS `f12` from `test`.`t1` `alias1` semi join (`test`.`t2` `alias2` join `test`.`t1`) join `test`.`t3` where ((`test`.`t3`.`f12` = `test`.`alias2`.`f12`))

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-09-24 14:45:49 +0200
build-date: 2011-09-25 11:24:23 +0300
revno: 3194
branch-nick: maria-5.3

test case:

--source include/have_innodb.inc
CREATE TABLE t1 ( f13 int(11) NOT NULL , PRIMARY KEY (f13)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (16),(24);

CREATE TABLE t2 ( f14 int(11) NOT NULL , f12 varchar(1) NOT NULL , KEY (f12,f14)) ENGINE=InnoDB;
INSERT INTO t2 VALUES (6,'y');

CREATE TABLE t3 ( f12 varchar(1) NOT NULL ) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y');

SET SESSION optimizer_switch='semijoin=ON,loosescan=ON';
SELECT * FROM t3 WHERE (f12) IN ( SELECT alias2.f12 FROM t1 AS alias1 , t2 AS alias2 , t1 WHERE alias1.f13 = 24 );

returns "y", "y". Expected just a single "y".

[Sergey Petrunia (sergefp)]

It seems, the problem occurs when LooseScan strategy is used together with join buffering. As far as I remember, loosescan didn't work with join buffering, so it is odd that join buffering is used here.

[Sergey Petrunia (sergefp)]

Presence of constant table is also essential.