1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test subqueries", "[subqueries]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // scalar subquery
14 result = con.Query("SELECT (SELECT 42)");
15 REQUIRE(CHECK_COLUMN(result, 0, {42}));
16
17 // nested subquery
18 result = con.Query("SELECT (SELECT (SELECT 42))");
19 REQUIRE(CHECK_COLUMN(result, 0, {42}));
20
21 // test aliasing of subquery
22 result = con.Query("SELECT * FROM (SELECT 42) v1(a);");
23 REQUIRE(CHECK_COLUMN(result, 0, {42}));
24 REQUIRE(result->names[0] == "a");
25
26 // not enough aliases: defaults to using names for missing columns
27 result = con.Query("SELECT * FROM (SELECT 42, 41 AS x) v1(a);");
28 REQUIRE(CHECK_COLUMN(result, 0, {42}));
29 REQUIRE(CHECK_COLUMN(result, 1, {41}));
30 REQUIRE(result->names[0] == "a");
31 REQUIRE(result->names[1] == "x");
32
33 // too many aliases: fails
34 REQUIRE_FAIL(con.Query("SELECT * FROM (SELECT 42, 41 AS x) v1(a, b, c);"));
35
36 result = con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
37 result = con.Query("INSERT INTO test VALUES (11, 22)");
38 result = con.Query("INSERT INTO test VALUES (12, 21)");
39 result = con.Query("INSERT INTO test VALUES (13, 22)");
40
41 // select single tuple only in scalar subquery
42 result = con.Query("SELECT (SELECT a * 42 FROM test)");
43 REQUIRE(CHECK_COLUMN(result, 0, {11 * 42}));
44
45 // operations on subquery
46 result = con.Query("SELECT a*(SELECT 42) FROM test");
47 REQUIRE(CHECK_COLUMN(result, 0, {11 * 42, 12 * 42, 13 * 42}));
48
49 result = con.Query("CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d "
50 "INTEGER, e INTEGER)");
51 result = con.Query("INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104)");
52 result = con.Query("INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105)");
53
54 result = con.Query("SELECT c-(SELECT sum(c) FROM t1) FROM t1");
55 REQUIRE(CHECK_COLUMN(result, 0, {102 - 208, 106 - 208}));
56
57 result = con.Query("SELECT CASE WHEN c>(SELECT sum(c)/count(*) FROM t1) "
58 "THEN a*2 ELSE b*10 END FROM t1");
59 REQUIRE(CHECK_COLUMN(result, 0, {1000, 214}));
60
61 // correlated subqueries
62 result = con.Query("SELECT a, (SELECT SUM(b) FROM test tsub WHERE "
63 "test.a=tsub.a) FROM test");
64 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
65 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
66
67 result = con.Query("SELECT a, (SELECT CASE WHEN test.a=11 THEN 22 ELSE NULL END) FROM test ORDER BY a");
68 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
69 REQUIRE(CHECK_COLUMN(result, 1, {22, Value(), Value()}));
70
71 result =
72 con.Query("SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub) FROM test ORDER BY a");
73 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
74 REQUIRE(CHECK_COLUMN(result, 1, {22, Value(), Value()}));
75
76 result = con.Query(
77 "SELECT a, (SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test tsub LIMIT 1) FROM test ORDER BY a");
78 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
79 REQUIRE(CHECK_COLUMN(result, 1, {22, Value(), Value()}));
80
81 result = con.Query("SELECT * from test where a=(SELECT MIN(a) FROM test t "
82 "WHERE t.b=test.b)");
83 REQUIRE(CHECK_COLUMN(result, 0, {11, 12}));
84 REQUIRE(CHECK_COLUMN(result, 1, {22, 21}));
85
86 // exists / in / any subqueries
87 result = con.Query("SELECT * FROM test WHERE EXISTS (SELECT a FROM test ts "
88 "WHERE ts.a = test.a AND b>21)");
89 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
90 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
91
92 // duplicate name in subquery
93 REQUIRE_FAIL(con.Query("SELECT * FROM (SELECT 42 AS a, 44 AS a) tbl1"));
94}
95
96TEST_CASE("Test subqueries with (NOT) IN clause", "[subqueries]") {
97 unique_ptr<QueryResult> result;
98 DuckDB db(nullptr);
99 Connection con(db);
100 con.EnableQueryVerification();
101
102 // scalar subquery
103 result = con.Query("SELECT 1 AS one WHERE 1 IN (SELECT 1);");
104 REQUIRE(CHECK_COLUMN(result, 0, {1}));
105
106 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (id INTEGER, b INTEGER);"));
107 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 22)"));
108 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 21)"));
109 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 23)"));
110
111 result = con.Query("SELECT * FROM test WHERE b IN (SELECT b FROM test "
112 "WHERE b * id < 30) ORDER BY id, b");
113 REQUIRE(CHECK_COLUMN(result, 0, {1}));
114 REQUIRE(CHECK_COLUMN(result, 1, {22}));
115
116 result = con.Query("SELECT * FROM test WHERE b NOT IN (SELECT b FROM test "
117 "WHERE b * id < 30) ORDER BY id, b");
118 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
119 REQUIRE(CHECK_COLUMN(result, 1, {21, 23}));
120}
121
122TEST_CASE("Test correlated subqueries in WHERE clause", "[subqueries]") {
123 unique_ptr<QueryResult> result;
124 DuckDB db(nullptr);
125 Connection con(db);
126 con.EnableQueryVerification();
127
128 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (id INTEGER, b INTEGER);"));
129 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 22)"));
130 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 21)"));
131 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 22)"));
132
133 // correlated subquery with one correlated expression
134 // result = con.Query("SELECT * FROM test WHERE b=(SELECT MIN(b) FROM test
135 // AS "
136 // "a WHERE a.id=test.id)");
137 // REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
138 // REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
139
140 // correlated subquery with two correlated expressions
141 result = con.Query("SELECT * FROM test WHERE b=(SELECT MIN(b) FROM test AS "
142 "a WHERE a.id=test.id AND a.id < test.b)");
143 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
144 REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
145}
146
147TEST_CASE("Joins in subqueries", "[subqueries]") {
148 unique_ptr<QueryResult> result;
149 DuckDB db(nullptr);
150 Connection con(db);
151 con.EnableQueryVerification();
152
153 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (id INTEGER, test_value INTEGER);"));
154 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 22)"));
155 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 21)"));
156 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 22)"));
157
158 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (id INTEGER, test2_value INTEGER);"));
159 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 44)"));
160 REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (2, 42)"));
161
162 result = con.Query("SELECT * FROM test, test2 WHERE test.id=test2.id AND "
163 "test_value*test2_value=(SELECT MIN(test_value*test2_value) FROM test "
164 "AS a, test2 WHERE a.id=test.id AND a.id=test2.id)");
165 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
166 REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
167 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
168 REQUIRE(CHECK_COLUMN(result, 3, {44, 42}));
169}
170
171TEST_CASE("UNIONS of subqueries", "[subqueries]") {
172 unique_ptr<QueryResult> result;
173 DuckDB db(nullptr);
174 Connection con(db);
175 con.EnableQueryVerification();
176
177 result = con.Query("select * from (select 42) sq1 union all select * from "
178 "(select 43) sq2;");
179 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
180}
181
182TEST_CASE("Aliasing and aggregation in subqueries", "[subqueries]") {
183 unique_ptr<QueryResult> result;
184 DuckDB db(nullptr);
185 Connection con(db);
186 con.EnableQueryVerification();
187
188 REQUIRE_NO_FAIL(con.Query("create table a(i integer)"));
189 REQUIRE_NO_FAIL(con.Query("insert into a values (42)"));
190
191 // this is logical
192 result = con.Query("select * from (select i as j from a group by j) sq1 where j = 42");
193 REQUIRE(CHECK_COLUMN(result, 0, {42}));
194
195 // this is not but still allowed
196 result = con.Query("select * from (select i as j from a group by i) sq1 where j = 42");
197 REQUIRE(CHECK_COLUMN(result, 0, {42}));
198}
199