MDEV-5293: outer join, join buffering, and order by - invalid query plan

- make_join_readinfo() has the code that forces use of "Using temporary; 
  Using filesort" when join buffering is in use.
  That code didn't handle all cases, in particular it didn't hande the case 
  where ORDER BY originally has tables from multiple columns, but the 
  optimizer eventually figures out that doing filesort() on one table 
  will be sufficient.  Adjusted the code to handle that case.

1 files changed, 20 insertions, 0 deletions

diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 2d06c3e2a30..585ef677492 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3635,6 +3635,26 @@ set join_buffer_size=default;
 set optimizer_switch=@tmp_optimizer_switch;
 
 DROP table t1,t2,t3;
+set join_buffer_size= default;
+set @@optimizer_switch=@save_optimizer_switch;
+
+
+--echo #
+--echo # MDEV-5293: outer join, join buffering, and order by - invalid query plan
+--echo #
+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;
+--echo #The following must use "Using temporary; Using filesort" and not just "Using filesort":
+explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
+
+drop table t0,t1,t2;
 
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;