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.

3 files changed, 56 insertions, 1 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 618ce7e540b..b3ca6dc0df2 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5637,4 +5637,23 @@ c
 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;
+#
+# MDEV-5293: outer join, join buffering, and order by - invalid query plan
+#
+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;
+#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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	system	NULL	NULL	NULL	NULL	1	Using temporary; 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)
+drop table t0,t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;
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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cd81b41d0ec..a1f125b11f0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10284,7 +10284,23 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
                               join->group_list ?
 			       join->join_tab+join->const_tables :
                                join->get_sort_by_join_tab();
-     if (sort_by_tab)
+      /*
+        It could be that sort_by_tab==NULL, and the plan is to use filesort()
+        on the first table.
+      */
+      if (join->order)
+      {
+        join->simple_order= 0;
+        join->need_tmp= 1;
+      }
+
+      if (join->group && !join->group_optimized_away)
+      {
+        join->need_tmp= 1;
+        join->simple_group= 0;
+      }
+      
+      if (sort_by_tab)
       {
         join->need_tmp= 1;
         join->simple_order= join->simple_group= 0;