revision-id: efb075c752ad37402fb754a53523e07fd24e8188 (mariadb-10.2.31-1002-gefb075c) parent(s): 4352c77c5a3ac89acc5fd90a38f806d0ec500aa4 author: Igor Babaev committer: Igor Babaev timestamp: 2021-07-09 12:00:02 -0700 message: MDEV-25565 Preliminary commit Some new test cases are to be added after rebase. --- mysql-test/r/win.result | 111 ++++++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 70 ++++++++++++++++++++++++++++++ sql/sql_union.cc | 26 ++++++++++++ sql/sql_window.cc | 12 ++++++ sql/sql_window.h | 5 ++- 5 files changed, 223 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 8a31dcc..432c12b 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3911,5 +3911,116 @@ sum(i) over () IN ( SELECT 1 FROM t1 a) 0 DROP TABLE t1; # +# MDEV-25565: 2-nd call of SP with SELECT joining a view / derived table +and returning the result of calculation of 2 window +functions that use the same window specification +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create table t2 (b int); +insert into t2 values (1), (4), (9), (8), (2), (9), (7), (1); +create view v2 as select a from t1 group by a; +create view v1 as select * from v2; +create procedure sp1() select v1.a, +sum(v1.a) over (partition by v1.a order by v1.a) as k, +avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a; +call sp1(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp1(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select v1.a, +sum(v1.a) over (partition by v1.a order by v1.a) as k, +avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +create procedure sp2() select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt; +call sp2(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp2(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +create procedure sp3() select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from ( select * from (select * from t1 group by a) as dt2 ) as dt1, +t2 +where t2.b=dt1.a ) as dt; +call sp3(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +call sp3(); +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +prepare stmt from "select * from +( select dt1.a, +sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, +avg(dt1.a) over (partition by dt1.a order by dt1.a) as m +from ( select * from (select * from t1 group by a) as dt2 ) as dt1, +t2 +where t2.b=dt1.a ) as dt"; +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +execute stmt; +a k m +1 2 1.0000 +1 2 1.0000 +7 7 7.0000 +deallocate prepare stmt; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop view v1,v2; +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c07a81f..e42c8c9 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2557,5 +2557,75 @@ SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-25565: 2-nd call of SP with SELECT joining a view / derived table +--echo and returning the result of calculation of 2 window +--echo functions that use the same window specification +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5); +create table t2 (b int); +insert into t2 values (1), (4), (9), (8), (2), (9), (7), (1); + +create view v2 as select a from t1 group by a; +create view v1 as select * from v2; + +let $q1= +select v1.a, + sum(v1.a) over (partition by v1.a order by v1.a) as k, + avg(v1.a) over (partition by v1.a order by v1.a) as m +from v1, t2 where t2.b = v1.a; + +eval create procedure sp1() $q1; +call sp1(); +call sp1(); + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + + +let $q2= +select * from + ( select dt1.a, + sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, + avg(dt1.a) over (partition by dt1.a order by dt1.a) as m + from (select * from v2) as dt1, t2 where t2.b=dt1.a ) as dt; + +eval create procedure sp2() $q2; +call sp2(); +call sp2(); + +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q3= +select * from + ( select dt1.a, + sum(dt1.a) over (partition by dt1.a order by dt1.a) as k, + avg(dt1.a) over (partition by dt1.a order by dt1.a) as m + from ( select * from (select * from t1 group by a) as dt2 ) as dt1, + t2 + where t2.b=dt1.a ) as dt; + +eval create procedure sp3() $q3; +call sp3(); +call sp3(); + +eval prepare stmt from "$q3"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop view v1,v2; +drop table t1,t2; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7baedfb..f3c90b8 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -30,6 +30,7 @@ #include "filesort.h" // filesort_free_buffers #include "sql_view.h" #include "sql_cte.h" +#include "item_windowfunc.h" bool mysql_union(THD *thd, LEX *lex, select_result *result, SELECT_LEX_UNIT *unit, ulong setup_tables_done_option) @@ -1550,6 +1551,29 @@ static void cleanup_order(ORDER *order) } +static void cleanup_window_funcs(List<Item_window_func> &win_funcs) +{ + List_iterator_fast<Item_window_func> it(win_funcs); + Item_window_func *win_func; + while ((win_func= it++)) + { + Window_spec *win_spec= win_func->window_spec; + if (!win_spec) + continue; + if (win_spec->save_partition_list) + { + win_spec->partition_list= win_spec->save_partition_list; + win_spec->save_partition_list= NULL; + } + if (win_spec->save_order_list) + { + win_spec->order_list= win_spec->save_order_list; + win_spec->save_order_list= NULL; + } + } +} + + bool st_select_lex::cleanup() { bool error= FALSE; @@ -1558,6 +1582,8 @@ bool st_select_lex::cleanup() cleanup_order(order_list.first); cleanup_order(group_list.first); + cleanup_window_funcs(window_funcs); + if (join) { List_iterator<TABLE_LIST> ti(leaf_tables); diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 612c6e6..3ef751b 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -479,9 +479,15 @@ int compare_window_funcs_by_window_specs(Item_window_func *win_func1, Let's use only one of the lists. */ if (!win_spec1->name() && win_spec2->name()) + { + win_spec1->save_partition_list= win_spec1->partition_list; win_spec1->partition_list= win_spec2->partition_list; + } else + { + win_spec2->save_partition_list= win_spec2->partition_list; win_spec2->partition_list= win_spec1->partition_list; + } cmp= compare_order_lists(win_spec1->order_list, win_spec2->order_list); @@ -494,9 +500,15 @@ int compare_window_funcs_by_window_specs(Item_window_func *win_func1, Let's use only one of the lists. */ if (!win_spec1->name() && win_spec2->name()) + { + win_spec1->save_order_list= win_spec2->order_list; win_spec1->order_list= win_spec2->order_list; + } else + { + win_spec1->save_order_list= win_spec2->order_list; win_spec2->order_list= win_spec1->order_list; + } cmp= compare_window_frames(win_spec1->window_frame, win_spec2->window_frame); diff --git a/sql/sql_window.h b/sql/sql_window.h index e0c1563..417d0bc 100644 --- a/sql/sql_window.h +++ b/sql/sql_window.h @@ -99,8 +99,10 @@ class Window_spec : public Sql_alloc LEX_STRING *window_ref; SQL_I_List<ORDER> *partition_list; + SQL_I_List<ORDER> *save_partition_list; SQL_I_List<ORDER> *order_list; + SQL_I_List<ORDER> *save_order_list; Window_frame *window_frame; @@ -111,7 +113,8 @@ class Window_spec : public Sql_alloc SQL_I_List<ORDER> *ord_list, Window_frame *win_frame) : window_names_are_checked(false), window_ref(win_ref), - partition_list(part_list), order_list(ord_list), + partition_list(part_list), save_partition_list(NULL), + order_list(ord_list), save_order_list(NULL), window_frame(win_frame), referenced_win_spec(NULL) {} virtual char *name() { return NULL; }