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
|
# name: test/sql/subquery/lateral/lateral_join_nested.test_slow
# description: Lateral join inside a lateral join
# group: [lateral]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers(i INTEGER);
statement ok
INSERT INTO integers VALUES (1), (2), (3), (NULL);
# nested correlated lateral joins
query III
SELECT i1.i, SUM(r2.k), COUNT(*) FROM integers i1, LATERAL (SELECT i1.i+i2.i FROM integers i2, LATERAL (SELECT i2.i+i3.i FROM integers i3) r3) r2(k) GROUP BY i1.i ORDER BY i1.i;
----
NULL NULL 16
1 36 16
2 48 16
3 60 16
query III
SELECT i1.i, SUM(r2.k), COUNT(*) FROM integers i1, LATERAL (SELECT i1.i+i2.i FROM integers i2, LATERAL (SELECT i1.i+i2.i+i3.i FROM integers i3) r3) r2(k) GROUP BY i1.i ORDER BY i1.i;
----
NULL NULL 16
1 36 16
2 48 16
3 60 16
# lateral join inside correlated subquery
query I
SELECT (SELECT k+l FROM (SELECT i1.i + 1) t1(k), LATERAL (SELECT k + 1) t2(l)) FROM integers i1 ORDER BY i;
----
NULL
5
7
9
# random stress tests for laterals and subqueries
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT l FROM (SELECT 42) t(l) , (SELECT i * 2) t2(j), (SELECT i + j) t3(k) WHERE k IN (SELECT l*3 FROM (SELECT 42 l) t4(l) WHERE k-i = j));
----
42
query III
SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT 1 WHERE k-i=100) t4(l));
----
42 142 1
query III
SELECT * FROM (SELECT 42) t1(i), (SELECT 22) t2(j), (SELECT 1 WHERE i+j=64) t3(l);
----
42 22 1
query III
SELECT * FROM (SELECT 64) t1(i), (SELECT 22) t2(j), (SELECT 1 WHERE i+j=64) t3(l);
----
query III
SELECT * FROM (SELECT 42) t1(i), (SELECT 22) t2(j), (SELECT i+j WHERE i+j=64) t3(l);
----
42 22 64
query III
SELECT * FROM (SELECT i,j,k FROM (SELECT 42) t(i), (SELECT i * 2) t2(j), (SELECT i + j) t3(k));
----
42 84 126
query III
SELECT * FROM (SELECT i,j,k FROM (SELECT 42) t(i), (SELECT i * 2) t2(j), (SELECT i + j) t3(k));
----
42 84 126
query III
SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT 1 WHERE i+k=0) t4(l));
----
query III
SELECT * FROM (SELECT * FROM (SELECT 42) t1(i), (SELECT 22) t2(j), (SELECT 1 WHERE i+j=64) t3(l));
----
42 22 1
query III
SELECT * FROM (SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT 1 WHERE k-i=100) t4(l)));
----
42 142 1
query III
SELECT * FROM (SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT 1 WHERE i-k=-100) t4(l)));
----
42 142 1
query IIII
SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT * FROM (SELECT 242 l) t4(l), (SELECT 1 WHERE i+l+k=426) t5(m)));
----
42 142 242 1
query I
SELECT i FROM (SELECT 42) t(i) WHERE i*2 IN (SELECT j FROM (SELECT i) t(l) , (SELECT l+i) t2(j), (SELECT i+j) t3(k));
----
42
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT k FROM (SELECT 42) t(k) WHERE k IN (SELECT l FROM (SELECT 42 l) t4(l) WHERE i-k IN (SELECT * FROM (SELECT i+5))));
----
query III
SELECT * FROM (SELECT 42) t(i), (SELECT * FROM (SELECT 142 k) t3(k), (SELECT 1 WHERE i+k=0) t4(l));
----
query II
SELECT * FROM (SELECT 42) t4(m), (SELECT 64) t5(n) WHERE m IN ( SELECT j FROM (SELECT 21*m*n) t(i), (SELECT m) t2(j));
----
42 64
query I
SELECT * FROM (SELECT 42) t4(m) WHERE m IN ( SELECT i FROM (SELECT m) t(i), (SELECT i*m/2 * 2/m) t2(j));
----
42
query I
SELECT * FROM (SELECT 10) t(i) WHERE i IN (SELECT k FROM (SELECT 20) t(k) WHERE k IN (SELECT l FROM (SELECT 30) t4(l) WHERE i-k IN (SELECT * FROM (SELECT i+100))));
----
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT k FROM (SELECT 42) t(k) WHERE k IN (SELECT l FROM (SELECT 42 l) t4(l) WHERE i-k IN (SELECT * FROM (SELECT 0))));
----
42
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT k FROM (SELECT 42) t(k) WHERE k IN (SELECT i FROM (SELECT i) t4 WHERE i*2-k IN (SELECT * FROM (SELECT i))));
----
42
query III
SELECT * FROM (SELECT 42) t1(i), (SELECT 22) t2(j), (SELECT i+j WHERE i+j=(SELECT 64)) t3(l);
----
42 22 64
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT k FROM (SELECT i) t(k) WHERE k = 0);
----
query I
SELECT * FROM (SELECT 42) t(i) WHERE i IN (SELECT l FROM (SELECT 42) t(l) WHERE l IN (SELECT l FROM (SELECT 42 l) t4(l), (SELECT l+5) t5(n) WHERE i+2*l IN (SELECT k FROM (SELECT i+5), (SELECT i * 2) t2(j), (SELECT i + j) t3(k) WHERE k-l IN (SELECT i*2))));
----
42
query I
SELECT (SELECT (SELECT k FROM (SELECT i) t2(j), (SELECT j) t3(k))) FROM (SELECT 42) t(i);
----
42
query I
SELECT i FROM (SELECT 42) t(i) WHERE i IN (SELECT l FROM (SELECT 42) t(l) , (SELECT 42) t2(j), (SELECT i+j+l) t3(k));
----
42
query IIII
SELECT * FROM (SELECT 42) t(i),
(SELECT *
FROM (SELECT 142) t3(y),
(SELECT *
FROM (SELECT 242) t4(z),
(SELECT 58 WHERE i+y+z=426) t5(m)))
WHERE z-y-m IN (SELECT l FROM (SELECT 42) t(l)
WHERE l IN (SELECT l FROM (SELECT 42 l) t4(l),
(SELECT l+5) t5(n)
WHERE i+2*l IN (SELECT k FROM (SELECT i+5),
(SELECT i * 2) t2(j),
(SELECT i + j) t3(k)
WHERE k-l IN (SELECT i*2))
));
----
42 142 242 58
query I
SELECT (SELECT (SELECT k
FROM (SELECT i) t2(j),
(SELECT j) t3(k)))
FROM (SELECT 42) t(i)
WHERE i IN (SELECT l FROM (SELECT 42) t(l)
WHERE l IN (SELECT l FROM (SELECT 42 l) t4(l),
(SELECT l+5) t5(n)
WHERE i+2*l IN (SELECT k FROM (SELECT i+5),
(SELECT i * 2) t2(j),
(SELECT i + j) t3(k)
WHERE k-l IN (SELECT i*2))
));
----
42
query I
SELECT * FROM (SELECT 42) t(i)
WHERE i IN (SELECT l FROM (SELECT 42) t4(l)
WHERE l IN (SELECT l FROM (SELECT (SELECT z-y As l
FROM (SELECT x) t20(y),
(SELECT y+x) t21(z))
FROM (SELECT 42) t22(x)),
(SELECT l+5) t5(n)
WHERE i+2*l IN (SELECT k FROM (SELECT i+5),
(SELECT i * 2) t2(j),
(SELECT i + j) t3(k)
WHERE k-l IN (SELECT i*2))
));
----
42
query I
SELECT (SELECT res.*
FROM (SELECT 1) AS t2(z),
(SELECT (SELECT i
FROM (SELECT MAX(k) FROM generate_series(1,10) t(k)) AS t(i)
GROUP BY i
HAVING (COUNT(*)) = x)
) as res
)
FROM generate_series(1, 5) t(x);
----
10
NULL
NULL
NULL
NULL
statement ok
create view strings as (with recursive t(a) as (select 1 union select a+1 from t where a < 5) select * from t order by a);
query I
select sum(a)
from strings
where a in (select k
from (select 1) t(l) ,
(select 1+l) t2(j),
(select a-j
from strings) t3(k));
----
6
statement ok
create view strings2 as (with recursive t2(b) as (select 0 union select 10*a from strings where a < 5) select * from t2 order by b);
query I
select *
from strings2
where b in (select k
from (select 10) t(l) ,
(select 10+l) t2(j),
(select l+10*a
from strings) t3(k))
group by b
having b%20 == 0
order by b desc;
----
40
20
|
