1#include "catch.hpp"
2#include "dbgen.hpp"
3#include "test_helpers.hpp"
4
5#include <chrono>
6#include <iostream>
7#include "duckdb/common/string_util.hpp"
8
9using namespace duckdb;
10using namespace std;
11
12TEST_CASE("Test TPC-H SF0.01 with relations", "[tpch][.]") {
13 unique_ptr<QueryResult> result;
14 DuckDB db(nullptr);
15 Connection con(db);
16 double sf = 0.01;
17
18 tpch::dbgen(sf, db);
19
20 auto lineitem = con.Table("lineitem");
21 auto part = con.Table("part");
22 auto supplier = con.Table("supplier");
23 auto partsupp = con.Table("partsupp");
24 auto nation = con.Table("nation");
25 auto region = con.Table("region");
26 auto orders = con.Table("orders");
27 auto customer = con.Table("customer");
28 // Q01
29 result =
30 lineitem->Filter("l_shipdate <= DATE '1998-09-02'")
31 ->Aggregate(
32 {"l_returnflag", "l_linestatus", "sum(l_quantity) AS sum_qty", "sum(l_extendedprice) AS sum_base_price",
33 "sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price",
34 "sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge", "avg(l_quantity) AS avg_qty",
35 "avg(l_extendedprice) AS avg_price", "avg(l_discount) AS avg_disc", "count(*) AS count_order"})
36 ->Order("l_returnflag, l_linestatus")
37 ->Execute();
38 COMPARE_CSV(result, tpch::get_answer(sf, 1), true);
39
40 // Q02
41 auto partsupp_region = partsupp->Join(supplier, "s_suppkey=ps_suppkey")
42 ->Join(nation, "s_nationkey=n_nationkey")
43 ->Join(region, "n_regionkey=r_regionkey");
44 partsupp_region->CreateView("partsupp_region");
45 auto part_join = partsupp_region->Join(part, "p_partkey=ps_partkey");
46 result =
47 part_join
48 ->Filter({"p_size=15", "p_type LIKE '%BRASS'", "r_name='EUROPE'",
49 "ps_supplycost = ( SELECT min(ps_supplycost) FROM partsupp_region WHERE p_partkey = ps_partkey "
50 "AND r_name = 'EUROPE')"})
51 ->Project({"s_acctbal", "s_name", "n_name", "p_partkey", "p_mfgr", "s_address", "s_phone", "s_comment"})
52 ->Order({"s_acctbal DESC", "n_name", "s_name", "p_partkey"})
53 ->Limit(100)
54 ->Execute();
55 COMPARE_CSV(result, tpch::get_answer(sf, 2), true);
56
57 // Q03
58 auto cust_join = customer->Join(orders, "c_custkey=o_custkey")->Join(lineitem, "l_orderkey=o_orderkey");
59 result =
60 cust_join
61 ->Filter({"c_mktsegment = 'BUILDING'", "o_orderdate < DATE '1995-03-15'", "l_shipdate > DATE '1995-03-15'"})
62 ->Aggregate(
63 {"l_orderkey", "sum(l_extendedprice * (1 - l_discount)) AS revenue", "o_orderdate", "o_shippriority"})
64 ->Order("revenue DESC, o_orderdate")
65 ->Limit(10)
66 ->Execute();
67 COMPARE_CSV(result, tpch::get_answer(sf, 3), true);
68
69 // Q06
70 result = lineitem
71 ->Filter({"l_shipdate >= cast('1994-01-01' AS date)", "l_shipdate < cast('1995-01-01' AS date)",
72 "l_discount BETWEEN 0.05 AND 0.07", "l_quantity < 24;"})
73 ->Aggregate("sum(l_extendedprice * l_discount) AS revenue")
74 ->Execute();
75 COMPARE_CSV(result, tpch::get_answer(sf, 6), true);
76
77 // Q12
78 result =
79 lineitem->Join(orders, "l_orderkey=o_orderkey")
80 ->Filter({"l_shipmode IN ('MAIL', 'SHIP')", "l_commitdate < l_receiptdate", "l_shipdate < l_commitdate",
81 "l_receiptdate >= cast('1994-01-01' AS date)", "l_receiptdate < cast('1995-01-01' AS date)"})
82 ->Aggregate({"l_shipmode",
83 "sum(CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) "
84 "AS high_line_count",
85 "sum(CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 "
86 "END) AS low_line_count"})
87 ->Order("l_shipmode")
88 ->Execute();
89 COMPARE_CSV(result, tpch::get_answer(sf, 12), true);
90}
91