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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
create table t1 (i int);
insert into t1 values (5),(6),(0);
--echo #
--echo # Try out all set functions with window functions as arguments.
--echo # Any such usage should return an error.
--echo #
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select MIN( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select MIN(DISTINCT SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select MAX( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select MAX(DISTINCT SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select SUM( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select SUM(DISTINCT SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select AVG( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select AVG(DISTINCT SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select COUNT( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select COUNT(DISTINCT SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select BIT_AND( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select BIT_OR( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select BIT_XOR( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select STD( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select STDDEV( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select STDDEV_POP( SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select STDDEV_SAMP(SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select VARIANCE(SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select VAR_POP(SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select VAR_SAMP(SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select GROUP_CONCAT(SUM(i) OVER (order by i) )
from t1;
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select GROUP_CONCAT(DISTINCT SUM(i) OVER (order by i) )
from t1;
--echo #
--echo # Test that partition instead of order by in over doesn't change result.
--echo #
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select SUM( SUM(i) OVER (PARTITION BY i) )
from t1;
--echo #
--echo # Test that no arguments in OVER() clause lead to crash in this case.
--echo #
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
select SUM( SUM(i) OVER () )
from t1;
drop table t1;
-- echo #
-- echo # MDEV-13774: Server Crash on Execuate of SQL Statement
-- echo #
create table t1 (i int);
insert into t1 values (5),(6),(0);
--error ER_SUM_FUNC_WITH_WINDOW_FUNC_AS_ARG
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;
--echo #
--echo # A way to get the aggregation result.
--echo #
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;
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;
drop table t1;
|
