Bug #22781: SQL_BIG_RESULT fails to influence sort plan

 Currently SQL_BIG_RESULT is checked only at compile time.
 However, additional optimizations may take place after
 this check that change the sort method from 'filesort'
 to sorting via index. As a result the actual plan
 executed is not the one specified by the SQL_BIG_RESULT
 hint. Similarly, there is no such test when executing
 EXPLAIN, resulting in incorrect output.
 The patch corrects the problem by testing for
 SQL_BIG_RESULT both during the explain and execution
 phases.


mysql-test/r/bdb.result:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - updated sql_big_result testcase
mysql-test/r/group_by.result:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - test case with MyISAM
mysql-test/r/innodb.result:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - updated sql_big_result testcase
mysql-test/r/innodb_mysql.result:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - test case with InnoDB
mysql-test/r/myisam.result:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - updated sql_big_result testcase
mysql-test/t/group_by.test:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - test case with MyISAM
mysql-test/t/innodb_mysql.test:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - test case with InnoDB
sql/sql_select.cc:
  Bug #22781: SQL_BIG_RESULT fails to influence sort plan
   - When SQL_BIG_RESULT is specified, disable the optimization performed
  at execution/explain time that decides to use an index instead
  of filesort.

1 files changed, 10 insertions, 3 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b328d9162d5..e9c282c1d24 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1399,7 +1399,8 @@ JOIN::exec()
       simple_order= simple_group;
       skip_sort_order= 0;
     }
-    if (order &&
+    if (order && 
+        (order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
 	(const_tables == tables ||
  	 ((simple_order || skip_sort_order) &&
 	  test_if_skip_sort_order(&join_tab[const_tables], order,
@@ -11995,11 +11996,17 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   table=  tab->table;
   select= tab->select;
 
-  if (test_if_skip_sort_order(tab,order,select_limit,0))
+  /*
+    When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
+    and thus force sorting on disk.
+  */
+  if ((order != join->group_list || 
+       !(join->select_options & SELECT_BIG_RESULT)) &&
+      test_if_skip_sort_order(tab,order,select_limit,0))
     DBUG_RETURN(0);
   if (!(sortorder=make_unireg_sortorder(order,&length)))
     goto err;				/* purecov: inspected */
-  /* It's not fatal if the following alloc fails */
+
   table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
                                              MYF(MY_WME | MY_ZEROFILL));
   table->status=0;				// May be wrong if quick_select