revision-id: 8cf192c034f1359ae716e3f363dc2856a85e06ff (mariadb-10.2.40-236-g8cf192c034f) parent(s): c1d7b4575e67bd0ef458457859cdf7de32b3d4f9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-02-07 17:09:04 +0300 message: MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode (Backport Varun Gupta's patch + edit the commit comment) Name resolution code produced errors for valid queries with window functions (but not for queries which used aggregate functions as window functions). Name resolution code worked incorrectly, because window function objects had is_window_func_sum_expr()=false. This was so, because mark_as_window_func_sum_expr() was only called for aggregate functions used as window functions. The fix is to call it for any window function. --- mysql-test/r/win.result | 32 ++++++++++++++++++++++ .../suite/encryption/r/tempfiles_encrypted.result | 32 ++++++++++++++++++++++ mysql-test/t/win.test | 27 ++++++++++++++++++ sql/item_windowfunc.cc | 3 ++ sql/sql_yacc.yy | 3 -- 5 files changed, 94 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index bc017ea70a3..30650f29555 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -4198,5 +4198,37 @@ drop procedure sp7; drop view v1,v2; drop table t1; # +# MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode +# +CREATE TABLE t1(a VARCHAR(10), b int); +INSERT INTO t1 VALUES +('Maths', 60),('Maths', 60), +('Maths', 70),('Maths', 55), +('Biology', 60), ('Biology', 70); +SET @save_sql_mode= @@sql_mode; +SET sql_mode = 'ONLY_FULL_GROUP_BY'; +SELECT +RANK() OVER (PARTITION BY a ORDER BY b) AS rank, +a, b FROM t1 ORDER BY a, b DESC; +rank a b +2 Biology 70 +1 Biology 60 +4 Maths 70 +2 Maths 60 +2 Maths 60 +1 Maths 55 +SET sql_mode= @save_sql_mode; +DROP TABLE t1; +CREATE TABLE t1(i int,j 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 i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i; +i LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) +1 6 +2 4 +3 2 +4 2 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index f66a5fd0b25..aaba6bb044a 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -4204,6 +4204,38 @@ drop procedure sp7; drop view v1,v2; drop table t1; # +# MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode +# +CREATE TABLE t1(a VARCHAR(10), b int); +INSERT INTO t1 VALUES +('Maths', 60),('Maths', 60), +('Maths', 70),('Maths', 55), +('Biology', 60), ('Biology', 70); +SET @save_sql_mode= @@sql_mode; +SET sql_mode = 'ONLY_FULL_GROUP_BY'; +SELECT +RANK() OVER (PARTITION BY a ORDER BY b) AS rank, +a, b FROM t1 ORDER BY a, b DESC; +rank a b +2 Biology 70 +1 Biology 60 +4 Maths 70 +2 Maths 60 +2 Maths 60 +1 Maths 55 +SET sql_mode= @save_sql_mode; +DROP TABLE t1; +CREATE TABLE t1(i int,j 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 i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i; +i LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) +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 72e789dff3f..126ed735c88 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2703,6 +2703,33 @@ drop procedure sp7; drop view v1,v2; drop table t1; +--echo # +--echo # MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode +--echo # + +CREATE TABLE t1(a VARCHAR(10), b int); + +INSERT INTO t1 VALUES +('Maths', 60),('Maths', 60), +('Maths', 70),('Maths', 55), +('Biology', 60), ('Biology', 70); + +SET @save_sql_mode= @@sql_mode; +SET sql_mode = 'ONLY_FULL_GROUP_BY'; + +SELECT + RANK() OVER (PARTITION BY a ORDER BY b) AS rank, + a, b FROM t1 ORDER BY a, b DESC; + +SET sql_mode= @save_sql_mode; +DROP TABLE t1; + +CREATE TABLE t1(i int,j 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 i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index bb4a8a9f3af..03f99540771 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -93,6 +93,9 @@ Item_window_func::fix_fields(THD *thd, Item **ref) my_error(ER_NO_ORDER_LIST_IN_WINDOW_SPEC, MYF(0), window_func()->func_name()); return true; } + + window_func()->mark_as_window_func_sum_expr(); + /* TODO: why the last parameter is 'ref' in this call? What if window_func decides to substitute itself for something else and does *ref=.... ? diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a4b105862f3..6f3274aced5 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10557,9 +10557,6 @@ window_func: simple_window_func | sum_expr - { - ((Item_sum *) $1)->mark_as_window_func_sum_expr(); - } ; simple_window_func: