1create table t1 (i int); 2insert into t1 values (5),(6),(0); 3# 4# Try out all set functions with window functions as arguments. 5# Any such usage should return an error. 6# 7select MIN( SUM(i) OVER (order by i) ) 8from t1; 9ERROR HY000: Window functions can not be used as arguments to group functions. 10select MIN(DISTINCT SUM(i) OVER (order by i) ) 11from t1; 12ERROR HY000: Window functions can not be used as arguments to group functions. 13select MAX( SUM(i) OVER (order by i) ) 14from t1; 15ERROR HY000: Window functions can not be used as arguments to group functions. 16select MAX(DISTINCT SUM(i) OVER (order by i) ) 17from t1; 18ERROR HY000: Window functions can not be used as arguments to group functions. 19select SUM( SUM(i) OVER (order by i) ) 20from t1; 21ERROR HY000: Window functions can not be used as arguments to group functions. 22select SUM(DISTINCT SUM(i) OVER (order by i) ) 23from t1; 24ERROR HY000: Window functions can not be used as arguments to group functions. 25select AVG( SUM(i) OVER (order by i) ) 26from t1; 27ERROR HY000: Window functions can not be used as arguments to group functions. 28select AVG(DISTINCT SUM(i) OVER (order by i) ) 29from t1; 30ERROR HY000: Window functions can not be used as arguments to group functions. 31select COUNT( SUM(i) OVER (order by i) ) 32from t1; 33ERROR HY000: Window functions can not be used as arguments to group functions. 34select COUNT(DISTINCT SUM(i) OVER (order by i) ) 35from t1; 36ERROR HY000: Window functions can not be used as arguments to group functions. 37select BIT_AND( SUM(i) OVER (order by i) ) 38from t1; 39ERROR HY000: Window functions can not be used as arguments to group functions. 40select BIT_OR( SUM(i) OVER (order by i) ) 41from t1; 42ERROR HY000: Window functions can not be used as arguments to group functions. 43select BIT_XOR( SUM(i) OVER (order by i) ) 44from t1; 45ERROR HY000: Window functions can not be used as arguments to group functions. 46select STD( SUM(i) OVER (order by i) ) 47from t1; 48ERROR HY000: Window functions can not be used as arguments to group functions. 49select STDDEV( SUM(i) OVER (order by i) ) 50from t1; 51ERROR HY000: Window functions can not be used as arguments to group functions. 52select STDDEV_POP( SUM(i) OVER (order by i) ) 53from t1; 54ERROR HY000: Window functions can not be used as arguments to group functions. 55select STDDEV_SAMP(SUM(i) OVER (order by i) ) 56from t1; 57ERROR HY000: Window functions can not be used as arguments to group functions. 58select VARIANCE(SUM(i) OVER (order by i) ) 59from t1; 60ERROR HY000: Window functions can not be used as arguments to group functions. 61select VAR_POP(SUM(i) OVER (order by i) ) 62from t1; 63ERROR HY000: Window functions can not be used as arguments to group functions. 64select VAR_SAMP(SUM(i) OVER (order by i) ) 65from t1; 66ERROR HY000: Window functions can not be used as arguments to group functions. 67select GROUP_CONCAT(SUM(i) OVER (order by i) ) 68from t1; 69ERROR HY000: Window functions can not be used as arguments to group functions. 70select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) ) 71from t1; 72ERROR HY000: Window functions can not be used as arguments to group functions. 73# 74# Test that partition instead of order by in over doesn't change result. 75# 76select SUM( SUM(i) OVER (PARTITION BY i) ) 77from t1; 78ERROR HY000: Window functions can not be used as arguments to group functions. 79# 80# Test that no arguments in OVER() clause lead to crash in this case. 81# 82select SUM( SUM(i) OVER () ) 83from t1; 84ERROR HY000: Window functions can not be used as arguments to group functions. 85drop table t1; 86# 87# MDEV-13774: Server Crash on Execuate of SQL Statement 88# 89create table t1 (i int); 90insert into t1 values (5),(6),(0); 91select SUM( 92IF( SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0 93AND 94SUM( IF(i,1,0)) OVER (PARTITION BY i) > 0, 951, 960) ) 97from t1; 98ERROR HY000: Window functions can not be used as arguments to group functions. 99# 100# A way to get the aggregation result. 101# 102select 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 103from t1 104order by i; 105i if_col 1060 0 1075 1 1086 1 109select sum(if_col) 110from (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 111from t1) tmp; 112sum(if_col) 1132 114drop table t1; 115