diff options
| -rw-r--r-- | mysql-test/r/win_as_arg_to_aggregate_func.result | 114 | ||||
| -rw-r--r-- | mysql-test/t/win_as_arg_to_aggregate_func.test | 139 | ||||
| -rw-r--r-- | sql/item_sum.cc | 23 | ||||
| -rw-r--r-- | sql/share/errmsg-utf8.txt | 3 |
4 files changed, 277 insertions, 2 deletions
diff --git a/mysql-test/r/win_as_arg_to_aggregate_func.result b/mysql-test/r/win_as_arg_to_aggregate_func.result new file mode 100644 index 00000000000..e3f0091d6d2 --- /dev/null +++ b/mysql-test/r/win_as_arg_to_aggregate_func.result @@ -0,0 +1,114 @@ +create table t1 (i int); +insert into t1 values (5),(6),(0); +# +# Try out all set functions with window functions as arguments. +# Any such usage should return an error. +# +select MIN( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MIN(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MAX( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select MAX(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select SUM( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select SUM(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select AVG( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select AVG(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select COUNT( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select COUNT(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_AND( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_OR( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select BIT_XOR( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STD( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV_POP( SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select STDDEV_SAMP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VARIANCE(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VAR_POP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select VAR_SAMP(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select GROUP_CONCAT(SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# Test that partition instead of order by in over doesn't change result. +# +select SUM( SUM(i) OVER (PARTITION BY i) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# Test that no arguments in OVER() clause lead to crash in this case. +# +select SUM( SUM(i) OVER () ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +drop table t1; +# +# MDEV-13774: Server Crash on Execuate of SQL Statement +# +create table t1 (i int); +insert into t1 values (5),(6),(0); +select SUM( +IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 +AND +SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, +1, +0) ) +from t1; +ERROR HY000: Window functions can not be used as arguments to group functions. +# +# A way to get the aggregation result. +# +select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1 +order by i; +i if_col +0 0 +5 1 +6 1 +select sum(if_col) +from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1) tmp; +sum(if_col) +2 +drop table t1; diff --git a/mysql-test/t/win_as_arg_to_aggregate_func.test b/mysql-test/t/win_as_arg_to_aggregate_func.test new file mode 100644 index 00000000000..93c9238bedf --- /dev/null +++ b/mysql-test/t/win_as_arg_to_aggregate_func.test @@ -0,0 +1,139 @@ +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--echo # +--echo # Try out all set functions with window functions as arguments. +--echo # Any such usage should return an error. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MIN(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select MAX(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM(DISTINCT SUM(i) OVER (order by i) ) +from t1; + + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select AVG(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select COUNT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_AND( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_OR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select BIT_XOR( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STD( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_POP( SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select STDDEV_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VARIANCE(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_POP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select VAR_SAMP(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(SUM(i) OVER (order by i) ) +from t1; + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) +from t1; + +--echo # +--echo # Test that partition instead of order by in over doesn't change result. +--echo # + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER (PARTITION BY i) ) +from t1; + +--echo # +--echo # Test that no arguments in OVER() clause lead to crash in this case. +--echo # +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( SUM(i) OVER () ) +from t1; +drop table t1; + +-- echo # +-- echo # MDEV-13774: Server Crash on Execuate of SQL Statement +-- echo # +create table t1 (i int); +insert into t1 values (5),(6),(0); + +--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG +select SUM( + IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 + AND + SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, + 1, + 0) ) +from t1; + +--echo # +--echo # A way to get the aggregation result. +--echo # + +select i, IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col +from t1 +order by i; + +select sum(if_col) +from (select IF(SUM(IF(i,1,0)) OVER (PARTITION BY i) > 0 AND SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0,1,0) AS if_col + from t1) tmp; +drop table t1; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 35e8dea46a7..dff6e6adf6b 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -100,7 +100,11 @@ bool Item_sum::init_sum_func_check(THD *thd) The method verifies whether context conditions imposed on a usage of any set function are met for this occurrence. - It checks whether the set function occurs in the position where it + + The function first checks if we are using any window functions as + arguments to the set function. In that case it returns an error. + + Afterwards, it checks whether the set function occurs in the position where it can be aggregated and, when it happens to occur in argument of another set function, the method checks that these two functions are aggregated in different subqueries. @@ -151,6 +155,20 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref) curr_sel->name_visibility_map); bool invalid= FALSE; DBUG_ASSERT(curr_sel->name_visibility_map); // should be set already + + /* + Window functions can not be used as arguments to sum functions. + Aggregation happes before window function computation, so there + are no values to aggregate over. + */ + if (with_window_func) + { + my_message(ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG, + ER_THD(thd, ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG), + MYF(0)); + return TRUE; + } + if (window_func_sum_expr_flag) return false; /* @@ -1108,6 +1126,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) return TRUE; set_if_bigger(decimals, args[i]->decimals); with_subselect|= args[i]->with_subselect; + with_window_func|= args[i]->with_window_func; } result_field=0; max_length=float_length(decimals); @@ -1139,6 +1158,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) return TRUE; Type_std_attributes::set(args[0]); with_subselect= args[0]->with_subselect; + with_window_func|= args[0]->with_window_func; Item *item2= item->real_item(); if (item2->type() == Item::FIELD_ITEM) @@ -3480,6 +3500,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref) args[i]->check_cols(1)) return TRUE; with_subselect|= args[i]->with_subselect; + with_window_func|= args[i]->with_window_func; } /* skip charset aggregation for order columns */ diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 1e63cedcba5..49b2c8b76ef 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7740,4 +7740,5 @@ ER_RDB_TTL_DURATION_FORMAT ER_PER_INDEX_CF_DEPRECATED eng "The per-index column family option has been deprecated" - +ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG + eng "Window functions can not be used as arguments to group functions." |