1#include "catch.hpp"
2#include "test_helpers.hpp"
3#include "duckdb/common/file_system.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test simple relation API", "[relation_api]") {
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12 unique_ptr<QueryResult> result;
13 shared_ptr<Relation> tbl, filter, proj, proj2, v1, v2, v3;
14
15 // create some tables
16 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
17 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
18
19 // simple projection
20 REQUIRE_NOTHROW(tbl = con.Table("integers"));
21 REQUIRE_NOTHROW(result = tbl->Project("i + 1")->Execute());
22 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
23
24 // we support * expressions
25 REQUIRE_NOTHROW(result = tbl->Project("*")->Execute());
26 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
27
28 // we can also read the table directly
29 REQUIRE_NOTHROW(result = tbl->Execute());
30 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
31
32 // we can stack projections
33 REQUIRE_NOTHROW(
34 result =
35 tbl->Project("i + 1 AS i")->Project("i + 1 AS i")->Project("i + 1 AS i")->Project("i + 1 AS i")->Execute());
36 REQUIRE(CHECK_COLUMN(result, 0, {5, 6, 7}));
37
38 // we can execute the same projection multiple times
39 REQUIRE_NOTHROW(proj = tbl->Project("i + 1"));
40 for (idx_t i = 0; i < 10; i++) {
41 REQUIRE_NOTHROW(result = proj->Execute());
42 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
43 }
44
45 result = con.Query("SELECT i+1 FROM (SELECT * FROM integers WHERE i <> 2) relation");
46 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
47 // filter and projection
48 REQUIRE_NOTHROW(filter = tbl->Filter("i <> 2"));
49 REQUIRE_NOTHROW(proj = filter->Project("i + 1"));
50 REQUIRE_NOTHROW(result = proj->Execute());
51 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
52
53 // we can reuse the same filter again and perform a different projection
54 REQUIRE_NOTHROW(proj = filter->Project("i * 10"));
55 REQUIRE_NOTHROW(result = proj->Execute());
56 REQUIRE(CHECK_COLUMN(result, 0, {10, 30}));
57
58 // add a limit
59 REQUIRE_NOTHROW(result = proj->Limit(1)->Execute());
60 REQUIRE(CHECK_COLUMN(result, 0, {10}));
61
62 // and an offset
63 REQUIRE_NOTHROW(result = proj->Limit(1, 1)->Execute());
64 REQUIRE(CHECK_COLUMN(result, 0, {30}));
65
66 // lets add some aliases
67 REQUIRE_NOTHROW(proj = filter->Project("i + 1 AS a"));
68 REQUIRE_NOTHROW(result = proj->Execute());
69 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
70 // we can check the column names
71 REQUIRE(proj->Columns()[0].name == "a");
72 REQUIRE(proj->Columns()[0].type == SQLType::INTEGER);
73
74 // we can also alias like this
75 REQUIRE_NOTHROW(proj = filter->Project("i + 1", "a"));
76 REQUIRE_NOTHROW(result = proj->Execute());
77 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
78 // we can check the column names
79 REQUIRE(proj->Columns()[0].name == "a");
80 REQUIRE(proj->Columns()[0].type == SQLType::INTEGER);
81
82 // now we can use that column to perform additional projections
83 REQUIRE_NOTHROW(result = proj->Project("a + 1")->Execute());
84 REQUIRE(CHECK_COLUMN(result, 0, {3, 5}));
85
86 // we can also filter on this again
87 REQUIRE_NOTHROW(result = proj->Filter("a=2")->Execute());
88 REQUIRE(CHECK_COLUMN(result, 0, {2}));
89
90 // filters can also contain conjunctions
91 REQUIRE_NOTHROW(result = proj->Filter("a=2 OR a=4")->Execute());
92 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
93
94 // alias
95 REQUIRE_NOTHROW(result = proj->Project("a + 1")->Alias("bla")->Execute());
96 REQUIRE(CHECK_COLUMN(result, 0, {3, 5}));
97
98 // now test ordering
99 REQUIRE_NOTHROW(result = proj->Order("a DESC")->Execute());
100 REQUIRE(CHECK_COLUMN(result, 0, {4, 2}));
101
102 // top n
103 REQUIRE_NOTHROW(result = proj->Order("a")->Limit(1)->Execute());
104 REQUIRE(CHECK_COLUMN(result, 0, {2}));
105 REQUIRE_NOTHROW(result = proj->Order("a DESC")->Limit(1)->Execute());
106 REQUIRE(CHECK_COLUMN(result, 0, {4}));
107
108 // test set operations
109 REQUIRE_NOTHROW(result = tbl->Union(tbl)->Order("i")->Execute());
110 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3}));
111 REQUIRE_NOTHROW(result = tbl->Except(tbl)->Order("i")->Execute());
112 REQUIRE(CHECK_COLUMN(result, 0, {}));
113 REQUIRE_NOTHROW(result = tbl->Intersect(tbl)->Order("i")->Execute());
114 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
115 REQUIRE_NOTHROW(result = tbl->Except(tbl->Filter("i=2"))->Order("i")->Execute());
116 REQUIRE(CHECK_COLUMN(result, 0, {1, 3}));
117 REQUIRE_NOTHROW(result = tbl->Intersect(tbl->Filter("i=2"))->Order("i")->Execute());
118 REQUIRE(CHECK_COLUMN(result, 0, {2}));
119
120 // set operations with projections
121 REQUIRE_NOTHROW(proj = tbl->Project("i::TINYINT AS i, i::SMALLINT, i::BIGINT, i::VARCHAR"));
122 REQUIRE_NOTHROW(proj2 = tbl->Project("(i+10)::TINYINT, (i+10)::SMALLINT, (i+10)::BIGINT, (i+10)::VARCHAR"));
123 REQUIRE_NOTHROW(result = proj->Union(proj2)->Order("i")->Execute());
124 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 11, 12, 13}));
125 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 11, 12, 13}));
126 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 11, 12, 13}));
127 REQUIRE(CHECK_COLUMN(result, 3, {"1", "2", "3", "11", "12", "13"}));
128
129 // distinct
130 REQUIRE_NOTHROW(result = tbl->Union(tbl)->Union(tbl)->Distinct()->Order("1")->Execute());
131 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
132
133 // join
134 REQUIRE_NOTHROW(v1 = con.Values({{1, 10}, {2, 5}, {3, 4}}, {"id", "j"}, "v1"));
135 REQUIRE_NOTHROW(v2 = con.Values({{1, 27}, {2, 8}, {3, 20}}, {"id", "k"}, "v2"));
136 REQUIRE_NOTHROW(v3 = con.Values({{1, 2}, {2, 6}, {3, 10}}, {"id", "k"}, "v3"));
137 REQUIRE_NOTHROW(result = v1->Join(v2, "v1.id=v2.id")->Execute());
138 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
139 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
140 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3}));
141 REQUIRE(CHECK_COLUMN(result, 3, {27, 8, 20}));
142
143 // projection after a join
144 REQUIRE_NOTHROW(result = v1->Join(v2, "v1.id=v2.id")->Project("v1.id+v2.id, j+k")->Execute());
145 REQUIRE(CHECK_COLUMN(result, 0, {2, 4, 6}));
146 REQUIRE(CHECK_COLUMN(result, 1, {37, 13, 24}));
147
148 // chain multiple joins
149 auto multi_join = v1->Join(v2, "v1.id=v2.id")->Join(v3, "v1.id=v3.id");
150 REQUIRE_NOTHROW(result = multi_join->Project("v1.id+v2.id+v3.id")->Execute());
151 REQUIRE(CHECK_COLUMN(result, 0, {3, 6, 9}));
152
153 // multiple joins followed by a filter and a projection
154 REQUIRE_NOTHROW(result = multi_join->Filter("v1.id=1")->Project("v1.id+v2.id+v3.id")->Execute());
155 REQUIRE(CHECK_COLUMN(result, 0, {3}));
156 // multiple joins followed by multiple filters
157 REQUIRE_NOTHROW(result = multi_join->Filter("v1.id>0")
158 ->Filter("v2.id < 3")
159 ->Filter("v3.id=2")
160 ->Project("v1.id+v2.id+v3.id")
161 ->Execute());
162 REQUIRE(CHECK_COLUMN(result, 0, {6}));
163
164 // test explain
165 REQUIRE_NO_FAIL(multi_join->Explain());
166}
167
168TEST_CASE("Test combinations of set operations", "[relation_api]") {
169 DuckDB db(nullptr);
170 Connection con(db);
171 con.EnableQueryVerification();
172 unique_ptr<QueryResult> result;
173 shared_ptr<Relation> values, v1, v2, v3;
174
175 REQUIRE_NOTHROW(values = con.Values({{1, 10}, {2, 5}, {3, 4}}, {"i", "j"}));
176
177 // union between values
178 auto vunion = values->Union(values);
179 REQUIRE_NOTHROW(result = vunion->Order("i")->Execute());
180 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3}));
181 REQUIRE(CHECK_COLUMN(result, 1, {10, 10, 5, 5, 4, 4}));
182
183 // different ops after a union
184 // order and limit
185 REQUIRE_NOTHROW(result = vunion->Order("i")->Limit(1)->Execute());
186 REQUIRE(CHECK_COLUMN(result, 0, {1}));
187 REQUIRE(CHECK_COLUMN(result, 1, {10}));
188 // multiple orders and limits
189 REQUIRE_NOTHROW(result = vunion->Order("i")->Limit(4)->Order("j")->Limit(2)->Execute());
190 REQUIRE(CHECK_COLUMN(result, 0, {2, 2}));
191 REQUIRE(CHECK_COLUMN(result, 1, {5, 5}));
192 // filter
193 REQUIRE_NOTHROW(result = vunion->Filter("i=1")->Execute());
194 REQUIRE(CHECK_COLUMN(result, 0, {1, 1}));
195 REQUIRE(CHECK_COLUMN(result, 1, {10, 10}));
196 // multiple filters
197 REQUIRE_NOTHROW(result = vunion->Filter("i<3")->Filter("j=5")->Execute());
198 REQUIRE(CHECK_COLUMN(result, 0, {2, 2}));
199 REQUIRE(CHECK_COLUMN(result, 1, {5, 5}));
200 // distinct
201 REQUIRE_NOTHROW(result = vunion->Distinct()->Order("j DESC")->Execute());
202 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
203 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
204 // multiple distincts followed by a top-n
205 REQUIRE_NOTHROW(result = vunion->Distinct()->Distinct()->Distinct()->Order("j DESC")->Limit(2)->Execute());
206 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
207 REQUIRE(CHECK_COLUMN(result, 1, {10, 5}));
208 // top-n followed by multiple distincts
209 REQUIRE_NOTHROW(result = vunion->Order("j DESC")->Limit(2)->Distinct()->Distinct()->Distinct()->Execute());
210 REQUIRE(CHECK_COLUMN(result, 0, {1}));
211 REQUIRE(CHECK_COLUMN(result, 1, {10}));
212
213 // multiple set ops
214 REQUIRE_NOTHROW(result = vunion->Union(vunion)->Distinct()->Order("1")->Execute());
215 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
216 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
217 REQUIRE_NOTHROW(result = vunion->Intersect(vunion)->Order("1")->Execute());
218 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3}));
219 REQUIRE(CHECK_COLUMN(result, 1, {10, 10, 5, 5, 4, 4}));
220 REQUIRE_NOTHROW(result = vunion->Except(vunion)->Execute());
221 REQUIRE(CHECK_COLUMN(result, 0, {}));
222 REQUIRE(CHECK_COLUMN(result, 1, {}));
223
224 // setops require the same amount of columns on both sides
225 REQUIRE_NOTHROW(v1 = con.Values("(1, 2), (3, 4)"));
226 REQUIRE_NOTHROW(v2 = con.Values("(1)"));
227 REQUIRE_THROWS(v1->Union(v2)->Execute());
228
229 // setops require the same types on both sides
230 REQUIRE_NOTHROW(v1 = con.Values("(DATE '1992-01-01', 2)"));
231 REQUIRE_NOTHROW(v2 = con.Values("(3.0, 'hello')"));
232 REQUIRE_FAIL(v1->Union(v2)->Execute());
233}
234
235TEST_CASE("Test combinations of joins", "[relation_api]") {
236 DuckDB db(nullptr);
237 Connection con(db);
238 con.EnableQueryVerification();
239 unique_ptr<QueryResult> result;
240 shared_ptr<Relation> values, vjoin;
241
242 REQUIRE_NOTHROW(values = con.Values({{1, 10}, {2, 5}, {3, 4}}, {"i", "j"}));
243
244 auto v1 = values->Alias("v1");
245 auto v2 = values->Alias("v2");
246
247 // join on explicit join condition
248 vjoin = v1->Join(v2, "v1.i=v2.i");
249 REQUIRE_NOTHROW(result = vjoin->Order("v1.i")->Execute());
250 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
251 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
252 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3}));
253 REQUIRE(CHECK_COLUMN(result, 3, {10, 5, 4}));
254
255 // aggregate on EXPLICIT join
256 REQUIRE_NOTHROW(result = vjoin->Aggregate("SUM(v1.i) + SUM(v2.i), SUM(v1.j) + SUM(v2.j)")->Execute());
257 REQUIRE(CHECK_COLUMN(result, 0, {12}));
258 REQUIRE(CHECK_COLUMN(result, 1, {38}));
259
260 // implicit join
261 vjoin = v1->Join(v2, "i");
262 REQUIRE_NOTHROW(result = vjoin->Order("i")->Execute());
263 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
264 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
265 REQUIRE(CHECK_COLUMN(result, 2, {10, 5, 4}));
266
267 // implicit join on multiple columns
268 vjoin = v1->Join(v2, "i, j");
269 REQUIRE_NOTHROW(result = vjoin->Order("i")->Execute());
270 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
271 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
272
273 // aggregate on USING join
274 REQUIRE_NOTHROW(result = vjoin->Aggregate("SUM(i), SUM(j)")->Execute());
275 REQUIRE(CHECK_COLUMN(result, 0, {6}));
276 REQUIRE(CHECK_COLUMN(result, 1, {19}));
277
278 // joining on a column that doesn't exist results in an error
279 REQUIRE_THROWS(v1->Join(v2, "blabla"));
280 // also with explicit join condition
281 REQUIRE_THROWS(v1->Join(v2, "v1.i=v2.blabla"));
282
283 // set ops involving joins
284 REQUIRE_NOTHROW(result = vjoin->Union(vjoin)->Distinct()->Order("i")->Execute());
285 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
286 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
287
288 // do a bunch of joins in a loop
289 auto v1tmp = v1;
290 auto v2tmp = v2;
291 for (idx_t i = 0; i < 4; i++) {
292 REQUIRE_NOTHROW(v1tmp = v1tmp->Join(v2tmp->Alias(to_string(i)), "i, j"));
293 }
294 REQUIRE_NOTHROW(result = v1tmp->Order("i")->Execute());
295 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
296 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
297
298 // now add on some projections and such
299 auto complex_join = v1tmp->Order("i")->Limit(2)->Order("i DESC")->Limit(1)->Project("i+1, j+1");
300 for (idx_t i = 0; i < 3; i++) {
301 REQUIRE_NOTHROW(result = complex_join->Execute());
302 REQUIRE(CHECK_COLUMN(result, 0, {3}));
303 REQUIRE(CHECK_COLUMN(result, 1, {6}));
304 }
305
306 // create and query a view
307 REQUIRE_NOTHROW(complex_join->CreateView("test123"));
308 result = con.Query("SELECT * FROM test123");
309 REQUIRE(CHECK_COLUMN(result, 0, {3}));
310 REQUIRE(CHECK_COLUMN(result, 1, {6}));
311
312 // joins of tables that have output modifiers attached to them (limit, order by, distinct)
313 auto v1_modified = v1->Limit(100)->Order("1")->Distinct()->Filter("i<1000");
314 auto v2_modified = v2->Limit(100)->Order("1")->Distinct()->Filter("i<1000");
315 vjoin = v1_modified->Join(v2_modified, "i, j");
316 REQUIRE_NOTHROW(result = vjoin->Order("i")->Execute());
317 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
318 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
319}
320
321TEST_CASE("Test view creation of relations", "[relation_api]") {
322 DuckDB db(nullptr);
323 Connection con(db);
324 con.EnableQueryVerification();
325 unique_ptr<QueryResult> result;
326 shared_ptr<Relation> tbl, filter, proj, proj2;
327
328 // create some tables
329 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
330 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
331
332 // simple view creation
333 REQUIRE_NOTHROW(tbl = con.Table("integers"));
334 REQUIRE_NOTHROW(result = tbl->Query("test", "SELECT * FROM test"));
335 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
336
337 // add a projection
338 REQUIRE_NOTHROW(result = tbl->Project("i + 1")->Query("test", "SELECT * FROM test"));
339 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
340
341 // multiple projections
342 proj = tbl->Project("i + 1", "i");
343 for (idx_t i = 0; i < 10; i++) {
344 proj = proj->Project("i + 1", "i");
345 }
346 REQUIRE_NOTHROW(result = proj->Execute());
347 REQUIRE(CHECK_COLUMN(result, 0, {12, 13, 14}));
348 REQUIRE_NOTHROW(result = proj->Query("test", "SELECT * FROM test"));
349 REQUIRE(CHECK_COLUMN(result, 0, {12, 13, 14}));
350
351 // we can also use more complex SQL
352 REQUIRE_NOTHROW(result = proj->Query("test", "SELECT SUM(t1.i) FROM test t1 JOIN test t2 ON t1.i=t2.i"));
353 REQUIRE(CHECK_COLUMN(result, 0, {39}));
354
355 // limit
356 REQUIRE_NOTHROW(result = tbl->Limit(1)->Query("test", "SELECT * FROM test"));
357 REQUIRE(CHECK_COLUMN(result, 0, {1}));
358 // order
359 REQUIRE_NOTHROW(result = tbl->Order("i DESC")->Limit(1)->Query("test", "SELECT * FROM test"));
360 REQUIRE(CHECK_COLUMN(result, 0, {3}));
361 // union
362 auto node = tbl->Order("i DESC")->Limit(1);
363 REQUIRE_NOTHROW(result = node->Union(node)->Query("test", "SELECT * FROM test"));
364 REQUIRE(CHECK_COLUMN(result, 0, {3, 3}));
365 // distinct
366 REQUIRE_NOTHROW(result = node->Union(node)->Distinct()->Query("test", "SELECT * FROM test"));
367 REQUIRE(CHECK_COLUMN(result, 0, {3}));
368
369 // manually create views and query from them
370 result = con.Query("SELECT i+1 FROM integers UNION SELECT i+10 FROM integers ORDER BY 1");
371 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4, 11, 12, 13}));
372
373 tbl->Project("i + 1")->CreateView("test1");
374 tbl->Project("i + 10")->CreateView("test2");
375 result = con.Query("SELECT * FROM test1 UNION SELECT * FROM test2 ORDER BY 1");
376 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4, 11, 12, 13}));
377}
378
379TEST_CASE("Test table creations using the relation API", "[relation_api]") {
380 DuckDB db(nullptr);
381 Connection con(db);
382 con.EnableQueryVerification();
383 unique_ptr<QueryResult> result;
384 shared_ptr<Relation> values;
385
386 // create a table from a Values statement
387 REQUIRE_NOTHROW(values = con.Values({{1, 10}, {2, 5}, {3, 4}}, {"i", "j"}));
388 REQUIRE_NOTHROW(values->Create("integers"));
389
390 result = con.Query("SELECT * FROM integers ORDER BY i");
391 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
392 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4}));
393
394 // insert from a set of values
395 REQUIRE_NOTHROW(con.Values({{4, 7}, {5, 8}})->Insert("integers"));
396
397 result = con.Query("SELECT * FROM integers ORDER BY i");
398 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
399 REQUIRE(CHECK_COLUMN(result, 1, {10, 5, 4, 7, 8}));
400
401 // create a table from a query
402 REQUIRE_NOTHROW(
403 con.Table("integers")->Filter("i BETWEEN 3 AND 4")->Project("i + 1 AS k, 'hello' AS l")->Create("new_values"));
404
405 result = con.Query("SELECT * FROM new_values ORDER BY k");
406 REQUIRE(CHECK_COLUMN(result, 0, {4, 5}));
407 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "hello"}));
408}
409
410TEST_CASE("Test table deletions and updates", "[relation_api]") {
411 DuckDB db(nullptr);
412 Connection con(db);
413 con.EnableQueryVerification();
414 unique_ptr<QueryResult> result;
415
416 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
417 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
418
419 auto tbl = con.Table("integers");
420
421 // update
422 tbl->Update("i=i+10", "i=2");
423
424 result = con.Query("SELECT * FROM integers ORDER BY 1");
425 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 12}));
426
427 // we can only have a single expression in the condition liset
428 REQUIRE_THROWS(tbl->Update("i=1", "i=3,i<100"));
429
430 tbl->Delete("i=3");
431
432 result = con.Query("SELECT * FROM integers ORDER BY 1");
433 REQUIRE(CHECK_COLUMN(result, 0, {1, 12}));
434
435 // delete without condition
436 tbl->Delete();
437
438 result = con.Query("SELECT * FROM integers ORDER BY 1");
439 REQUIRE(CHECK_COLUMN(result, 0, {}));
440
441 // we cannot run update/delete on anything but base table relations
442 REQUIRE_THROWS(tbl->Limit(1)->Delete());
443 REQUIRE_THROWS(tbl->Limit(1)->Update("i=1"));
444}
445
446TEST_CASE("Test aggregates in relation API", "[relation_api]") {
447 DuckDB db(nullptr);
448 Connection con(db);
449 unique_ptr<QueryResult> result;
450
451 // create a table
452 REQUIRE_NOTHROW(con.Values("(1, 5), (2, 6), (1, 7)", {"i", "j"})->Create("integers"));
453
454 // perform some aggregates
455 auto tbl = con.Table("integers");
456
457 // ungrouped aggregate
458 REQUIRE_NOTHROW(result = tbl->Aggregate("SUM(i), SUM(j)")->Execute());
459 REQUIRE(CHECK_COLUMN(result, 0, {4}));
460 REQUIRE(CHECK_COLUMN(result, 1, {18}));
461 // we cannot put aggregates in a Project clause
462 REQUIRE_THROWS(result = tbl->Project("SUM(i), SUM(j)")->Execute());
463 REQUIRE_THROWS(result = tbl->Project("i, SUM(j)")->Execute());
464 // implicitly grouped aggregate
465 REQUIRE_NOTHROW(result = tbl->Aggregate("i, SUM(j)")->Order("1")->Execute());
466 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
467 REQUIRE(CHECK_COLUMN(result, 1, {12, 6}));
468 REQUIRE_NOTHROW(result = tbl->Aggregate("SUM(j), i")->Order("2")->Execute());
469 REQUIRE(CHECK_COLUMN(result, 0, {12, 6}));
470 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
471 // grouped aggregates can be expressions
472 REQUIRE_NOTHROW(result = tbl->Aggregate("i+1 AS i, SUM(j)")->Order("1")->Execute());
473 REQUIRE(CHECK_COLUMN(result, 0, {2, 3}));
474 REQUIRE(CHECK_COLUMN(result, 1, {12, 6}));
475 // they can also involve multiple columns
476 REQUIRE_NOTHROW(result = tbl->Aggregate("i+i AS i, SUM(j)")->Order("1")->Execute());
477 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
478 REQUIRE(CHECK_COLUMN(result, 1, {12, 6}));
479 // we cannot combine non-aggregates with aggregates
480 REQUIRE_THROWS(result = tbl->Aggregate("i + SUM(j) AS i")->Order("1")->Execute());
481 REQUIRE_THROWS(result = tbl->Aggregate("i, i + SUM(j)")->Order("1")->Execute());
482 // group by multiple columns
483 REQUIRE_NOTHROW(result = tbl->Aggregate("i, j, SUM(i + j)")->Order("1, 2")->Execute());
484 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2}));
485 REQUIRE(CHECK_COLUMN(result, 1, {5, 7, 6}));
486 REQUIRE(CHECK_COLUMN(result, 2, {6, 8, 8}));
487 // subqueries as groups
488 REQUIRE_NOTHROW(result = tbl->Aggregate("(SELECT i), SUM(i + j)")->Order("1")->Execute());
489 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
490 REQUIRE(CHECK_COLUMN(result, 1, {14, 8}));
491 // subqueries as aggregates
492 REQUIRE_NOTHROW(result = tbl->Aggregate("(SELECT i), (SELECT SUM(i + j))")->Order("1")->Execute());
493 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
494 REQUIRE(CHECK_COLUMN(result, 1, {14, 8}));
495 // constants without a grouping column
496 REQUIRE_NOTHROW(result = tbl->Aggregate("'hello', SUM(i + j)")->Order("1")->Execute());
497 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
498 REQUIRE(CHECK_COLUMN(result, 1, {22}));
499 // constants with a grouping column
500 REQUIRE_NOTHROW(result = tbl->Aggregate("i, 'hello', SUM(i + j)")->Order("1")->Execute());
501 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
502 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "hello"}));
503 REQUIRE(CHECK_COLUMN(result, 2, {14, 8}));
504 // aggregate with only non-aggregate columns becomes a distinct
505 REQUIRE_NOTHROW(result = tbl->Aggregate("i")->Order("1")->Execute());
506 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
507 REQUIRE_NOTHROW(result = tbl->Aggregate("i, j")->Order("1, 2")->Execute());
508 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2}));
509 REQUIRE(CHECK_COLUMN(result, 1, {5, 7, 6}));
510
511 // now test aggregates with explicit groups
512 REQUIRE_NOTHROW(result = tbl->Aggregate("i", "i")->Order("1")->Execute());
513 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
514 REQUIRE_NOTHROW(result = tbl->Aggregate("i, SUM(j)", "i")->Order("1")->Execute());
515 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
516 REQUIRE(CHECK_COLUMN(result, 1, {12, 6}));
517 // explicit groups can be combined with aggregates
518 REQUIRE_NOTHROW(result = tbl->Aggregate("i, i+SUM(j)", "i")->Order("1")->Execute());
519 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
520 REQUIRE(CHECK_COLUMN(result, 1, {13, 8}));
521 // when using explicit groups, we cannot have non-explicit groups
522 REQUIRE_THROWS(tbl->Aggregate("j, i+SUM(j)", "i")->Order("1")->Execute());
523
524 // project -> aggregate -> project -> aggregate
525 // SUM(j) = 18 -> 18 + 1 = 19 -> 19 * 2 = 38
526 result = tbl->Aggregate("SUM(j) AS k")->Project("k+1 AS l")->Aggregate("SUM(l) AS m")->Project("m*2")->Execute();
527 REQUIRE(CHECK_COLUMN(result, 0, {38}));
528
529 // aggregate after output modifiers
530 result = tbl->Order("i")->Limit(100)->Aggregate("SUM(j) AS k")->Execute();
531 REQUIRE(CHECK_COLUMN(result, 0, {18}));
532}
533
534TEST_CASE("Test interaction of relations with transactions", "[relation_api]") {
535 DuckDB db(nullptr);
536 Connection con1(db), con2(db);
537 unique_ptr<QueryResult> result;
538
539 con1.BeginTransaction();
540 con2.BeginTransaction();
541
542 // create a table in con1
543 REQUIRE_NOTHROW(con1.Values("(1), (2), (3)")->Create("integers"));
544
545 // con1 can see it, but con2 can't see it yet
546 REQUIRE_NOTHROW(con1.Table("integers"));
547 REQUIRE_THROWS(con2.Table("integers"));
548
549 // we can also rollback
550 con1.Rollback();
551
552 REQUIRE_THROWS(con1.Table("integers"));
553 REQUIRE_THROWS(con2.Table("integers"));
554
555 // recreate the table, this time in auto-commit mode
556 REQUIRE_NOTHROW(con1.Values("(1), (2), (3)")->Create("integers"));
557
558 // con2 still can't see it, because it is in its own transaction
559 REQUIRE_NOTHROW(con1.Table("integers"));
560 REQUIRE_THROWS(con2.Table("integers"));
561
562 // after con2 commits, both can see the table
563 con2.Commit();
564
565 REQUIRE_NOTHROW(con1.Table("integers"));
566 REQUIRE_NOTHROW(con2.Table("integers"));
567}
568
569TEST_CASE("Test interaction of relations with schema changes", "[relation_api]") {
570 DuckDB db(nullptr);
571 Connection con(db);
572 unique_ptr<QueryResult> result;
573
574 // create some tables
575 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
576 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
577
578 // create a table scan of the integers table
579 auto tbl_scan = con.Table("integers")->Project("i+1")->Order("1");
580 REQUIRE_NOTHROW(result = tbl_scan->Execute());
581 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
582
583 // now drop the table
584 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
585
586 // the scan now fails, because the table is dropped!
587 REQUIRE_FAIL(tbl_scan->Execute());
588
589 // if we recreate the table, it works again
590 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
591 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
592
593 REQUIRE_NOTHROW(result = tbl_scan->Execute());
594 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
595
596 // but what if we recreate an incompatible table?
597 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
598 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i VARCHAR)"));
599 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES ('hello')"));
600
601 // this results in a binding error!
602 REQUIRE_FAIL(tbl_scan->Execute());
603
604 // now what if we run a query that still binds successfully, but changes result?
605 auto tbl = con.Table("integers");
606 REQUIRE_NO_FAIL(tbl->Execute());
607
608 // add extra columns
609 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
610 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i VARCHAR, j VARCHAR)"));
611 REQUIRE_FAIL(tbl->Execute());
612
613 // change type of column
614 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
615 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i DATE)"));
616 REQUIRE_FAIL(tbl->Execute());
617
618 // different name also results in an error
619 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
620 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(k VARCHAR)"));
621 REQUIRE_FAIL(tbl->Execute());
622
623 // but once we go back to the original table it works again!
624 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
625 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i VARCHAR)"));
626 REQUIRE_NO_FAIL(tbl->Execute());
627}
628
629TEST_CASE("Test junk SQL in expressions", "[relation_api]") {
630 DuckDB db(nullptr);
631 Connection con(db);
632 unique_ptr<QueryResult> result;
633
634 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
635 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
636
637 auto tbl = con.Table("integers");
638 REQUIRE_THROWS(tbl->Filter("1=1; DELETE FROM tables;"));
639 REQUIRE_THROWS(tbl->Filter("1=1 UNION SELECT 42"));
640 REQUIRE_THROWS(tbl->Order("1 DESC; DELETE FROM tables;"));
641 REQUIRE_THROWS(tbl->Update("i=1; DELETE FROM TABLES"));
642 REQUIRE_THROWS(con.Values("(1, 1); SELECT 42"));
643 REQUIRE_THROWS(con.Values("(1, 1) UNION SELECT 42"));
644}
645
646TEST_CASE("We cannot mix statements from multiple databases", "[relation_api]") {
647 DuckDB db(nullptr), db2(nullptr);
648 Connection con(db), con2(db2);
649 unique_ptr<QueryResult> result;
650
651 // create some tables
652 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
653 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
654 REQUIRE_NO_FAIL(con2.Query("CREATE TABLE integers(i INTEGER)"));
655 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (4)"));
656
657 auto i1 = con.Table("integers");
658 auto i2 = con2.Table("integers");
659
660 // we cannot mix statements from different connections without a wrapper!
661 REQUIRE_THROWS(i2->Union(i1));
662 REQUIRE_THROWS(i2->Except(i1));
663 REQUIRE_THROWS(i2->Intersect(i1));
664 REQUIRE_THROWS(i2->Join(i1, "i"));
665
666 // FIXME: what about a wrapper to scan data from other databases/connections?
667}
668
669TEST_CASE("Test view relations", "[relation_api]") {
670 DuckDB db(nullptr);
671 Connection con(db);
672 unique_ptr<QueryResult> result;
673
674 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
675 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
676 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT i+1 AS i, i+2 FROM integers"));
677 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v2(a,b) AS SELECT i+1, i+2 FROM integers"));
678
679 auto i1 = con.View("v1");
680 result = i1->Execute();
681 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
682 REQUIRE(CHECK_COLUMN(result, 1, {3, 4, 5}));
683
684 result = con.View("v2")->Project("a+b")->Execute();
685 REQUIRE(CHECK_COLUMN(result, 0, {5, 7, 9}));
686
687 // non-existant view
688 REQUIRE_THROWS(con.View("blabla"));
689
690 // combining views
691 result = con.View("v1")->Join(con.View("v2"), "v1.i=v2.a")->Execute();
692 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 4}));
693 REQUIRE(CHECK_COLUMN(result, 1, {3, 4, 5}));
694 REQUIRE(CHECK_COLUMN(result, 2, {2, 3, 4}));
695 REQUIRE(CHECK_COLUMN(result, 3, {3, 4, 5}));
696}
697
698TEST_CASE("Test table function relations", "[relation_api]") {
699 DuckDB db(nullptr);
700 Connection con(db);
701 unique_ptr<QueryResult> result;
702
703 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
704
705 auto i1 = con.TableFunction("sqlite_master");
706 result = i1->Execute();
707 REQUIRE(CHECK_COLUMN(result, 0, {"table"}));
708 REQUIRE(CHECK_COLUMN(result, 1, {"integers"}));
709
710 // function with parameters
711 auto i2 = con.TableFunction("pragma_table_info", {"integers"});
712 result = i2->Execute();
713 REQUIRE(CHECK_COLUMN(result, 0, {0}));
714 REQUIRE(CHECK_COLUMN(result, 1, {"i"}));
715 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER"}));
716
717 // we can do ops on table functions
718 result = i2->Filter("cid=0")->Project("concat(name, ' ', type), length(name), reverse(lower(type))")->Execute();
719 REQUIRE(CHECK_COLUMN(result, 0, {"i INTEGER"}));
720 REQUIRE(CHECK_COLUMN(result, 1, {1}));
721 REQUIRE(CHECK_COLUMN(result, 2, {"regetni"}));
722
723 // non-existant table function
724 REQUIRE_THROWS(con.TableFunction("blabla"));
725}
726