MDEV-10879: Window Functions final ordering of result setbb-10.2-vicentiu3

Window functions now force an ordering to the result set if none is
specified through order by. The ordering that will be returned is the final ordering
that is used during window function computation. For multiple window functions,
it should generally be the most specific sort ordering for the final
window function in the select list.

1 files changed, 18 insertions, 1 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 2c7ccbf3e2b..2f784a1212c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -26349,9 +26349,20 @@ AGGR_OP::end_send()
 
   // Update ref array
   join_tab->join->set_items_ref_array(*join_tab->ref_array);
+
+  /* During window function computation, if no order by is specified explicitly
+     for the select, the final result will end up with a non-deterministic
+     order of rows. This can become confusing especially when you expect
+
+     row_number() to return monotonically increasing values. To address this
+     issue, we save the final ordering from the temporary table and use
+     that when presenting the results.
+  */
+  bool save_final_window_funcs_ordering= join_tab->filesort ? false : true;
   if (join_tab->window_funcs_step)
   {
-    if (join_tab->window_funcs_step->exec(join))
+    if (join_tab->window_funcs_step->exec(join,
+                                          save_final_window_funcs_ordering))
       return NESTED_LOOP_ERROR;
   }
 
@@ -26405,6 +26416,12 @@ AGGR_OP::end_send()
     }
   }
 
+  if (save_final_window_funcs_ordering)
+  {
+    delete join_tab->filesort_result;
+    join_tab->filesort_result= NULL;
+  }
+
   // Finish rnd scn after sending records
   if (join_tab->table->file->inited)
     join_tab->table->file->ha_rnd_end();