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.
Analysis:
The optimizer protects itself from getting "Using join buffer" together with
"Using filesort" by a convoluted mess of logic in various locations.
General approach is:
needed. If yes, it will not use "Using filesort".
== Piece #1 ==
At the end of make_join_readinfo() there is a piece of code that starts
with this comment:
If a join buffer is used to join a table the ordering by an index
for the first non-constant table cannot be employed anymore.
The code there changes "filesort on the first table" plan into a
"Using filesort; Using temporary" plan.
For our example, the change will not be done, because sort_by_tab==NULL,
which is caused by join->get_sort_by_join_tab()==NULL (which I think is
incorrect).
join->get_sort_by_join_tab()==NULL, because JOIN::sort_by_table==NULL. It is
set by this call in JOIN::optimize:
sort_by_table= get_sort_by_table(order, group_list, select_lex->leaf_tables);
Locally, it makes sense - when get_sort_by_table() is called, it sees
"ORDER BY t0.a, t1.a", the optimizer doesn't yet know that t0.a is a constant,
hence sort_by_table=NULL.
== Piece #2 ==
JOIN::optimize also has this piece of code:
// Can't use sort on head table if using join buffering
{ ... }if (full_join || hash_join)
It switches to using "Using temporary; Using filesort" when
JOIN::full_join=TRUE.
JOIN::full_join is set by get_best_combination(), it will receive a value of
true if the join plan has a table that has:
join->best_positions[tablenr].use_join_buffer==true
(the actual condition is more complex, but that's the idea).
In turn, the value of POSITION::use_join_buffer comes from best_access_path().
The code in best_access_path has:
best_uses_jbuf= test(!disable_jbuf && !((s->table->map &
join->outer_join)));
...
pos->use_join_buffer= best_uses_jbuf;
Note that best_uses_jbuf==FALSE when the table is an inner table of outer join.
(I guess, this is old code. Before BKA was introduced, join buffering couldn't
be used with outer joins. This is no longer the case, but the old logic is
still there)
Anyhow, if this example didn't have LEFT JOIN, Piece#2 would have taken care of
switching to "Using temporary; Using filesort". Bot with left join, it didn't.