Wrong result with innner join, LooseScan, two-column IN() predicate

[Philip Stoev]

The following query:

SELECT *
FROM t1, t2
WHERE (t2.a , t1.b) IN (
 SELECT a , b
 FROM t3
);

returns the matching row twice:

+------+---+
| b | a |
+------+---+
| 5 | 6 |
| 5 | 6 |
+------+---+

whereas the correct result is:

+------+---+
| b | a |
+------+---+
| 5 | 6 |
+------+---+

explain:
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | t3 | ALL | b | NULL | NULL | NULL | 5 | Using where; LooseScan |
| 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.a | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------+

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 17:20:27 +0300
revno: 3166
branch-nick: maria-5.3

test case:

CREATE TABLE t1 (b int) ;
INSERT INTO t1 VALUES (1),(5);

CREATE TABLE t2 (a int, PRIMARY KEY (a)) ;
INSERT INTO t2 VALUES (6),(10);

CREATE TABLE t3 (a int, b int, KEY (b)) ;
INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5);

SET SESSION optimizer_switch='loosescan=on';

SELECT *
FROM t1, t2
WHERE (t2.a , t1.b) IN (
 SELECT a , b
 FROM t3
);

[Sergey Petrunia (sergefp)]

The query plan of

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | t3 | ALL | b | NULL | NULL | NULL | 5 | Using where; LooseScan |
| 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.a | 1 | Using index |

is invalid, as LooseScan cannot be used together with "ALL" access method. LooseScan relies on table access method to produce duplicates grouped together, which is possible when access methods produce records in certain order. "ALL" is a full table scan, which does not guarantee any particular order.