MDEV-10859: Wrong result of aggregate window function in query with HAVING and no ORDER BY

Window functions need to be computed after applying the HAVING clause.
An optimization that we have for regular, non-window function, cases is
to apply having only during sending of the rows to the client. This
allows rows that should be filtered from the temporary table used to
store aggregation results to be stored there.

This behaviour is undesireable for window functions, as we have to
compute window functions on the result-set after HAVING is applied.
Storing extra rows in the table leads to wrong values as the frame
bounds might capture those -to be filtered afterwards- rows.

1 files changed, 6 insertions, 0 deletions

diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index 37095ad78ca..2bdac89f293 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -2840,6 +2840,12 @@ bool Window_funcs_computation::setup(THD *thd,
   order_window_funcs_by_window_specs(window_funcs);
 
   SQL_SELECT *sel= NULL;
+  /*
+     If the tmp table is filtered during sorting
+     (ex: SELECT with HAVING && ORDER BY), we must make sure to keep the
+     filtering conditions when we perform sorting for window function
+     computation.
+  */
   if (tab->filesort && tab->filesort->select)
   {
     sel= tab->filesort->select;