revision-id: 8c2b5e8af1ee41b00bd973e38efcae9ccc997bef (mariadb-10.2.31-305-g8c2b5e8af1e) parent(s): 7521b2113e61b3a6841c338590ca21f088bd1e1b author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-09 17:42:55 +0530 message: MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode II Make sure that window functions are not marked as sum items. --- mysql-test/r/win.result | 22 ++++++++++++++++++++++ mysql-test/t/win.test | 21 +++++++++++++++++++++ sql/item_sum.h | 2 +- sql/sql_yacc.yy | 3 +++ 4 files changed, 47 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 71953871295..8f1f5b1510f 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3808,5 +3808,27 @@ MIN(d) OVER () 1 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; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index a768b893432..824155ffb76 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2481,6 +2481,27 @@ INSERT INTO t1 VALUES (1),(2); SELECT MIN(d) OVER () FROM t1; 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; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_sum.h b/sql/item_sum.h index 290aa5c50e3..297abfb96da 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -340,7 +340,7 @@ class Item_sum :public Item_func_or_sum */ bool with_distinct; - /* TRUE if this is aggregate function of a window function */ + /* TRUE if this is a sum item used as a window function */ bool window_func_sum_expr_flag; public: 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 {