1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test correlated subqueries", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13
14 con.EnableQueryVerification();
15 con.EnableProfiling();
16
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
18 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
19
20 // scalar select with correlation
21 result = con.Query("SELECT i, (SELECT 42+i1.i) AS j FROM integers i1 ORDER BY i;");
22 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
23 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 43, 44, 45}));
24 // ORDER BY correlated subquery
25 result = con.Query("SELECT i FROM integers i1 ORDER BY (SELECT 100-i1.i);");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 3, 2, 1}));
27 // subquery returning multiple results
28 result = con.Query("SELECT i, (SELECT 42+i1.i FROM integers) AS j FROM integers i1 ORDER BY i;");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
30 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 43, 44, 45}));
31 // subquery with LIMIT
32 result = con.Query("SELECT i, (SELECT 42+i1.i FROM integers LIMIT 1) AS j FROM integers i1 ORDER BY i;");
33 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
34 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 43, 44, 45}));
35 // subquery with LIMIT 0
36 result = con.Query("SELECT i, (SELECT 42+i1.i FROM integers LIMIT 0) AS j FROM integers i1 ORDER BY i;");
37 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
38 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
39 // subquery with WHERE clause that is always FALSE
40 result = con.Query("SELECT i, (SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;");
41 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
42 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
43 // correlated EXISTS with WHERE clause that is always FALSE
44 result =
45 con.Query("SELECT i, EXISTS(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
47 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
48 // correlated ANY with WHERE clause that is always FALSE
49 result =
50 con.Query("SELECT i, i=ANY(SELECT i FROM integers WHERE 1=0 AND i1.i=i) AS j FROM integers i1 ORDER BY i;");
51 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
52 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
53 // subquery with OFFSET is not supported
54 REQUIRE_FAIL(
55 con.Query("SELECT i, (SELECT i+i1.i FROM integers LIMIT 1 OFFSET 1) AS j FROM integers i1 ORDER BY i;"));
56 // subquery with ORDER BY is not supported
57 REQUIRE_FAIL(con.Query(
58 "SELECT i, (SELECT i+i1.i FROM integers ORDER BY 1 LIMIT 1 OFFSET 1) AS j FROM integers i1 ORDER BY i;"));
59 // correlated filter without FROM clause
60 result = con.Query("SELECT i, (SELECT 42 WHERE i1.i>2) AS j FROM integers i1 ORDER BY i;");
61 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
62 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 42}));
63 // correlated filter with matching entry on NULL
64 result = con.Query("SELECT i, (SELECT 42 WHERE i1.i IS NULL) AS j FROM integers i1 ORDER BY i;");
65 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
66 REQUIRE(CHECK_COLUMN(result, 1, {42, Value(), Value(), Value()}));
67 // scalar select with correlation in projection
68 result = con.Query("SELECT i, (SELECT i+i1.i FROM integers WHERE i=1) AS j FROM integers i1 ORDER BY i;");
69 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
70 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
71 // scalar select with correlation in filter
72 result = con.Query("SELECT i, (SELECT i FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;");
73 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
74 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
75 // scalar select with operation in projection
76 result = con.Query("SELECT i, (SELECT i+1 FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;");
77 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
78 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
79 // correlated scalar select with constant in projection
80 result = con.Query("SELECT i, (SELECT 42 FROM integers WHERE i=i1.i) AS j FROM integers i1 ORDER BY i;");
81 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
82 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 42, 42, 42}));
83}
84
85TEST_CASE("Test correlated aggregate subqueries", "[subquery]") {
86 unique_ptr<QueryResult> result;
87 DuckDB db(nullptr);
88 Connection con(db);
89
90 con.EnableQueryVerification();
91 con.EnableProfiling();
92
93 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
94 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
95
96 // aggregate with correlation in final projection
97 result = con.Query("SELECT i, (SELECT MIN(i)+i1.i FROM integers) FROM integers i1 ORDER BY i;");
98 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
99 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
100 // aggregate with correlation inside aggregation
101 result = con.Query("SELECT i, (SELECT MIN(i+2*i1.i) FROM integers) FROM integers i1 ORDER BY i;");
102 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
103 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 5, 7}));
104 result =
105 con.Query("SELECT i, SUM(i), (SELECT SUM(i)+SUM(i1.i) FROM integers) FROM integers i1 GROUP BY i ORDER BY i;");
106 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
107 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
108 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 7, 8, 9}));
109 result = con.Query(
110 "SELECT i, SUM(i), (SELECT SUM(i)+COUNT(i1.i) FROM integers) FROM integers i1 GROUP BY i ORDER BY i;");
111 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
112 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
113 REQUIRE(CHECK_COLUMN(result, 2, {6, 7, 7, 7}));
114
115 // correlated COUNT(*)
116 result = con.Query("SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
118 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 1, 0}));
119
120 // aggregate with correlation inside aggregation
121 result = con.Query("SELECT i, (SELECT MIN(i+2*i1.i) FROM integers) FROM integers i1 ORDER BY i;");
122 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
123 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 5, 7}));
124 // aggregate ONLY inside subquery
125 result = con.Query("SELECT (SELECT SUM(i1.i)) FROM integers i1;");
126 REQUIRE(CHECK_COLUMN(result, 0, {6}));
127 // aggregate ONLY inside subquery, with column reference outside of subquery
128 result = con.Query("SELECT FIRST(i), (SELECT SUM(i1.i)) FROM integers i1;");
129 REQUIRE(CHECK_COLUMN(result, 0, {1}));
130 REQUIRE(CHECK_COLUMN(result, 1, {6}));
131 // this will fail, because "i" is not an aggregate but the SUM(i1.i) turns this query into an aggregate
132 REQUIRE_FAIL(con.Query("SELECT i, (SELECT SUM(i1.i)) FROM integers i1;"));
133 REQUIRE_FAIL(con.Query("SELECT i+1, (SELECT SUM(i1.i)) FROM integers i1;"));
134
135 result = con.Query("SELECT MIN(i), (SELECT SUM(i1.i)) FROM integers i1;");
136 REQUIRE(CHECK_COLUMN(result, 0, {1}));
137 REQUIRE(CHECK_COLUMN(result, 1, {6}));
138
139 result = con.Query("SELECT (SELECT SUM(i1.i)), (SELECT SUM(i1.i)) FROM integers i1;");
140 REQUIRE(CHECK_COLUMN(result, 0, {6}));
141 REQUIRE(CHECK_COLUMN(result, 1, {6}));
142
143 // subquery inside aggregation
144 result = con.Query("SELECT SUM(i), SUM((SELECT i FROM integers WHERE i=i1.i)) FROM integers i1;");
145 REQUIRE(CHECK_COLUMN(result, 0, {6}));
146 REQUIRE(CHECK_COLUMN(result, 1, {6}));
147 result = con.Query("SELECT SUM(i), (SELECT SUM(i) FROM integers WHERE i>SUM(i1.i)) FROM integers i1;");
148 REQUIRE(CHECK_COLUMN(result, 0, {6}));
149 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
150 // subquery with aggregation inside aggregation should fail
151 REQUIRE_FAIL(con.Query("SELECT SUM((SELECT SUM(i))) FROM integers"));
152 // aggregate with correlation in filter
153 result = con.Query("SELECT i, (SELECT MIN(i) FROM integers WHERE i>i1.i) FROM integers i1 ORDER BY i;");
154 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
155 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, Value()}));
156 // aggregate with correlation in both filter and projection
157 result = con.Query("SELECT i, (SELECT MIN(i)+i1.i FROM integers WHERE i>i1.i) FROM integers i1 ORDER BY i;");
158 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
159 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 5, Value()}));
160 // aggregate with correlation in GROUP BY
161 result = con.Query("SELECT i, (SELECT MIN(i) FROM integers GROUP BY i1.i) AS j FROM integers i1 ORDER BY i;");
162 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
163 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 1, 1}));
164 // aggregate with correlation in HAVING clause
165 result = con.Query("SELECT i, (SELECT i FROM integers GROUP BY i HAVING i=i1.i) AS j FROM integers i1 ORDER BY i;");
166 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
167 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
168 // correlated subquery in HAVING
169 result = con.Query("SELECT i1.i, SUM(i) FROM integers i1 GROUP BY i1.i HAVING SUM(i)=(SELECT MIN(i) FROM integers "
170 "WHERE i<>i1.i+1) ORDER BY 1;");
171 REQUIRE(CHECK_COLUMN(result, 0, {1}));
172 REQUIRE(CHECK_COLUMN(result, 1, {1}));
173 result = con.Query("SELECT i % 2 AS j, SUM(i) FROM integers i1 GROUP BY j HAVING SUM(i)=(SELECT SUM(i) FROM "
174 "integers WHERE i<>j+1) ORDER BY 1;");
175 REQUIRE(CHECK_COLUMN(result, 0, {1}));
176 REQUIRE(CHECK_COLUMN(result, 1, {4}));
177
178 // aggregate query with non-aggregate subquery without group by
179 result = con.Query("SELECT (SELECT i+SUM(i1.i) FROM integers WHERE i=1 LIMIT 1) FROM integers i1;");
180 REQUIRE(CHECK_COLUMN(result, 0, {7}));
181
182 result = con.Query("SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers) FROM integers i1 ORDER BY 1;");
183 REQUIRE(CHECK_COLUMN(result, 0, {12}));
184 result = con.Query("SELECT (SELECT SUM(i)+SUM((CASE WHEN i IS NOT NULL THEN i*0 ELSE 0 END)+i1.i) FROM integers) "
185 "FROM integers i1 ORDER BY 1;");
186 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 10, 14, 18}));
187
188 // aggregate query with non-aggregate subquery with group by
189 result =
190 con.Query("SELECT i, (SELECT i+SUM(i1.i) FROM integers WHERE i=1) FROM integers i1 GROUP BY i ORDER BY i;");
191 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
192 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4}));
193
194 // subquery inside aggregate
195 result = con.Query("SELECT SUM((SELECT i+i1.i FROM integers WHERE i=1)) FROM integers i1;");
196 REQUIRE(CHECK_COLUMN(result, 0, {9}));
197
198 result =
199 con.Query("SELECT i, SUM(i1.i), (SELECT SUM(i1.i) FROM integers) AS k FROM integers i1 GROUP BY i ORDER BY i;");
200 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
201 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
202 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 1, 2, 3}));
203
204 // aggregation of both entries inside subquery
205 // aggregate on group inside subquery
206 result =
207 con.Query("SELECT i1.i AS j, (SELECT SUM(j+i) FROM integers) AS k FROM integers i1 GROUP BY j ORDER BY j;");
208 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
209 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 9, 12, 15}));
210 result = con.Query("SELECT (SELECT SUM(i1.i*i) FROM integers) FROM integers i1 ORDER BY i;");
211 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 6, 12, 18}));
212 result =
213 con.Query("SELECT i, (SELECT SUM(i1.i)) AS k, (SELECT SUM(i1.i)) AS l FROM integers i1 GROUP BY i ORDER BY i;");
214 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
215 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
216 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 1, 2, 3}));
217 // refer aggregation inside subquery
218 result =
219 con.Query("SELECT i, (SELECT SUM(i1.i)*SUM(i) FROM integers) AS k FROM integers i1 GROUP BY i ORDER BY i;");
220 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
221 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
222 // refer to GROUP BY inside subquery
223 result = con.Query("SELECT i AS j, (SELECT j*SUM(i) FROM integers) AS k FROM integers i1 GROUP BY j ORDER BY j;");
224 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
225 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
226 // refer to GROUP BY without alias but with full name
227 result =
228 con.Query("SELECT i AS j, (SELECT i1.i*SUM(i) FROM integers) AS k FROM integers i1 GROUP BY j ORDER BY j;");
229 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
230 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
231 // perform SUM on subquery
232 result =
233 con.Query("SELECT i, SUM((SELECT SUM(i)*i1.i FROM integers)) AS k FROM integers i1 GROUP BY i ORDER BY i;");
234 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
235 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
236
237 // aggregate subqueries cannot be nested
238 REQUIRE_FAIL(con.Query(
239 "SELECT i, SUM((SELECT SUM(i)*SUM(i1.i) FROM integers)) AS k FROM integers i1 GROUP BY i ORDER BY i;"));
240
241 // aggregation but ONLY inside subquery results in implicit aggregation
242 result = con.Query("SELECT (SELECT SUM(i1.i)) FROM integers i1;");
243 REQUIRE(CHECK_COLUMN(result, 0, {6}));
244 result = con.Query("SELECT FIRST(i), (SELECT SUM(i1.i)) FROM integers i1;");
245 REQUIRE(CHECK_COLUMN(result, 0, {1}));
246 REQUIRE(CHECK_COLUMN(result, 1, {6}));
247
248 // aggregate that uses correlated column in aggregation
249 result = con.Query("SELECT i AS j, (SELECT MIN(i1.i) FROM integers GROUP BY i HAVING i=j) FROM integers i1 GROUP "
250 "BY j ORDER BY j;");
251 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
252 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
253
254 // ORDER BY correlated subquery
255 result = con.Query("SELECT i, SUM(i1.i) FROM integers i1 GROUP BY i ORDER BY (SELECT SUM(i1.i) FROM integers);");
256 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
257 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
258
259 // LIMIT 0 on correlated subquery
260 result = con.Query(
261 "SELECT i, SUM((SELECT SUM(i)*i1.i FROM integers LIMIT 0)) AS k FROM integers i1 GROUP BY i ORDER BY i;");
262 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
263 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
264
265 // GROUP BY correlated subquery
266 result = con.Query(
267 "SELECT (SELECT i+i1.i FROM integers WHERE i=1) AS k, SUM(i) AS j FROM integers i1 GROUP BY k ORDER BY 1;");
268 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 4}));
269 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
270
271 // correlated subquery in WHERE
272 result = con.Query("SELECT SUM(i) FROM integers i1 WHERE i>(SELECT (i+i1.i)/2 FROM integers WHERE i=1);");
273 REQUIRE(CHECK_COLUMN(result, 0, {5}));
274 // correlated aggregate in WHERE
275 result = con.Query("SELECT SUM(i) FROM integers i1 WHERE i>(SELECT (SUM(i)+i1.i)/2 FROM integers WHERE i=1);");
276 REQUIRE(CHECK_COLUMN(result, 0, {5}));
277
278 // use scalar subquery as argument to ALL/ANY
279 result = con.Query("SELECT i, (SELECT MIN(i) FROM integers WHERE i=i1.i) >= ALL(SELECT i FROM integers WHERE i IS "
280 "NOT NULL) FROM integers i1 ORDER BY i;");
281 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
282 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
283 result = con.Query("SELECT i, (SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i IS "
284 "NOT NULL) FROM integers i1 ORDER BY i;");
285 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
286 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, false, false}));
287 result = con.Query("SELECT i, NOT((SELECT MIN(i) FROM integers WHERE i<>i1.i) > ANY(SELECT i FROM integers WHERE i "
288 "IS NOT NULL)) FROM integers i1 ORDER BY i;");
289 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
290 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
291
292 // aggregates with multiple parameters
293 result = con.Query("SELECT (SELECT COVAR_POP(i1.i, i2.i) FROM integers i2) FROM integers i1 ORDER BY 1");
294 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, 0}));
295
296 result = con.Query("SELECT (SELECT COVAR_POP(i2.i, i1.i) FROM integers i2) FROM integers i1 ORDER BY 1");
297 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, 0}));
298
299 result = con.Query("SELECT (SELECT COVAR_POP(i1.i+i2.i, i1.i+i2.i) FROM integers i2) FROM integers i1 ORDER BY 1");
300 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0.666667, 0.666667, 0.666667}));
301}
302
303TEST_CASE("Test correlated EXISTS subqueries", "[subquery]") {
304 unique_ptr<QueryResult> result;
305 DuckDB db(nullptr);
306 Connection con(db);
307
308 con.EnableQueryVerification();
309 con.EnableProfiling();
310
311 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
312 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
313
314 // correlated EXISTS
315 result = con.Query("SELECT i, EXISTS(SELECT i FROM integers WHERE i1.i>2) FROM integers i1 ORDER BY i;");
316 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
317 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, true}));
318 result = con.Query("SELECT i, EXISTS(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
319 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
320 REQUIRE(CHECK_COLUMN(result, 1, {false, true, true, true}));
321 result =
322 con.Query("SELECT i, EXISTS(SELECT i FROM integers WHERE i IS NULL OR i>i1.i*10) FROM integers i1 ORDER BY i;");
323 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
324 REQUIRE(CHECK_COLUMN(result, 1, {true, true, true, true}));
325 result =
326 con.Query("SELECT i, EXISTS(SELECT i FROM integers WHERE i1.i>i OR i1.i IS NULL) FROM integers i1 ORDER BY i;");
327 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
328 REQUIRE(CHECK_COLUMN(result, 1, {true, false, true, true}));
329 result = con.Query("SELECT i FROM integers i1 WHERE EXISTS(SELECT i FROM integers WHERE i=i1.i) ORDER BY i;");
330 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
331 // correlated EXISTS with aggregations
332 result = con.Query("SELECT EXISTS(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;");
333 REQUIRE(CHECK_COLUMN(result, 0, {true}));
334 result = con.Query("SELECT i, SUM(i) FROM integers i1 GROUP BY i HAVING EXISTS(SELECT i FROM integers WHERE "
335 "i>MIN(i1.i)) ORDER BY i;");
336 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
337 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
338 result = con.Query("SELECT EXISTS(SELECT i+MIN(i1.i) FROM integers WHERE i=3) FROM integers i1;");
339 REQUIRE(CHECK_COLUMN(result, 0, {true}));
340 result = con.Query("SELECT EXISTS(SELECT i+MIN(i1.i) FROM integers WHERE i=5) FROM integers i1;");
341 REQUIRE(CHECK_COLUMN(result, 0, {false}));
342 // GROUP BY correlated exists
343 result = con.Query(
344 "SELECT EXISTS(SELECT i FROM integers WHERE i=i1.i) AS g, COUNT(*) FROM integers i1 GROUP BY g ORDER BY g;");
345 REQUIRE(CHECK_COLUMN(result, 0, {false, true}));
346 REQUIRE(CHECK_COLUMN(result, 1, {1, 3}));
347 // SUM on exists
348 result = con.Query(
349 "SELECT SUM(CASE WHEN EXISTS(SELECT i FROM integers WHERE i=i1.i) THEN 1 ELSE 0 END) FROM integers i1;");
350 REQUIRE(CHECK_COLUMN(result, 0, {3}));
351
352 // aggregates with multiple parameters
353 result = con.Query("SELECT (SELECT COVAR_POP(i1.i, i2.i) FROM integers i2) FROM integers i1 ORDER BY 1");
354 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, 0}));
355
356 result = con.Query("SELECT (SELECT COVAR_POP(i2.i, i1.i) FROM integers i2) FROM integers i1 ORDER BY 1");
357 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, 0}));
358
359 result = con.Query("SELECT (SELECT COVAR_POP(i1.i+i2.i, i1.i+i2.i) FROM integers i2) FROM integers i1 ORDER BY 1");
360 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0.666667, 0.666667, 0.666667}));
361
362 result = con.Query("SELECT (SELECT COVAR_POP(i2.i, i2.i) FROM integers i2) FROM integers i1 ORDER BY 1;");
363 REQUIRE(CHECK_COLUMN(result, 0, {0.666667, 0.666667, 0.666667, 0.666667}));
364
365 result = con.Query("SELECT (SELECT COVAR_POP(i1.i, i1.i) FROM integers i2 LIMIT 1) FROM integers i1 ORDER BY 1;");
366 REQUIRE(CHECK_COLUMN(result, 0, {0.666667}));
367}
368
369TEST_CASE("Test correlated ANY/ALL subqueries", "[subquery]") {
370 unique_ptr<QueryResult> result;
371 DuckDB db(nullptr);
372 Connection con(db);
373
374 con.EnableQueryVerification();
375 con.EnableProfiling();
376
377 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
378 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
379
380 // correlated ANY/ALL
381 result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
382 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true, true}));
383 result =
384 con.Query("SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;");
385 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
386 result = con.Query("SELECT i=ALL(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;");
387 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, false}));
388
389 // correlated ANY/ALL
390 result = con.Query("SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i=i1.i) ORDER BY i;");
391 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
392 result = con.Query("SELECT i FROM integers i1 WHERE i<>ANY(SELECT i FROM integers WHERE i=i1.i) ORDER BY i;");
393 REQUIRE(CHECK_COLUMN(result, 0, {}));
394 result = con.Query("SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i;");
395 REQUIRE(CHECK_COLUMN(result, 0, {}));
396 result = con.Query("SELECT i FROM integers i1 WHERE i>ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i;");
397 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
398 result = con.Query(
399 "SELECT i FROM integers i1 WHERE i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) ORDER BY i;");
400 REQUIRE(CHECK_COLUMN(result, 0, {3}));
401 // if there is i=ANY() where the subquery returns an EMPTY result set and i=NULL, the result becomes FALSE instead
402 // of NULL
403 result = con.Query("SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
404 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true, true}));
405 result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
406 REQUIRE(CHECK_COLUMN(result, 0, {false, true, true, true}));
407 result = con.Query("SELECT i<>ALL(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
408 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, false}));
409 result = con.Query("SELECT i<>ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i;");
410 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
411 result = con.Query("SELECT i=ALL(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;");
412 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, false}));
413 result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;");
414 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
415 result = con.Query("SELECT i>ANY(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;");
416 REQUIRE(CHECK_COLUMN(result, 0, {false, false, true, true}));
417 result = con.Query("SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers) FROM integers i1 ORDER BY i;");
418 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, Value()}));
419 result =
420 con.Query("SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;");
421 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, false, true}));
422 result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers i1 ORDER BY i;");
423 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, true, true}));
424 result = con.Query("SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers i1 ORDER BY i;");
425 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
426 // correlated ANY/ALL with aggregations
427 result = con.Query("SELECT MIN(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;");
428 REQUIRE(CHECK_COLUMN(result, 0, {false}));
429 result = con.Query("SELECT SUM(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;");
430 REQUIRE(CHECK_COLUMN(result, 0, {true}));
431 // correlated subquery with correlated any
432 result = con.Query("SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) "
433 "FROM integers i1;");
434 REQUIRE(CHECK_COLUMN(result, 0, {true}));
435 // zero results for all
436 result = con.Query("SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i AND i>10) FROM integers i1 ORDER BY i;");
437 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
438}
439
440TEST_CASE("Test for COUNT(*) and SUM(i) IS NULL in subqueries", "[subquery]") {
441 unique_ptr<QueryResult> result;
442 DuckDB db(nullptr);
443 Connection con(db);
444
445 con.EnableQueryVerification();
446 con.EnableProfiling();
447
448 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
449 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
450
451 // COUNT(*) and SUM(i) IS NULL aggregates
452 result = con.Query("SELECT i, (SELECT i FROM integers i2 WHERE i=(SELECT SUM(i) FROM integers i2 WHERE i2.i>i1.i)) "
453 "FROM integers i1 ORDER BY 1;");
454 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
455 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 3, Value()}));
456 result =
457 con.Query("SELECT i, (SELECT SUM(i) IS NULL FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;");
458 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
459 REQUIRE(CHECK_COLUMN(result, 1, {true, false, false, true}));
460 result = con.Query("SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;");
461 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
462 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 1, 0}));
463 result = con.Query(
464 "SELECT i, (SELECT COUNT(i) FROM integers i2 WHERE i2.i>i1.i OR i2.i IS NULL) FROM integers i1 ORDER BY i;");
465 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
466 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 1, 0}));
467 result = con.Query(
468 "SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i OR i2.i IS NULL) FROM integers i1 ORDER BY i;");
469 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
470 REQUIRE(CHECK_COLUMN(result, 1, {1, 3, 2, 1}));
471 result = con.Query("SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i OR (i1.i IS NULL AND i2.i IS "
472 "NULL)) FROM integers i1 ORDER BY i;");
473 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
474 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 1, 0}));
475 result =
476 con.Query("SELECT i FROM integers i1 WHERE (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i)=0 ORDER BY i;");
477 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 3}));
478 result = con.Query("SELECT i, (SELECT i FROM integers i2 WHERE i-2=(SELECT COUNT(*) FROM integers i2 WHERE "
479 "i2.i>i1.i)) FROM integers i1 ORDER BY 1;");
480 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
481 REQUIRE(CHECK_COLUMN(result, 1, {2, Value(), 3, 2}));
482 result = con.Query(
483 "SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i GROUP BY i1.i) FROM integers i1 ORDER BY i;");
484 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
485 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 1, Value()}));
486 result = con.Query("SELECT i, (SELECT CASE WHEN (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i)=0 THEN 1 ELSE 0 "
487 "END) FROM integers i1 ORDER BY i;");
488 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
489 REQUIRE(CHECK_COLUMN(result, 1, {1, 0, 0, 1}));
490 result = con.Query("SELECT i, (SELECT COUNT(*) FROM integers i2 WHERE i2.i>i1.i) FROM integers i1 ORDER BY i;");
491 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
492 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 1, 0}));
493}
494
495TEST_CASE("Test multiple correlated columns and strings", "[subquery]") {
496 unique_ptr<QueryResult> result;
497 DuckDB db(nullptr);
498 Connection con(db);
499
500 con.EnableQueryVerification();
501 con.EnableProfiling();
502
503 // multiple correlated columns and strings
504 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER, str VARCHAR);"));
505 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1, 'a'), (12, 2, 'b'), (13, 3, 'c')"));
506
507 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER, c INTEGER, str2 VARCHAR);"));
508 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (11, 1, 'a'), (12, 1, 'b'), (13, 4, 'b')"));
509
510 result = con.Query("SELECT a, SUM(a), (SELECT SUM(a)+SUM(t1.b) FROM test) FROM test t1 GROUP BY a ORDER BY a;");
511 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
512 REQUIRE(CHECK_COLUMN(result, 1, {11, 12, 13}));
513 REQUIRE(CHECK_COLUMN(result, 2, {37, 38, 39}));
514
515 // scalar query with multiple correlated columns
516 result = con.Query("SELECT (SELECT test.a+test.b+SUM(test2.a) FROM test2 WHERE str=str2) FROM test ORDER BY 1;");
517 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 23, 39}));
518
519 // exists with multiple correlated columns
520 result = con.Query("SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 "
521 "WHERE test.a=test2.a AND test.b<>test2.c);");
522 REQUIRE(CHECK_COLUMN(result, 0, {12, 13}));
523 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
524 REQUIRE(CHECK_COLUMN(result, 2, {"b", "c"}));
525
526 // ANY with multiple correlated columns
527 result = con.Query("SELECT a, a>=ANY(SELECT test2.a+c-b FROM test2 WHERE c>=b AND str=str2) FROM test ORDER BY 1;");
528 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
529 REQUIRE(CHECK_COLUMN(result, 1, {true, false, false}));
530
531 // string comparison
532 result = con.Query("SELECT str, str=ANY(SELECT str2 FROM test2) FROM test");
533 REQUIRE(CHECK_COLUMN(result, 0, {"a", "b", "c"}));
534 REQUIRE(CHECK_COLUMN(result, 1, {true, true, false}));
535 result = con.Query("SELECT str, str=ANY(SELECT str2 FROM test2 WHERE test.a<>test2.a) FROM test");
536 REQUIRE(CHECK_COLUMN(result, 0, {"a", "b", "c"}));
537 REQUIRE(CHECK_COLUMN(result, 1, {false, true, false}));
538}
539
540TEST_CASE("Test complex correlated subqueries", "[subquery]") {
541 unique_ptr<QueryResult> result;
542 DuckDB db(nullptr);
543 Connection con(db);
544
545 con.EnableQueryVerification();
546 con.EnableProfiling();
547
548 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
549 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
550
551 // correlated expression in subquery
552 result = con.Query(
553 "SELECT i, (SELECT s1.i FROM (SELECT * FROM integers WHERE i=i1.i) s1) AS j FROM integers i1 ORDER BY i;");
554 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
555 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
556 // join on two subqueries that both have a correlated expression in them
557 result = con.Query("SELECT i, (SELECT s1.i FROM (SELECT i FROM integers WHERE i=i1.i) s1 INNER JOIN (SELECT i FROM "
558 "integers WHERE i=4-i1.i) s2 ON s1.i>s2.i) AS j FROM integers i1 ORDER BY i;");
559 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
560 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 3}));
561
562 // implicit join with correlated expression in filter
563 result = con.Query("SELECT i, (SELECT s1.i FROM integers s1, integers s2 WHERE s1.i=s2.i AND s1.i=4-i1.i) AS j "
564 "FROM integers i1 ORDER BY i;");
565 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
566 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 2, 1}));
567 // join with a correlated expression in the join condition
568 result = con.Query("SELECT i, (SELECT s1.i FROM integers s1 INNER JOIN integers s2 ON s1.i=s2.i AND s1.i=4-i1.i) "
569 "AS j FROM integers i1 ORDER BY i;");
570 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
571 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 2, 1}));
572 // inner join on correlated subquery
573 result = con.Query("SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i FROM "
574 "integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;");
575 REQUIRE(CHECK_COLUMN(result, 0, {1}));
576 REQUIRE(CHECK_COLUMN(result, 1, {2}));
577 // inner join on non-equality subquery
578 result = con.Query("SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=s2.i) ORDER BY s1.i;");
579 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
580 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
581 result = con.Query("SELECT * FROM integers s1 INNER JOIN integers s2 ON (SELECT s1.i=i FROM integers WHERE s2.i=i) "
582 "ORDER BY s1.i;");
583 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
584 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
585 // left outer join on correlated subquery
586 result = con.Query("SELECT * FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT 2*SUM(i)*s1.i FROM "
587 "integers)=(SELECT SUM(i)*s2.i FROM integers) ORDER BY s1.i;");
588 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
589 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, Value(), Value()}));
590
591 // left outer join in correlated expression
592 REQUIRE_FAIL(con.Query("SELECT i, (SELECT SUM(s1.i) FROM integers s1 LEFT OUTER JOIN integers s2 ON s1.i=s2.i OR "
593 "s1.i=i1.i-1) AS j FROM integers i1 ORDER BY i;"));
594 // REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
595 // REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 9, 12}));
596 // full outer join: both sqlite and postgres actually cannot run this one
597 REQUIRE_FAIL(con.Query("SELECT i, (SELECT SUM(s1.i) FROM integers s1 FULL OUTER JOIN integers s2 ON s1.i=s2.i OR "
598 "s1.i=i1.i-1) AS j FROM integers i1 ORDER BY i;"));
599 // REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
600 // REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 9, 12}));
601
602 // correlated expression inside window function not supported
603 REQUIRE_FAIL(con.Query("SELECT i, (SELECT row_number() OVER (ORDER BY i)) FROM integers i1 ORDER BY i;"));
604
605 // union with correlated expression
606 result = con.Query("SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers WHERE i=i1.i) AS j "
607 "FROM integers i1 ORDER BY i;");
608 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
609 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
610 // except with correlated expression
611 result = con.Query("SELECT i, (SELECT i FROM integers WHERE i IS NOT NULL EXCEPT SELECT i FROM integers WHERE "
612 "i<>i1.i) AS j FROM integers i1 WHERE i IS NOT NULL ORDER BY i;");
613 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
614 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
615 // intersect with correlated expression
616 result = con.Query("SELECT i, (SELECT i FROM integers WHERE i=i1.i INTERSECT SELECT i FROM integers WHERE i=i1.i) "
617 "AS j FROM integers i1 ORDER BY i;");
618 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
619 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
620 // multiple setops
621 result = con.Query("SELECT i, (SELECT i FROM integers WHERE i=i1.i UNION SELECT i FROM integers WHERE i<>i1.i "
622 "EXCEPT SELECT i FROM integers WHERE i<>i1.i) AS j FROM integers i1 ORDER BY i;");
623 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
624 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
625
626 // uncorrelated query inside correlated query
627 result = con.Query("SELECT i, (SELECT (SELECT SUM(i) FROM integers)+42+i1.i) AS j FROM integers i1 ORDER BY i;");
628 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
629 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 49, 50, 51}));
630}
631
632TEST_CASE("Test window functions in correlated subqueries", "[subquery]") {
633 unique_ptr<QueryResult> result;
634 DuckDB db(nullptr);
635 Connection con(db);
636
637 con.EnableQueryVerification();
638 con.EnableProfiling();
639
640 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
641 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
642
643 // window functions in correlated subquery
644 result = con.Query(
645 "SELECT i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM integers i1 ORDER BY i;");
646 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
647 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 1, 1}));
648 result = con.Query("SELECT i1.i, (SELECT rank() OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM integers i1, "
649 "integers i2 ORDER BY i1.i;");
650 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value(), 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3}));
651 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value(), 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
652 result = con.Query("SELECT i1.i, (SELECT row_number() OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM integers "
653 "i1, integers i2 ORDER BY i1.i;");
654 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value(), 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3}));
655 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value(), 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
656 result = con.Query(
657 "SELECT i, (SELECT SUM(i) OVER (ORDER BY i) FROM integers WHERE i1.i=i) FROM integers i1 ORDER BY i;");
658 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
659 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
660 result = con.Query("SELECT i, (SELECT SUM(s1.i) OVER (ORDER BY s1.i) FROM integers s1, integers s2 WHERE i1.i=s1.i "
661 "LIMIT 1) FROM integers i1 ORDER BY i;");
662 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
663 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 4, 8, 12}));
664}
665
666TEST_CASE("Test nested correlated subqueries", "[subquery]") {
667 unique_ptr<QueryResult> result;
668 DuckDB db(nullptr);
669 Connection con(db);
670
671 con.EnableQueryVerification();
672 con.EnableProfiling();
673
674 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
675 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
676
677 // nested correlated queries
678 result = con.Query("SELECT i, (SELECT (SELECT 42+i1.i)+42+i1.i) AS j FROM integers i1 ORDER BY i;");
679 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
680 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 86, 88, 90}));
681 result = con.Query("SELECT i, (SELECT (SELECT (SELECT (SELECT 42+i1.i)++i1.i)+42+i1.i)+42+i1.i) AS j FROM integers "
682 "i1 ORDER BY i;");
683 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
684 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 130, 134, 138}));
685 result = con.Query("SELECT i, (SELECT (SELECT i1.i+SUM(i2.i)) FROM integers i2) AS j FROM integers i1 ORDER BY i;");
686 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
687 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 7, 8, 9}));
688 // correlated query inside uncorrelated query
689 result = con.Query(
690 "SELECT i, (SELECT (SELECT (SELECT (SELECT i1.i+i1.i+i1.i+i1.i+i1.i)))) AS j FROM integers i1 ORDER BY i;");
691 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
692 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 5, 10, 15}));
693 result = con.Query("SELECT i, (SELECT SUM(i)+(SELECT 42+i1.i) FROM integers) AS j FROM integers i1 ORDER BY i;");
694 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
695 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 49, 50, 51}));
696 result = con.Query(
697 "SELECT i, (SELECT ((SELECT ((SELECT ((SELECT SUM(i)+SUM(i4.i)+SUM(i3.i)+SUM(i2.i)+SUM(i1.i) FROM integers "
698 "i5)) FROM integers i4)) FROM integers i3)) FROM integers i2) AS j FROM integers i1 GROUP BY i ORDER BY i;");
699 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
700 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 25, 26, 27}));
701 result = con.Query("SELECT i, (SELECT (SELECT (SELECT (SELECT i1.i+i1.i+i1.i+i1.i+i1.i+i2.i) FROM integers i2 "
702 "WHERE i2.i=i1.i))) AS j FROM integers i1 ORDER BY i;");
703 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
704 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 12, 18}));
705 result = con.Query("SELECT (SELECT (SELECT SUM(i1.i)+SUM(i2.i)+SUM(i3.i) FROM integers i3) FROM integers i2) FROM "
706 "integers i1 ORDER BY 1");
707 REQUIRE(CHECK_COLUMN(result, 0, {18}));
708
709 // explicit join on subquery
710 result = con.Query("SELECT i, (SELECT SUM(s1.i) FROM integers s1 INNER JOIN integers s2 ON (SELECT "
711 "i1.i+s1.i)=(SELECT i1.i+s2.i)) AS j FROM integers i1 ORDER BY i;");
712 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
713 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6, 6, 6}));
714 // nested aggregate queries
715 result = con.Query("SELECT i, SUM(i), (SELECT (SELECT SUM(i)+SUM(i1.i)+SUM(i2.i) FROM integers) FROM integers i2) "
716 "FROM integers i1 GROUP BY i ORDER BY i;");
717 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
718 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
719 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 13, 14, 15}));
720
721 // correlated ANY inside subquery
722 result = con.Query("SELECT i, (SELECT SUM(ss1.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM "
723 "integers WHERE i<>s1.i)) ss1) AS j FROM integers i1 ORDER BY i;");
724 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
725 REQUIRE(CHECK_COLUMN(result, 1, {5, 5, 5, 5}));
726 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i AND i=ANY(SELECT i "
727 "FROM integers WHERE i=s1.i)) ss2) AS j FROM integers i1 ORDER BY i;");
728 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
729 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
730
731 // left outer join on correlated subquery within subquery
732 // not supported yet: left outer join on JoinSide::BOTH
733 REQUIRE_FAIL(con.Query("SELECT i, (SELECT SUM(s1.i) FROM integers s1 LEFT OUTER JOIN integers s2 ON (SELECT "
734 "i1.i+s1.i)=(SELECT i1.i+s2.i)) AS j FROM integers i1 ORDER BY i;"));
735 // REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
736 // REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 6, 6}));
737 result =
738 con.Query("SELECT i, (SELECT SUM(ss1.i)+SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM "
739 "integers WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE i=ANY(SELECT i FROM "
740 "integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
741 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
742 REQUIRE(CHECK_COLUMN(result, 1, {10, 10, 10, 10}));
743 // left outer join with correlation on LHS
744 result = con.Query("SELECT i, (SELECT SUM(s1.i) FROM (SELECT i FROM integers WHERE i=i1.i) s1 LEFT OUTER JOIN "
745 "integers s2 ON s1.i=s2.i) AS j FROM integers i1 ORDER BY i;");
746 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
747 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
748 result = con.Query("SELECT i, (SELECT SUM(s1.i) FROM (SELECT i FROM integers WHERE i<>i1.i) s1 LEFT OUTER JOIN "
749 "integers s2 ON s1.i=s2.i) AS j FROM integers i1 ORDER BY i;");
750 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
751 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 5, 4, 3}));
752 // left outer join with correlation on RHS
753 result = con.Query("SELECT i, (SELECT SUM(s2.i) FROM integers s1 LEFT OUTER JOIN (SELECT i FROM integers WHERE "
754 "i=i1.i) s2 ON s1.i=s2.i) AS j FROM integers i1 ORDER BY i;");
755 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
756 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
757 result = con.Query("SELECT i, (SELECT SUM(s2.i) FROM integers s1 LEFT OUTER JOIN (SELECT i FROM integers WHERE "
758 "i<>i1.i) s2 ON s1.i=s2.i) AS j FROM integers i1 ORDER BY i;");
759 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
760 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 5, 4, 3}));
761
762 result = con.Query(
763 "SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE CASE WHEN (i=i1.i AND i=ANY(SELECT i FROM "
764 "integers WHERE i=s1.i)) THEN true ELSE false END) ss2) AS j FROM integers i1 ORDER BY i;");
765 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
766 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
767 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i AND i=ANY(SELECT i "
768 "FROM integers WHERE i=s1.i)) ss2) AS j FROM integers i1 ORDER BY i;");
769 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
770 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
771
772 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i) ss2) AS j FROM "
773 "integers i1 ORDER BY i;");
774 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
775 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
776 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=ANY(SELECT i FROM "
777 "integers WHERE i=s1.i)) ss2) AS j FROM integers i1 ORDER BY i;");
778 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
779 REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 6, 6}));
780 result = con.Query("SELECT i, (SELECT i=ANY(SELECT i FROM integers WHERE i=s1.i) FROM integers s1 WHERE i=i1.i) AS "
781 "j FROM integers i1 ORDER BY i;");
782 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
783 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, true}));
784 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i OR i=ANY(SELECT i "
785 "FROM integers WHERE i=s1.i)) ss2) AS j FROM integers i1 ORDER BY i;");
786 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
787 REQUIRE(CHECK_COLUMN(result, 1, {6, 6, 6, 6}));
788 result = con.Query(
789 "SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE CASE WHEN (i=i1.i AND i=ANY(SELECT i FROM "
790 "integers WHERE i=s1.i)) THEN true ELSE false END) ss2) AS j FROM integers i1 ORDER BY i;");
791 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
792 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
793 result = con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i AND EXISTS(SELECT i "
794 "FROM integers WHERE i=s1.i)) ss2) AS j FROM integers i1 ORDER BY i;");
795 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
796 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3}));
797
798 // complex left outer join with correlation on RHS
799 result = con.Query("SELECT i, (SELECT SUM(ss1.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM "
800 "integers WHERE i<>s1.i)) ss1) AS j FROM integers i1 ORDER BY i;");
801 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
802 REQUIRE(CHECK_COLUMN(result, 1, {5, 5, 5, 5}));
803 result =
804 con.Query("SELECT i, (SELECT SUM(ss1.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM integers "
805 "WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE i=i1.i AND i=ANY(SELECT i FROM "
806 "integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
807 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
808 REQUIRE(CHECK_COLUMN(result, 1, {5, 5, 5, 5}));
809 result =
810 con.Query("SELECT i, (SELECT SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM integers "
811 "WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE i=i1.i AND i=ANY(SELECT i FROM "
812 "integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
813 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
814 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 2, 3}));
815 result =
816 con.Query("SELECT i, (SELECT SUM(ss1.i)+SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i>ANY(SELECT i FROM "
817 "integers WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE i=i1.i AND "
818 "i=ANY(SELECT i FROM integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
819 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
820 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 7, 8}));
821 // complex left outer join with correlation on LHS
822 result =
823 con.Query("SELECT i, (SELECT SUM(ss1.i)+SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i AND "
824 "i>ANY(SELECT i FROM integers WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE "
825 "i=ANY(SELECT i FROM integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
826 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
827 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 4, 6}));
828 // complex left outer join with correlation on both sides
829 result = con.Query(
830 "SELECT i, (SELECT SUM(ss1.i)+SUM(ss2.i) FROM (SELECT i FROM integers s1 WHERE i=i1.i AND i>ANY(SELECT i FROM "
831 "integers WHERE i<>s1.i)) ss1 LEFT OUTER JOIN (SELECT i FROM integers s1 WHERE i<>i1.i OR i=ANY(SELECT i FROM "
832 "integers WHERE i=s1.i)) ss2 ON ss1.i=ss2.i) AS j FROM integers i1 ORDER BY i;");
833 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
834 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), 4, 6}));
835 // test correlated queries with correlated expressions inside FROM clause
836 // subquery
837 result = con.Query("SELECT i, (SELECT * FROM (SELECT (SELECT 42+i1.i)) s1) AS j FROM integers i1 ORDER BY i;");
838 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
839 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 43, 44, 45}));
840 // cross product
841 result = con.Query("SELECT i, (SELECT s1.k+s2.k FROM (SELECT (SELECT 42+i1.i) AS k) s1, (SELECT (SELECT 42+i1.i) "
842 "AS k) s2) AS j FROM integers i1 ORDER BY i;");
843 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
844 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 86, 88, 90}));
845 // join
846 result = con.Query("SELECT i, (SELECT s1.k+s2.k FROM (SELECT (SELECT 42+i1.i) AS k) s1 LEFT OUTER JOIN (SELECT "
847 "(SELECT 42+i1.i) AS k) s2 ON s1.k=s2.k) AS j FROM integers i1 ORDER BY i;");
848 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
849 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 86, 88, 90}));
850
851 // IN list inside correlated subquery
852 result = con.Query("SELECT i, (SELECT i1.i IN (1, 2, 3, 4, 5, 6, 7, 8)) AS j FROM integers i1 ORDER BY i;");
853 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
854 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, true}));
855
856 // nested correlated subqueries with multiple aggregate parameters
857 result = con.Query("SELECT (SELECT (SELECT COVAR_POP(i1.i, i3.i) FROM integers i3) FROM integers i2 LIMIT 1) FROM "
858 "integers i1 ORDER BY 1");
859 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, 0}));
860
861 result = con.Query("SELECT (SELECT (SELECT COVAR_POP(i2.i, i3.i) FROM integers i3) FROM integers i2 LIMIT 1) FROM "
862 "integers i1 ORDER BY 1");
863 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0}));
864}
865
866TEST_CASE("Test varchar correlated subqueries", "[subquery]") {
867 unique_ptr<QueryResult> result;
868 DuckDB db(nullptr);
869 Connection con(db);
870
871 con.EnableQueryVerification();
872 con.EnableProfiling();
873 // varchar tests
874 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(v VARCHAR)"));
875 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello'), ('world'), (NULL)"));
876 // ANY
877 result = con.Query("SELECT NULL IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY v");
878 REQUIRE(CHECK_COLUMN(result, 0, {false, Value(), Value()}));
879 result = con.Query("SELECT 3 IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY v");
880 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false}));
881 result = con.Query("SELECT 'hello' IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY v");
882 REQUIRE(CHECK_COLUMN(result, 0, {false, true, false}));
883 result = con.Query("SELECT 'bla' IN (SELECT * FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY v");
884 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false}));
885 result =
886 con.Query("SELECT 'hello' IN (SELECT * FROM strings WHERE v=s1.v or v IS NULL) FROM strings s1 ORDER BY v");
887 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, Value()}));
888 result = con.Query("SELECT 'bla' IN (SELECT * FROM strings WHERE v=s1.v or v IS NULL) FROM strings s1 ORDER BY v");
889 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
890 // EXISTS
891 result = con.Query("SELECT * FROM strings WHERE EXISTS(SELECT NULL, v) ORDER BY v");
892 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "hello", "world"}));
893 result =
894 con.Query("SELECT * FROM strings s1 WHERE EXISTS(SELECT v FROM strings WHERE v=s1.v OR v IS NULL) ORDER BY v");
895 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "hello", "world"}));
896 result = con.Query("SELECT * FROM strings s1 WHERE EXISTS(SELECT v FROM strings WHERE v=s1.v) ORDER BY v");
897 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world"}));
898 // // scalar query
899 result = con.Query("SELECT (SELECT v FROM strings WHERE v=s1.v) FROM strings s1 ORDER BY v");
900 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "hello", "world"}));
901 result = con.Query(
902 "SELECT (SELECT v FROM strings WHERE v=s1.v OR (v='hello' AND s1.v IS NULL)) FROM strings s1 ORDER BY v");
903 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "hello", "world"}));
904}
905
906TEST_CASE("Test correlated subqueries based on TPC-DS", "[subquery]") {
907 unique_ptr<QueryResult> result;
908 DuckDB db(nullptr);
909 Connection con(db);
910 con.EnableQueryVerification();
911
912 REQUIRE_NO_FAIL(con.Query("CREATE TABLE item(i_manufact INTEGER)"));
913
914 REQUIRE_NO_FAIL(con.Query(
915 "SELECT * FROM item i1 WHERE (SELECT count(*) AS item_cnt FROM item WHERE (i_manufact = i1.i_manufact AND "
916 "i_manufact=3) OR (i_manufact = i1.i_manufact AND i_manufact=3)) > 0 ORDER BY 1 LIMIT 100;"));
917 REQUIRE_NO_FAIL(con.Query(
918 "SELECT * FROM item i1 WHERE (SELECT count(*) AS item_cnt FROM item WHERE (i_manufact = i1.i_manufact AND "
919 "i_manufact=3) OR (i_manufact = i1.i_manufact AND i_manufact=3)) ORDER BY 1 LIMIT 100;"));
920}
921
922TEST_CASE("Test correlated subquery with grouping columns", "[subquery]") {
923 unique_ptr<QueryResult> result;
924 DuckDB db(nullptr);
925 Connection con(db);
926 con.EnableQueryVerification();
927
928 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64), Product_Name "
929 "varchar(64), TotalSales int); "));
930 REQUIRE_NO_FAIL(con.Query(
931 "CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6 INT, col7 INT, col8 INT);"));
932 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO "
933 "Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);"));
934 REQUIRE_NO_FAIL(con.Query("INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88), "
935 "(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);"));
936
937 result = con.Query("SELECT col1 IN (SELECT ColID FROM tbl_ProductSales) FROM another_T;");
938 REQUIRE(CHECK_COLUMN(result, 0, {true, false, false, false}));
939 result = con.Query("SELECT col1 IN (SELECT ColID + col1 FROM tbl_ProductSales) FROM another_T;");
940 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
941 result = con.Query("SELECT col1 IN (SELECT ColID + col1 FROM tbl_ProductSales) FROM another_T GROUP BY col1;");
942 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
943 result =
944 con.Query("SELECT col1 IN (SELECT ColID + another_T.col1 FROM tbl_ProductSales) FROM another_T GROUP BY col1;");
945 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
946 result = con.Query(
947 "SELECT (col1 + 1) AS k, k IN (SELECT ColID + k FROM tbl_ProductSales) FROM another_T GROUP BY k ORDER BY 1;");
948 REQUIRE(CHECK_COLUMN(result, 0, {2, 12, 112, 1112}));
949 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
950 result = con.Query(
951 "SELECT (col1 + 1) IN (SELECT ColID + (col1 + 1) FROM tbl_ProductSales) FROM another_T GROUP BY (col1 + 1);");
952 REQUIRE(CHECK_COLUMN(result, 0, {false, false, false, false}));
953
954 // this should fail, col1 + 42 is not a grouping column
955 REQUIRE_FAIL(con.Query("SELECT col1+1, col1+42 FROM another_T GROUP BY col1+1;"));
956 // this should also fail, col1 + 42 is not a grouping column
957 REQUIRE_FAIL(con.Query(
958 "SELECT (col1 + 1) IN (SELECT ColID + (col1 + 42) FROM tbl_ProductSales) FROM another_T GROUP BY (col1 + 1);"));
959
960 // having without GROUP BY in subquery
961 result = con.Query("SELECT col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MIN(col8) IS NULL) FROM another_T "
962 "GROUP BY col1, col2, col5, col8;");
963 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true, true}));
964 result = con.Query("SELECT CASE WHEN 1 IN (SELECT MAX(col7) UNION ALL (SELECT MIN(ColID) FROM tbl_ProductSales "
965 "INNER JOIN another_T t2 ON t2.col5 = t2.col1)) THEN 2 ELSE NULL END FROM another_T t1;");
966 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
967 result = con.Query("SELECT CASE WHEN 1 IN (SELECT (SELECT MAX(col7))) THEN 2 ELSE NULL END FROM another_T t1;");
968 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
969 // UNION ALL with correlated subquery on either side
970 result =
971 con.Query("SELECT CASE WHEN 1 IN (SELECT (SELECT MAX(col7)) UNION ALL (SELECT MIN(ColID) FROM tbl_ProductSales "
972 "INNER JOIN another_T t2 ON t2.col5 = t2.col1)) THEN 2 ELSE NULL END FROM another_T t1;");
973 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
974 result = con.Query("SELECT CASE WHEN 1 IN (SELECT (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 "
975 "ON t2.col5 = t2.col1) UNION ALL (SELECT MAX(col7))) THEN 2 ELSE NULL END FROM another_T t1;");
976 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
977
978 // correlated column comparison with correlated subquery
979 result = con.Query("SELECT (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col7 <> (SELECT "
980 "MAX(t1.col1 + t3.col4) FROM another_T t3)) FROM another_T t1;");
981 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1}));
982 result = con.Query("SELECT (SELECT MIN(ColID) FROM tbl_ProductSales INNER JOIN another_T t2 ON t1.col7 <> "
983 "ANY(SELECT MAX(t1.col1 + t3.col4) FROM another_T t3)) FROM another_T t1;");
984 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1}));
985
986 // LEFT JOIN between correlated columns not supported for now
987 REQUIRE_FAIL(con.Query(
988 "SELECT CASE WHEN NOT col1 NOT IN (SELECT (SELECT MAX(col7)) UNION (SELECT MIN(ColID) FROM tbl_ProductSales "
989 "LEFT JOIN another_T t2 ON t2.col5 = t1.col1)) THEN 1 ELSE 2 END FROM another_T t1 GROUP BY col1 ORDER BY 1;"));
990 // REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 2, 2}));
991
992 // correlated columns in window functions not supported yet
993 REQUIRE_FAIL(con.Query("SELECT EXISTS (SELECT RANK() OVER (PARTITION BY SUM(DISTINCT col5))) FROM another_T t1;"));
994 // REQUIRE(CHECK_COLUMN(result, 0, {true}));
995 REQUIRE_FAIL(con.Query("SELECT (SELECT SUM(col2) OVER (PARTITION BY SUM(col2) ORDER BY MAX(col1 + ColID) ROWS "
996 "UNBOUNDED PRECEDING) FROM tbl_ProductSales) FROM another_T t1 GROUP BY col1"));
997}
998