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
|
# name: test/optimizer/statistics/statistics_aggregate.test
# description: Statistics propagation test with aggregates expression
# group: [statistics]
statement ok
SET default_null_order='nulls_first';
statement ok
CREATE TABLE integers AS SELECT * FROM (VALUES (1), (2), (3)) tbl(i);
statement ok
PRAGMA explain_output = OPTIMIZED_ONLY;
# statistics propagation in groups
# no null values
query II
EXPLAIN SELECT i IS NULL FROM (SELECT i FROM integers GROUP BY i) integers(i);
----
logical_opt <!REGEX>:.*IS_NULL.*
# i=4 is out of range [1,3]
query II
EXPLAIN SELECT i=4 FROM (SELECT i FROM integers GROUP BY i) integers(i);
----
logical_opt <!REGEX>:.*\(i = 4\).*
# i=3 is in range [1,3]
query II
EXPLAIN SELECT i=3 FROM (SELECT i FROM integers GROUP BY i) integers(i);
----
logical_opt <REGEX>:.*\(i = 3\).*
# count without null values
query I
SELECT COUNT(i) FROM integers;
----
3
query II
SELECT i, COUNT(i) FROM integers GROUP BY i ORDER BY i;
----
1 1
2 1
3 1
statement ok
INSERT INTO integers VALUES (NULL);
# count with null values
query I
SELECT COUNT(i) FROM integers;
----
3
query II
SELECT i, COUNT(i) FROM integers GROUP BY i ORDER BY i;
----
NULL 0
1 1
2 1
3 1
# sum
# sum that fits in int64
statement ok
CREATE TABLE bigints AS SELECT i::BIGINT as i FROM (VALUES (1), (2), (3)) tbl(i);
query I
SELECT SUM(i) FROM bigints;
----
6
# avg
query I
SELECT AVG(i) FROM bigints;
----
2
# sum no longer fits in int64
statement ok
INSERT INTO bigints VALUES (9223372036854775806);
query I
SELECT SUM(i) FROM bigints;
----
9223372036854775812
query I
SELECT AVG(i) FROM bigints;
----
2305843009213693952
statement ok
DROP TABLE bigints;
statement ok
CREATE TABLE bigints AS SELECT i::BIGINT as i FROM (VALUES (-1), (-2), (-3)) tbl(i);
query I
SELECT SUM(i) FROM bigints;
----
-6
# avg
query I
SELECT AVG(i) FROM bigints;
----
-2
# sum no longer fits in int64 [negative]
statement ok
INSERT INTO bigints VALUES (-9223372036854775806);
query I
SELECT SUM(i) FROM bigints;
----
-9223372036854775812
query I
SELECT AVG(i) FROM bigints;
----
-2305843009213693952
# now with decimals
# sum that fits in int64
statement ok
CREATE TABLE decimals AS SELECT i::DECIMAL(18,1) as i FROM (VALUES (1), (2), (3)) tbl(i);
query I
SELECT SUM(i) FROM decimals;
----
6.0
# avg
query I
SELECT AVG(i) FROM decimals;
----
2.0
# sum no longer fits in int64
statement ok
INSERT INTO decimals SELECT 99999999999999999.9 FROM repeat(1, 10)
query I
SELECT SUM(i) FROM decimals;
----
1000000000000000005.0
query I
SELECT AVG(i) FROM decimals;
----
76923076923076923
|
