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
|
# name: test/sql/function/list/aggregates/var_stddev.test
# description: Test the list_var_samp, list_var_pop, list_stddev_pop, list_stddev_samp aggregate functions
# group: [aggregates]
statement ok
create table stddev_test(val integer[])
statement ok
insert into stddev_test values ([42, 43, 42, 1000, NULL, NULL]), ([1, 1, 2, 2, 1, 3]), ([]), ([NULL]), (NULL)
query I
SELECT list_stddev_samp([1])
----
NULL
query I
SELECT list_var_samp([1])
----
NULL
# stddev_samp
query I
select round(list_stddev_samp(val), 1) from stddev_test
----
478.8
0.8
NULL
NULL
NULL
query III
select list_sum(val), round(list_stddev_samp(val), 1), list_min(val) from stddev_test
----
1127 478.8 42
10 0.8 1
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
# stddev_pop
query I
select round(list_stddev_pop(val), 1) from stddev_test
----
414.7
0.7
NULL
NULL
NULL
query III
select list_sum(val), round(list_stddev_pop(val), 1), list_min(val) from stddev_test
----
1127 414.7 42
10 0.7 1
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
# var_samp
query I
select round(list_var_samp(val), 1) from stddev_test
----
229281.6
0.7
NULL
NULL
NULL
query I
select round(list_aggr(val, 'variance'), 1) from stddev_test
----
229281.6
0.7
NULL
NULL
NULL
query III
select list_sum(val), round(list_var_samp(val), 1), list_min(val) from stddev_test
----
1127 229281.6 42
10 0.7 1
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
# var_pop
query I
select round(list_var_pop(val), 1) from stddev_test
----
171961.2
0.6
NULL
NULL
NULL
query III
select list_sum(val), round(list_var_pop(val), 1), list_min(val) from stddev_test
----
1127 171961.2 42
10 0.6 1
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
# stddev_samp
query I
select round(list_aggr(val, 'stddev'), 1) from stddev_test
----
478.8
0.8
NULL
NULL
NULL
query I
select list_aggr([0], 'stddev')
----
NULL
query I
select list_aggr([0, 0], 'stddev')
----
0
statement error
select list_aggr([1e301, -1e301], 'stddev')
----
<REGEX>:.*Out of Range Error.*out of range.*
statement error
select list_var_samp([1e301, -1e301])
----
<REGEX>:.*Out of Range Error.*out of range.*
statement error
select list_var_pop([1e301, -1e301])
----
<REGEX>:.*Out of Range Error.*out of range.*
# incorrect usage
statement error
SELECT list_stddev_samp()
----
<REGEX>:.*Binder Error.*does not support the supplied arguments.*
# stddev_pop unexpectedly does not fetch any rows, test for list_stddev_pop
statement ok
CREATE TABLE t0 (c0 DOUBLE[]);
statement ok
INSERT INTO t0 VALUES([1E200, 0]);
statement error
SELECT list_stddev_pop(c0) FROM t0;
----
<REGEX>:.*Out of Range Error.*out of range.*
|
