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