/
aggregation.py
173 lines (135 loc) · 5.71 KB
/
aggregation.py
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
173
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
from textwrap import dedent
from materialize.checks.actions import Testdrive
from materialize.checks.checks import Check
class Aggregation(Check):
def initialize(self) -> Testdrive:
return Testdrive(
dedent(
"""
> CREATE SCHEMA aggregation_schema
> CREATE TABLE aggregation_schema.t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER);
> INSERT INTO aggregation_schema.t1 VALUES (1,1,1);
"""
)
)
def manipulate(self) -> list[Testdrive]:
return [
Testdrive(dedent(s))
for s in [
"""
> SET search_path=aggregation_schema;
> CREATE MATERIALIZED VIEW aggregation_schema.order_by1 AS SELECT * FROM t1 ORDER BY f3 DESC, f2 ASC , f1 DESC LIMIT 999999999;
> CREATE MATERIALIZED VIEW aggregation_schema.limit_one1 AS SELECT * FROM t1 LIMIT 1;
> CREATE MATERIALIZED VIEW aggregation_schema.limit_many1 AS SELECT * FROM t1 LIMIT 999999999;
> CREATE MATERIALIZED VIEW aggregation_schema.top_level_distinct1 AS SELECT DISTINCT f1, f2 FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_count1 AS SELECT COUNT(*) FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_aggregation1 AS SELECT COUNT(f1), MIN(f1), MAX(f1), SUM(f1) FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_aggregation_distinct1 AS SELECT COUNT(DISTINCT f1), MIN(DISTINCT f1), MAX(DISTINCT f1), SUM(DISTINCT f1) FROM t1;
> INSERT INTO aggregation_schema.t1 VALUES (2,2,2), (3,3,3), (NULL, NULL, NULL);
""",
"""
> SET search_path=aggregation_schema;
> INSERT INTO aggregation_schema.t1 VALUES (3,3,3), (4,4,4), (NULL, NULL, NULL);
> CREATE MATERIALIZED VIEW aggregation_schema.order_by2 AS SELECT * FROM t1 ORDER BY f3 DESC, f2 ASC , f1 DESC LIMIT 999999999;
> CREATE MATERIALIZED VIEW aggregation_schema.limit_one2 AS SELECT * FROM t1 LIMIT 1;
> CREATE MATERIALIZED VIEW aggregation_schema.limit_many2 AS SELECT * FROM t1 LIMIT 999999999;
> CREATE MATERIALIZED VIEW aggregation_schema.top_level_distinct2 AS SELECT DISTINCT f1, f2 FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_count2 AS SELECT COUNT(*) FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_aggregation2 AS SELECT COUNT(f1), MIN(f1), MAX(f1), SUM(f1) FROM t1;
> CREATE MATERIALIZED VIEW aggregation_schema.global_aggregation_distinct2 AS SELECT COUNT(DISTINCT f1), MIN(DISTINCT f1), MAX(DISTINCT f1), SUM(DISTINCT f1) FROM t1;
> INSERT INTO aggregation_schema.t1 VALUES (5,5,5), (6,6,6), (NULL, NULL, NULL);
""",
]
]
def validate(self) -> Testdrive:
return Testdrive(
dedent(
"""
> SET search_path=aggregation_schema;
> SELECT * FROM order_by1;
1 1 1
2 2 2
3 3 3
3 3 3
4 4 4
5 5 5
6 6 6
<null> <null> <null>
<null> <null> <null>
<null> <null> <null>
> SELECT * FROM limit_one1;
1 1 1
> SELECT * FROM limit_many1;
1 1 1
2 2 2
3 3 3
3 3 3
4 4 4
5 5 5
6 6 6
<null> <null> <null>
<null> <null> <null>
<null> <null> <null>
> SELECT * FROM top_level_distinct1;
1 1
2 2
3 3
4 4
5 5
6 6
<null> <null>
> SELECT * FROM global_count1;
10
> SELECT * FROM global_aggregation1;
7 1 6 24
> SELECT * FROM global_aggregation_distinct1;
6 1 6 21
> SELECT * FROM order_by2;
1 1 1
2 2 2
3 3 3
3 3 3
4 4 4
5 5 5
6 6 6
<null> <null> <null>
<null> <null> <null>
<null> <null> <null>
> SELECT * FROM limit_one2;
1 1 1
> SELECT * FROM limit_many2;
1 1 1
2 2 2
3 3 3
3 3 3
4 4 4
5 5 5
6 6 6
<null> <null> <null>
<null> <null> <null>
<null> <null> <null>
> SELECT * FROM top_level_distinct2;
1 1
2 2
3 3
4 4
5 5
6 6
<null> <null>
> SELECT * FROM global_count2;
10
> SELECT * FROM global_aggregation2;
7 1 6 24
> SELECT * FROM global_aggregation_distinct2;
6 1 6 21
"""
)
)