1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
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;
|
