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
|
# name: test/sql/cte/test_cte.test
# description: Test Common Table Expressions (CTE)
# group: [cte]
statement ok
PRAGMA enable_verification
statement ok
create table a(i integer);
statement ok
insert into a values (42);
query I
with cte1 as (Select i as j from a) select * from cte1;
----
42
query I
with cte1 as (Select i as j from a) select x from cte1 t1(x);
----
42
query I
with cte1(xxx) as (Select i as j from a) select xxx from cte1;
----
42
query I
with cte1(xxx) as (Select i as j from a) select x from cte1 t1(x);
----
42
query II
with cte1 as (Select i as j from a), cte2 as (select ref.j as k from cte1 as ref), cte3 as (select ref2.j+1 as i from cte1 as ref2) select * from cte2 , cte3;
----
42 43
query I
with cte1 as (select i as j from a), cte2 as (select ref.j as k from cte1 as ref), cte3 as (select ref2.j+1 as i from cte1 as ref2) select * from cte2 union all select * FROM cte3;
----
42
43
# duplicate CTE alias
statement error
with cte1 as (select 42), cte1 as (select 42) select * FROM cte1;
----
# multiple uses of same CTE
query II
with cte1 as (Select i as j from a) select * from cte1 cte11, cte1 cte12;
----
42 42
# refer to CTE in subquery
query I
with cte1 as (Select i as j from a) select * from cte1 where j = (select max(j) from cte1 as cte2);
----
42
# multi-column name alias
query II
with cte1(x, y) as (select 42 a, 84 b) select zzz, y from cte1 t1(zzz);
----
42 84
# use a CTE in a view definition
statement ok
create view va AS (with cte as (Select i as j from a) select * from cte);
query I
select * from va
----
42
# nested CTE views that re-use CTE aliases
query I
with cte AS (SELECT * FROM va) SELECT * FROM cte;
----
42
# multiple ctes in a view definition
statement ok
create view vb AS (with cte1 as (Select i as j from a), cte2 as (select ref.j+1 as k from cte1 as ref) select * from cte2);
query I
select * from vb
----
43
# cte in set operation node
query I
SELECT 1 UNION ALL (WITH cte AS (SELECT 42) SELECT * FROM cte);
----
1
42
# cte in nested set operation node
query I
SELECT 1 UNION ALL (WITH cte AS (SELECT 42) SELECT * FROM cte UNION ALL SELECT * FROM cte);
----
1
42
42
# cte in recursive cte
query I
WITH RECURSIVE cte(d) AS (
SELECT 1
UNION ALL
(WITH c(d) AS (SELECT * FROM cte)
SELECT d + 1
FROM c
WHERE FALSE
)
)
SELECT max(d) FROM cte;
----
1
# test CTE with nested aliases in where clause
query II
with cte (a) as (
select 1
)
select
a as alias1,
alias1 as alias2
from cte
where alias2 > 0;
----
1 1
# recursive CTE and a non-recursive CTE with except
query I
WITH RECURSIVE t AS (
SELECT 1 AS a
UNION ALL
SELECT a+1
FROM t
WHERE a < 10
), s AS (
(VALUES (5), (6), (7), (8), (9), (10), (11), (12), (13), (42))
EXCEPT
TABLE t
)
SELECT * FROM s AS _(x) ORDER BY x;
----
11
12
13
42
# recursive CTE with except in recursive part (but not as a recursive anchor)
query I
WITH RECURSIVE t AS (
select 1 as a
union all
(select a+1
from t
where a < 10
except
SELECT 4)
), s AS (
(values (5), (6), (7))
except
table t
)
SELECT * FROM s AS _(x) ORDER BY x;
----
5
6
7
query I
WITH RECURSIVE
t(b) AS MATERIALIZED (
(WITH helper(c) AS (
SELECT 5
), h1 AS
(SELECT * FROM helper h
UNION
SELECT 7 FROM helper h)
SELECT * FROM h1)
)
SELECT * FROM t ORDER BY b;
----
5
7
require no_alternative_verify
# FIXME: this one should work with ALTERNATIVE_VERIFY, but doesn't yet
# something wrong with binding a CTE inside a recursive CTE
query I
WITH RECURSIVE
t(b) AS MATERIALIZED (
(WITH helper(c) AS (
SELECT 5
)
SELECT * FROM helper h
UNION
SELECT 7
)
)
SELECT * FROM t ORDER BY b;
----
5
7
# reference to CTE before its actually defined, can't with ALTERNATIVE_VERIFY because everything gets materialized
statement error
with cte3 as (select ref2.j as i from cte1 as ref2), cte1 as (Select i as j from a), cte2 as (select ref.j+1 as k from cte1 as ref) select * from cte2 union all select * FROM cte3;
----
Catalog Error: Table with name cte1 does not exist!
statement error
WITH t(x) AS (SELECT x)
SELECT *
FROM range(10) AS _(x), LATERAL (SELECT * FROM t);
----
statement error
WITH cte AS (SELECT x)
SELECT b.x
FROM (SELECT 1) _(x), LATERAL (SELECT * FROM cte) b(x)
----
query III
WITH RECURSIVE rec(a, b, c) AS (select a,b,c from (values(1,2,3),(1,2,3)) s(a,b,c) union select 1,2,3) select * from rec;
----
1 2 3
|
