Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Somewhat similar to bug 707827 , but shows up even with the default value for join_buffer_size. The query returns rows for which the ON condition does not match.
Query:
SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 FORCE KEY (f10) ON t1.f3 AND t2.f10 = t1.f11 ORDER BY t1.f1;
(STRAIGHT_JOIN and FORCE KEY are only in order to fix the execution plan)
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where; Using temporary; Using filesort
1 SIMPLE t2 ref f10 f10 4 test.t1.f11 2 Using join buffer (flat, BNLH join)
Test case:
--source include/have_innodb.inc
SET SESSION join_cache_level = 7;
SET SESSION optimizer_switch = 'join_cache_bka=off';
CREATE TABLE t2 ( f2 int(11), f10 varchar(1), KEY (f10) ) ;
INSERT IGNORE INTO t2 VALUES (NULL,NULL);
CREATE TABLE t1 ( f1 int(11), f3 int(11), f11 varchar(1) ) ENGINE=InnoDB;
INSERT IGNORE INTO t1 VALUES (16,3,'d');
SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 FORCE KEY (f10) ON t1.f3 AND t2.f10 = t1.f11 ORDER BY t1.f1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 707914
Re: Wrong result with join_cache_level=7 , (flat, BNLH join)
This bug is a duplicate of bug 707827. The fact the test case for this bug does not change the value
for join_buffer_size does not matter as the optimizer employs a buffer of a most suitable size and
in this case it's small.