revision-id: b2bb6a033125432c807be8464506ca12f09028fe (mariadb-10.2.22-46-gb2bb6a03312) parent(s): bf71d263621c90cbddc7bde9bf071dae503f333f author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-08 17:21:57 +0530 message: MDEV-18373: DENSE_RANK is not calculated correctly Need to call split_sum_func if an aggregate function is part of order by or partition by clause so that we have the required fields inside the temporary table, as all the fields inside the partition by and order by clause of the window function needs to be there in the temp table used for window function computation. --- mysql-test/r/win.result | 26 ++++++++++++++++++++++++++ mysql-test/t/win.test | 14 ++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 48 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 2e80c2c961b..54d2c85f8cd 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3488,3 +3488,29 @@ ifnull(max(n1) over (partition by n1),'aaa') 4 drop table t1; drop view v1; +# +# MDEV-18373: DENSE_RANK is not calculated correctly +# +create table t1 (a int, b int); +insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600); +select dense_rank() over (order by sum(a)) from t1 group by b; +dense_rank() over (order by sum(a)) +2 +1 +3 +select dense_rank() over (order by sum(a)+1) from t1 group by b; +dense_rank() over (order by sum(a)+1) +2 +1 +3 +select row_number() over (partition by sum(a)) from t1 group by b; +row_number() over (partition by sum(a)) +1 +1 +1 +select row_number() over (partition by sum(a)+1) from t1 group by b; +row_number() over (partition by sum(a)+1) +1 +1 +1 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 18bdfa31691..f4d8f207d45 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2240,3 +2240,17 @@ explain select * from v1; select * from v1; drop table t1; drop view v1; + +--echo # +--echo # MDEV-18373: DENSE_RANK is not calculated correctly +--echo # + +create table t1 (a int, b int); +insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600); + +select dense_rank() over (order by sum(a)) from t1 group by b; +select dense_rank() over (order by sum(a)+1) from t1 group by b; + +select row_number() over (partition by sum(a)) from t1 group by b; +select row_number() over (partition by sum(a)+1) from t1 group by b; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 91a6445c870..bc6e50aa563 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22557,6 +22557,10 @@ int setup_order(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, my_error(ER_WINDOW_FUNCTION_IN_WINDOW_SPEC, MYF(0)); return 1; } + if (from_window_spec && (*order->item)->with_sum_func && + (*order->item)->type() != Item::SUM_FUNC_ITEM) + (*order->item)->split_sum_func(thd, ref_pointer_array, + all_fields, SPLIT_SUM_SELECT); } return 0; } @@ -22624,6 +22628,10 @@ setup_group(THD *thd, Ref_ptr_array ref_pointer_array, TABLE_LIST *tables, my_error(ER_WINDOW_FUNCTION_IN_WINDOW_SPEC, MYF(0)); return 1; } + if (from_window_spec && (*ord->item)->with_sum_func && + (*ord->item)->type() != Item::SUM_FUNC_ITEM) + (*ord->item)->split_sum_func(thd, ref_pointer_array, + all_fields, SPLIT_SUM_SELECT); } if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY && context_analysis_place == IN_GROUP_BY)