Fixed bug mdev-11867.

If a window function with aggregation is over the result
set of a grouping query then the argument of the aggregate
function from the window function is allowed to be an
aggregate function itself.

5 files changed, 67 insertions, 0 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 0a003b0fe28..0606737c3ef 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -2432,3 +2432,31 @@ id	sum(a) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
 8	2100
 9	9200
 DROP TABLE t1;
+#
+# MDEV-11867: window function with aggregation
+#             over the result of grouping
+#
+create table t1 (
+username  varchar(32),
+amount int
+);
+insert into t1 values
+('user1',1),
+('user1',5),
+('user1',3),
+('user2',10),
+('user2',20),
+('user2',30);
+select username, sum(amount) as s, avg(sum(amount)) over (order by s desc)
+from t1
+group by username;
+username	s	avg(sum(amount)) over (order by s desc)
+user1	9	34.5000
+user2	60	60.0000
+select username, sum(amount), avg(sum(amount)) over (order by sum(amount) desc)
+from t1
+group by username;
+username	sum(amount)	avg(sum(amount)) over (order by sum(amount) desc)
+user1	9	34.5000
+user2	60	60.0000
+drop table t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 00fbec7d88d..6444c4df6a4 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1463,3 +1463,32 @@ FROM t1;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-11867: window function with aggregation
+--echo #             over the result of grouping
+--echo #
+
+create table t1 (
+  username  varchar(32),
+  amount int
+);
+
+insert into t1 values
+('user1',1),
+('user1',5),
+('user1',3),
+('user2',10),
+('user2',20),
+('user2',30);
+
+select username, sum(amount) as s, avg(sum(amount)) over (order by s desc)
+  from t1
+group by username;
+
+select username, sum(amount), avg(sum(amount)) over (order by sum(amount) desc)
+  from t1
+group by username;
+
+drop table t1;
+
+
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 51a6c2bd3eb..23f8638b724 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -151,6 +151,8 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
                                curr_sel->name_visibility_map);
   bool invalid= FALSE;
   DBUG_ASSERT(curr_sel->name_visibility_map); // should be set already
+  if (window_func_sum_expr_flag)
+    return false;
   /*  
     The value of max_arg_level is updated if an argument of the set function
     contains a column reference resolved  against a subquery whose level is
@@ -460,6 +462,7 @@ void Item_sum::mark_as_sum_func()
   const_item_cache= false;
   with_sum_func= 1;
   with_field= 0;
+  window_func_sum_expr_flag= false;
 }
 
 
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 84049814ef9..940726b5d8e 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -340,6 +340,9 @@ private:
   */
   bool with_distinct;
 
+  /* TRUE if this is aggregate function of a window function */
+  bool window_func_sum_expr_flag;
+
 public:
 
   bool has_force_copy_fields() const { return force_copy_fields; }
@@ -551,6 +554,7 @@ public:
   virtual void cleanup();
   bool check_vcol_func_processor(void *arg);
   virtual void setup_window_func(THD *thd, Window_spec *window_spec) {}
+  void mark_as_window_func_sum_expr() { window_func_sum_expr_flag= true; }
 };
 
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0cd2c563319..12423e71f1f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10464,6 +10464,9 @@ window_func:
           simple_window_func
         |
           sum_expr
+          {
+            ((Item_sum *) $1)->mark_as_window_func_sum_expr();
+          }
         ;
 
 simple_window_func: