Prohibit using window functions of some types with

window frames in full accordance with the SQL standard.

5 files changed, 63 insertions, 0 deletions

diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index e8f74fa2f59..ff5c161b9d5 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -451,6 +451,25 @@ count(*) over (w2 rows between 2 following and current row) as CNT
 from t1
 window w1 as (partition by c), w2 as (w1 order by pk);
 ERROR HY000: Unacceptable combination of window frame bound specifications
+select 
+pk, c, 
+row_number() over (partition by c order by pk 
+range between unbounded preceding and current row) as r 
+from t1;
+ERROR HY000: Window frame is not allowed with 'row_number'
+select 
+pk, c, 
+rank() over w1 as r
+from t1
+window w1 as (partition by c order by pk 
+rows between 2 preceding and 2 following);
+ERROR HY000: Window frame is not allowed with 'rank'
+select 
+pk, c, 
+dense_rank() over (partition by c order by pk
+rows between 1 preceding and 1 following) as r
+from t1;
+ERROR HY000: Window frame is not allowed with 'dense_rank'
 drop table t0,t1;
 #
 # MDEV-9634: Window function produces incorrect value
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index d61fb689ee6..4106a8321f9 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -289,6 +289,27 @@ select
 from t1
 window w1 as (partition by c), w2 as (w1 order by pk);
 
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  row_number() over (partition by c order by pk 
+                     range between unbounded preceding and current row) as r 
+from t1;
+
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  rank() over w1 as r
+from t1
+window w1 as (partition by c order by pk 
+              rows between 2 preceding and 2 following);
+
+--error ER_NOT_ALLOWED_WINDOW_FRAME
+select 
+  pk, c, 
+  dense_rank() over (partition by c order by pk
+                    rows between 1 preceding and 1 following) as r
+from t1;
 
 drop table t0,t1;
 
diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc
index e446924ff65..7f668ab8fab 100644
--- a/sql/item_windowfunc.cc
+++ b/sql/item_windowfunc.cc
@@ -53,6 +53,12 @@ Item_window_func::fix_fields(THD *thd, Item **ref)
   if (window_name && resolve_window_name(thd))
     return true;
   
+  if (window_spec->window_frame && is_frame_prohibited())
+  {
+    my_error(ER_NOT_ALLOWED_WINDOW_FRAME, MYF(0), window_func->func_name());
+    return true;
+  }
+
   /*
     TODO: why the last parameter is 'ref' in this call? What if window_func
     decides to substitute itself for something else and does *ref=.... ? 
diff --git a/sql/item_windowfunc.h b/sql/item_windowfunc.h
index 56d0a0b70b1..4e0ba7fc43b 100644
--- a/sql/item_windowfunc.h
+++ b/sql/item_windowfunc.h
@@ -436,6 +436,20 @@ public:
       force_return_blank(true),
       read_value_from_result_field(false) {}
 
+  bool is_frame_prohibited()
+  {
+    switch (window_func->sum_func()) {
+    case Item_sum::ROW_NUMBER_FUNC:
+    case Item_sum::RANK_FUNC:
+    case Item_sum::DENSE_RANK_FUNC:
+    case Item_sum::PERCENT_RANK_FUNC:
+    case Item_sum::CUME_DIST_FUNC:
+      return true;
+    default: 
+      return false;
+    }
+  }  
+
   /*
     Computation functions.
     TODO: consoder merging these with class Group_bound_tracker.
@@ -581,6 +595,7 @@ public:
   bool fix_fields(THD *thd, Item **ref);
 
   bool resolve_window_name(THD *thd);
+
 };
 
 #endif /* ITEM_WINDOWFUNC_INCLUDED */
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index b1614930ab1..4636a9484f4 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7148,6 +7148,8 @@ ER_WINDOW_FRAME_IN_REFERENCED_WINDOW_SPEC
         eng "Referenced window specification '%s' cannot contain window frame"
 ER_BAD_COMBINATION_OF_WINDOW_FRAME_BOUND_SPECS
         eng "Unacceptable combination of window frame bound specifications"
+ER_NOT_ALLOWED_WINDOW_FRAME
+        eng "Window frame is not allowed with '%s'"
 ER_RANGE_FRAME_NEEDS_SIMPLE_ORDERBY
         eng "RANGE-type frame requires ORDER BY clause with single sort key"
 ER_WRONG_TYPE_FOR_ROWS_FRAME