MDEV-12779 Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER

Users expect window functions to produce a certain ordering of rows in
the final result set. Although the standard does not require this, we
already have the filesort result done for when we computed the window
function. If there is no ORDER BY attached to the query, just keep it
till the SELECT is completely evaluated and use that to print the
result.

Update test cases as many did not take care to guarantee a stable
result.

1 files changed, 12 insertions, 5 deletions

diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index cd09e174808..465c6ae032c 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -2751,7 +2751,7 @@ bool Window_func_runner::exec(THD *thd, TABLE *tbl, SORT_INFO *filesort_result)
 }
 
 
-bool Window_funcs_sort::exec(JOIN *join)
+bool Window_funcs_sort::exec(JOIN *join, bool keep_filesort_result)
 {
   THD *thd= join->thd;
   JOIN_TAB *join_tab= join->join_tab + join->total_join_tab_cnt();
@@ -2766,8 +2766,11 @@ bool Window_funcs_sort::exec(JOIN *join)
 
   bool is_error= runner.exec(thd, tbl, filesort_result);
 
-  delete join_tab->filesort_result;
-  join_tab->filesort_result= NULL;
+  if (!keep_filesort_result)
+  {
+    delete join_tab->filesort_result;
+    join_tab->filesort_result= NULL;
+  }
   return is_error;
 }
 
@@ -2876,14 +2879,18 @@ bool Window_funcs_computation::setup(THD *thd,
 }
 
 
-bool Window_funcs_computation::exec(JOIN *join)
+bool Window_funcs_computation::exec(JOIN *join, bool keep_last_filesort_result)
 {
   List_iterator<Window_funcs_sort> it(win_func_sorts);
   Window_funcs_sort *srt;
+  uint counter= 0; /* Count how many sorts we've executed. */
   /* Execute each sort */
   while ((srt = it++))
   {
-    if (srt->exec(join))
+    counter++;
+    bool keep_filesort_result= keep_last_filesort_result &&
+                               counter == win_func_sorts.elements;
+    if (srt->exec(join, keep_filesort_result))
       return true;
   }
   return false;