revision-id: e793e4e260b6faa4666d4ff439a6a7023daedceb (mariadb-10.2.23-110-ge793e4e260b) parent(s): 386a759efe552519fb6a4f9acc6f630b2872316e author: Varun Gupta committer: Varun Gupta timestamp: 2019-05-04 03:21:42 +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 | 15 +++++++++++++++ sql/sql_select.cc | 8 ++++++++ 3 files changed, 49 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 849da8668f1..5da8a7f8a3c 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3581,5 +3581,31 @@ x b c 8 64 248 drop table t1; # +# 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 b, dense_rank() over (order by sum(a)) from t1 group by b; +b dense_rank() over (order by sum(a)) +1515 2 +1600 1 +2000 3 +select b, dense_rank() over (order by sum(a)+1) from t1 group by b; +b dense_rank() over (order by sum(a)+1) +1515 2 +1600 1 +2000 3 +select b, row_number() over (partition by sum(a)) from t1 group by b; +b row_number() over (partition by sum(a)) +1515 1 +1600 1 +2000 1 +select b, row_number() over (partition by sum(a)+1) from t1 group by b; +b row_number() over (partition by sum(a)+1) +1515 1 +1600 1 +2000 1 +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 270af3833c9..40da44c0426 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2310,6 +2310,21 @@ SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) ove drop table t1; +--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 b, dense_rank() over (order by sum(a)) from t1 group by b; +select b, dense_rank() over (order by sum(a)+1) from t1 group by b; + +select b, row_number() over (partition by sum(a)) from t1 group by b; +select b, row_number() over (partition by sum(a)+1) from t1 group by b; + +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7665fedd0cf..521cd863cfc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22584,6 +22584,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; } @@ -22651,6 +22655,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)