More rows returned with outer_join_with_cache=on and join_cache_level=6 in maria-5.3-mwl128
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug 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
*******
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*******
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)
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)
SET optimizer_
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;
Related branches
Changed in maria: | |
assignee: | nobody → Igor Babaev (igorb-seattle) |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |