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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
# name: test/sql/window/test_empty_frames.test
# description: Empty aggregate frames
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE t1 (id INTEGER, ch CHAR(1)) ;
statement ok
INSERT INTO t1 VALUES (1, 'A');
statement ok
INSERT INTO t1 VALUES (2, 'B');
statement ok
INSERT INTO t1 VALUES (NULL, 'B');
#
# Original bug report
#
foreach agg count(*) count_star()
query II
SELECT id, ${agg} OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 0
2 1
NULL 0
endloop
#
# All zeroes
#
foreach agg approx_count_distinct count entropy
query II
SELECT id, ${agg}(id) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 0
2 0
NULL 0
endloop
#
# All NULLs
#
foreach agg any_value arbitrary avg bit_and bit_or bit_xor favg first fsum group_concat histogram kahan_sum kurtosis last listagg mad max mean median min mode product sem skewness stddev stddev_pop stddev_samp sum sumkahan var_pop var_samp variance
query II
SELECT id, ${agg}(id) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
endloop
#
# Custom results
#
# Numeric parameter argument
foreach agg approx_quantile quantile quantile_cont quantile_disc reservoir_quantile
query II
SELECT id, ${agg}(id, 0.5) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
endloop
# Mixed arguments
foreach agg arg_max arg_min argmax argmin max_by min_by
query II
SELECT id, ${agg}(id, ch) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
endloop
foreach agg array_agg list
query II
SELECT id, ${agg}(id) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 [NULL]
NULL NULL
endloop
# Boolean argument
foreach agg bool_and bool_or
query II
SELECT id, ${agg}(id > 0) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
endloop
# Two numeric arguments =>NULL
foreach agg corr covar_pop covar_samp regr_avgx regr_avgy regr_intercept regr_r2 regr_slope regr_sxx regr_sxy regr_syy
query II
SELECT id, ${agg}(id, id) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
endloop
# Two numeric arguments => 0
foreach agg regr_count
query II
SELECT id, ${agg}(id, id) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 0
2 0
NULL 0
endloop
query II
SELECT id, string_agg(id, ' ') OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
query II
SELECT id, bitstring_agg(id, 1, 3) OVER (PARTITION BY ch ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
FROM t1
ORDER BY 1;
----
1 NULL
2 NULL
NULL NULL
|
