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
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
|
from tests.dialects.test_dialect import Validator
class TestPipeSyntax(Validator):
def test_select(self):
self.validate_identity("FROM x", "SELECT * FROM x")
self.validate_identity(
"FROM x |> SELECT x1, x2", "WITH __tmp1 AS (SELECT x1, x2 FROM x) SELECT * FROM __tmp1"
)
self.validate_identity(
"FROM x |> SELECT x.x1, x.x2",
"WITH __tmp1 AS (SELECT x.x1, x.x2 FROM x) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> SELECT x1 as c1, x2 as c2",
"WITH __tmp1 AS (SELECT x1 AS c1, x2 AS c2 FROM x) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> SELECT x1 + 1 as x1_a, x2 - 1 as x2_a |> WHERE x1_a > 1",
"WITH __tmp1 AS (SELECT x1 + 1 AS x1_a, x2 - 1 AS x2_a FROM x) SELECT * FROM __tmp1 WHERE x1_a > 1",
)
self.validate_identity(
"FROM x |> SELECT x1 + 1 as x1_a, x2 - 1 as x2_a |> WHERE x1_a > 1 |> SELECT x2_a",
"WITH __tmp1 AS (SELECT x1 + 1 AS x1_a, x2 - 1 AS x2_a FROM x), __tmp2 AS (SELECT x2_a FROM __tmp1 WHERE x1_a > 1) SELECT * FROM __tmp2",
)
self.validate_identity(
"FROM x |> WHERE x1 > 0 OR x2 > 0 |> WHERE x3 > 1 AND x4 > 1 |> SELECT x1, x4",
"WITH __tmp1 AS (SELECT x1, x4 FROM x WHERE (x1 > 0 OR x2 > 0) AND (x3 > 1 AND x4 > 1)) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> WHERE x1 > 1 |> WHERE x2 > 2 |> SELECT x1 as gt1, x2 as gt2",
"WITH __tmp1 AS (SELECT x1 AS gt1, x2 AS gt2 FROM x WHERE x1 > 1 AND x2 > 2) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> WHERE x1 > 1 AND x2 > 2 |> SELECT x1 as gt1, x2 as gt2 |> SELECT gt1 * 2 + gt2 * 2 AS gt2_2",
"WITH __tmp1 AS (SELECT x1 AS gt1, x2 AS gt2 FROM x WHERE x1 > 1 AND x2 > 2), __tmp2 AS (SELECT gt1 * 2 + gt2 * 2 AS gt2_2 FROM __tmp1) SELECT * FROM __tmp2",
)
self.validate_identity(
"SELECT 1 AS y, 2 AS x |> SELECT x, y",
"WITH __tmp1 AS (SELECT x, y FROM (SELECT 1 AS y, 2 AS x)) SELECT * FROM __tmp1",
)
self.validate_identity(
"SELECT x1, x2, x3 FROM x |> AS a_x |> WHERE a_x.x1 > 0",
"WITH a_x AS (SELECT x1, x2, x3 FROM x) SELECT * FROM a_x WHERE a_x.x1 > 0",
)
self.validate_identity(
"SELECT x,y FROM (SELECT 1 as x, 2 as y) |> SELECT x, y",
"WITH __tmp1 AS (SELECT x, y FROM (SELECT 1 AS x, 2 AS y)) SELECT * FROM __tmp1",
)
self.validate_identity(
"SELECT 'foo1' AS item1, 2 AS item2 UNION ALL SELECT 'foo2' AS item1, 5 AS item2 |> EXTEND SUM(item2) OVER() AS item2_sum",
"WITH __tmp1 AS (SELECT *, SUM(item2) OVER () AS item2_sum FROM (SELECT 'foo1' AS item1, 2 AS item2 UNION ALL SELECT 'foo2' AS item1, 5 AS item2)) SELECT * FROM __tmp1",
)
self.validate_identity(
"SELECT x, x1 FROM (FROM (SELECT 1 as x, 2 as x1) |> AGGREGATE SUM(x1) as xx GROUP BY x,x1) |> SELECT x",
"WITH __tmp2 AS (SELECT x FROM (SELECT * FROM (WITH __tmp1 AS (SELECT SUM(x1) AS xx, x, x1 FROM (SELECT 1 AS x, 2 AS x1) GROUP BY x, x1) SELECT * FROM __tmp1))) SELECT * FROM __tmp2",
)
self.validate_identity(
"FROM (SELECT 1 as x1) AS x |> SELECT x.x1 |> UNION ALL (FROM (SELECT 1 AS c) |> SELECT c) |> SELECT x1",
"SELECT * FROM (WITH __tmp1 AS (SELECT x.x1 FROM (SELECT 1 AS x1) AS x), __tmp3 AS (SELECT * FROM __tmp1), __tmp4 AS (SELECT * FROM __tmp3 UNION ALL SELECT * FROM (WITH __tmp2 AS (SELECT c FROM (SELECT 1 AS c)) SELECT * FROM __tmp2)), __tmp5 AS (SELECT x1 FROM __tmp4) SELECT * FROM __tmp5)",
)
self.validate_identity(
"FROM (SELECT x1 FROM (SELECT 1 as x1) |> SELECT x1) |> SELECT x1",
"SELECT * FROM (WITH __tmp2 AS (SELECT x1 FROM ((WITH __tmp1 AS (SELECT x1 FROM (SELECT 1 AS x1)) SELECT * FROM __tmp1))) SELECT * FROM __tmp2)",
)
self.validate_identity(
"SELECT * FROM (FROM t2 |> SELECT id)",
"SELECT * FROM (WITH __tmp1 AS (SELECT id FROM t2) SELECT * FROM __tmp1)",
)
self.validate_identity(
"SELECT * FROM t1 LEFT JOIN (FROM t2 |> SELECT id) ON TRUE",
"SELECT * FROM t1 LEFT JOIN (WITH __tmp1 AS (SELECT id FROM t2) SELECT * FROM __tmp1) ON TRUE",
)
def test_order_by(self):
self.validate_identity("FROM x |> ORDER BY x1", "SELECT * FROM x ORDER BY x1")
self.validate_identity(
"FROM x |> ORDER BY x1 |> ORDER BY x2", "SELECT * FROM x ORDER BY x2"
)
self.validate_identity(
"FROM x |> ORDER BY x1 |> WHERE x1 > 0 OR x1 != 1 |> ORDER BY x2 |> WHERE x2 > 0 AND x2 != 1 |> SELECT x1, x2",
"WITH __tmp1 AS (SELECT x1, x2 FROM x WHERE (x1 > 0 OR x1 <> 1) AND (x2 > 0 AND x2 <> 1) ORDER BY x2) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> ORDER BY x1 |> WHERE x1 > 0 |> SELECT x1",
"WITH __tmp1 AS (SELECT x1 FROM x WHERE x1 > 0 ORDER BY x1) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> WHERE x1 > 0 |> SELECT x1 |> ORDER BY x1",
"WITH __tmp1 AS (SELECT x1 FROM x WHERE x1 > 0) SELECT * FROM __tmp1 ORDER BY x1",
)
self.validate_identity(
"FROM x |> SELECT x1, x2, x3 |> ORDER BY x1 DESC NULLS FIRST, x2 ASC NULLS LAST, x3",
"WITH __tmp1 AS (SELECT x1, x2, x3 FROM x) SELECT * FROM __tmp1 ORDER BY x1 DESC NULLS FIRST, x2 ASC NULLS LAST, x3",
)
def test_limit(self):
for option in ("LIMIT 1", "LIMIT 1 OFFSET 2"):
with self.subTest(f"Testing pipe syntax LIMIT and OFFSET option: {option}"):
self.validate_identity(f"FROM x |> {option}", f"SELECT * FROM x {option}")
self.validate_identity(f"FROM x |> {option}", f"SELECT * FROM x {option}")
self.validate_identity(
f"FROM x |> {option} |> SELECT x1, x2 |> WHERE x1 > 0 |> WHERE x2 > 0 |> ORDER BY x1, x2",
f"WITH __tmp1 AS (SELECT x1, x2 FROM x {option}) SELECT * FROM __tmp1 WHERE x1 > 0 AND x2 > 0 ORDER BY x1, x2",
)
self.validate_identity(
f"FROM x |> SELECT x1, x2 |> WHERE x1 > 0 |> WHERE x2 > 0 |> ORDER BY x1, x2 |> {option}",
f"WITH __tmp1 AS (SELECT x1, x2 FROM x) SELECT * FROM __tmp1 WHERE x1 > 0 AND x2 > 0 ORDER BY x1, x2 {option}",
)
self.validate_identity(
"FROM x |> SELECT x1, x2 |> LIMIT 2 |> LIMIT 4",
"WITH __tmp1 AS (SELECT x1, x2 FROM x) SELECT * FROM __tmp1 LIMIT 2",
)
self.validate_identity(
"FROM x |> SELECT x1, x2 |> LIMIT 2 OFFSET 2 |> LIMIT 4 OFFSET 2",
"WITH __tmp1 AS (SELECT x1, x2 FROM x) SELECT * FROM __tmp1 LIMIT 2 OFFSET 4",
)
def test_aggregate(self):
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1), MAX(x2), MIN(x3)",
"WITH __tmp1 AS (SELECT SUM(x1), MAX(x2), MIN(x3) FROM x) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1) AS s_x1 |> SELECT s_x1",
"WITH __tmp1 AS (SELECT SUM(x1) AS s_x1 FROM x), __tmp2 AS (SELECT s_x1 FROM __tmp1) SELECT * FROM __tmp2",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1), MAX(x2), MIN(x3) GROUP BY x4, x5",
"WITH __tmp1 AS (SELECT SUM(x1), MAX(x2), MIN(x3), x4, x5 FROM x GROUP BY x4, x5) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1), MAX(x2), MIN(x3) GROUP BY x4 AS a_x4, x5 AS a_x5",
"WITH __tmp1 AS (SELECT SUM(x1), MAX(x2), MIN(x3), x4 AS a_x4, x5 AS a_x5 FROM x GROUP BY a_x4, a_x5) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1) as s_x1 GROUP BY x1 |> SELECT s_x1, x1 as ss_x1",
"WITH __tmp1 AS (SELECT SUM(x1) AS s_x1, x1 FROM x GROUP BY x1), __tmp2 AS (SELECT s_x1, x1 AS ss_x1 FROM __tmp1) SELECT * FROM __tmp2",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1) GROUP",
"WITH __tmp1 AS (SELECT SUM(x1) AS GROUP FROM x) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> AGGREGATE SUM(x1) as s_x1 GROUP BY x2 as g_x2 |> WHERE s_x1 > 0",
"WITH __tmp1 AS (SELECT SUM(x1) AS s_x1, x2 AS g_x2 FROM x GROUP BY g_x2) SELECT * FROM __tmp1 WHERE s_x1 > 0",
)
for order_option in ("ASC", "DESC", "ASC NULLS LAST", "DESC NULLS FIRST"):
with self.subTest(f"Testing pipe syntax AGGREGATE for order option: {order_option}"):
self.validate_all(
f"WITH __tmp1 AS (SELECT SUM(x1) AS x_s FROM x ORDER BY x_s {order_option}) SELECT * FROM __tmp1",
read={
"bigquery": f"FROM x |> AGGREGATE SUM(x1) AS x_s {order_option}",
},
)
self.validate_all(
f"WITH __tmp1 AS (SELECT SUM(x1) AS x_s, x1 AS g_x1 FROM x GROUP BY g_x1 ORDER BY x_s {order_option}) SELECT * FROM __tmp1",
read={
"bigquery": f"FROM x |> AGGREGATE SUM(x1) AS x_s {order_option} GROUP BY x1 AS g_x1",
},
)
with self.subTest(
f"Testing pipe syntax AGGREGATE with GROUP AND ORDER BY for order option: {order_option}"
):
self.validate_all(
f"WITH __tmp1 AS (SELECT SUM(x1) AS x_s, x1 AS g_x1 FROM x GROUP BY g_x1 ORDER BY g_x1 {order_option}), __tmp2 AS (SELECT g_x1, x_s FROM __tmp1) SELECT * FROM __tmp2",
read={
"bigquery": f"FROM x |> AGGREGATE SUM(x1) AS x_s GROUP AND ORDER BY x1 AS g_x1 {order_option} |> SELECT g_x1, x_s",
},
)
def test_set_operators(self):
self.validate_identity(
"FROM x |> SELECT x.x1 |> UNION ALL (SELECT 1 AS c)",
"WITH __tmp1 AS (SELECT x.x1 FROM x), __tmp2 AS (SELECT * FROM __tmp1), __tmp3 AS (SELECT * FROM __tmp2 UNION ALL SELECT 1 AS c) SELECT * FROM __tmp3",
)
for op_operator in (
"UNION ALL",
"UNION DISTINCT",
"INTERSECT DISTINCT",
"EXCEPT DISTINCT",
):
with self.subTest(f"Testing pipe syntax SET OPERATORS: {op_operator}"):
self.validate_all(
f"FROM x|> {op_operator} (SELECT y1 FROM y), (SELECT z1 FROM z)",
write={
"bigquery": f"WITH __tmp1 AS (SELECT * FROM x), __tmp2 AS (SELECT * FROM __tmp1 {op_operator} SELECT y1 FROM y {op_operator} SELECT z1 FROM z) SELECT * FROM __tmp2"
},
)
for op_prefix in ("LEFT OUTER", "FULL OUTER"):
for op_operator in (
"UNION ALL",
"UNION DISTINCT",
"INTERSECT DISTINCT",
"EXCEPT DISTINCT",
):
with self.subTest(f"Testing pipe syntax SET OPERATORS: {op_prefix} {op_operator}"):
self.validate_all(
f"FROM x|> SELECT x1, x2 |> {op_prefix} {op_operator} BY NAME (SELECT y1, y2 FROM y), (SELECT z1, z2 FROM z)",
write={
"bigquery": f"WITH __tmp1 AS (SELECT x1, x2 FROM x), __tmp2 AS (SELECT * FROM __tmp1), __tmp3 AS (SELECT * FROM __tmp2 {op_prefix} {op_operator} BY NAME SELECT y1, y2 FROM y {op_prefix} {op_operator} BY NAME SELECT z1, z2 FROM z) SELECT * FROM __tmp3",
},
)
self.validate_identity(
"FROM d.x |> SELECT x.x1 |> UNION (SELECT 2 AS a1) |> SELECT x1 |> UNION (SELECT 3 as a2) |> SELECT x1 |> WHERE x1 > 100",
"""WITH __tmp1 AS (
SELECT
x.x1
FROM d.x
), __tmp2 AS (
SELECT
*
FROM __tmp1
), __tmp3 AS (
SELECT
*
FROM __tmp2
UNION
SELECT
2 AS a1
), __tmp4 AS (
SELECT
x1
FROM __tmp3
), __tmp5 AS (
SELECT
*
FROM __tmp4
), __tmp6 AS (
SELECT
*
FROM __tmp5
UNION
SELECT
3 AS a2
), __tmp7 AS (
SELECT
x1
FROM __tmp6
)
SELECT
*
FROM __tmp7
WHERE
x1 > 100""",
pretty=True,
)
self.validate_identity(
"FROM c.x |> UNION ALL (SELECT 2 AS a1, '2' as a2) |> AGGREGATE AVG(x1) as m_x1 |> SELECT * |> UNION ALL (SELECT y1 FROM c.y) |> SELECT m_x1",
"""WITH __tmp1 AS (
SELECT
*
FROM c.x
), __tmp2 AS (
SELECT
*
FROM __tmp1
UNION ALL
SELECT
2 AS a1,
'2' AS a2
), __tmp3 AS (
SELECT
AVG(x1) AS m_x1
FROM __tmp2
), __tmp4 AS (
SELECT
*
FROM __tmp3
), __tmp5 AS (
SELECT
*
FROM __tmp4
), __tmp6 AS (
SELECT
*
FROM __tmp5
UNION ALL
SELECT
y1
FROM c.y
), __tmp7 AS (
SELECT
m_x1
FROM __tmp6
)
SELECT
*
FROM __tmp7""",
pretty=True,
)
self.validate_identity(
"FROM c.x |> UNION ALL (SELECT 2 AS a1, '2' as a2) |> UNION ALL (SELECT y1 FROM c.y) |> WHERE x > 200",
"""WITH __tmp1 AS (
SELECT
*
FROM c.x
), __tmp2 AS (
SELECT
*
FROM __tmp1
UNION ALL
SELECT
2 AS a1,
'2' AS a2
), __tmp3 AS (
SELECT
*
FROM __tmp2
), __tmp4 AS (
SELECT
*
FROM __tmp3
UNION ALL
SELECT
y1
FROM c.y
)
SELECT
*
FROM __tmp4
WHERE
x > 200""",
pretty=True,
)
def test_join(self):
self.validate_identity("FROM x |> CROSS JOIN y", "SELECT * FROM x CROSS JOIN y")
for join_type in (
"JOIN",
"INNER JOIN",
"FULL JOIN",
"FULL OUTER JOIN",
"LEFT JOIN",
"LEFT OUTER JOIN",
"RIGHT JOIN",
"RIGHT OUTER JOIN",
):
with self.subTest(f"Testing pipe syntax no projecton with JOIN : {join_type}"):
self.validate_identity(
f"FROM x |> {join_type} y ON x.id = y.id",
f"SELECT * FROM x {join_type} y ON x.id = y.id",
)
with self.subTest(f"Testing pipe syntax projection with JOIN: {join_type}"):
self.validate_identity(
f"FROM x |> SELECT id |> {join_type} y ON x.id = y.id",
f"WITH __tmp1 AS (SELECT id FROM x) SELECT * FROM __tmp1 {join_type} y ON x.id = y.id",
)
with self.subTest(f"Testing pipe syntax complex queries with JOIN: {join_type}"):
self.validate_identity(
f"FROM x |> {join_type} y ON x.id = y.id |> SELECT x1 as a_x1, x2 |> UNION ALL (SELECT 1, 2) |> WHERE a_x1 > 0",
f"""WITH __tmp1 AS (
SELECT
x1 AS a_x1,
x2
FROM x
{join_type} y
ON x.id = y.id
), __tmp2 AS (
SELECT
*
FROM __tmp1
), __tmp3 AS (
SELECT
*
FROM __tmp2
UNION ALL
SELECT
1,
2
)
SELECT
*
FROM __tmp3
WHERE
a_x1 > 0""",
pretty=True,
)
def test_pivot_unpivot(self):
self.validate_identity(
"FROM x |> PIVOT(SUM(x1) FOR quarter IN ('foo1', 'foo2'))",
"WITH __tmp1 AS (SELECT * FROM x PIVOT(SUM(x1) FOR quarter IN ('foo1', 'foo2'))) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> JOIN y on x.id = y.id |> PIVOT(SUM(x1) FOR quarter IN ('foo1', 'foo2'))",
"WITH __tmp1 AS (SELECT * FROM x PIVOT(SUM(x1) FOR quarter IN ('foo1', 'foo2')) JOIN y ON x.id = y.id) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> UNPIVOT(col FOR item IN (foo1, foo2))",
"WITH __tmp1 AS (SELECT * FROM x UNPIVOT(col FOR item IN (foo1, foo2))) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> JOIN y on x.id = y.id |> UNPIVOT(col FOR item IN (foo1, foo2))",
"WITH __tmp1 AS (SELECT * FROM x UNPIVOT(col FOR item IN (foo1, foo2)) JOIN y ON x.id = y.id) SELECT * FROM __tmp1",
)
def test_as(self):
self.validate_identity(
"FROM x |> AS a_x |> WHERE a_x.x1 > 0",
"WITH a_x AS (SELECT * FROM x) SELECT * FROM a_x WHERE a_x.x1 > 0",
)
self.validate_identity(
"FROM x AS t |> AGGREGATE SUM(x1) AS s_x1 GROUP BY id, x2 |> AS t1 |> JOIN y AS t2 ON t1.id = t2.id |> SELECT t2.id, s_x1",
"WITH __tmp1 AS (SELECT SUM(x1) AS s_x1, id, x2 FROM x AS t GROUP BY id, x2), t1 AS (SELECT * FROM __tmp1), __tmp2 AS (SELECT t2.id, s_x1 FROM t1 JOIN y AS t2 ON t1.id = t2.id) SELECT * FROM __tmp2",
)
self.validate_identity(
"FROM x |> JOIN y ON x.x1 = y.y1 |> AS a |> WHERE a.x2 > 1",
"WITH a AS (SELECT * FROM x JOIN y ON x.x1 = y.y1) SELECT * FROM a WHERE a.x2 > 1",
)
def test_extend(self):
self.validate_identity(
"FROM x |> EXTEND id IN (1, 2) AS is_1_2, id + 1 as a_id",
"WITH __tmp1 AS (SELECT *, id IN (1, 2) AS is_1_2, id + 1 AS a_id FROM x) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> SELECT x.x1, x.x2 |> EXTEND x1 + 1 as x1_1, x2 + 1 as x2_1 |> WHERE x1_1 > 0 AND x2_1 > 0",
"WITH __tmp1 AS (SELECT x.x1, x.x2 FROM x), __tmp2 AS (SELECT *, x1 + 1 AS x1_1, x2 + 1 AS x2_1 FROM __tmp1) SELECT * FROM __tmp2 WHERE x1_1 > 0 AND x2_1 > 0",
)
self.validate_identity(
"FROM (SELECT 'foo1' AS item1, 2 AS item2 UNION ALL SELECT 'foo2' AS item1, 5 AS item2) |> EXTEND SUM(item2) OVER() AS item2_sum",
"SELECT * FROM (WITH __tmp1 AS (SELECT *, SUM(item2) OVER () AS item2_sum FROM (SELECT 'foo1' AS item1, 2 AS item2 UNION ALL SELECT 'foo2' AS item1, 5 AS item2)) SELECT * FROM __tmp1)",
)
def test_tablesample(self):
self.validate_identity(
"FROM x |> TABLESAMPLE SYSTEM (1 PERCENT)",
"SELECT * FROM x TABLESAMPLE SYSTEM (1 PERCENT)",
)
self.validate_identity(
"FROM x |> SELECT x.x1 |> TABLESAMPLE SYSTEM (1 PERCENT)",
"WITH __tmp1 AS (SELECT x.x1 FROM x TABLESAMPLE SYSTEM (1 PERCENT)) SELECT * FROM __tmp1",
)
self.validate_identity(
"FROM x |> TABLESAMPLE SYSTEM (1 PERCENT) |> WHERE x.x1 > 0 |> SELECT x1, x2",
"WITH __tmp1 AS (SELECT x1, x2 FROM x WHERE x.x1 > 0 TABLESAMPLE SYSTEM (1 PERCENT)) SELECT * FROM __tmp1",
)
|
