[Commits] efb075c: MDEV-25565 Preliminary commit

IgorBabaev igor at mariadb.com
Fri Jul 9 22:00:02 EEST 2021


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; }


More information about the commits mailing list