revision-id: 97b067babde074e877ae2bf40f3e7c8bcde05813 (mariadb-10.2.29-62-g97b067babde) parent(s): f0aa073f2bf3d8d85b3d028df89cdb4cdfc4002d author: Varun Gupta committer: Varun Gupta timestamp: 2019-12-13 20:07:33 +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 | 17 +++++++++++++++++ mysql-test/t/win.test | 16 ++++++++++++++++ sql/sql_window.cc | 20 ++++++++++++++++++-- 3 files changed, 51 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 805fd2ed3d7..bb834c39cd8 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3643,5 +3643,22 @@ x foo drop table t1; # +# MDEV-19901: Wrong window function calculation +# +create table t1 (a int, b int); +insert into t1 values (1, 1), (1, 2), (2, 3), (2, 4); +SELECT +SUM(b) OVER () AS x, +SUM(b) OVER (PARTITION BY a) AS y, +SUM(b) OVER (ORDER BY b) AS z +FROM t1 +ORDER BY z; +x y z +10 3 1 +10 3 3 +10 7 6 +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 0f79834567b..96ec9b0b167 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2351,6 +2351,22 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x; 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), (1, 2), (2, 3), (2, 4); + +SELECT + SUM(b) OVER () AS x, + SUM(b) OVER (PARTITION BY a) AS y, + SUM(b) OVER (ORDER BY b) AS z +FROM t1 +ORDER BY z; + +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..9d8c47a7bb2 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -362,9 +362,9 @@ int compare_order_lists(SQL_I_List<ORDER> *part_list1, return cmp; } if (elem1) - return CMP_GT_C; - if (elem2) return CMP_LT_C; + if (elem2) + return CMP_GT_C; return CMP_EQ; } @@ -546,6 +546,22 @@ typedef int (*Item_window_func_cmp)(Item_window_func *f1, - window frame compatibility. The changes between the groups are marked by setting item_window_func->marker. + + The sorting of the window functions is done in such a way that window + functions that can be computed together are adjacent and the first window + function in this list of compatible functions for sorting + has the LONGEST list for ordering. + + For example lets consider these 3 window functions + sum(a) OVER (PARTITION BY a) + sum(a) OVER (PARTITION BY a, b) + sum(a) OVER (PARTITION BY a, b, c) + + After sorting the order would be + sum(a) OVER (PARTITION BY a, b, c) + sum(a) OVER (PARTITION BY a, b) + sum(a) OVER (PARTITION BY a) + This would only require sorting once(sort criteria a,b,c). */ static