Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query
SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
returns one extra NULL as compared to plan without a join buffer.
explain:
mysql> EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9\G
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: - 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where; Using join buffer (flat, BNL join)
2 rows in set (0.00 sec)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Test case:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
f7 int) ;
INSERT INTO t1 VALUES ('9');
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
pk int,
f7 int) ;
INSERT INTO t2 VALUES ('9',NULL),('1',NULL);
SET optimizer_switch='outer_join_with_cache=on';
SET SESSION join_cache_level = 6;
SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
SET SESSION join_cache_level = 0;
SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
EXPLAIN SELECT t2.f7 FROM t2 LEFT JOIN t1 ON t2.f7 WHERE t1.f7 OR t2.pk < 9;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 674423