revision-id: 16a2b009ec7554dd1f76eee95e3e8124bd886607 (mariadb-10.2.31-281-g16a2b009ec7) parent(s): 667bb528500a68cff26b442c125ba0e4d109c9b3 author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-22 18:24:08 +0530 message: MDEV-15313: Error with aggregate function inside of last_value() window function The issue here is for window functions like LAG/LAST_VALUE/FIRST_VALUE did not allow aggregate functions like SUM, COUNT as their arguments. The fix ensures that aggregate functions will be allowed as arguments to all window functions. --- mysql-test/r/win.result | 13 +++++++++++++ mysql-test/t/win.test | 10 ++++++++++ sql/sql_yacc.yy | 3 +++ 3 files changed, 26 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index c73f9f8ce6b..b4bd29496ed 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3788,5 +3788,18 @@ row_number() OVER() 3 DROP TABLE t1; # +# MDEV-15313: Error with aggregate function inside of last_value() window function +# +CREATE TABLE t1(a int,b int); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +SELECT a, last_value(count(a)) OVER (partition BY a) FROM t1 GROUP BY a; +a last_value(count(a)) OVER (partition BY a) +1 6 +2 4 +3 2 +4 2 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 37c107633d9..5b5eb3c0cf8 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2459,6 +2459,16 @@ ANALYZE FORMAT=JSON SELECT row_number() OVER() FROM t1; SELECT row_number() OVER() FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-15313: Error with aggregate function inside of last_value() window function +--echo # + +CREATE TABLE t1(a int,b int); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +SELECT a, last_value(count(a)) OVER (partition BY a) FROM t1 GROUP BY a; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 386c86cb3e2..71ff052fabc 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10495,6 +10495,9 @@ window_func_expr: window_func: simple_window_func + { + ((Item_sum *) $1)->mark_as_window_func_sum_expr(); + } | sum_expr {