1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test Common Table Expressions (CTE)", "[cte]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("create table a(i integer);"));
14 REQUIRE_NO_FAIL(con.Query("insert into a values (42);"));
15
16 result = con.Query("with cte1 as (Select i as j from a) select * from cte1;");
17 REQUIRE(CHECK_COLUMN(result, 0, {42}));
18
19 result = con.Query("with cte1 as (Select i as j from a), cte2 as (select "
20 "ref.j as k from cte1 as ref), cte3 as (select ref2.j+1 "
21 "as i from cte1 as ref2) select * from cte2 , cte3;");
22 REQUIRE(CHECK_COLUMN(result, 0, {42}));
23 REQUIRE(CHECK_COLUMN(result, 1, {43}));
24
25 result = con.Query("with cte1 as (select i as j from a), cte2 as (select ref.j as k from "
26 "cte1 as ref), cte3 as (select ref2.j+1 as i from cte1 as ref2) select "
27 "* from cte2 union all select * FROM cte3;");
28 REQUIRE(CHECK_COLUMN(result, 0, {42, 43}));
29
30 // duplicate CTE alias
31 REQUIRE_FAIL(con.Query("with cte1 as (select 42), cte1 as (select 42) select * FROM cte1;"));
32
33 // reference to CTE before its actually defined
34 result = con.Query("with cte3 as (select ref2.j as i from cte1 as ref2), cte1 as (Select "
35 "i as j from a), cte2 as (select ref.j+1 as k from cte1 as ref) select "
36 "* from cte2 union all select * FROM cte3;");
37 REQUIRE(CHECK_COLUMN(result, 0, {43, 42}));
38
39 // multiple uses of same CTE
40 result = con.Query("with cte1 as (Select i as j from a) select * "
41 "from cte1 cte11, cte1 cte12;");
42 REQUIRE(CHECK_COLUMN(result, 0, {42}));
43 REQUIRE(CHECK_COLUMN(result, 1, {42}));
44
45 // refer to CTE in subquery
46 result = con.Query("with cte1 as (Select i as j from a) select * from cte1 "
47 "where j = (select max(j) from cte1 as cte2);");
48 REQUIRE(CHECK_COLUMN(result, 0, {42}));
49}
50
51TEST_CASE("Test Recursive Common Table Expressions UNION ALL (CTE)", "[rec_cte_union_all]") {
52 unique_ptr<QueryResult> result;
53 DuckDB db(nullptr);
54 Connection con(db);
55 con.EnableQueryVerification();
56
57 // simple recursive CTE
58 result = con.Query("with recursive t as (select 1 as x union all select x+1 from t where x < 3) select * from t");
59 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
60
61 // simple recursive CTE with an alias
62 result =
63 con.Query("with recursive t as (select 1 as x union all select x+1 from t as m where m.x < 3) select * from t");
64 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
65
66 // recursive CTE with multiple references and aliases
67 result = con.Query("with recursive t as (select 1 as x union all select m.x+f.x from t as m, t as f where m.x < 3) "
68 "select * from t");
69 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4}));
70
71 // strings and multiple columns
72 result = con.Query("with recursive t as (select 1 as x, 'hello' as y union all select x+1, y || '-' || 'hello' "
73 "from t where x < 3) select * from t;");
74 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
75 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "hello-hello", "hello-hello-hello"}));
76
77 // referencing same CTE multiple times
78 result = con.Query("with recursive t as (select 1 as x union all select x+1 from t where x < 3) select min(a1.x) "
79 "from t a1, t a2;");
80 REQUIRE(CHECK_COLUMN(result, 0, {1}));
81 // nested uncorrelated subquery
82 result = con.Query(
83 "with recursive t as (select 1 as x union all select x+(SELECT 1) from t where x < 3) select * from t;");
84 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
85
86 // nested correlated subquery
87 // Not supported at the moment.
88 // REQUIRE_FAIL(con.Query("with recursive t as (select 1 as x union all select (SELECT x+1) from t where x < 3)
89 // select * from t;")); result = con.Query("with recursive t as (select 1 as x union all select (SELECT x+1) from
90 // t where x < 3) select * from t;"); REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
91
92 // use with recursive in table creation
93 REQUIRE_NO_FAIL(con.Query("create table integers as with recursive t as (select 1 as x union all select x+1 from t "
94 "where x < 3) select * from t;"));
95
96 // more complex uncorrelated subquery
97 result = con.Query("with recursive t as (select (select min(x) from integers) as x union all select x+1 from t "
98 "where x < 3) select * from t;");
99 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
100
101 // aggregate functions are not allowed in the recursive term of ctes
102 REQUIRE_FAIL(
103 con.Query("with recursive t as (select 1 as x union all select sum(x+1) from t where x < 3) select * from t"));
104
105 // order by is not allowed in the recursive term of ctes
106 REQUIRE_FAIL(con.Query(
107 "with recursive t as (select 1 as x union all select sum(x+1) from t where x < 3 order by x) select * from t"));
108
109 // limit is not allowed in the recursive term of ctes
110 REQUIRE_FAIL(con.Query(
111 "with recursive t as (select 1 as x union all select sum(x+1) from t where x < 3 LIMIT 1) select * from t"));
112
113 // offset is not allowed in the recursive term of ctes
114 REQUIRE_FAIL(con.Query(
115 "with recursive t as (select 1 as x union all select sum(x+1) from t where x < 3 OFFSET 1) select * from t"));
116
117 // offset is not allowed in the recursive term of ctes
118 REQUIRE_FAIL(con.Query("with recursive t as (select 1 as x union all select sum(x+1) from t where x < 3 LIMIT 1 "
119 "OFFSET 1) select * from t"));
120}
121
122TEST_CASE("Test Recursive Common Table Expressions UNION (CTE)", "[rec_cte_union]") {
123 unique_ptr<QueryResult> result;
124 DuckDB db(nullptr);
125 Connection con(db);
126 con.EnableQueryVerification();
127
128 // simple recursive CTE
129 result =
130 con.Query("with recursive t as (select 1 as x union select x+1 from t where x < 3) select * from t order by x");
131 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
132
133 // UNION semantics prevents infinite loop here
134 result = con.Query("with recursive t as (select 1 as x union select x from t) select * from t");
135 REQUIRE(CHECK_COLUMN(result, 0, {1}));
136
137 // simple recursive CTE with an alias
138 result = con.Query(
139 "with recursive t as (select 1 as x union select x+1 from t as m where m.x < 3) select * from t order by x");
140 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
141
142 // recursive CTE with multiple references and aliases
143 result = con.Query("with recursive t as (select 1 as x union select m.x+f.x from t as m, t as f where m.x < 3) "
144 "select * from t order by x");
145 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 4}));
146
147 // strings and multiple columns
148 result = con.Query("with recursive t as (select 1 as x, 'hello' as y union select x+1, y || '-' || 'hello' from t "
149 "where x < 3) select * from t order by x;");
150 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
151 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "hello-hello", "hello-hello-hello"}));
152
153 // referencing same CTE multiple times
154 result = con.Query(
155 "with recursive t as (select 1 as x union select x+1 from t where x < 3) select min(a1.x) from t a1, t a2;");
156 REQUIRE(CHECK_COLUMN(result, 0, {1}));
157 // nested uncorrelated subquery
158 result = con.Query(
159 "with recursive t as (select 1 as x union select x+(SELECT 1) from t where x < 3) select * from t order by x;");
160 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
161
162 // nested correlated subquery
163 // Not supported at the moment.
164 // REQUIRE_FAIL(con.Query("with recursive t as (select 1 as x union select (SELECT x+1) from t where x < 3)
165 // select * from t;")); result = con.Query("with recursive t as (select 1 as x union all select (SELECT x+1) from
166 // t where x < 3) select * from t;"); REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
167
168 // use with recursive in table creation
169 REQUIRE_NO_FAIL(con.Query("create table integers as with recursive t as (select 1 as x union select x+1 from t "
170 "where x < 3) select * from t;"));
171
172 // more complex uncorrelated subquery
173 result = con.Query("with recursive t as (select (select min(x) from integers) as x union select x+1 from t where x "
174 "< 3) select * from t order by x;");
175 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
176
177 // aggregate functions are not allowed in the recursive term of ctes
178 REQUIRE_FAIL(
179 con.Query("with recursive t as (select 1 as x union select sum(x+1) from t where x < 3) select * from t"));
180
181 // order by is not allowed in the recursive term of ctes
182 REQUIRE_FAIL(con.Query(
183 "with recursive t as (select 1 as x union select sum(x+1) from t where x < 3 order by x) select * from t"));
184
185 // limit is not allowed in the recursive term of ctes
186 REQUIRE_FAIL(con.Query(
187 "with recursive t as (select 1 as x union select sum(x+1) from t where x < 3 LIMIT 1) select * from t"));
188
189 // offset is not allowed in the recursive term of ctes
190 REQUIRE_FAIL(con.Query(
191 "with recursive t as (select 1 as x union select sum(x+1) from t where x < 3 OFFSET 1) select * from t"));
192
193 // offset is not allowed in the recursive term of ctes
194 REQUIRE_FAIL(con.Query("with recursive t as (select 1 as x union select sum(x+1) from t where x < 3 LIMIT 1 OFFSET "
195 "1) select * from t"));
196}
197