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 uncorrelated subqueries", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13
14 con.EnableProfiling();
15 con.EnableQueryVerification();
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 subqueries
21 result = con.Query("SELECT * FROM integers WHERE i=(SELECT 1)");
22 REQUIRE(CHECK_COLUMN(result, 0, {1}));
23 result = con.Query("SELECT * FROM integers WHERE i=(SELECT SUM(1))");
24 REQUIRE(CHECK_COLUMN(result, 0, {1}));
25 result = con.Query("SELECT * FROM integers WHERE i=(SELECT MIN(i) FROM integers)");
26 REQUIRE(CHECK_COLUMN(result, 0, {1}));
27 result = con.Query("SELECT * FROM integers WHERE i=(SELECT MAX(i) FROM integers)");
28 REQUIRE(CHECK_COLUMN(result, 0, {3}));
29 result = con.Query("SELECT *, (SELECT MAX(i) FROM integers) FROM integers ORDER BY i");
30 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
31 REQUIRE(CHECK_COLUMN(result, 1, {3, 3, 3, 3}));
32 // group by on subquery
33 result = con.Query("SELECT (SELECT 42) AS k, MAX(i) FROM integers GROUP BY k");
34 REQUIRE(CHECK_COLUMN(result, 0, {42}));
35 REQUIRE(CHECK_COLUMN(result, 1, {3}));
36 // subquery as parameter to aggregate
37 result = con.Query("SELECT i, MAX((SELECT 42)) FROM integers GROUP BY i ORDER BY i");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
39 REQUIRE(CHECK_COLUMN(result, 1, {42, 42, 42, 42}));
40
41 // scalar subquery returning zero results should result in NULL
42 result = con.Query("SELECT (SELECT * FROM integers WHERE i>10) FROM integers");
43 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
44
45 // return more than one row in a scalar subquery
46 // controversial: in postgres this gives an error
47 // but SQLite accepts it and just uses the first value
48 // we choose to agree with SQLite here
49 result = con.Query("SELECT * FROM integers WHERE i=(SELECT i FROM integers WHERE i IS NOT NULL ORDER BY i)");
50 REQUIRE(CHECK_COLUMN(result, 0, {1}));
51 // i.e. the above query is equivalent to this query
52 result =
53 con.Query("SELECT * FROM integers WHERE i=(SELECT i FROM integers WHERE i IS NOT NULL ORDER BY i LIMIT 1)");
54 REQUIRE(CHECK_COLUMN(result, 0, {1}));
55
56 // returning multiple columns should fail though
57 REQUIRE_FAIL(con.Query("SELECT * FROM integers WHERE i=(SELECT 1, 2)"));
58 REQUIRE_FAIL(con.Query("SELECT * FROM integers WHERE i=(SELECT i, i + 2 FROM integers)"));
59 // but not for EXISTS queries!
60 REQUIRE_NO_FAIL(con.Query("SELECT * FROM integers WHERE EXISTS (SELECT 1, 2)"));
61 REQUIRE_NO_FAIL(con.Query("SELECT * FROM integers WHERE EXISTS (SELECT i, i + 2 FROM integers)"));
62 // SELECT * should be fine if the star only expands to a single column
63 result = con.Query("SELECT (SELECT * FROM integers WHERE i=1)");
64 REQUIRE(CHECK_COLUMN(result, 0, {1}));
65 // but not if the star expands to more than one column!
66 REQUIRE_FAIL(con.Query("SELECT (SELECT * FROM integers i1, integers i2)"));
67
68 // uncorrelated subquery in SELECT
69 result = con.Query("SELECT (SELECT i FROM integers WHERE i=1)");
70 REQUIRE(CHECK_COLUMN(result, 0, {1}));
71 result = con.Query("SELECT * FROM integers WHERE i > (SELECT i FROM integers WHERE i=1)");
72 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
73}
74
75TEST_CASE("Test uncorrelated exists subqueries", "[subquery]") {
76 unique_ptr<QueryResult> result;
77 DuckDB db(nullptr);
78 Connection con(db);
79
80 con.EnableProfiling();
81 con.EnableQueryVerification();
82
83 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
84 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
85
86 // uncorrelated EXISTS
87 result = con.Query("SELECT * FROM integers WHERE EXISTS(SELECT 1) ORDER BY i");
88 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
89 result = con.Query("SELECT * FROM integers WHERE EXISTS(SELECT * FROM integers) ORDER BY i");
90 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
91 result = con.Query("SELECT * FROM integers WHERE NOT EXISTS(SELECT * FROM integers) ORDER BY i");
92 REQUIRE(CHECK_COLUMN(result, 0, {}));
93 result = con.Query("SELECT * FROM integers WHERE EXISTS(SELECT NULL) ORDER BY i");
94 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
95
96 // exists in SELECT clause
97 result = con.Query("SELECT EXISTS(SELECT * FROM integers)");
98 REQUIRE(CHECK_COLUMN(result, 0, {true}));
99 result = con.Query("SELECT EXISTS(SELECT * FROM integers WHERE i>10)");
100 REQUIRE(CHECK_COLUMN(result, 0, {false}));
101
102 // multiple exists
103 result = con.Query("SELECT EXISTS(SELECT * FROM integers), EXISTS(SELECT * FROM integers)");
104 REQUIRE(CHECK_COLUMN(result, 0, {true}));
105 REQUIRE(CHECK_COLUMN(result, 1, {true}));
106
107 // exists used in operations
108 result = con.Query("SELECT EXISTS(SELECT * FROM integers) AND EXISTS(SELECT * FROM integers)");
109 REQUIRE(CHECK_COLUMN(result, 0, {true}));
110
111 // nested EXISTS
112 result = con.Query("SELECT EXISTS(SELECT EXISTS(SELECT * FROM integers))");
113 REQUIRE(CHECK_COLUMN(result, 0, {true}));
114
115 // uncorrelated IN
116 result = con.Query("SELECT * FROM integers WHERE 1 IN (SELECT 1) ORDER BY i");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
118 result = con.Query("SELECT * FROM integers WHERE 1 IN (SELECT * FROM integers) ORDER BY i");
119 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
120 result = con.Query("SELECT * FROM integers WHERE 1 IN (SELECT NULL::INTEGER) ORDER BY i");
121 REQUIRE(CHECK_COLUMN(result, 0, {}));
122
123 // scalar NULL results
124 result = con.Query("SELECT 1 IN (SELECT NULL::INTEGER) FROM integers");
125 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
126 result = con.Query("SELECT NULL IN (SELECT * FROM integers) FROM integers");
127 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value(), Value()}));
128
129 // add aggregations after the subquery
130 result = con.Query("SELECT SUM(i) FROM integers WHERE 1 IN (SELECT * FROM integers)");
131 REQUIRE(CHECK_COLUMN(result, 0, {6}));
132}
133
134TEST_CASE("Test uncorrelated ANY subqueries", "[subquery]") {
135 unique_ptr<QueryResult> result;
136 DuckDB db(nullptr);
137 Connection con(db);
138
139 con.EnableProfiling();
140 con.EnableQueryVerification();
141
142 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
143 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
144
145 // uncorrelated ANY
146 result = con.Query("SELECT i FROM integers WHERE i <= ANY(SELECT i FROM integers)");
147 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
148 result = con.Query("SELECT i FROM integers WHERE i > ANY(SELECT i FROM integers)");
149 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
150 result = con.Query("SELECT i, i > ANY(SELECT i FROM integers) FROM integers ORDER BY i");
151 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
152 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), true, true}));
153 result = con.Query("SELECT i, i > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i");
154 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
155 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
156 result = con.Query("SELECT i, NULL > ANY(SELECT i FROM integers) FROM integers ORDER BY i");
157 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
158 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
159 result = con.Query("SELECT i, NULL > ANY(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i");
160 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
161 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), Value()}));
162 result = con.Query("SELECT i FROM integers WHERE i = ANY(SELECT i FROM integers)");
163 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
164 result = con.Query("SELECT i, i = ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i");
165 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
166 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
167 result = con.Query("SELECT i, i = ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i");
168 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
169 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), true}));
170 result = con.Query("SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2) FROM integers ORDER BY i");
171 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
172 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, false}));
173 result = con.Query("SELECT i, i <> ANY(SELECT i FROM integers WHERE i>2 OR i IS NULL) FROM integers ORDER BY i");
174 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
175 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, Value()}));
176 // use a bunch of cross products to make bigger data sets (> STANDARD_VECTOR_SIZE)
177 result = con.Query("SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, "
178 "integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i");
179 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
180 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, true}));
181 result = con.Query("SELECT i, i = ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, "
182 "integers i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 2) FROM integers ORDER BY i");
183 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
184 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, false, true}));
185 result = con.Query("SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, "
186 "integers i5, integers i6 WHERE i1.i IS NOT NULL) FROM integers ORDER BY i");
187 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
188 REQUIRE(CHECK_COLUMN(result, 1, {Value(), true, true, true}));
189 result =
190 con.Query("SELECT i, i >= ANY(SELECT i1.i FROM integers i1, integers i2, integers i3, integers i4, integers "
191 "i5, integers i6 WHERE i1.i IS NOT NULL AND i1.i <> 1 LIMIT 1) FROM integers ORDER BY i");
192 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
193 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, true, true}));
194}
195
196TEST_CASE("Test uncorrelated ALL subqueries", "[subquery]") {
197 unique_ptr<QueryResult> result;
198 DuckDB db(nullptr);
199 Connection con(db);
200
201 con.EnableProfiling();
202 con.EnableQueryVerification();
203
204 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
205 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
206
207 // uncorrelated ALL
208 result = con.Query("SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers)");
209 REQUIRE(CHECK_COLUMN(result, 0, {}));
210 result = con.Query("SELECT i, i >= ALL(SELECT i FROM integers) FROM integers ORDER BY i");
211 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
212 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, Value()}));
213 result = con.Query("SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL)");
214 REQUIRE(CHECK_COLUMN(result, 0, {3}));
215 result = con.Query("SELECT i, i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) FROM integers ORDER BY i");
216 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
217 REQUIRE(CHECK_COLUMN(result, 1, {Value(), false, false, true}));
218
219 result = con.Query("SELECT i FROM integers WHERE i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL)");
220 REQUIRE(CHECK_COLUMN(result, 0, {3}));
221 result = con.Query("SELECT i FROM integers WHERE i > ALL(SELECT MIN(i) FROM integers)");
222 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
223 result = con.Query("SELECT i FROM integers WHERE i < ALL(SELECT MAX(i) FROM integers)");
224 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
225 result = con.Query("SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers)");
226 REQUIRE(CHECK_COLUMN(result, 0, {}));
227 result = con.Query("SELECT i FROM integers WHERE i <= ALL(SELECT i FROM integers WHERE i IS NOT NULL)");
228 REQUIRE(CHECK_COLUMN(result, 0, {1}));
229 result = con.Query("SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i=1)");
230 REQUIRE(CHECK_COLUMN(result, 0, {1}));
231 result = con.Query("SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i=1)");
232 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
233 result = con.Query("SELECT i FROM integers WHERE i = ALL(SELECT i FROM integers WHERE i IS NOT NULL)");
234 REQUIRE(CHECK_COLUMN(result, 0, {}));
235 result = con.Query("SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i IS NOT NULL)");
236 REQUIRE(CHECK_COLUMN(result, 0, {}));
237 // zero results always results in TRUE for ALL, even if "i" is NULL
238 result = con.Query("SELECT i FROM integers WHERE i <> ALL(SELECT i FROM integers WHERE i>10) ORDER BY i;");
239 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
240 result = con.Query("SELECT i, i <> ALL(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
241 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
242 REQUIRE(CHECK_COLUMN(result, 1, {true, true, true, true}));
243 // zero results always results in FALSE for ANY
244 result = con.Query("SELECT i, i > ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
245 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
246 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
247 result = con.Query("SELECT i, i = ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
248 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
249 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
250 result = con.Query("SELECT i, i >= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
251 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
252 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
253 result = con.Query("SELECT i, i <= ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
254 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
255 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
256 result = con.Query("SELECT i, i < ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
257 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
258 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
259 result = con.Query("SELECT i, i <> ANY(SELECT i FROM integers WHERE i>10) FROM integers ORDER BY i;");
260 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
261 REQUIRE(CHECK_COLUMN(result, 1, {false, false, false, false}));
262
263 // nested uncorrelated subqueries
264 result = con.Query("SELECT (SELECT (SELECT (SELECT 42)))");
265 REQUIRE(CHECK_COLUMN(result, 0, {42}));
266 result = con.Query("SELECT (SELECT EXISTS(SELECT * FROM integers WHERE i>2)) FROM integers;");
267 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true, true}));
268
269 result = con.Query("SELECT (SELECT MAX(i) FROM integers) AS k, SUM(i) FROM integers GROUP BY k;");
270 REQUIRE(CHECK_COLUMN(result, 0, {3}));
271 REQUIRE(CHECK_COLUMN(result, 1, {6}));
272
273 // subqueries in GROUP BY clause
274 result = con.Query("SELECT i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL) AS k, SUM(i) FROM integers GROUP "
275 "BY k ORDER BY k;");
276 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false, true}));
277 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 3}));
278
279 result = con.Query(
280 "SELECT SUM(i) FROM integers GROUP BY (i >= ALL(SELECT i FROM integers WHERE i IS NOT NULL)) ORDER BY 1;");
281 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 3, 3}));
282
283 result = con.Query("SELECT i >= ALL(SELECT MIN(i) FROM integers WHERE i IS NOT NULL) AS k, SUM(i) FROM integers "
284 "GROUP BY k ORDER BY k;");
285 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true}));
286 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 6}));
287
288 // subquery in CASE statement
289 result = con.Query("SELECT i, SUM(CASE WHEN (i >= ALL(SELECT i FROM integers WHERE i=2)) THEN 1 ELSE 0 END) FROM "
290 "integers GROUP BY i ORDER BY i;");
291 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3}));
292 REQUIRE(CHECK_COLUMN(result, 1, {0, 0, 1, 1}));
293
294 // subquery in HAVING
295 result =
296 con.Query("SELECT i % 2 AS k, SUM(i) FROM integers GROUP BY k HAVING SUM(i) > (SELECT MAX(i) FROM integers)");
297 REQUIRE(CHECK_COLUMN(result, 0, {1}));
298 REQUIRE(CHECK_COLUMN(result, 1, {4}));
299
300 result = con.Query("SELECT i FROM integers WHERE NOT(i IN (SELECT i FROM integers WHERE i>1));");
301 REQUIRE(CHECK_COLUMN(result, 0, {1}));
302
303 // multiple subqueries in select without FROM
304 result = con.Query("SELECT (SELECT SUM(i) FROM integers), (SELECT 42)");
305 REQUIRE(CHECK_COLUMN(result, 0, {6}));
306 REQUIRE(CHECK_COLUMN(result, 1, {42}));
307}
308
309TEST_CASE("Test uncorrelated VARCHAR subqueries", "[subquery]") {
310 unique_ptr<QueryResult> result;
311 DuckDB db(nullptr);
312 Connection con(db);
313
314 con.EnableProfiling();
315 con.EnableQueryVerification();
316
317 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
318 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
319
320 // varchar tests
321 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(v VARCHAR)"));
322 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello'), ('world'), (NULL)"));
323 // ANY
324 result = con.Query("SELECT NULL IN (SELECT * FROM strings)");
325 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
326 result = con.Query("SELECT 'hello' IN (SELECT * FROM strings)");
327 REQUIRE(CHECK_COLUMN(result, 0, {true}));
328 result = con.Query("SELECT 'bla' IN (SELECT * FROM strings)");
329 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
330 result = con.Query("SELECT 'bla' IN (SELECT * FROM strings WHERE v IS NOT NULL)");
331 REQUIRE(CHECK_COLUMN(result, 0, {false}));
332 // EXISTS
333 result = con.Query("SELECT * FROM strings WHERE EXISTS(SELECT NULL)");
334 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "world", Value()}));
335 result = con.Query("SELECT * FROM strings WHERE EXISTS(SELECT v FROM strings WHERE v='bla')");
336 REQUIRE(CHECK_COLUMN(result, 0, {}));
337 // scalar query
338 result = con.Query("SELECT (SELECT v FROM strings WHERE v='hello') FROM strings");
339 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "hello", "hello"}));
340 result = con.Query("SELECT (SELECT v FROM strings WHERE v='bla') FROM strings");
341 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
342}
343
344TEST_CASE("Test subqueries from the paper 'Unnesting Arbitrary Subqueries'", "[subquery]") {
345 unique_ptr<QueryResult> result;
346 DuckDB db(nullptr);
347 Connection con(db);
348 con.EnableQueryVerification();
349 con.EnableProfiling();
350
351 REQUIRE_NO_FAIL(con.Query("CREATE TABLE students(id INTEGER, name VARCHAR, major VARCHAR, year INTEGER)"));
352 REQUIRE_NO_FAIL(
353 con.Query("CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, year INTEGER)"));
354
355 REQUIRE_NO_FAIL(con.Query("INSERT INTO students VALUES (1, 'Mark', 'CS', 2017)"));
356 REQUIRE_NO_FAIL(con.Query("INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017)"));
357 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015)"));
358 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016)"));
359 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015)"));
360 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016)"));
361
362 result = con.Query("SELECT s.name, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND e.grade=(SELECT "
363 "MAX(e2.grade) FROM exams e2 WHERE s.id=e2.sid) ORDER BY name, course;");
364 REQUIRE(CHECK_COLUMN(result, 0, {"Dirk", "Dirk", "Mark"}));
365 REQUIRE(CHECK_COLUMN(result, 1, {"Database Systems", "Graphics", "Database Systems"}));
366 REQUIRE(CHECK_COLUMN(result, 2, {7, 7, 10}));
367
368 result = con.Query("SELECT s.name, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND (s.major = 'CS' "
369 "OR s.major = 'Games Eng') AND e.grade <= (SELECT AVG(e2.grade) - 1 FROM exams e2 WHERE "
370 "s.id=e2.sid OR (e2.curriculum=s.major AND s.year>=e2.year)) ORDER BY name, course;");
371 REQUIRE(CHECK_COLUMN(result, 0, {"Dirk", "Dirk"}));
372 REQUIRE(CHECK_COLUMN(result, 1, {"Database Systems", "Graphics"}));
373 REQUIRE(CHECK_COLUMN(result, 2, {7, 7}));
374
375 result = con.Query("SELECT name, major FROM students s WHERE EXISTS(SELECT * FROM exams e WHERE e.sid=s.id AND "
376 "grade=10) OR s.name='Dirk' ORDER BY name");
377 REQUIRE(CHECK_COLUMN(result, 0, {"Dirk", "Mark"}));
378 REQUIRE(CHECK_COLUMN(result, 1, {"CS", "CS"}));
379}
380