1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4#include "duckdb.hpp"
5
6#include "dbgen.hpp"
7
8using namespace duckdb;
9using namespace std;
10
11TEST_CASE("Test filter and projection of nested struct", "[nested]") {
12 DuckDB db(nullptr);
13 Connection con(db);
14 unique_ptr<QueryResult> result;
15 con.EnableQueryVerification();
16
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE struct_data (g INTEGER, e INTEGER)"));
18 REQUIRE_NO_FAIL(
19 con.Query("INSERT INTO struct_data VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5), (3, 6), (5, NULL)"));
20
21 // all the wrong ways of holding this
22 REQUIRE_FAIL(con.Query("SELECT STRUCT_PACK() FROM struct_data"));
23 REQUIRE_FAIL(con.Query("SELECT STRUCT_PACK(e+1) FROM struct_data"));
24 REQUIRE_FAIL(con.Query("SELECT STRUCT_PACK(a := e, a := g) FROM struct_data"));
25 REQUIRE_FAIL(con.Query("SELECT STRUCT_PACK(e, e) FROM struct_data"));
26
27 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(e, 'e') FROM struct_data"));
28 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(e) FROM struct_data"));
29 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT('e') FROM struct_data"));
30 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT() FROM struct_data"));
31
32 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), 'zz') FROM struct_data"));
33 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g)) FROM struct_data"));
34 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), g) FROM struct_data"));
35 REQUIRE_FAIL(con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), '42) FROM struct_data"));
36
37 REQUIRE_FAIL(con.Query("CREATE TABLE test AS SELECT e, STRUCT_PACK(e) FROM struct_data"));
38
39 result = con.Query("SELECT STRUCT_PACK(a := 42, b := 43)");
40 REQUIRE(CHECK_COLUMN(result, 0,
41 {Value::STRUCT({make_pair("a", Value::INTEGER(42)), make_pair("b", Value::INTEGER(43))})}));
42
43 result = con.Query("SELECT e, STRUCT_PACK(e) FROM struct_data ORDER BY e LIMIT 2");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 1}));
45 REQUIRE(CHECK_COLUMN(
46 result, 1, {Value::STRUCT({make_pair("e", Value())}), Value::STRUCT({make_pair("e", Value::INTEGER(1))})}));
47
48 result = con.Query("SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), 'xx') as ee FROM struct_data");
49 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, Value()}));
50 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5, 6, Value()}));
51
52 result =
53 con.Query("SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), 'xx') as s FROM struct_data WHERE e > 4");
54 REQUIRE(CHECK_COLUMN(result, 0, {5, 6}));
55 REQUIRE(CHECK_COLUMN(result, 1, {5, 6}));
56
57 result = con.Query(
58 "SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), 'xx') as s FROM struct_data WHERE e IS NULL");
59 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
60 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
61
62 result = con.Query("SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e/2), 'xx') as s FROM struct_data WHERE e > 4");
63 REQUIRE(CHECK_COLUMN(result, 0, {5, 6}));
64 REQUIRE(CHECK_COLUMN(result, 1, {2, 3}));
65
66 result = con.Query("SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e/2), 'xx')*2 as s FROM struct_data WHERE e > 4");
67 REQUIRE(CHECK_COLUMN(result, 0, {5, 6}));
68 REQUIRE(CHECK_COLUMN(result, 1, {4, 6}));
69
70 result = con.Query(
71 "SELECT e, STRUCT_EXTRACT(STRUCT_PACK(xx := e, yy := g), 'xx') as ee FROM struct_data ORDER BY e DESC");
72 REQUIRE(CHECK_COLUMN(result, 0, {6, 5, 4, 3, 2, 1, Value()}));
73 REQUIRE(CHECK_COLUMN(result, 1, {6, 5, 4, 3, 2, 1, Value()}));
74
75 result = con.Query("SELECT e, STRUCT_EXTRACT(STRUCT_PACK(a := e, b := ROWID, c := 42), 'c') as ee FROM struct_data "
76 "ORDER BY ROWID");
77 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, Value()}));
78 REQUIRE(CHECK_COLUMN(result, 1, {42, 42, 42, 42, 42, 42, 42}));
79
80 result = con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(a := 42, b := 43), 'a') FROM struct_data");
81 REQUIRE(CHECK_COLUMN(result, 0, {42, 42, 42, 42, 42, 42, 42}));
82
83 result = con.Query("SELECT STRUCT_EXTRACT(STRUCT_PACK(a := 42, b := 43), 'a') s");
84 REQUIRE(CHECK_COLUMN(result, 0, {42}));
85
86 result = con.Query("SELECT STRUCT_EXTRACT(STRUCT_EXTRACT(STRUCT_PACK(a := STRUCT_PACK(x := 'asdf', y := NULL), b "
87 ":= 43), 'a'), 'x') s");
88 REQUIRE(CHECK_COLUMN(result, 0, {"asdf"}));
89}
90
91TEST_CASE("Test packing and unpacking lineitem into structs", "[nested][.]") {
92 DuckDB db(nullptr);
93 Connection con(db);
94 unique_ptr<QueryResult> result;
95 // con.EnableQueryVerification(); // FIXME something odd happening here
96
97 auto sf = 0.01;
98
99 tpch::dbgen(sf, db, DEFAULT_SCHEMA, "_org");
100 REQUIRE_NO_FAIL(con.Query(
101 "CREATE VIEW lineitem AS SELECT STRUCT_EXTRACT(struct, 'orderkey') l_orderkey, STRUCT_EXTRACT(struct, "
102 "'partkey') l_partkey, STRUCT_EXTRACT(struct, 'suppkey') l_suppkey, STRUCT_EXTRACT(struct, 'linenumber') "
103 "l_linenumber, STRUCT_EXTRACT(struct, 'quantity') l_quantity, STRUCT_EXTRACT(struct, 'extendedprice') "
104 "l_extendedprice, STRUCT_EXTRACT(struct, 'discount') l_discount, STRUCT_EXTRACT(struct, 'tax') l_tax, "
105 "STRUCT_EXTRACT(struct, 'returnflag') l_returnflag, STRUCT_EXTRACT(struct, 'linestatus') l_linestatus, "
106 "STRUCT_EXTRACT(struct, 'shipdate') l_shipdate, STRUCT_EXTRACT(struct, 'commitdate') l_commitdate, "
107 "STRUCT_EXTRACT(struct, 'receiptdate') l_receiptdate, STRUCT_EXTRACT(struct, 'shipinstruct') l_shipinstruct, "
108 "STRUCT_EXTRACT(struct, 'shipmode') l_shipmode, STRUCT_EXTRACT(struct, 'comment') l_comment FROM (SELECT "
109 "STRUCT_PACK(quantity := l_quantity , extendedprice := l_extendedprice , discount := l_discount , tax := l_tax "
110 ", returnflag := l_returnflag , linestatus := l_linestatus , shipdate := l_shipdate , commitdate := "
111 "l_commitdate , receiptdate := l_receiptdate , shipinstruct := l_shipinstruct , shipmode := l_shipmode , "
112 "comment := l_comment , orderkey := l_orderkey , partkey := l_partkey , suppkey := l_suppkey , linenumber := "
113 "l_linenumber) struct FROM lineitem_org) structs"));
114
115 result = con.Query(tpch::get_query(1));
116 COMPARE_CSV(result, tpch::get_answer(sf, 1), true);
117}
118