1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test binding parameters with union expressions", "[setops]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test(a INTEGER);"));
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2(b INTEGER);"));
14
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1), (2), (3), (NULL);"));
16 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (2), (3), (4), (NULL);"));
17
18 result = con.Query("(SELECT a FROM test ORDER BY a+1) UNION SELECT b FROM test2 ORDER BY 1;");
19 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
20 REQUIRE(result->types.size() == 1);
21
22 // union returns only one column
23 result = con.Query("SELECT * FROM (SELECT * FROM test UNION SELECT * FROM test2) res ORDER BY 1;");
24 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
25 REQUIRE(result->types.size() == 1);
26 // we can only bind by the column names of the first query
27 result = con.Query("SELECT a FROM (SELECT * FROM test UNION SELECT * FROM test2) res ORDER BY 1;");
28 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
29 REQUIRE(result->types.size() == 1);
30 // hence this does not work: "b" is from the second query
31 REQUIRE_FAIL(con.Query("SELECT b FROM (SELECT * FROM test UNION SELECT * FROM test2) res ORDER BY 1;"));
32 // it works if we reverse the tables
33 result = con.Query("SELECT b FROM (SELECT * FROM test2 UNION SELECT * FROM test) res ORDER BY 1;");
34 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
35 REQUIRE(result->types.size() == 1);
36 // we can give explicit subquery aliases
37 result = con.Query("SELECT col1 FROM (SELECT * FROM test2 UNION SELECT * FROM test) res(col1) ORDER BY 1;");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
39 REQUIRE(result->types.size() == 1);
40
41 // we can ORDER BY names from both sides
42 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 ORDER BY a;");
43 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
44 REQUIRE(result->types.size() == 1);
45 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 ORDER BY b;");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
47 REQUIRE(result->types.size() == 1);
48 // if names are ambiguous, throw an error
49 REQUIRE_FAIL(con.Query("SELECT 1, a FROM test UNION SELECT b AS a, 1 FROM test2 ORDER BY a;"));
50 // if expressions are ambiguous as well, throw an error
51 REQUIRE_FAIL(con.Query("SELECT 1, a+1 FROM test UNION SELECT a+1, 1 FROM test ORDER BY a+1;"));
52 // also if we have multiple setops
53 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 UNION SELECT b AS c FROM test2 ORDER BY c;");
54 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
55 REQUIRE(result->types.size() == 1);
56 // we can also order by the expression itself
57 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 UNION SELECT b + 1 FROM test2 ORDER BY b + 1;");
58 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4, 5}));
59 REQUIRE(result->types.size() == 1);
60 // multiple columns order
61 result = con.Query("SELECT a, 10 - a AS b FROM test UNION SELECT b, b + 1 FROM test2 ORDER BY 2;");
62 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 2, 3, 4, 3, 2, 1}));
63 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 3, 4, 5, 7, 8, 9}));
64 // ambiguous naming reference should fail
65 REQUIRE_FAIL(con.Query("SELECT a, 10 - a AS b FROM test UNION SELECT b, b + 1 FROM test2 ORDER BY b;"));
66 // and by constant references
67 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 ORDER BY 1;");
68 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
69 REQUIRE(result->types.size() == 1);
70 // out of range constant reference
71 REQUIRE_FAIL(con.Query("SELECT a FROM test UNION SELECT b FROM test2 ORDER BY 2;"));
72
73 // what if our subqueries have an order by clause?
74 result = con.Query("(SELECT a FROM test ORDER BY a+1) UNION SELECT b FROM test2 ORDER BY 1;");
75 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
76 REQUIRE(result->types.size() == 1);
77
78 // unions with SELECT * also allows orders
79 result = con.Query("SELECT * FROM test UNION SELECT * FROM test2 ORDER BY a;");
80 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
81 result = con.Query("SELECT * FROM test UNION SELECT * FROM test2 ORDER BY b;");
82 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
83
84 // test union with/without table specifiers
85 result = con.Query("SELECT a FROM test UNION SELECT * FROM test2 ORDER BY test.a;");
86 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
87 result = con.Query("SELECT a FROM test UNION SELECT b FROM test2 ORDER BY test2.b;");
88 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
89 result = con.Query("SELECT test.a FROM test UNION SELECT * FROM test2 ORDER BY a;");
90 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
91 result = con.Query("SELECT test.a FROM test UNION SELECT test2.b FROM test2 ORDER BY b;");
92 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
93
94 // what about multiple set ops?
95 result = con.Query(
96 "SELECT a FROM test UNION SELECT * FROM test2 UNION SELECT * FROM test t1 ORDER BY test.a, test2.b, t1.a;");
97 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
98 result = con.Query("SELECT a FROM test UNION SELECT * FROM test2 UNION SELECT * FROM test t1 ORDER BY a;");
99 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
100 // and subqueries
101 result = con.Query("SELECT a FROM (SELECT * FROM test) bla UNION SELECT * FROM test2 ORDER BY bla.a;");
102 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4}));
103 // what if we have cross products or joins
104 result = con.Query("SELECT t1.a, t2.a FROM test t1, test t2 WHERE t1.a=t2.a UNION SELECT b, b - 1 FROM test2 ORDER "
105 "BY t1.a, t2.a, test2.b;");
106 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 2, 3, 3, 4}));
107 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 1, 2, 2, 3, 3}));
108}
109
110TEST_CASE("Test union with nulls", "[setops]") {
111 unique_ptr<QueryResult> result;
112 DuckDB db(nullptr);
113 Connection con(db);
114
115 result = con.Query("SELECT NULL as a, NULL as b, 1 as id UNION SELECT CAST('2015-10-11 00:00:00' AS TIMESTAMP) as "
116 "a, CAST('2015-10-11 12:34:56' AS TIMESTAMP) as b, 2 as id ORDER BY 3");
117 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value::TIMESTAMP(2015, 10, 11, 0, 0, 0, 0)}));
118 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value::TIMESTAMP(2015, 10, 11, 12, 34, 56, 0)}));
119 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
120}
121