Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.5, 5.3.12, 5.5.33a
-
None
-
None
Description
A combination of outer join, join buffering, and order by causes invalid query plans to be generated.
Test dataset:
create table t0 (a int primary key) engine=myisam;
|
insert into t0 values (1);
|
|
|
create table t1(a int) engine=myisam;
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
alter table t1 add b int;
|
|
|
create table t2 like t1;
|
insert into t2 select * from t1;
|
Query and EXPLAIN:
explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE t0 system NULL NULL NULL NULL 1 Using filesort
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
|
The query plan uses both "Using filesort" and "Using join buffer". It will produce the results in the wrong order.
I'll elaborate why:
- The query has ORDER BY and so must produce rows in order.
"Using filesort" means that the first table (t1 in the example) is read by filesort(), which produces the sorted sequence, which is then joined to table t2. - However, table t2 uses join buffer, which will break the ordering created by filesort.
