Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.20, 5.3.4
-
None
-
None
-
None
Description
The following query
SELECT * FROM t1, t2
|
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );
|
on test data returns 1 row instead of expected 4 rows.
t2 is an InnoDB table with 1 row.
bzr version-info
|
revision-id: igor@askmonty.org-20120103040636-nc6o55vsxqadd1n0
|
date: 2012-01-02 20:06:36 -0800
|
build-date: 2012-01-06 02:26:41 +0400
|
revno: 3376
|
branch-nick: maria-5.3
|
Reproducible on 5.5, but there the scenario additionally requires join_cache_level=0, as otherwise it hits a plan with flat BNL. On 5.3 revno 3376 it is reproducible with the default join_cache_level=2.
EXPLAIN with InnoDB table (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00
|
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where
|
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
|
1 PRIMARY t1 eq_ref a a 4 test.t2.b 1 100.00 Using index; FirstMatch(t2)
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`c` = `test`.`t2`.`c`) and (`test`.`t1`.`a` = `test`.`t2`.`b`))
|
EXPLAIN with MyISAM table (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
|
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
|
1 PRIMARY t1 const a a 4 const 1 100.00 Using index
|
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
|
Warnings:
|
Note 1003 select `test`.`t1`.`a` AS `a`,1 AS `b`,1 AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where 1
|
|
|
Minimal optimizer_switch: semijoin=on,firstmatch=on (current defaults)
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:
CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
|
|
# t2 needs to be InnoDB
|
CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (1,1);
|
|
|
SELECT * FROM t1, t2
|
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );
|
Expected result:
a b c
|
1 1 1
|
2 1 1
|
3 1 1
|
4 1 1
|
Actual result:
a b c
|
1 1 1
|