1#include "catch.hpp"
2#include "dsdgen.hpp"
3#include "test_helpers.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test TPC-DS SF0 Query Compilation", "[tpcds]") {
9 DuckDB db(nullptr);
10 Connection con(db);
11 unique_ptr<QueryResult> result;
12 con.EnableQueryVerification();
13
14 // create schema only
15 tpcds::dbgen(0, db);
16
17 // this is to make sure we do not get regressions in query compilation
18 unordered_set<size_t> missing_queries = {55, 101};
19 for (size_t q = 1; q < 104; q++) {
20 if (missing_queries.count(q) != 0) {
21 continue;
22 }
23 REQUIRE_NO_FAIL(con.Query(tpcds::get_query(q)));
24 }
25}
26
27TEST_CASE("Test TPC-DS SF0.1 Query Execution", "[tpcds][.]") {
28 DuckDB db(nullptr);
29 Connection con(db);
30 unique_ptr<QueryResult> result;
31
32 tpcds::dbgen(0.01, db);
33 con.EnableProfiling();
34
35 unordered_set<size_t> missing_queries = {14, 40, 51, 55, 68, 76, 86, 89, 101};
36 for (size_t q = 1; q < 104; q++) {
37 if (missing_queries.count(q) != 0) {
38 continue;
39 }
40 REQUIRE_NO_FAIL(con.Query(tpcds::get_query(q)));
41 }
42}
43
44TEST_CASE("Test TPC-DS SF1", "[tpcds][.]") {
45 return;
46 DuckDB db(nullptr);
47 Connection con(db);
48 unique_ptr<QueryResult> result;
49
50 // create and load data
51 tpcds::dbgen(1, db);
52
53 // verify table counts
54 result = con.Query("SELECT COUNT(*) FROM call_center");
55 REQUIRE(CHECK_COLUMN(result, 0, {6}));
56 result = con.Query("SELECT COUNT(*) FROM catalog_page");
57 REQUIRE(CHECK_COLUMN(result, 0, {11718}));
58 result = con.Query("SELECT COUNT(*) FROM catalog_returns");
59 REQUIRE(CHECK_COLUMN(result, 0, {144067}));
60 result = con.Query("SELECT COUNT(*) FROM catalog_sales");
61 REQUIRE(CHECK_COLUMN(result, 0, {1441548}));
62 result = con.Query("SELECT COUNT(*) FROM customer");
63 REQUIRE(CHECK_COLUMN(result, 0, {100000}));
64 result = con.Query("SELECT COUNT(*) FROM customer_demographics");
65 REQUIRE(CHECK_COLUMN(result, 0, {1920800}));
66 result = con.Query("SELECT COUNT(*) FROM customer_address");
67 REQUIRE(CHECK_COLUMN(result, 0, {50000}));
68 result = con.Query("SELECT COUNT(*) FROM date_dim");
69 REQUIRE(CHECK_COLUMN(result, 0, {73049}));
70 result = con.Query("SELECT COUNT(*) FROM household_demographics");
71 REQUIRE(CHECK_COLUMN(result, 0, {7200}));
72 result = con.Query("SELECT COUNT(*) FROM inventory");
73 REQUIRE(CHECK_COLUMN(result, 0, {11745000}));
74 result = con.Query("SELECT COUNT(*) FROM income_band");
75 REQUIRE(CHECK_COLUMN(result, 0, {20}));
76 result = con.Query("SELECT COUNT(*) FROM inventory");
77 REQUIRE(CHECK_COLUMN(result, 0, {11745000}));
78 result = con.Query("SELECT COUNT(*) FROM item");
79 REQUIRE(CHECK_COLUMN(result, 0, {18000}));
80 result = con.Query("SELECT COUNT(*) FROM promotion");
81 REQUIRE(CHECK_COLUMN(result, 0, {300}));
82 result = con.Query("SELECT COUNT(*) FROM reason");
83 REQUIRE(CHECK_COLUMN(result, 0, {35}));
84 result = con.Query("SELECT COUNT(*) FROM ship_mode");
85 REQUIRE(CHECK_COLUMN(result, 0, {20}));
86 result = con.Query("SELECT COUNT(*) FROM store");
87 REQUIRE(CHECK_COLUMN(result, 0, {12}));
88 result = con.Query("SELECT COUNT(*) FROM store_returns");
89 // TODO: this count is slightly off, why?
90 // REQUIRE(CHECK_COLUMN(result, 0, {287514}));
91 result = con.Query("SELECT COUNT(*) FROM store_sales");
92 REQUIRE(CHECK_COLUMN(result, 0, {2880404}));
93 result = con.Query("SELECT COUNT(*) FROM time_dim");
94 REQUIRE(CHECK_COLUMN(result, 0, {86400}));
95 result = con.Query("SELECT COUNT(*) FROM warehouse");
96 REQUIRE(CHECK_COLUMN(result, 0, {5}));
97 result = con.Query("SELECT COUNT(*) FROM web_page");
98 REQUIRE(CHECK_COLUMN(result, 0, {60}));
99 result = con.Query("SELECT COUNT(*) FROM web_returns");
100 // TODO: this count is slightly off, why?
101 // REQUIRE(CHECK_COLUMN(result, 0, {71763}));
102 result = con.Query("SELECT COUNT(*) FROM web_sales");
103 REQUIRE(CHECK_COLUMN(result, 0, {719384}));
104 result = con.Query("SELECT COUNT(*) FROM web_site");
105 REQUIRE(CHECK_COLUMN(result, 0, {30}));
106
107 con.EnableProfiling();
108
109 // // run queries, these work already
110 // con.Query(tpcds::get_query(6))->Print();
111 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q06])->Print();
112 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q07])->Print();
113 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q10])->Print();
114 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q12])->Print();
115 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q15])->Print();
116 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q19])->Print();
117 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q20])->Print();
118 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q21])->Print();
119 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q25])->Print();
120 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q27])->Print();
121 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q29])->Print();
122 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q33])->Print();
123 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q35])->Print();
124 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q37])->Print();
125 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q40])->Print();
126 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q42])->Print();
127 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q43])->Print();
128 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q45])->Print();
129 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q48])->Print();
130 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q50])->Print();
131 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q52])->Print();
132 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q53])->Print();
133 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q55])->Print();
134 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q61])->Print();
135 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q62])->Print();
136 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q63])->Print();
137 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q65])->Print();
138 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q73])->Print();
139 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q79])->Print();
140 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q82])->Print();
141 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q85])->Print();
142 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q88])->Print();
143 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q89])->Print();
144 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q90])->Print();
145 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q91])->Print();
146 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q92])->Print();
147 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q93])->Print();
148 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q96])->Print();
149 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q98])->Print();
150 // con.Query(TPCDS_QUERIES[TPCDS_QUERY_ID::Q99])->Print();
151 //
152 // // TODO result verification
153}
154