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