revision-id: 301a5e487bb625b41543f11626ab524ad348d0ba (mariadb-10.2.29-49-g301a5e487bb) parent(s): 425748f1b5890a4bb3c7dcadf8f81f567f9a26fe author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-10 01:43:57 +0530 message: MDEV-19901: Wrong window function calculation The function compare_window_funcs_by_window_specs() does not correctly compare the window functions and this leads to skipping sorting for some window functions as it assumes that the sort order has not changed. --- mysql-test/r/win.result | 27 +++++++++++++++++++++++++++ mysql-test/t/win.test | 22 ++++++++++++++++++++++ sql/sql_window.cc | 2 +- 3 files changed, 50 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 13d452f3ef2..b543863bc50 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3634,5 +3634,32 @@ rank() over (partition by 'abc' order by 'xyz') 1 drop table t1; # +# MDEV-19901: Wrong window function calculation +# +create table t1 (a int, b int); +insert into t1 values (1, 1), (2, 1), (3, 2), (4, 2); +SELECT +SUM(a) OVER (ORDER BY a) AS x, +SUM(a) OVER (PARTITION BY b) AS y, +SUM(a) OVER () AS x +FROM t1 +ORDER BY a; +x y x +1 3 10 +3 3 10 +6 7 10 +10 7 10 +SELECT +SUM(a) OVER (ORDER BY a) AS x, +SUM(a) OVER (PARTITION BY b) AS y, +SUM(a) OVER () AS z +FROM t1; +x y z +1 3 10 +3 3 10 +6 7 10 +10 7 10 +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index a4d42ce2b91..79a357b3b34 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2341,6 +2341,28 @@ select rank() over (partition by 'abc' order by 'xyz') from t1; select rank() over (partition by 'abc' order by 'xyz') from t1; drop table t1; +--echo # +--echo # MDEV-19901: Wrong window function calculation +--echo # + +create table t1 (a int, b int); +insert into t1 values (1, 1), (2, 1), (3, 2), (4, 2); + +SELECT + SUM(a) OVER (ORDER BY a) AS x, + SUM(a) OVER (PARTITION BY b) AS y, + SUM(a) OVER () AS x +FROM t1 +ORDER BY a; + +SELECT + SUM(a) OVER (ORDER BY a) AS x, + SUM(a) OVER (PARTITION BY b) AS y, + SUM(a) OVER () AS z +FROM t1; + +drop table t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_window.cc b/sql/sql_window.cc index b258b8f56c9..f3f1915c6e4 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -521,7 +521,7 @@ int compare_window_funcs_by_window_specs(Item_window_func *win_func1, if (CMP_LT_C <= cmp && cmp <= CMP_GT_C) cmp= win_spec1->partition_list->elements < - win_spec2->partition_list->elements ? CMP_GT_C : CMP_LT_C; + win_spec2->partition_list->elements ? CMP_LT_C : CMP_GT_C; return cmp; }