1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test basic joins of tables", "[joins]") {
8 DuckDB db(nullptr);
9 Connection con(db);
10 unique_ptr<QueryResult> result;
11 con.EnableQueryVerification();
12
13 // create tables
14 con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
15 con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)");
16
17 con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);");
18 con.Query("INSERT INTO test2 VALUES (1, 10), (1, 20), (2, 30)");
19
20 SECTION("simple cross product + join condition") {
21 result = con.Query("SELECT a, test.b, c FROM test, test2 WHERE test.b "
22 "= test2.b ORDER BY c;");
23 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 12}));
24 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 2}));
25 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
26 }
27 SECTION("ambiguous reference to column") {
28 REQUIRE_FAIL(con.Query("SELECT b FROM test, test2 WHERE test.b > test2.b;"));
29 }
30 SECTION("simple cross product + multiple join conditions") {
31 result = con.Query("SELECT a, test.b, c FROM test, test2 WHERE test.b=test2.b AND test.a-1=test2.c");
32 REQUIRE(CHECK_COLUMN(result, 0, {11}));
33 REQUIRE(CHECK_COLUMN(result, 1, {1}));
34 REQUIRE(CHECK_COLUMN(result, 2, {10}));
35 }
36 SECTION("use join columns in subquery") {
37 result = con.Query("SELECT a, (SELECT test.a), c FROM test, test2 WHERE "
38 "test.b = test2.b ORDER BY c;");
39 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 12}));
40 REQUIRE(CHECK_COLUMN(result, 1, {11, 11, 12}));
41 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
42 }
43 SECTION("explicit join") {
44 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON "
45 "test.b = test2.b ORDER BY c;");
46 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 12}));
47 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 2}));
48 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
49 }
50 SECTION("explicit join with condition the wrong way around") {
51 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON "
52 "test2.b = test.b ORDER BY c;");
53 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 12}));
54 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 2}));
55 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
56 }
57 SECTION("explicit join with additional condition that is no left-right "
58 "comparision") {
59 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON "
60 "test2.b = test.b and test.b = 2;");
61 REQUIRE(CHECK_COLUMN(result, 0, {12}));
62 REQUIRE(CHECK_COLUMN(result, 1, {2}));
63 REQUIRE(CHECK_COLUMN(result, 2, {30}));
64 }
65
66 SECTION("explicit join with additional condition that is constant") {
67 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON "
68 "test2.b = test.b and 2 = 2 ORDER BY c;");
69 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 12}));
70 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 2}));
71 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
72 }
73
74 SECTION("explicit join with only condition that is no left-right comparision") {
75 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON "
76 "test.b = 2 ORDER BY c;");
77 REQUIRE(CHECK_COLUMN(result, 0, {12, 12, 12}));
78 REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 2}));
79 REQUIRE(CHECK_COLUMN(result, 2, {10, 20, 30}));
80 }
81 SECTION("explicit join with only condition that is constant") {
82 result = con.Query("SELECT a, test.b, c FROM test INNER JOIN test2 ON NULL = 2;");
83 REQUIRE(CHECK_COLUMN(result, 0, {}));
84 REQUIRE(CHECK_COLUMN(result, 1, {}));
85 REQUIRE(CHECK_COLUMN(result, 2, {}));
86 }
87
88 SECTION("equality join where both lhs and rhs keys are projected") {
89 result = con.Query("SELECT * FROM (VALUES (1)) tbl(i) JOIN (VALUES (1)) tbl2(j) ON (i=j);");
90 REQUIRE(CHECK_COLUMN(result, 0, {1}));
91 REQUIRE(CHECK_COLUMN(result, 1, {1}));
92 }
93 SECTION("equality join where both lhs and rhs keys are projected with filter") {
94 result =
95 con.Query("SELECT * FROM (VALUES (1), (2)) tbl(i) JOIN (VALUES (1), (2)) tbl2(j) ON (i=j) WHERE i+j=2;");
96 REQUIRE(CHECK_COLUMN(result, 0, {1}));
97 REQUIRE(CHECK_COLUMN(result, 1, {1}));
98 }
99}
100
101TEST_CASE("Test join with > STANDARD_VECTOR_SIZE duplicates", "[joins][.]") {
102 DuckDB db(nullptr);
103 Connection con(db);
104 unique_ptr<QueryResult> result;
105 con.EnableQueryVerification();
106
107 size_t element_count = STANDARD_VECTOR_SIZE * 10;
108 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
109 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
110 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
111
112 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);"));
113 for (size_t i = 0; i < element_count; i++) {
114 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 10)"));
115 }
116 REQUIRE_NO_FAIL(con.Query("COMMIT"));
117
118 result = con.Query("SELECT COUNT(*) FROM test2;");
119 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(element_count)}));
120
121 result = con.Query("SELECT COUNT(*) FROM test INNER JOIN test2 ON test.b=test2.b");
122 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(element_count)}));
123}
124
125TEST_CASE("Equality + inequality joins", "[joins]") {
126 DuckDB db(nullptr);
127 Connection con(db);
128 unique_ptr<QueryResult> result;
129 con.EnableQueryVerification();
130
131 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
132 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
133
134 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER, c INTEGER);"));
135 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (11, 1), (12, 1), (13, 4)"));
136
137 result = con.Query("SELECT test.a, b, c FROM test, test2 WHERE test.a = "
138 "test2.a AND test.b <> test2.c ORDER BY test.a;");
139 REQUIRE(CHECK_COLUMN(result, 0, {12, 13}));
140 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
141 REQUIRE(CHECK_COLUMN(result, 2, {1, 4}));
142
143 result = con.Query("SELECT test.a, b, c FROM test, test2 WHERE test.a = "
144 "test2.a AND test.b < test2.c ORDER BY test.a;");
145 REQUIRE(CHECK_COLUMN(result, 0, {13}));
146 REQUIRE(CHECK_COLUMN(result, 1, {3}));
147 REQUIRE(CHECK_COLUMN(result, 2, {4}));
148
149 result = con.Query("SELECT test.a, b, c FROM test, test2 WHERE test.a = "
150 "test2.a AND test.b <= test2.c ORDER BY test.a;");
151 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
152 REQUIRE(CHECK_COLUMN(result, 1, {1, 3}));
153 REQUIRE(CHECK_COLUMN(result, 2, {1, 4}));
154
155 result = con.Query("SELECT test.a, b, c FROM test, test2 WHERE test.a = "
156 "test2.a AND test.b > test2.c ORDER BY test.a;");
157 REQUIRE(CHECK_COLUMN(result, 0, {12}));
158 REQUIRE(CHECK_COLUMN(result, 1, {2}));
159 REQUIRE(CHECK_COLUMN(result, 2, {1}));
160
161 result = con.Query("SELECT test.a, b, c FROM test, test2 WHERE test.a = "
162 "test2.a AND test.b >= test2.c ORDER BY test.a;");
163 REQUIRE(CHECK_COLUMN(result, 0, {11, 12}));
164 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
165 REQUIRE(CHECK_COLUMN(result, 2, {1, 1}));
166}
167
168TEST_CASE("Equality + inequality anti and semi joins", "[joins]") {
169 DuckDB db(nullptr);
170 Connection con(db);
171 unique_ptr<QueryResult> result;
172 con.EnableQueryVerification();
173
174 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER, str VARCHAR);"));
175 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1, 'a'), (12, 2, 'b'), (13, 3, 'c')"));
176
177 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER, c INTEGER, str2 VARCHAR);"));
178 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (11, 1, 'd'), (12, 1, 'e'), (13, 4, 'f')"));
179
180 result = con.Query("SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 "
181 "WHERE test.a=test2.a AND test.b<>test2.c);");
182 REQUIRE(CHECK_COLUMN(result, 0, {12, 13}));
183 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
184 REQUIRE(CHECK_COLUMN(result, 2, {"b", "c"}));
185
186 result = con.Query("SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE "
187 "test.a=test2.a AND test.b<>test2.c) AND NOT EXISTS(SELECT * "
188 "FROM test2 WHERE test.a=test2.a AND test.b<test2.c);");
189 REQUIRE(CHECK_COLUMN(result, 0, {12}));
190 REQUIRE(CHECK_COLUMN(result, 1, {2}));
191 REQUIRE(CHECK_COLUMN(result, 2, {"b"}));
192
193 result = con.Query("SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM "
194 "test2 WHERE test.a=test2.a AND test.b<test2.c);");
195 REQUIRE(CHECK_COLUMN(result, 0, {11, 12}));
196 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
197 REQUIRE(CHECK_COLUMN(result, 2, {"a", "b"}));
198
199 result = con.Query("SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE "
200 "test.a=test2.a AND test.b<test2.c) AND NOT EXISTS(SELECT * FROM test2 "
201 "WHERE test.a=test2.a AND test.b>test2.c);");
202 REQUIRE(CHECK_COLUMN(result, 0, {11}));
203 REQUIRE(CHECK_COLUMN(result, 1, {1}));
204 REQUIRE(CHECK_COLUMN(result, 2, {"a"}));
205
206 result = con.Query("SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE "
207 "test.a=test2.a AND test.b<>test2.c) AND test.a > 11;");
208 REQUIRE(CHECK_COLUMN(result, 0, {12, 13}));
209 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
210 REQUIRE(CHECK_COLUMN(result, 2, {"b", "c"}));
211}
212
213TEST_CASE("Equality + inequality anti and semi joins with selection vector", "[joins]") {
214 DuckDB db(nullptr);
215 Connection con(db);
216 unique_ptr<QueryResult> result;
217 con.EnableQueryVerification();
218
219 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER, str VARCHAR);"));
220 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1, 'a'), (12, 2, 'b'), (13, 3, 'c')"));
221
222 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (a INTEGER, c INTEGER, str2 VARCHAR);"));
223 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (11, 1, 'd'), (12, 1, 'e'), (13, 4, 'f')"));
224
225 result = con.Query("SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM "
226 "test2 WHERE test.a=test2.a AND test.b<test2.c AND "
227 "test2.a>14) AND NOT EXISTS(SELECT * FROM test2 WHERE "
228 "test.a=test2.a AND test.b>test2.c AND test2.a<10);");
229 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
230 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3}));
231 REQUIRE(CHECK_COLUMN(result, 2, {"a", "b", "c"}));
232
233 result = con.Query("SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM "
234 "test2 WHERE test.a=test2.a AND test.b<test2.c AND "
235 "test2.a=12) AND NOT EXISTS(SELECT * FROM test2 WHERE "
236 "test.a=test2.a AND test.b>test2.c AND test2.a=12);");
237 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
238 REQUIRE(CHECK_COLUMN(result, 1, {1, 3}));
239 REQUIRE(CHECK_COLUMN(result, 2, {"a", "c"}));
240
241 result = con.Query("SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE "
242 "test.a=test2.a AND test.b<>test2.c) AND test.a < 13;");
243 REQUIRE(CHECK_COLUMN(result, 0, {12}));
244 REQUIRE(CHECK_COLUMN(result, 1, {2}));
245 REQUIRE(CHECK_COLUMN(result, 2, {"b"}));
246}
247
248TEST_CASE("Test range joins", "[joins]") {
249 DuckDB db(nullptr);
250 Connection con(db);
251 unique_ptr<QueryResult> result;
252 con.EnableQueryVerification();
253
254 // create tables
255 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
256 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
257
258 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);"));
259 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 10), (1, 20), (2, 30)"));
260
261 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b<test2.b");
262 REQUIRE(CHECK_COLUMN(result, 0, {1}));
263 REQUIRE(CHECK_COLUMN(result, 1, {2}));
264
265 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b "
266 "<= test2.b ORDER BY 1,2");
267 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 2}));
268 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 2, 2}));
269
270 // range join on multiple predicates
271 result = con.Query(
272 "SELECT test.a, test.b, test2.b, test2.c FROM test, test2 WHERE test.a>test2.c AND test.b <= test2.b");
273 REQUIRE(CHECK_COLUMN(result, 0, {11}));
274 REQUIRE(CHECK_COLUMN(result, 1, {1}));
275 REQUIRE(CHECK_COLUMN(result, 2, {1}));
276 REQUIRE(CHECK_COLUMN(result, 3, {10}));
277
278 // introduce some NULL values
279 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, NULL), (NULL, 1)"));
280 // join result should be unchanged
281 result = con.Query(
282 "SELECT test.a, test.b, test2.b, test2.c FROM test, test2 WHERE test.a>test2.c AND test.b <= test2.b");
283 REQUIRE(CHECK_COLUMN(result, 0, {11}));
284 REQUIRE(CHECK_COLUMN(result, 1, {1}));
285 REQUIRE(CHECK_COLUMN(result, 2, {1}));
286 REQUIRE(CHECK_COLUMN(result, 3, {10}));
287
288 // on the RHS as well
289 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, NULL), (NULL, 10)"));
290 // join result should be unchanged
291 result = con.Query(
292 "SELECT test.a, test.b, test2.b, test2.c FROM test, test2 WHERE test.a>test2.c AND test.b <= test2.b");
293 REQUIRE(CHECK_COLUMN(result, 0, {11}));
294 REQUIRE(CHECK_COLUMN(result, 1, {1}));
295 REQUIRE(CHECK_COLUMN(result, 2, {1}));
296 REQUIRE(CHECK_COLUMN(result, 3, {10}));
297}
298
299TEST_CASE("Test inequality joins", "[joins]") {
300 DuckDB db(nullptr);
301 Connection con(db);
302 unique_ptr<QueryResult> result;
303 con.EnableQueryVerification();
304
305 // create tables
306 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
307 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
308
309 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);"));
310 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 10), (1, 20), (2, 30)"));
311
312 // inequality join
313 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b <> test2.b ORDER BY test.b, test2.b");
314 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 2, 3, 3, 3}));
315 REQUIRE(CHECK_COLUMN(result, 1, {2, 1, 1, 1, 1, 2}));
316 // inequality join with filter
317 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b <> test2.b AND test.b <> 1 AND test2.b <> "
318 "2 ORDER BY test.b, test2.b");
319 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 3, 3}));
320 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 1, 1}));
321
322 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (NULL, NULL)"));
323 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (NULL, NULL)"));
324 // inequality join with NULL values
325 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b <> test2.b ORDER BY test.b, test2.b");
326 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 2, 3, 3, 3}));
327 REQUIRE(CHECK_COLUMN(result, 1, {2, 1, 1, 1, 1, 2}));
328
329 // inequality join with filter and NULL values
330 result = con.Query("SELECT test.b, test2.b FROM test, test2 WHERE test.b <> test2.b AND test.b <> 1 AND test2.b <> "
331 "2 ORDER BY test.b, test2.b");
332 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 3, 3}));
333 REQUIRE(CHECK_COLUMN(result, 1, {1, 1, 1, 1}));
334}
335
336TEST_CASE("Test USING joins", "[joins]") {
337 DuckDB db(nullptr);
338 Connection con(db);
339 unique_ptr<QueryResult> result;
340 con.EnableQueryVerification();
341
342 // create tables
343 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1 (a INTEGER, b INTEGER, c INTEGER)"));
344 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (1,2,3)"));
345
346 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t2 (a INTEGER, b INTEGER, c INTEGER)"));
347 REQUIRE_NO_FAIL(con.Query("INSERT INTO t2 VALUES (1,2,3), (2,2,4), (1,3,4)"));
348
349 // USING join
350 result = con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a) ORDER BY t2.b");
351 REQUIRE(CHECK_COLUMN(result, 0, {1, 1}));
352 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
353 REQUIRE(CHECK_COLUMN(result, 2, {3, 4}));
354
355 result = con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(b) ORDER BY t2.c");
356 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
357 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
358 REQUIRE(CHECK_COLUMN(result, 2, {3, 4}));
359
360 result = con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a,b)");
361 REQUIRE(CHECK_COLUMN(result, 0, {1}));
362 REQUIRE(CHECK_COLUMN(result, 1, {2}));
363 REQUIRE(CHECK_COLUMN(result, 2, {3}));
364
365 result = con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a,b,c)");
366 REQUIRE(CHECK_COLUMN(result, 0, {1}));
367 REQUIRE(CHECK_COLUMN(result, 1, {2}));
368 REQUIRE(CHECK_COLUMN(result, 2, {3}));
369
370 // USING columns can be used without requiring a table specifier
371 result = con.Query("SELECT a+1 FROM t1 JOIN t2 USING(a) ORDER BY a");
372 REQUIRE(CHECK_COLUMN(result, 0, {2, 2}));
373
374 REQUIRE_FAIL(con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(a+b)"));
375 REQUIRE_FAIL(con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(\"\")"));
376 REQUIRE_FAIL(con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(d)"));
377 REQUIRE_FAIL(con.Query("SELECT t2.a, t2.b, t2.c FROM t1 JOIN t2 USING(t1.a)"));
378
379 result = con.Query("SELECT * FROM t1 JOIN t2 USING(a,b)");
380 REQUIRE(result->names.size() == 4);
381 REQUIRE(result->names[0] == "a");
382 REQUIRE(result->names[1] == "b");
383 REQUIRE(result->names[2] == "c");
384 REQUIRE(result->names[3] == "c");
385
386 REQUIRE(CHECK_COLUMN(result, 0, {1}));
387 REQUIRE(CHECK_COLUMN(result, 1, {2}));
388 REQUIRE(CHECK_COLUMN(result, 2, {3}));
389 REQUIRE(CHECK_COLUMN(result, 3, {3}));
390
391 // CONTROVERSIAL:
392 // we do not allow this because it is ambiguous: "b" can be bind to both "t1.b" or "t2.b" and this would give
393 // different results SQLite allows this, PostgreSQL does not
394 REQUIRE_FAIL(con.Query("SELECT * FROM t1 JOIN t2 USING(a) JOIN t2 t2b USING (b);"));
395 // a chain with the same column name is allowed though!
396 result = con.Query("SELECT * FROM t1 JOIN t2 USING(a) JOIN t2 t2b USING (a) ORDER BY 1, 2, 3, 4, 5, 6, 7");
397 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1}));
398 REQUIRE(CHECK_COLUMN(result, 1, {2, 2, 2, 2}));
399 REQUIRE(CHECK_COLUMN(result, 2, {3, 3, 3, 3}));
400 REQUIRE(CHECK_COLUMN(result, 3, {2, 2, 3, 3}));
401 REQUIRE(CHECK_COLUMN(result, 4, {3, 3, 4, 4}));
402 REQUIRE(CHECK_COLUMN(result, 5, {2, 3, 2, 3}));
403 REQUIRE(CHECK_COLUMN(result, 6, {3, 4, 3, 4}));
404
405 REQUIRE(result->names.size() == 7);
406 REQUIRE(result->names[0] == "a");
407 REQUIRE(result->names[1] == "b");
408 REQUIRE(result->names[2] == "c");
409 REQUIRE(result->names[3] == "b");
410 REQUIRE(result->names[4] == "c");
411 REQUIRE(result->names[5] == "b");
412 REQUIRE(result->names[6] == "c");
413}
414
415TEST_CASE("Test chaining USING joins", "[joins]") {
416 DuckDB db(nullptr);
417 Connection con(db);
418 unique_ptr<QueryResult> result;
419 con.EnableQueryVerification();
420
421 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1 (a INTEGER, b INTEGER)"));
422 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (1, 2)"));
423
424 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t2 (b INTEGER, c INTEGER)"));
425 REQUIRE_NO_FAIL(con.Query("INSERT INTO t2 VALUES (2, 3)"));
426
427 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t3 (c INTEGER, d INTEGER)"));
428 REQUIRE_NO_FAIL(con.Query("INSERT INTO t3 VALUES (3, 4)"));
429
430 // multiple joins with using
431 // single column
432 result = con.Query("SELECT * FROM t1 JOIN t2 USING (b) JOIN t3 USING (c) ORDER BY 1, 2, 3, 4;");
433 REQUIRE(CHECK_COLUMN(result, 0, {1}));
434 REQUIRE(CHECK_COLUMN(result, 1, {2}));
435 REQUIRE(CHECK_COLUMN(result, 2, {3}));
436 REQUIRE(CHECK_COLUMN(result, 3, {4}));
437
438 REQUIRE(result->names.size() == 4);
439 REQUIRE(result->names[0] == "a");
440 REQUIRE(result->names[1] == "b");
441 REQUIRE(result->names[2] == "c");
442 REQUIRE(result->names[3] == "d");
443
444 // column does not exist on left side of join
445 REQUIRE_FAIL(con.Query("SELECT * FROM t1 JOIN t2 USING (c)"));
446 // column does not exist on right side of join
447 REQUIRE_FAIL(con.Query("SELECT * FROM t1 JOIN t2 USING (a)"));
448
449 REQUIRE_NO_FAIL(con.Query("DROP TABLE t1"));
450 REQUIRE_NO_FAIL(con.Query("DROP TABLE t2"));
451 REQUIRE_NO_FAIL(con.Query("DROP TABLE t3"));
452
453 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1 (a INTEGER, b INTEGER, c INTEGER)"));
454 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (1, 2, 2)"));
455
456 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t2 (b INTEGER, c INTEGER, d INTEGER, e INTEGER)"));
457 REQUIRE_NO_FAIL(con.Query("INSERT INTO t2 VALUES (2, 2, 3, 4)"));
458
459 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t3 (d INTEGER, e INTEGER)"));
460 REQUIRE_NO_FAIL(con.Query("INSERT INTO t3 VALUES (3, 4)"));
461
462 // multi column
463 result = con.Query("SELECT * FROM t1 JOIN t2 USING (b, c) JOIN t3 USING (d, e);");
464 REQUIRE(CHECK_COLUMN(result, 0, {1}));
465 REQUIRE(CHECK_COLUMN(result, 1, {2}));
466 REQUIRE(CHECK_COLUMN(result, 2, {2}));
467 REQUIRE(CHECK_COLUMN(result, 3, {3}));
468 REQUIRE(CHECK_COLUMN(result, 4, {4}));
469
470 REQUIRE(result->names.size() == 5);
471 REQUIRE(result->names[0] == "a");
472 REQUIRE(result->names[1] == "b");
473 REQUIRE(result->names[2] == "c");
474 REQUIRE(result->names[3] == "d");
475 REQUIRE(result->names[4] == "e");
476}
477
478TEST_CASE("Test joins with various columns that are only used in the join", "[joins]") {
479 DuckDB db(nullptr);
480 Connection con(db);
481 unique_ptr<QueryResult> result;
482 con.EnableQueryVerification();
483
484 // create tables
485 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
486 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
487
488 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);"));
489 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 10), (1, 20), (2, 30)"));
490
491 // count of single join
492 result = con.Query("SELECT COUNT(*) FROM test, test2 WHERE test.b = test2.b");
493 REQUIRE(CHECK_COLUMN(result, 0, {3}));
494 // now a sum
495 result = con.Query("SELECT SUM(test.a), MIN(test.a), MAX(test.a) FROM test, test2 WHERE test.b = test2.b");
496 REQUIRE(CHECK_COLUMN(result, 0, {34}));
497 REQUIRE(CHECK_COLUMN(result, 1, {11}));
498 REQUIRE(CHECK_COLUMN(result, 2, {12}));
499
500 // count of multi-way join
501 result = con.Query("SELECT COUNT(*) FROM test a1, test a2, test a3 WHERE a1.b=a2.b AND a2.b=a3.b");
502 REQUIRE(CHECK_COLUMN(result, 0, {3}));
503 // now a sum
504 result = con.Query("SELECT SUM(a1.a) FROM test a1, test a2, test a3 WHERE a1.b=a2.b AND a2.b=a3.b");
505 REQUIRE(CHECK_COLUMN(result, 0, {36}));
506
507 // count of multi-way join with filters
508 result = con.Query("SELECT COUNT(*) FROM test a1, test a2, test a3 WHERE a1.b=a2.b AND a2.b=a3.b AND a1.a=11 AND "
509 "a2.a=11 AND a3.a=11");
510 REQUIRE(CHECK_COLUMN(result, 0, {1}));
511
512 // unused columns that become unused because of optimizer
513 result = con.Query("SELECT (TRUE OR a1.a=a2.b) FROM test a1, test a2 WHERE a1.a=11 AND a2.a>=10");
514 REQUIRE(CHECK_COLUMN(result, 0, {true, true, true}));
515}
516
517TEST_CASE("Test joins with comparisons involving both sides of the join", "[joins]") {
518 DuckDB db(nullptr);
519 Connection con(db);
520 unique_ptr<QueryResult> result;
521 con.EnableQueryVerification();
522
523 // create tables
524 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
525 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (4, 1), (2, 2)"));
526
527 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (b INTEGER, c INTEGER);"));
528 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 2), (3, 0)"));
529
530 result = con.Query("SELECT * FROM test JOIN test2 ON test.a+test2.c=test.b+test2.b");
531 REQUIRE(CHECK_COLUMN(result, 0, {4}));
532 REQUIRE(CHECK_COLUMN(result, 1, {1}));
533 REQUIRE(CHECK_COLUMN(result, 2, {3}));
534 REQUIRE(CHECK_COLUMN(result, 3, {0}));
535
536 result = con.Query("SELECT * FROM test LEFT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY 1");
537 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
538 REQUIRE(CHECK_COLUMN(result, 1, {2, 1}));
539 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 3}));
540 REQUIRE(CHECK_COLUMN(result, 3, {Value(), 0}));
541
542 result = con.Query("SELECT * FROM test RIGHT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY 1");
543 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 4}));
544 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1}));
545 REQUIRE(CHECK_COLUMN(result, 2, {1, 3}));
546 REQUIRE(CHECK_COLUMN(result, 3, {2, 0}));
547}
548