1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test joins under setops", "[setops][.]") {
8 DuckDB db(nullptr);
9 Connection con(db);
10 con.EnableQueryVerification();
11 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(a INTEGER);"));
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(b INTEGER);"));
13 for (size_t i = 0; i < 1024; i++) {
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(i) + ")"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (" + to_string(i) + ")"));
16 }
17 auto result =
18 con.Query("(SELECT * FROM test, test2 WHERE a=b) UNION (SELECT * FROM test,test2 WHERE a=b) ORDER BY 1");
19 REQUIRE_NO_FAIL(*result);
20 REQUIRE(result->collection.count == 1024);
21}
22
23TEST_CASE("Test joins under setops with CTEs", "[setops][.]") {
24 unique_ptr<QueryResult> result;
25 DuckDB db(nullptr);
26 Connection con(db);
27 con.EnableQueryVerification();
28 con.EnableProfiling();
29 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(a INTEGER);"));
30 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(b INTEGER);"));
31 for (size_t i = 0; i < 1024; i++) {
32 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(i) + ")"));
33 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (" + to_string(i) + ")"));
34 }
35 result = con.Query("WITH test_cte AS ((SELECT * FROM test, test2 WHERE a=b) UNION (SELECT * FROM test,test2 WHERE "
36 "a=b)) SELECT SUM(ta.a) FROM test_cte ta, test_cte tb WHERE ta.a=tb.a");
37 REQUIRE(CHECK_COLUMN(result, 0, {523776}));
38}
39
40TEST_CASE("Test joins under setops with CTEs and aggregations", "[setops][.]") {
41 unique_ptr<QueryResult> result;
42 DuckDB db(nullptr);
43 Connection con(db);
44 con.EnableQueryVerification();
45 con.EnableProfiling();
46 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(a INTEGER);"));
47 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(b INTEGER);"));
48 for (size_t i = 0; i < 1024; i++) {
49 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(i) + ")"));
50 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (" + to_string(i) + ")"));
51 }
52 result = con.Query("WITH test_cte AS ((SELECT * FROM test, test2 WHERE a=b) UNION (SELECT * FROM test,test2 WHERE "
53 "a=b)), results AS (SELECT SUM(ta.a) AS sum_a FROM test_cte ta, test_cte tb WHERE ta.a=tb.a) "
54 "SELECT * FROM (SELECT * FROM results GROUP BY sum_a UNION SELECT * FROM results GROUP BY sum_a "
55 "UNION SELECT * FROM results GROUP BY sum_a UNION SELECT * FROM results GROUP BY sum_a) AS t");
56 REQUIRE(CHECK_COLUMN(result, 0, {523776}));
57}
58