Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.20, 5.3.4
-
Fix Version/s: None
-
Component/s: None
-
Labels: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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This bug has also been filed in LP as https://bugs.launchpad.net/maria/+bug/912538