1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test UNION/EXCEPT/INTERSECT", "[setop]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 result = con.Query("SELECT 1 UNION ALL SELECT 2");
13 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
14
15 result = con.Query("SELECT 1, 'a' UNION ALL SELECT 2, 'b'");
16 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
17 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
18
19 result = con.Query("SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT "
20 "3, 'c' order by 1");
21 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
22 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b", "c"}));
23
24 result = con.Query("SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT "
25 "3, 'c' UNION ALL SELECT 4, 'd' order by 1");
26 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
27 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b", "c", "d"}));
28
29 // UNION/EXCEPT/INTERSECT with NULL values
30 result = con.Query("SELECT NULL UNION SELECT NULL");
31 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
32 result = con.Query("SELECT NULL EXCEPT SELECT NULL");
33 REQUIRE(CHECK_COLUMN(result, 0, {}));
34 result = con.Query("SELECT NULL INTERSECT SELECT NULL");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
36
37 // create tables
38 result = con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
39 result = con.Query("INSERT INTO test VALUES (11, 1), (12, 1), (13, 2)");
40
41 // UNION ALL, no unique results
42 result = con.Query("SELECT a FROM test WHERE a < 13 UNION ALL SELECT a "
43 "FROM test WHERE a = 13");
44 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
45
46 result = con.Query("SELECT b FROM test WHERE a < 13 UNION ALL SELECT b "
47 "FROM test WHERE a > 11");
48 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 2}));
49
50 // mixing types, should upcast
51 result = con.Query("SELECT 1 UNION ALL SELECT 'asdf'");
52 REQUIRE(CHECK_COLUMN(result, 0, {"1", "asdf"}));
53
54 result = con.Query("SELECT NULL UNION ALL SELECT 'asdf'");
55 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "asdf"}));
56
57 // only UNION, distinct results
58
59 result = con.Query("SELECT 1 UNION SELECT 1");
60 REQUIRE(CHECK_COLUMN(result, 0, {1}));
61
62 result = con.Query("SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' "
63 "UNION SELECT 1, 'a' order by 1");
64 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
65 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b", "c"}));
66
67 result = con.Query("SELECT b FROM test WHERE a < 13 UNION SELECT b FROM "
68 "test WHERE a > 11 ORDER BY 1");
69 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
70
71 // mixed fun
72 result = con.Query("SELECT 1, 'a' UNION ALL SELECT 1, 'a' UNION SELECT 2, "
73 "'b' UNION SELECT 1, 'a' ORDER BY 1");
74 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
75 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
76
77 result = con.Query("SELECT 1, 'a' UNION ALL SELECT 1, 'a' UNION SELECT 2, "
78 "'b' UNION SELECT 1, 'a' ORDER BY 1 DESC");
79 REQUIRE(CHECK_COLUMN(result, 0, {2, 1}));
80 REQUIRE(CHECK_COLUMN(result, 1, {"b", "a"}));
81}
82
83TEST_CASE("Test UNION in subquery with aliases", "[setop]") {
84 unique_ptr<QueryResult> result;
85 DuckDB db(nullptr);
86 Connection con(db);
87
88 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t(i INTEGER)"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO t VALUES (42)"));
90
91 result = con.Query("select i, j from (select i, 1 as j from t group by i "
92 "union all select i, 2 as j from t group by i) sq1");
93
94 REQUIRE(CHECK_COLUMN(result, 0, {42, 42}));
95 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
96}
97
98TEST_CASE("Test EXCEPT / INTERSECT", "[setop]") {
99 unique_ptr<QueryResult> result;
100 DuckDB db(nullptr);
101 Connection con(db);
102
103 REQUIRE_NO_FAIL(con.Query("CREATE TABLE a(i INTEGER)"));
104 REQUIRE_NO_FAIL(con.Query("INSERT INTO a VALUES (41), (42), (43)"));
105
106 REQUIRE_NO_FAIL(con.Query("CREATE TABLE b(i INTEGER)"));
107 REQUIRE_NO_FAIL(con.Query("INSERT INTO b VALUES (40), (43), (43)"));
108
109 result = con.Query("select * from a except select * from b order by 1");
110 REQUIRE(CHECK_COLUMN(result, 0, {41, 42}));
111
112 result = con.Query("select * from a intersect select * from b");
113 REQUIRE(CHECK_COLUMN(result, 0, {43}));
114}
115
116TEST_CASE("Test nested EXCEPT", "[setop]") {
117 unique_ptr<QueryResult> result;
118 DuckDB db(nullptr);
119 Connection con(db);
120
121 REQUIRE_NO_FAIL(con.Query("create table a (i integer)"));
122 REQUIRE_NO_FAIL(con.Query("create table b(i integer)"));
123 REQUIRE_NO_FAIL(con.Query("create table c (i integer)"));
124
125 REQUIRE_NO_FAIL(con.Query("insert into a values(42), (43), (44)"));
126 REQUIRE_NO_FAIL(con.Query("insert into b values(43)"));
127 REQUIRE_NO_FAIL(con.Query("insert into c values(44)"));
128
129 result = con.Query("select * from a except select * from b except select * from c");
130 REQUIRE(CHECK_COLUMN(result, 0, {42}));
131}
132
133TEST_CASE("Test UNION type casting", "[setop]") {
134 unique_ptr<QueryResult> result;
135 DuckDB db(nullptr);
136 Connection con(db);
137
138 // type casting in single union
139 result = con.Query("SELECT 1 UNION SELECT 1.0");
140 REQUIRE(CHECK_COLUMN(result, 0, {1}));
141
142 // type casting in nested union
143 result = con.Query("SELECT 1 UNION (SELECT 1.0 UNION SELECT 1.0 UNION SELECT 1.0) UNION SELECT 1;");
144 REQUIRE(CHECK_COLUMN(result, 0, {1}));
145
146 result = con.Query("SELECT 1 UNION (SELECT '1' UNION SELECT '1' UNION SELECT '1') UNION SELECT 1;");
147 REQUIRE(CHECK_COLUMN(result, 0, {"1"}));
148}
149