1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5
6#include "duckdb.hpp"
7#include "duckdb/parser/parsed_data/create_table_function_info.hpp"
8#include "duckdb/function/table_function.hpp"
9#include "duckdb/function/scalar_function.hpp"
10#include "duckdb/execution/operator/list.hpp"
11#include "duckdb/catalog/catalog_entry/list.hpp"
12#include "duckdb/function/function.hpp"
13#include "duckdb/planner/expression/list.hpp"
14#include "duckdb/parser/expression/function_expression.hpp"
15#include "duckdb/main/client_context.hpp"
16#include "duckdb/function/aggregate_function.hpp"
17#include "duckdb/parser/parsed_data/create_aggregate_function_info.hpp"
18
19using namespace duckdb;
20using namespace std;
21
22TEST_CASE("Test scalar lists", "[nested]") {
23 DuckDB db(nullptr);
24 Connection con(db);
25 con.EnableQueryVerification();
26 unique_ptr<QueryResult> result;
27
28 con.Query("CREATE TABLE list_data (g INTEGER, e INTEGER)");
29 con.Query("INSERT INTO list_data VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5), (3, 6), (5, NULL)");
30
31 result = con.Query("SELECT LIST_VALUE(1, 2, 3, '4') a, LIST_VALUE('a','b','c') b, LIST_VALUE(42, NULL) c, "
32 "LIST_VALUE(NULL, NULL, NULL) d, LIST_VALUE() e");
33 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({1, 2, 3, 4})}));
34 REQUIRE(CHECK_COLUMN(result, 1, {Value::LIST({"a", "b", "c"})}));
35 REQUIRE(CHECK_COLUMN(result, 2, {Value::LIST({42, Value()})}));
36 REQUIRE(CHECK_COLUMN(result, 3, {Value::LIST({Value(), Value(), Value()})}));
37 REQUIRE(CHECK_COLUMN(result, 4, {Value::LIST({})}));
38
39 result = con.Query(
40 "SELECT a FROM (VALUES (LIST_VALUE(1, 2, 3, 4)), (LIST_VALUE()), (LIST_VALUE(NULL)), (LIST_VALUE(42))) lv(a)");
41 REQUIRE(CHECK_COLUMN(result, 0,
42 {Value::LIST({1, 2, 3, 4}), Value::LIST({}), Value::LIST({Value()}), Value::LIST({42})}));
43
44 result = con.Query("SELECT * FROM (VALUES ((LIST_VALUE()), (LIST_VALUE(NULL)), LIST_VALUE(1, 2))) lv(a)");
45 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({})}));
46 REQUIRE(CHECK_COLUMN(result, 1, {Value::LIST({Value()})}));
47 REQUIRE(CHECK_COLUMN(result, 2, {Value::LIST({Value::INTEGER(1), Value::INTEGER(2)})}));
48
49 result = con.Query("SELECT * FROM (VALUES (LIST_VALUE(1, 2)), (LIST_VALUE()), (LIST_VALUE(NULL))) lv(a)");
50 REQUIRE(CHECK_COLUMN(
51 result, 0, {Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}), Value::LIST({}), Value::LIST({Value()})}));
52
53 // casting null to list or empty list to something else should work
54 result = con.Query("SELECT LIST_VALUE(1, 2, 3) UNION ALL SELECT LIST_VALUE(NULL) UNION ALL SELECT LIST_VALUE() "
55 "UNION ALL SELECT NULL");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({1, 2, 3}), Value::LIST({Value()}), Value::LIST({}), Value()}));
57
58 result = con.Query(" SELECT NULL UNION ALL SELECT LIST_VALUE() UNION ALL SELECT LIST_VALUE(NULL) UNION ALL SELECT "
59 "LIST_VALUE(1, 2, 3)");
60 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value::LIST({}), Value::LIST({Value()}), Value::LIST({1, 2, 3})}));
61
62 // empty list should not abort UNNEST
63 result = con.Query("SELECT UNNEST(a) ua FROM (VALUES (LIST_VALUE(1, 2, 3, 4)), (LIST_VALUE()), (LIST_VALUE(NULL)), "
64 "(LIST_VALUE(42))) lv(a)");
65 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, Value(), 42}));
66
67 // TODO this should work but does not. its also kind of obscure
68 // result = con.Query("SELECT UNNEST(a) ua FROM (VALUES (LIST_VALUE()), (LIST_VALUE(1, 2, 3, 4)),
69 //(LIST_VALUE(NULL)), "
70 // "(LIST_VALUE(42))) lv(a)");
71 // REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, Value(), 42}));
72 //
73 // list child type mismatch
74 REQUIRE_FAIL(con.Query("SELECT * FROM (VALUES (LIST_VALUE(1, 2)), (LIST_VALUE()), (LIST_VALUE('a'))) lv(a)"));
75
76 // can't cast lists to stuff
77 REQUIRE_FAIL(con.Query("SELECT CAST(LIST_VALUE(42) AS INTEGER)"));
78
79 // can't add a number to a list
80 REQUIRE_FAIL(con.Query("SELECT LIST_VALUE(42) + 4"));
81
82 // can't add a number to a list
83 REQUIRE_FAIL(con.Query("SELECT LIST_VALUE(42, 'a')"));
84
85 // can have unnest anywhere
86 result = con.Query("SELECT CAST(UNNEST(LIST_VALUE(42))+2 AS INTEGER)");
87 REQUIRE(CHECK_COLUMN(result, 0, {44}));
88
89 result = con.Query("SELECT LIST_VALUE(g, e, 42, NULL) FROM list_data WHERE g > 2");
90 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({3, 6, 42, Value()}), Value::LIST({5, Value(), 42, Value()})}));
91
92 result = con.Query("SELECT CASE WHEN g = 2 THEN LIST_VALUE(g, e, 42) ELSE LIST_VALUE(84, NULL) END FROM list_data "
93 "WHERE g > 1 UNION ALL SELECT LIST_VALUE(NULL)");
94 REQUIRE(CHECK_COLUMN(result, 0,
95 {Value::LIST({2, 3, 42}), Value::LIST({2, 4, 42}), Value::LIST({2, 5, 42}),
96 Value::LIST({84, Value()}), Value::LIST({84, Value()}), Value::LIST({Value()})}));
97
98 // this should fail because the list child types do not match
99 REQUIRE_FAIL(con.Query(
100 "SELECT CASE WHEN g = 2 THEN LIST_VALUE(g, e, 42) ELSE LIST_VALUE('eeek') END FROM list_data WHERE g > 1"));
101}
102
103TEST_CASE("Test filter and projection of nested lists", "[nested]") {
104 DuckDB db(nullptr);
105 Connection con(db);
106 con.EnableQueryVerification();
107 unique_ptr<QueryResult> result;
108
109 con.Query("CREATE TABLE list_data (g INTEGER, e INTEGER)");
110 con.Query("INSERT INTO list_data VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5), (3, 6), (5, NULL)");
111
112 result = con.Query("SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 (a)");
113 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)})}));
114
115 result = con.Query("SELECT UNNEST(l1) FROM (SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 (a)) t1");
116 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
117
118 result = con.Query("SELECT * FROM (SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 (a)) t1, (SELECT LIST(b) l2 "
119 "FROM (VALUES (4), (5), (6), (7)) AS t2 (b)) t2");
120 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)})}));
121 REQUIRE(CHECK_COLUMN(result, 1,
122 {Value::LIST({Value::INTEGER(4), Value::INTEGER(5), Value::INTEGER(6), Value::INTEGER(7)})}));
123
124 result = con.Query("SELECT UNNEST(l1) u1, UNNEST(l2) u2 FROM (SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 "
125 "(a)) t1, (SELECT LIST(b) l2 FROM (VALUES (4), (5), (6), (7)) AS t2 (b)) t2");
126 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, Value()}));
127 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6, 7}));
128
129 result = con.Query("SELECT UNNEST(l1), l2 FROM (SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 (a)) t1, "
130 "(SELECT LIST(b) l2 FROM (VALUES (4), (5), (6), (7)) AS t2 (b)) t2");
131 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
132 REQUIRE(CHECK_COLUMN(result, 1,
133 {Value::LIST({Value::INTEGER(4), Value::INTEGER(5), Value::INTEGER(6), Value::INTEGER(7)}),
134 Value::LIST({Value::INTEGER(4), Value::INTEGER(5), Value::INTEGER(6), Value::INTEGER(7)}),
135 Value::LIST({Value::INTEGER(4), Value::INTEGER(5), Value::INTEGER(6), Value::INTEGER(7)})}));
136
137 result = con.Query("SELECT l1, UNNEST(l2) FROM (SELECT LIST(a) l1 FROM (VALUES (1), (2), (3)) AS t1 (a)) t1, "
138 "(SELECT LIST(b) l2 FROM (VALUES (4), (5), (6), (7)) AS t2 (b)) t2");
139 REQUIRE(CHECK_COLUMN(result, 0,
140 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)}),
141 Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)}),
142 Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)}),
143 Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3)})}));
144 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6, 7}));
145
146 result = con.Query("SELECT UNNEST(LIST(e)) ue, LIST(g) from list_data");
147 REQUIRE(CHECK_COLUMN(result, 0,
148 {Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5),
149 Value::INTEGER(6), Value()}));
150 REQUIRE(CHECK_COLUMN(result, 1,
151 {Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
152 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
153 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
154 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
155 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
156 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
157 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
158 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
159 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
160 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
161 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
162 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)}),
163 Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
164 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)})}));
165
166 result = con.Query("SELECT g, LIST(e) from list_data GROUP BY g ORDER BY g");
167 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
168 REQUIRE(CHECK_COLUMN(result, 1,
169 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}),
170 Value::LIST({Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5)}),
171 Value::LIST({Value::INTEGER(6)}), Value::LIST({Value()})}));
172
173 result = con.Query("SELECT g, LIST(e) l1, LIST(e) l2 from list_data GROUP BY g ORDER BY g");
174 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
175 REQUIRE(CHECK_COLUMN(result, 1,
176 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}),
177 Value::LIST({Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5)}),
178 Value::LIST({Value::INTEGER(6)}), Value::LIST({Value()})}));
179 REQUIRE(CHECK_COLUMN(result, 2,
180 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}),
181 Value::LIST({Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5)}),
182 Value::LIST({Value::INTEGER(6)}), Value::LIST({Value()})}));
183
184 result = con.Query("SELECT g, LIST(e/2.0) from list_data GROUP BY g order by g");
185 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
186 REQUIRE(CHECK_COLUMN(result, 1,
187 {Value::LIST({Value::DOUBLE(0.5), Value::DOUBLE(1)}),
188 Value::LIST({Value::DOUBLE(1.5), Value::DOUBLE(2), Value::DOUBLE(2.5)}),
189 Value::LIST({Value::DOUBLE(3)}), Value::LIST({Value()})}));
190
191 result = con.Query("SELECT g, LIST(CAST(e AS VARCHAR)) from list_data GROUP BY g order by g");
192 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
193 REQUIRE(CHECK_COLUMN(result, 1,
194 {Value::LIST({Value("1"), Value("2")}), Value::LIST({Value("3"), Value("4"), Value("5")}),
195 Value::LIST({Value("6")}), Value::LIST({Value()})}));
196
197 result = con.Query("SELECT LIST(e) from list_data");
198 REQUIRE(CHECK_COLUMN(result, 0,
199 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(4),
200 Value::INTEGER(5), Value::INTEGER(6), Value()})}));
201
202 result = con.Query("SELECT UNNEST(LIST(e)) ue from list_data ORDER BY ue");
203 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4, 5, 6}));
204
205 result = con.Query("SELECT LIST(e), LIST(g) from list_data");
206 REQUIRE(CHECK_COLUMN(result, 0,
207 {Value::LIST({Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(4),
208 Value::INTEGER(5), Value::INTEGER(6), Value()})}));
209 REQUIRE(CHECK_COLUMN(result, 1,
210 {Value::LIST({Value::INTEGER(1), Value::INTEGER(1), Value::INTEGER(2), Value::INTEGER(2),
211 Value::INTEGER(2), Value::INTEGER(3), Value::INTEGER(5)})}));
212
213 result = con.Query("SELECT LIST(42)");
214 REQUIRE(CHECK_COLUMN(result, 0, {Value::LIST({Value::INTEGER(42)})}));
215
216 result = con.Query("SELECT LIST(42) FROM list_data");
217 REQUIRE(CHECK_COLUMN(result, 0,
218 {Value::LIST({Value::INTEGER(42), Value::INTEGER(42), Value::INTEGER(42), Value::INTEGER(42),
219 Value::INTEGER(42), Value::INTEGER(42), Value::INTEGER(42)})}));
220
221 result = con.Query("SELECT UNNEST(LIST(42))");
222 REQUIRE(CHECK_COLUMN(result, 0, {42}));
223
224 // unlist is alias of unnest for symmetry reasons
225 result = con.Query("SELECT UNLIST(LIST(42))");
226 REQUIRE(CHECK_COLUMN(result, 0, {42}));
227
228 result = con.Query("SELECT UNNEST(LIST(e)) ue, UNNEST(LIST(g)) ug from list_data ORDER BY ue");
229 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1, 2, 3, 4, 5, 6}));
230 REQUIRE(CHECK_COLUMN(result, 1, {5, 1, 1, 2, 2, 2, 3}));
231
232 result = con.Query("SELECT g, UNNEST(LIST(e)) ue, UNNEST(LIST(e+1)) ue2 from list_data GROUP BY g ORDER BY ue");
233 REQUIRE(CHECK_COLUMN(result, 0, {5, 1, 1, 2, 2, 2, 3}));
234 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3, 4, 5, 6}));
235 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 2, 3, 4, 5, 6, 7}));
236
237 result = con.Query("SELECT g, UNNEST(l) u FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1 ORDER BY u");
238 REQUIRE(CHECK_COLUMN(result, 0, {5, 1, 1, 2, 2, 2, 3}));
239 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2, 3, 4, 5, 6}));
240
241 result = con.Query("SELECT g, UNNEST(l)+1 u FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1 ORDER BY u");
242 REQUIRE(CHECK_COLUMN(result, 0, {5, 1, 1, 2, 2, 2, 3}));
243 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 2, 3, 4, 5, 6, 7}));
244
245 // omg omg, list of structs, structs of lists
246
247 result =
248 con.Query("SELECT g, STRUCT_PACK(a := g, b := le) sl FROM (SELECT g, LIST(e) le from list_data GROUP BY g) "
249 "xx WHERE g < 3 ORDER BY g");
250 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
251 REQUIRE(CHECK_COLUMN(
252 result, 1,
253 {Value::STRUCT(
254 {make_pair("a", Value::INTEGER(1)), make_pair("b", Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}))}),
255 Value::STRUCT({make_pair("a", Value::INTEGER(2)),
256 make_pair("b", Value::LIST({Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5)}))})}));
257
258 result = con.Query("SELECT LIST(STRUCT_PACK(a := g, b := le)) mind_blown FROM (SELECT g, LIST(e) le from list_data "
259 " GROUP BY g ORDER BY g) xx");
260
261 REQUIRE(CHECK_COLUMN(
262 result, 0,
263 {Value::LIST(
264 {Value::STRUCT({make_pair("a", Value::INTEGER(1)),
265 make_pair("b", Value::LIST({Value::INTEGER(1), Value::INTEGER(2)}))}),
266 Value::STRUCT({make_pair("a", Value::INTEGER(2)),
267 make_pair("b", Value::LIST({Value::INTEGER(3), Value::INTEGER(4), Value::INTEGER(5)}))}),
268 Value::STRUCT({make_pair("a", Value::INTEGER(3)), make_pair("b", Value::LIST({Value::INTEGER(6)}))}),
269 Value::STRUCT({make_pair("a", Value::INTEGER(5)), make_pair("b", Value::LIST({Value()}))})})}));
270
271 result = con.Query("SELECT g, LIST(STRUCT_PACK(a := e, b := e+1)) ls from list_data GROUP BY g ORDER BY g");
272 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 5}));
273 // TODO check second col
274
275 result =
276 con.Query("SELECT g, LIST(STRUCT_PACK(a := e, b := e+1)) ls from list_data WHERE g > 2GROUP BY g ORDER BY g");
277 REQUIRE(CHECK_COLUMN(result, 0, {3, 5}));
278 REQUIRE(CHECK_COLUMN(
279 result, 1,
280 {Value::LIST({Value::STRUCT({make_pair("a", Value::INTEGER(6)), make_pair("b", Value::INTEGER(7))})}),
281 Value::LIST({Value::STRUCT({make_pair("a", Value()), make_pair("b", Value())})})}));
282
283 // list of list of int
284 result = con.Query(
285 "SELECT g2, LIST(le) FROM (SELECT g % 2 g2, LIST(e) le from list_data GROUP BY g ORDER BY g) sq GROUP "
286 "BY g2 ORDER BY g2");
287 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
288
289 REQUIRE(CHECK_COLUMN(result, 1,
290 {Value::LIST({Value::LIST({3, 4, 5})}),
291 Value::LIST({Value::LIST({1, 2}), Value::LIST({6}), Value::LIST({Value()})})}));
292
293 result = con.Query("SELECT SUM(ue) FROM (SELECT UNNEST(le) ue FROM (SELECT g, LIST(e) le from list_data "
294 " GROUP BY g ORDER BY g) xx) xy");
295 REQUIRE(CHECK_COLUMN(result, 0, {21}));
296 // this is technically equivalent but is not supported
297 REQUIRE_FAIL(con.Query("SELECT SUM(UNNEST(le)) FROM ( SELECT g, LIST(e) le from list_data "
298 " GROUP BY g ORDER BY g) xx"));
299
300 // you're holding it wrong
301 REQUIRE_FAIL(con.Query("SELECT LIST(LIST(42))"));
302 REQUIRE_FAIL(con.Query("SELECT UNNEST(UNNEST(LIST(42))"));
303
304 REQUIRE_FAIL(con.Query("SELECT LIST()"));
305 REQUIRE_FAIL(con.Query("SELECT LIST() FROM list_data"));
306 REQUIRE_FAIL(con.Query("SELECT LIST(e, g) FROM list_data"));
307
308 REQUIRE_FAIL(con.Query("SELECT g, UNNEST(l+1) u FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1"));
309 REQUIRE_FAIL(con.Query("SELECT g, UNNEST(g) u FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1"));
310 REQUIRE_FAIL(con.Query("SELECT g, UNNEST() u FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1"));
311
312 REQUIRE_FAIL(con.Query("SELECT UNNEST(42)"));
313 REQUIRE_FAIL(con.Query("SELECT UNNEST()"));
314 REQUIRE_FAIL(con.Query("SELECT UNNEST(42) from list_data"));
315 REQUIRE_FAIL(con.Query("SELECT UNNEST() from list_data"));
316 REQUIRE_FAIL(con.Query("SELECT g FROM (SELECT g, LIST(e) l FROM list_data GROUP BY g) u1 where UNNEST(l) > 42"));
317}
318
319TEST_CASE("Test packing and unpacking lineitem into lists", "[nested][.]") {
320 DuckDB db(nullptr);
321 Connection con(db);
322 unique_ptr<MaterializedQueryResult> result;
323 con.EnableQueryVerification(); // FIXME something odd happening here
324 auto sf = 0.01;
325 // TODO this has a small limit in it right now because of performance issues. Fix this.
326 tpch::dbgen(sf, db, DEFAULT_SCHEMA, "_org");
327
328 REQUIRE_NO_FAIL(con.Query("CREATE TABLE lineitem_small AS SELECT * FROM lineitem_org LIMIT 1050;"));
329
330 REQUIRE_NO_FAIL(con.Query("CREATE OR REPLACE VIEW lineitem AS SELECT * FROM lineitem_small"));
331 // run the regular Q1 on the small lineitem set
332 result = con.Query(tpch::get_query(1));
333 // construct the expected values from the regular query
334 vector<vector<Value>> expected_values;
335 for (idx_t col_idx = 0; col_idx < result->sql_types.size(); col_idx++) {
336 vector<Value> column_list;
337 for (idx_t row_idx = 0; row_idx < result->collection.count; row_idx++) {
338 column_list.push_back(result->GetValue(col_idx, row_idx));
339 }
340 expected_values.push_back(column_list);
341 }
342
343 REQUIRE_NO_FAIL(con.Query(
344 "CREATE OR REPLACE VIEW lineitem AS SELECT l_orderkey, STRUCT_EXTRACT(struct, 'l_partkey') l_partkey, "
345 "STRUCT_EXTRACT(struct, 'l_suppkey') l_suppkey, STRUCT_EXTRACT(struct, 'l_linenumber') l_linenumber, "
346 "STRUCT_EXTRACT(struct, 'l_quantity') l_quantity, STRUCT_EXTRACT(struct, 'l_extendedprice') l_extendedprice, "
347 "STRUCT_EXTRACT(struct, 'l_discount') l_discount, STRUCT_EXTRACT(struct, 'l_tax') l_tax, "
348 "STRUCT_EXTRACT(struct, 'l_returnflag') l_returnflag, STRUCT_EXTRACT(struct, 'l_linestatus') l_linestatus, "
349 "STRUCT_EXTRACT(struct, 'l_shipdate') l_shipdate, STRUCT_EXTRACT(struct, 'l_commitdate') l_commitdate, "
350 "STRUCT_EXTRACT(struct, 'l_receiptdate') l_receiptdate, STRUCT_EXTRACT(struct, 'l_shipinstruct') "
351 "l_shipinstruct, STRUCT_EXTRACT(struct, 'l_shipmode') l_shipmode, STRUCT_EXTRACT(struct, 'l_comment') "
352 "l_comment FROM (SELECT l_orderkey, UNLIST(rest) struct FROM (SELECT l_orderkey, LIST(STRUCT_PACK(l_partkey "
353 ",l_suppkey ,l_linenumber ,l_quantity ,l_extendedprice ,l_discount ,l_tax ,l_returnflag ,l_linestatus "
354 ",l_shipdate ,l_commitdate ,l_receiptdate ,l_shipinstruct ,l_shipmode ,l_comment)) rest FROM (SELECT * FROM "
355 "lineitem_small ) lss GROUP BY l_orderkey) s1) s2;"));
356 result = con.Query(tpch::get_query(1));
357 for (idx_t col_idx = 0; col_idx < expected_values.size(); col_idx++) {
358 REQUIRE(CHECK_COLUMN(result, col_idx, expected_values[col_idx]));
359 }
360
361 // database as-a-value
362 REQUIRE_NO_FAIL(con.Query(
363 "CREATE OR REPLACE VIEW lineitem AS SELECT STRUCT_EXTRACT(ls, 'l_orderkey') l_orderkey, STRUCT_EXTRACT(ls, "
364 "'l_partkey') l_partkey, STRUCT_EXTRACT(ls, 'l_suppkey') l_suppkey, STRUCT_EXTRACT(ls, 'l_linenumber') "
365 "l_linenumber, STRUCT_EXTRACT(ls, 'l_quantity') l_quantity, STRUCT_EXTRACT(ls, 'l_extendedprice') "
366 "l_extendedprice, STRUCT_EXTRACT(ls, 'l_discount') l_discount, STRUCT_EXTRACT(ls, 'l_tax') l_tax, "
367 "STRUCT_EXTRACT(ls, 'l_returnflag') l_returnflag, STRUCT_EXTRACT(ls, 'l_linestatus') l_linestatus, "
368 "STRUCT_EXTRACT(ls, 'l_shipdate') l_shipdate, STRUCT_EXTRACT(ls, 'l_commitdate') l_commitdate, "
369 "STRUCT_EXTRACT(ls, 'l_receiptdate') l_receiptdate, STRUCT_EXTRACT(ls, 'l_shipinstruct') l_shipinstruct, "
370 "STRUCT_EXTRACT(ls, 'l_shipmode') l_shipmode, STRUCT_EXTRACT(ls, 'l_comment') l_comment FROM (SELECT "
371 "UNNEST(lineitem) ls FROM (SELECT LIST(STRUCT_PACK(l_orderkey, l_partkey ,l_suppkey ,l_linenumber ,l_quantity "
372 ",l_extendedprice ,l_discount ,l_tax ,l_returnflag ,l_linestatus ,l_shipdate ,l_commitdate ,l_receiptdate "
373 ",l_shipinstruct ,l_shipmode ,l_comment)) lineitem FROM (SELECT * FROM lineitem_small) s1) s2) s3;"));
374 result = con.Query(tpch::get_query(1));
375 for (idx_t col_idx = 0; col_idx < expected_values.size(); col_idx++) {
376 REQUIRE(CHECK_COLUMN(result, col_idx, expected_values[col_idx]));
377 }
378
379 REQUIRE_NO_FAIL(con.Query(
380 "CREATE OR REPLACE VIEW lineitem AS SELECT UNNEST(STRUCT_EXTRACT(lineitem, 'll_orderkey')) l_orderkey, "
381 "UNNEST(STRUCT_EXTRACT(lineitem, 'll_partkey')) l_partkey, UNNEST(STRUCT_EXTRACT(lineitem, 'll_suppkey')) "
382 "l_suppkey, UNNEST(STRUCT_EXTRACT(lineitem, 'll_linenumber')) l_linenumber, UNNEST(STRUCT_EXTRACT(lineitem, "
383 "'ll_quantity')) l_quantity, UNNEST(STRUCT_EXTRACT(lineitem, 'll_extendedprice')) l_extendedprice, "
384 "UNNEST(STRUCT_EXTRACT(lineitem, 'll_discount')) l_discount, UNNEST(STRUCT_EXTRACT(lineitem, 'll_tax')) l_tax, "
385 "UNNEST(STRUCT_EXTRACT(lineitem, 'll_returnflag')) l_returnflag, UNNEST(STRUCT_EXTRACT(lineitem, "
386 "'ll_linestatus')) l_linestatus, UNNEST(STRUCT_EXTRACT(lineitem, 'll_shipdate')) l_shipdate, "
387 "UNNEST(STRUCT_EXTRACT(lineitem, 'll_commitdate')) l_commitdate, UNNEST(STRUCT_EXTRACT(lineitem, "
388 "'ll_receiptdate')) l_receiptdate, UNNEST(STRUCT_EXTRACT(lineitem, 'll_shipinstruct')) l_shipinstruct, "
389 "UNNEST(STRUCT_EXTRACT(lineitem, 'll_shipmode')) l_shipmode, UNNEST(STRUCT_EXTRACT(lineitem, 'll_comment')) "
390 "l_comment FROM (SELECT STRUCT_PACK(ll_orderkey:= LIST(l_orderkey), ll_partkey := LIST(l_partkey ), ll_suppkey "
391 ":= LIST(l_suppkey ), ll_linenumber := LIST(l_linenumber ), ll_quantity := LIST(l_quantity ), ll_extendedprice "
392 ":= LIST(l_extendedprice ), ll_discount := LIST(l_discount ), ll_tax := LIST(l_tax ), ll_returnflag := "
393 "LIST(l_returnflag ), ll_linestatus := LIST(l_linestatus ), ll_shipdate := LIST(l_shipdate ), ll_commitdate := "
394 "LIST(l_commitdate ), ll_receiptdate := LIST(l_receiptdate ), ll_shipinstruct := LIST(l_shipinstruct ), "
395 "ll_shipmode := LIST(l_shipmode ), ll_comment:= LIST(l_comment)) lineitem FROM (SELECT * FROM lineitem_small) "
396 "s1) s2;"));
397 result = con.Query(tpch::get_query(1));
398 for (idx_t col_idx = 0; col_idx < expected_values.size(); col_idx++) {
399 REQUIRE(CHECK_COLUMN(result, col_idx, expected_values[col_idx]));
400 }
401}
402
403// TEST_CASE("Aggregate lists", "[nested]") {
404// DuckDB db(nullptr);
405// Connection con(db);
406// con.EnableQueryVerification();
407// unique_ptr<QueryResult> result;
408//
409// result = con.Query("SELECT SUM(a), b FROM (VALUES (42, LIST_VALUE(1, 2)), (42, LIST_VALUE(3, 4, 5)), (24,
410// LIST_VALUE(1, 2))) lv(a, b) GROUP BY b"); result->Print();
411//}
412