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", "[tpch]") {
13 unique_ptr<QueryResult> result;
14 DuckDB db(nullptr);
15 Connection con(db);
16
17 tpch::dbgen(0, db);
18 for (size_t i = 1; i <= 22; i++) {
19 REQUIRE_NO_FAIL(con.Query(tpch::get_query(i)));
20 }
21}
22
23TEST_CASE("Test TPC-H SF0.01", "[tpch]") {
24 unique_ptr<QueryResult> result;
25 double sf = 0.01;
26
27 // generate the TPC-H data for SF 0.1
28 DuckDB db(nullptr);
29 Connection con(db);
30 tpch::dbgen(sf, db);
31
32 // test all the basic queries
33 for (idx_t i = 1; i <= 22; i++) {
34 result = con.Query(tpch::get_query(i));
35 COMPARE_CSV(result, tpch::get_answer(sf, i), true);
36 }
37}
38
39TEST_CASE("Test TPC-H SF0.1", "[tpch][.]") {
40 unique_ptr<QueryResult> result;
41 double sf = 0.1;
42
43 // generate the TPC-H data for SF 0.1
44 DuckDB db(nullptr);
45 Connection con(db);
46 tpch::dbgen(sf, db);
47
48 con.EnableProfiling();
49
50 // check if all the counts are correct
51 result = con.Query("SELECT COUNT(*) FROM orders");
52 REQUIRE(CHECK_COLUMN(result, 0, {150000}));
53 result = con.Query("SELECT COUNT(*) FROM lineitem");
54 REQUIRE(CHECK_COLUMN(result, 0, {600572}));
55 result = con.Query("SELECT COUNT(*) FROM part");
56 REQUIRE(CHECK_COLUMN(result, 0, {20000}));
57 result = con.Query("SELECT COUNT(*) FROM partsupp");
58 REQUIRE(CHECK_COLUMN(result, 0, {80000}));
59 result = con.Query("SELECT COUNT(*) FROM supplier");
60 REQUIRE(CHECK_COLUMN(result, 0, {1000}));
61 result = con.Query("SELECT COUNT(*) FROM customer");
62 REQUIRE(CHECK_COLUMN(result, 0, {15000}));
63 result = con.Query("SELECT COUNT(*) FROM nation");
64 REQUIRE(CHECK_COLUMN(result, 0, {25}));
65 result = con.Query("SELECT COUNT(*) FROM region");
66 REQUIRE(CHECK_COLUMN(result, 0, {5}));
67
68 result = con.Query("SELECT * FROM lineitem WHERE l_orderkey <= 1 ORDER BY l_partkey;");
69 COMPARE_CSV(result,
70 "1|214|465|4|28|31197.88|0.09|0.06|N|O|1996-04-21|1996-03-30|1996-05-"
71 "16|NONE|AIR|lites. fluffily even "
72 "de\n1|1564|67|6|32|46897.92|0.07|0.02|N|O|1996-01-30|1996-02-07|1996-"
73 "02-03|DELIVER IN PERSON|MAIL|arefully slyly "
74 "ex\n1|2403|160|5|24|31329.6|0.1|0.04|N|O|1996-03-30|1996-03-14|1996-"
75 "04-01|NONE|FOB| pending foxes. slyly "
76 "re\n1|6370|371|3|8|10210.96|0.1|0.02|N|O|1996-01-29|1996-03-05|1996-"
77 "01-31|TAKE BACK RETURN|REG AIR|riously. regular, express "
78 "dep\n1|6731|732|2|36|58958.28|0.09|0.06|N|O|1996-04-12|1996-02-28|"
79 "1996-04-20|TAKE BACK RETURN|MAIL|ly final dependencies: slyly bold "
80 "\n1|15519|785|1|17|24386.67|0.04|0.02|N|O|1996-03-13|1996-02-12|1996-"
81 "03-22|DELIVER IN PERSON|TRUCK|egular courts above the",
82 false);
83
84 result = con.Query("SELECT SUM(l_quantity) FROM lineitem");
85 REQUIRE(CHECK_COLUMN(result, 0, {15334802}));
86 result = con.Query("SELECT l_quantity % 5 AS f, COUNT(*) FROM lineitem "
87 "GROUP BY f ORDER BY f;");
88 REQUIRE(CHECK_COLUMN(result, 0, {0, 1, 2, 3, 4}));
89 REQUIRE(CHECK_COLUMN(result, 1, {119525, 120331, 120426, 119986, 120304}));
90 result = con.Query("SELECT l_returnflag, SUM(l_quantity), COUNT(*) FROM "
91 "lineitem GROUP BY l_returnflag;");
92 REQUIRE(CHECK_COLUMN(result, 0, {"A", "N", "R"}));
93 REQUIRE(CHECK_COLUMN(result, 1, {3774200, 7775079, 3785523}));
94 REQUIRE(CHECK_COLUMN(result, 2, {147790, 304481, 148301}));
95 result = con.Query("SELECT l_returnflag, SUM(l_quantity), COUNT(*) FROM lineitem WHERE "
96 "l_shipdate <= cast('1998-09-02' as date) GROUP BY l_returnflag;");
97 REQUIRE(CHECK_COLUMN(result, 0, {"A", "N", "R"}));
98 REQUIRE(CHECK_COLUMN(result, 1, {3774200, 7554554, 3785523}));
99 REQUIRE(CHECK_COLUMN(result, 2, {147790, 295765, 148301}));
100
101 // this would really hurt without pushdown
102 result = con.Query("SELECT count(*) FROM lineitem JOIN orders ON "
103 "lineitem.l_orderkey=orders.o_orderkey WHERE "
104 "o_orderstatus='X' AND l_tax > 50");
105 REQUIRE(CHECK_COLUMN(result, 0, {0}));
106
107 // test all the basic queries
108 for (idx_t i = 1; i <= 22; i++) {
109 result = con.Query(tpch::get_query(i));
110 COMPARE_CSV(result, tpch::get_answer(sf, i), true);
111 }
112}
113