Details
Major
Description
The following test case does not return any rows when it's executed with join_cache_level > 2. The expected result is 1 row.
Please note that the seemingly unrelated EXPLAIN before the query is important, somehow it affects execution of the query in question (execution plan is different).
Test case:
SET join_cache_level=3; CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(7); CREATE TABLE t2 (i2 INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (7),(5); CREATE TABLE t3 (i3 INT) ENGINE=MyISAM; INSERT INTO t3 VALUES (7),(2); EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON (i2 = i1); SELECT * FROM t1 WHERE i1 IN ( SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 0 );
Expected result (actual result is empty):
i1 7
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 2 100.00 Using where; Using join buffer (flat, BNLH join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 2 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t3`.`i3` = `test`.`t2`.`i2`))
1
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
This is a serious issue with materialized semi-join.
I'm not sure it can't be reproduced with join_cache_level=1.
A fix for the bug was sent for a review to Sergey Petrunia on 2013-02-24.
The patch for this bug was pushed into the 5.3 tree on 2013-02-24 (rev 3628).
Later, on 2013-02-28, two commits fixed compiler problems on some platforms
caused by the template introduced by this patch (rev 3629 and rev 3632).
I've set priority to Minor because join_cache_level>2 is not a default value. Please feel free to adjust if needed.