revision-id: cacd3e4fb9c69cdf8b78f22ddd148e34e37869a8 (mariadb-10.2.16-206-gcacd3e4fb9c) parent(s): ab1ce2204e959bea596817494e932754ab5cbe88 author: Varun Gupta committer: Varun Gupta timestamp: 2018-10-24 09:03:47 +0200 message: MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode The ONLY_FULL_GROUP_BY mode states that for SELECT ... GROUP BY queries, disallow SELECTing columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like COUNT() or MAX(). This holds only for the GROUP BY clause of the query. The code also checks this for the partition clause of the window function which is incorrect. --- mysql-test/r/win.result | 37 +++++++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 25 +++++++++++++++++++++++++ sql/sql_select.cc | 3 ++- 3 files changed, 64 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index aef9b613433..b73fe346303 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3326,3 +3326,40 @@ SELECT DISTINCT BIT_AND(0) OVER (), MAX(1) FROM t1; BIT_AND(0) OVER () MAX(1) 0 1 drop table t1; +# +# MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode +# +CREATE TABLE t1 (name CHAR(10), test CHAR(10), score TINYINT); +INSERT INTO t1 VALUES +('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), +('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), +('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), +('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83); +SET @save_sql_mode= @@sql_mode; +SET sql_mode = 'ONLY_FULL_GROUP_BY'; +SELECT name, test, score, +AVG(score) OVER (PARTITION BY test) AS average_by_test +FROM t1; +name test score average_by_test +Chun SQL 75 65.2500 +Chun Tuning 73 68.7500 +Esben SQL 43 65.2500 +Esben Tuning 31 68.7500 +Kaolin SQL 56 65.2500 +Kaolin Tuning 88 68.7500 +Tatiana SQL 87 65.2500 +Tatiana Tuning 83 68.7500 +set @@sql_mode= @save_sql_mode; +SELECT name, test, score, +AVG(score) OVER (PARTITION BY test) AS average_by_test +FROM t1; +name test score average_by_test +Chun SQL 75 65.2500 +Chun Tuning 73 68.7500 +Esben SQL 43 65.2500 +Esben Tuning 31 68.7500 +Kaolin SQL 56 65.2500 +Kaolin Tuning 88 68.7500 +Tatiana SQL 87 65.2500 +Tatiana Tuning 83 68.7500 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 1617e85caf4..e24c6533dcd 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2092,3 +2092,28 @@ insert into t1 values (1),(2); SELECT DISTINCT row_number() OVER (), MAX(1) FROM t1; SELECT DISTINCT BIT_AND(0) OVER (), MAX(1) FROM t1; drop table t1; + +--echo # +--echo # MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode +--echo # + +CREATE TABLE t1 (name CHAR(10), test CHAR(10), score TINYINT); + +INSERT INTO t1 VALUES +('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), +('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), +('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), +('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83); + +SET @save_sql_mode= @@sql_mode; +SET sql_mode = 'ONLY_FULL_GROUP_BY'; + +SELECT name, test, score, + AVG(score) OVER (PARTITION BY test) AS average_by_test +FROM t1; + +set @@sql_mode= @save_sql_mode; +SELECT name, test, score, + AVG(score) OVER (PARTITION BY test) AS average_by_test +FROM t1; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a9adbd168a6..1fa80da85a6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22611,7 +22611,8 @@ setup_group(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, return 1; } } - if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) + if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && + context_analysis_place == IN_GROUP_BY) { /* Don't allow one to use fields that is not used in GROUP BY