[Commits] b2aeedcccfd9a37e2ce1818268c0510e29e66f51 Fixed bug mdev-10868. There was no implementation of the virtual method print() for the Item_window_func class. As a result for a view containing window function an invalid view definition could be written in the frm file. When a query that refers to this view was executed a syntax error was reported.

Igor Babaev igor at askmonty.org
Thu Sep 29 11:15:00 EEST 2016


commit b2aeedcccfd9a37e2ce1818268c0510e29e66f51
Author: Igor Babaev <igor at askmonty.org>
Commit: Igor Babaev <igor at askmonty.org>

    Fixed bug mdev-10868.
    There was no implementation of the virtual method print()
    for the Item_window_func class. As a result for a view
    containing window function an invalid view definition could
    be written in the frm file. When a query that refers to
    this view was executed a syntax error was reported.
---
 mysql-test/r/win.result |  116 ++++++++++++++++++++++++++++++++++++++++++++++-
 mysql-test/t/win.test   |   43 +++++++++++++++++
 sql/item_windowfunc.cc  |    7 +++
 sql/item_windowfunc.h   |    4 +-
 sql/sql_window.cc       |   78 +++++++++++++++++++++++++++++++
 sql/sql_window.h        |    7 +++
 6 files changed, 253 insertions(+), 2 deletions(-)

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index 78293c9..7cbf644 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -1779,7 +1779,7 @@ EXPLAIN
   "query_block": {
     "select_id": 1,
     "filesort": {
-      "sort_key": "X",
+      "sort_key": "row_number() over ( order by t1.s1,t1.s2)",
       "window_functions_computation": {
         "sorts": {
           "filesort": {
@@ -2236,3 +2236,117 @@ sum(t.a) over (partition by t.b order by a)	sqrt(ifnull((sum(t.a) over (partitio
 3.0000000000	1.7320508075688772
 0.0000000000	0
 drop table t;
+#
+# MDEV-10868: view definitions with window functions
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+pk	c
+1	1
+2	1
+3	1
+4	1
+5	2
+6	2
+7	2
+8	2
+9	2
+10	2
+select pk, c, c/count(*) over (partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+create view v1 as select pk, c, c/count(*) over (partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,(`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding  and 2 following )) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v1;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+create view v2 as select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+show create view v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,(`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding  and 2 following )) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v2;
+pk	c	CNT
+1	1	0.3333
+2	1	0.2500
+3	1	0.3333
+4	1	0.5000
+5	2	0.6667
+6	2	0.5000
+7	2	0.5000
+8	2	0.5000
+9	2	0.6667
+10	2	1.0000
+select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows unbounded preceding);
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.4000
+10	2	0.3333
+create view v3 as select pk, c, c/count(*) over w1 as CNT from t1
+window w1 as (partition by c order by pk rows unbounded preceding);
+show create view v3;
+View	Create View	character_set_client	collation_connection
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,(`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between  unbounded  preceding  and  current row )) AS `CNT` from `t1`	latin1	latin1_swedish_ci
+select * from v3;
+pk	c	CNT
+1	1	1.0000
+2	1	0.5000
+3	1	0.3333
+4	1	0.2500
+5	2	2.0000
+6	2	1.0000
+7	2	0.6667
+8	2	0.5000
+9	2	0.4000
+10	2	0.3333
+drop view v1,v2,v3;
+drop table t0,t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index 4b0e4a8..971e91a 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1373,3 +1373,46 @@ SELECT sum(t.a) over (partition by t.b order by a),
        sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0))
 from t;
 drop table t;
+
+--echo #
+--echo # MDEV-10868: view definitions with window functions
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int, c int);
+insert into t1 select a+1,1 from t0;
+update t1 set c=2 where pk not in (1,2,3,4);
+select * from t1;
+
+let $q=
+select pk, c, c/count(*) over (partition by c order by pk
+                               rows between 1 preceding and 2 following) as CNT
+from t1;
+
+eval $q;
+eval create view v1 as $q;
+show create view v1;
+select * from v1;
+
+let $q=
+select pk, c, c/count(*) over w1 as CNT from t1
+  window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
+
+eval $q;
+eval create view v2 as $q;
+show create view v2;
+select * from v2;
+
+let $q=
+select pk, c, c/count(*) over w1 as CNT from t1
+  window w1 as (partition by c order by pk rows unbounded preceding);
+
+eval $q;
+eval create view v3 as $q;
+show create view v3;
+select * from v3;
+
+drop view v1,v2,v3;
+drop table t0,t1;
diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc
index ccdbdd3..a13967e 100644
--- a/sql/item_windowfunc.cc
+++ b/sql/item_windowfunc.cc
@@ -446,3 +446,10 @@ void Item_sum_hybrid_simple::update_field()
 {
   DBUG_ASSERT(0);
 }
+
+void Item_window_func::print(String *str, enum_query_type query_type)
+{
+  window_func()->print(str, query_type);
+  str->append(" over ");
+  window_spec->print(str, query_type);
+}
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index 5b368e2..15e5c9a 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -963,7 +963,9 @@ class Item_window_func : public Item_func_or_sum
 
   bool resolve_window_name(THD *thd);
   
-  Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; }
+  void print(String *str, enum_query_type query_type);
+
+ Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; }
 
 };
 
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index 8fed241..28be70c 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -61,6 +61,25 @@
   return false;
 }
 
+void
+Window_spec::print(String *str, enum_query_type query_type)
+{
+  str->append('(');
+  if (partition_list->first)
+  {
+    str->append(STRING_WITH_LEN(" partition by "));
+    st_select_lex::print_order(str, partition_list->first, query_type);
+  }
+  if (order_list->first)
+  {
+    str->append(STRING_WITH_LEN(" order by "));
+    st_select_lex::print_order(str, order_list->first, query_type);
+  }
+  if (window_frame)
+    window_frame->print(str, query_type);
+  str->append(')');
+}
+
 bool
 Window_frame::check_frame_bounds()
 {
@@ -81,6 +100,65 @@
 }
 
 
+void
+Window_frame::print(String *str, enum_query_type query_type)
+{
+  switch (units) {
+  case UNITS_ROWS:
+    str->append(STRING_WITH_LEN(" rows "));
+    break;
+  case UNITS_RANGE: str->append(STRING_WITH_LEN(" range "));
+  }
+
+  str->append(STRING_WITH_LEN("between "));
+  top_bound->print(str, query_type);
+  str->append(STRING_WITH_LEN(" and "));
+  bottom_bound->print(str, query_type);
+ 
+  if (exclusion != EXCL_NONE)
+  {
+     str->append(STRING_WITH_LEN(" exclude ")); 
+     switch (exclusion) {
+     case EXCL_CURRENT_ROW: 
+       str->append(STRING_WITH_LEN(" current row "));
+       break;
+     case EXCL_GROUP: 
+       str->append(STRING_WITH_LEN(" group "));
+       break;
+     case EXCL_TIES: 
+       str->append(STRING_WITH_LEN(" ties "));
+       break;
+     default: 
+       ;
+     }
+  } 
+}
+
+
+void
+Window_frame_bound::print(String *str, enum_query_type query_type)
+{
+  if (precedence_type == CURRENT)
+  {
+    str->append(STRING_WITH_LEN(" current row "));
+    return;
+  }
+  if (is_unbounded())
+    str->append(STRING_WITH_LEN(" unbounded "));
+  else
+    offset->print(str ,query_type);  
+  switch (precedence_type) {
+  case PRECEDING:
+    str->append(STRING_WITH_LEN(" preceding "));
+    break;
+  case FOLLOWING:
+    str->append(STRING_WITH_LEN(" following "));
+    break;
+  default:
+    DBUG_ASSERT(0);
+  }
+}
+
 /*
   Setup window functions in a select
 */
diff --git a/sql/sql_window.h b/sql/sql_window.h
index b94a1fc..6a56fc8 100644
--- a/sql/sql_window.h
+++ b/sql/sql_window.h
@@ -45,6 +45,8 @@ class Window_frame_bound : public Sql_alloc
 
   bool is_unbounded() { return offset == NULL; }
 
+  void print(String *str, enum_query_type query_type);
+
 };
 
 
@@ -84,6 +86,8 @@ class Window_frame : public Sql_alloc
 
   bool check_frame_bounds();
 
+  void print(String *str, enum_query_type query_type);
+
 };
 
 class Window_spec : public Sql_alloc
@@ -125,6 +129,9 @@ class Window_spec : public Sql_alloc
   {
     *(partition_list->next)= NULL;
   }
+
+  void print(String *str, enum_query_type query_type);
+
 };
 
 class Window_def : public Window_spec


More information about the commits mailing list