Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.1, 5.5.29, 5.3.12
-
None
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