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