Wrong result with 3-way inner join, LooseScan,multipart keys

[Philip Stoev]

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:

+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------------------------------------------+
| 1 | PRIMARY | t5 | index | a | a | 10 | NULL | 2 | Using where; Using index; LooseScan |
| 1 | PRIMARY | t4 | ALL | NULL | NULL | NULL | NULL | 3 | Using join buffer (flat, BNL join) |
| 1 | PRIMARY | t2 | ref | b | b | 5 | test.t5.b | 2 | Using where; FirstMatch(t5) |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+-------+---------------+------+---------+-----------+------+-------------------------------------------------+

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 )
);

[Sergey Petrunia (sergefp)]

> In addition, the query plan always contains FirstMatch regardless of whether the firstmatch switch is on or off.

This is expected, When LooseScan strategy is taking care of removing duplicates produced by a multi-table subquery, one can see EXPLAIN outputs like this:

table_x LooseScan
...
table_y FirstMatch(table_x)

[Sergey Petrunia (sergefp)]

Note that the result is correct if I do "SET join_cache_level=0".

EXPLAIN shows that table t4 is "with LooseScan's range", and it uses join buffering.