Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
When executing simple queries with join_cache_level=4 , join_buffer_size = 164 the following simple query
SELECT alias2.f3 FROM t1 AS alias1 STRAIGHT_JOIN t2 AS alias2 FORCE KEY (f10) ON alias2.f10 = alias1.f10 ;
returned "8", even though the ON condition is FALSE for all rows. This seems to happen only for very low values of join_buffer_size. In real life, the issue was also observed without STRAIGHT_JOIN or FORCE KEY . InnoDB appears to be required for at least one of the tables.
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE alias2 ref f10 f10 4 test.alias1.f10 2 Using join buffer (flat, BNLH join)
Test case:
--source include/have_innodb.inc
CREATE TABLE t1 ( f10 varchar(1) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('o');
CREATE TABLE t2 ( f3 int(11), f10 varchar(1), KEY (f10)) ;
INSERT INTO t2 VALUES ('8',NULL);
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 164;
SELECT f3 FROM t1 STRAIGHT_JOIN t2 FORCE KEY (f10) ON t2.f10 = t1.f10 ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 707827