| 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 |
# |
| 14 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix tpch01 |
| 18 |
|
| 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 |
} {} |
| 126 |
|
| 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 |
} {/*SEARCH part USING INDEX bootleg_pti *SEARCH lineitem USING INDEX lpki2*/} |
| 169 |
do_test tpch01-1.1b { |
| 170 |
set ::eqpres |
| 171 |
} {/.* customer .* n1 .*/} |
| 172 |
do_test tpch01-1.1c { |
| 173 |
set ::eqpres |
| 174 |
} {/.* supplier .* n2 .*/} |
| 175 |
|
| 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 |
} { |
| 191 |
QUERY PLAN |
| 192 |
|--SEARCH orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?) |
| 193 |
|--SEARCH customer USING INDEX cpki (C_CUSTKEY=?) |
| 194 |
|--SEARCH nation USING INDEX npki (N_NATIONKEY=?) |
| 195 |
|--SEARCH lineitem USING INDEX lpki (L_ORDERKEY=?) |
| 196 |
|--USE TEMP B-TREE FOR GROUP BY |
| 197 |
`--USE TEMP B-TREE FOR ORDER BY |
| 198 |
} |
| 199 |
|
| 200 |
finish_test |