diff options
author | Igor Babaev <igor@askmonty.org> | 2016-03-18 00:33:53 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2016-03-18 00:33:53 -0700 |
commit | a197c6bb68d77a0ec73b28d1e6473022ea6b1ae0 (patch) | |
tree | a91588ab6a002b5ef7bedd006a6b23ee8957355c | |
parent | 761590dcd53162e7b73421eb6a61cd399f4dc70d (diff) | |
download | mariadb-git-a197c6bb68d77a0ec73b28d1e6473022ea6b1ae0.tar.gz |
Prohibit using window functions of some types with
window frames in full accordance with the SQL standard.
-rw-r--r-- | mysql-test/r/win.result | 19 | ||||
-rw-r--r-- | mysql-test/t/win.test | 21 | ||||
-rw-r--r-- | sql/item_windowfunc.cc | 6 | ||||
-rw-r--r-- | sql/item_windowfunc.h | 15 | ||||
-rw-r--r-- | sql/share/errmsg-utf8.txt | 2 |
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 |