| blob | ce48f8ec23d9655be3908bd2f655ec5d5cb2a11d |
1 # 2013-09-05
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12 # TPC-H test queries.
13 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix tpch01
19 do_execsql_test tpch01-1.0 {
20 CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
21 N_NAME CHAR(25) NOT NULL,
22 N_REGIONKEY INTEGER NOT NULL,
23 N_COMMENT VARCHAR(152));
24 CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
25 R_NAME CHAR(25) NOT NULL,
26 R_COMMENT VARCHAR(152));
27 CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
28 P_NAME VARCHAR(55) NOT NULL,
29 P_MFGR CHAR(25) NOT NULL,
30 P_BRAND CHAR(10) NOT NULL,
31 P_TYPE VARCHAR(25) NOT NULL,
32 P_SIZE INTEGER NOT NULL,
33 P_CONTAINER CHAR(10) NOT NULL,
34 P_RETAILPRICE DECIMAL(15,2) NOT NULL,
35 P_COMMENT VARCHAR(23) NOT NULL );
36 CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
37 S_NAME CHAR(25) NOT NULL,
38 S_ADDRESS VARCHAR(40) NOT NULL,
39 S_NATIONKEY INTEGER NOT NULL,
40 S_PHONE CHAR(15) NOT NULL,
41 S_ACCTBAL DECIMAL(15,2) NOT NULL,
42 S_COMMENT VARCHAR(101) NOT NULL);
43 CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
44 PS_SUPPKEY INTEGER NOT NULL,
45 PS_AVAILQTY INTEGER NOT NULL,
46 PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
47 PS_COMMENT VARCHAR(199) NOT NULL );
48 CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
49 C_NAME VARCHAR(25) NOT NULL,
50 C_ADDRESS VARCHAR(40) NOT NULL,
51 C_NATIONKEY INTEGER NOT NULL,
52 C_PHONE CHAR(15) NOT NULL,
53 C_ACCTBAL DECIMAL(15,2) NOT NULL,
54 C_MKTSEGMENT CHAR(10) NOT NULL,
55 C_COMMENT VARCHAR(117) NOT NULL);
56 CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
57 O_CUSTKEY INTEGER NOT NULL,
58 O_ORDERSTATUS CHAR(1) NOT NULL,
59 O_TOTALPRICE DECIMAL(15,2) NOT NULL,
60 O_ORDERDATE DATE NOT NULL,
61 O_ORDERPRIORITY CHAR(15) NOT NULL,
62 O_CLERK CHAR(15) NOT NULL,
63 O_SHIPPRIORITY INTEGER NOT NULL,
64 O_COMMENT VARCHAR(79) NOT NULL);
65 CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
66 L_PARTKEY INTEGER NOT NULL,
67 L_SUPPKEY INTEGER NOT NULL,
68 L_LINENUMBER INTEGER NOT NULL,
69 L_QUANTITY DECIMAL(15,2) NOT NULL,
70 L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
71 L_DISCOUNT DECIMAL(15,2) NOT NULL,
72 L_TAX DECIMAL(15,2) NOT NULL,
73 L_RETURNFLAG CHAR(1) NOT NULL,
74 L_LINESTATUS CHAR(1) NOT NULL,
75 L_SHIPDATE DATE NOT NULL,
76 L_COMMITDATE DATE NOT NULL,
77 L_RECEIPTDATE DATE NOT NULL,
78 L_SHIPINSTRUCT CHAR(25) NOT NULL,
79 L_SHIPMODE CHAR(10) NOT NULL,
80 L_COMMENT VARCHAR(44) NOT NULL);
81 CREATE INDEX npki on nation(N_NATIONKEY);
82 CREATE INDEX rpki on region(R_REGIONKEY);
83 CREATE INDEX ppki on part(P_PARTKEY);
84 CREATE INDEX spki on supplier(S_SUPPKEY);
85 CREATE INDEX pspki on partsupp(PS_PARTKEY, PS_SUPPKEY);
86 CREATE INDEX cpki on customer(C_CUSTKEY);
87 CREATE INDEX opki on orders(O_ORDERKEY);
88 CREATE INDEX lpki on lineitem(L_ORDERKEY, L_LINENUMBER);
89 CREATE INDEX nrki on nation(n_regionkey);
90 CREATE INDEX snki on supplier(s_nationkey);
91 CREATE INDEX cnki on customer(c_nationkey);
92 CREATE INDEX ocki on orders(O_CUSTKEY);
93 CREATE INDEX odi on orders(O_ORDERDATE);
94 CREATE INDEX lpki2 on lineitem(L_PARTKEY);
95 CREATE INDEX lski on lineitem(L_SUPPKEY);
96 CREATE INDEX lsdi on lineitem(L_SHIPDATE);
97 CREATE INDEX lcdi on lineitem(L_COMMITDATE);
98 CREATE INDEX lrdi on lineitem(L_RECEIPTDATE);
99 CREATE INDEX bootleg_nni on nation(N_NAME);
100 CREATE INDEX bootleg_psi on part(p_size);
101 CREATE INDEX bootleg_pti on part(p_type);
102 ANALYZE sqlite_master;
103 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lrdi','600572 236');
104 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lcdi','600572 244');
105 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lsdi','600572 238');
106 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lski','600572 601');
107 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lpki2','600572 31');
108 INSERT INTO sqlite_stat1 VALUES('LINEITEM','lpki','600572 5 1');
109 INSERT INTO sqlite_stat1 VALUES('ORDERS','odi','150000 63');
110 INSERT INTO sqlite_stat1 VALUES('ORDERS','ocki','150000 15');
111 INSERT INTO sqlite_stat1 VALUES('ORDERS','opki','150000 1');
112 INSERT INTO sqlite_stat1 VALUES('CUSTOMER','cnki','15000 600');
113 INSERT INTO sqlite_stat1 VALUES('CUSTOMER','cpki','15000 1');
114 INSERT INTO sqlite_stat1 VALUES('PARTSUPP','pspki','80000 4 1');
115 INSERT INTO sqlite_stat1 VALUES('SUPPLIER','snki','1000 40');
116 INSERT INTO sqlite_stat1 VALUES('SUPPLIER','spki','1000 1');
117 INSERT INTO sqlite_stat1 VALUES('PART','bootleg_pti','20000 134');
118 INSERT INTO sqlite_stat1 VALUES('PART','bootleg_psi','20000 400');
119 INSERT INTO sqlite_stat1 VALUES('PART','ppki','20000 1');
120 INSERT INTO sqlite_stat1 VALUES('REGION','rpki','5 1');
121 INSERT INTO sqlite_stat1 VALUES('NATION','bootleg_nni','25 1');
122 INSERT INTO sqlite_stat1 VALUES('NATION','nrki','25 5');
123 INSERT INTO sqlite_stat1 VALUES('NATION','npki','25 1');
124 ANALYZE sqlite_master;
125 } {}
127 do_test tpch01-1.1 {
128 unset -nocomplain ::eqpres
129 set ::eqpres [db eval {EXPLAIN QUERY PLAN
130 select
131 o_year,
132 sum(case
133 when nation = 'EGYPT' then volume
134 else 0
135 end) / sum(volume) as mkt_share
136 from
137 (
138 select
139 strftime('%Y', o_orderdate) as o_year,
140 l_extendedprice * (1 - l_discount) as volume,
141 n2.n_name as nation
142 from
143 part,
144 supplier,
145 lineitem,
146 orders,
147 customer,
148 nation n1,
149 nation n2,
150 region
151 where
152 p_partkey = l_partkey
153 and s_suppkey = l_suppkey
154 and l_orderkey = o_orderkey
155 and o_custkey = c_custkey
156 and c_nationkey = n1.n_nationkey
157 and n1.n_regionkey = r_regionkey
158 and r_name = 'MIDDLE EAST'
159 and s_nationkey = n2.n_nationkey
160 and o_orderdate between '1995-01-01' and '1996-12-31'
161 and p_type = 'LARGE PLATED STEEL'
162 ) as all_nations
163 group by
164 o_year
165 order by
166 o_year;}]
167 set ::eqpres
168 } {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
169 do_test tpch01-1.1b {
170 set ::eqpres
171 } {/.* customer .* nation AS n1 .*/}
172 do_test tpch01-1.1c {
173 set ::eqpres
174 } {/.* supplier .* nation AS n2 .*/}
176 do_eqp_test tpch01-1.2 {
177 select
178 c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,
179 c_acctbal, n_name, c_address, c_phone, c_comment
180 from
181 customer, orders, lineitem, nation
182 where
183 c_custkey = o_custkey and l_orderkey = o_orderkey
184 and o_orderdate >= '1994-08-01' and o_orderdate < date('1994-08-01', '+3 month')
185 and l_returnflag = 'R' and c_nationkey = n_nationkey
186 group by
187 c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
188 order by
189 revenue desc;
190 } {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
192 finish_test