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;