Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add test cases to cover TPC-H Q8. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
eb5cef8351d12c0f8550dac96ee7a6e4 |
| User & Date: | drh 2013-09-11 14:57:11.714 |
Context
|
2013-09-12
| ||
| 00:54 | Fix harmless compiler warning. (check-in: 2b510614dc user: mistachkin tags: trunk) | |
| 00:40 | Merge updates from trunk. (Closed-Leaf check-in: fca799f03a user: mistachkin tags: vsix2013) | |
|
2013-09-11
| ||
| 14:57 | Add test cases to cover TPC-H Q8. (check-in: eb5cef8351 user: drh tags: trunk) | |
|
2013-09-10
| ||
| 01:41 | Deterministically initialize the PRNG used as a tie-breaker in the ANALYZE command, so that the analysis is always the same given the same database. This simplifies testing. (check-in: 48ed8b565b user: drh tags: trunk) | |
Changes
Added test/tpch01.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |
# 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
} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
do_test tpch01-1.1b {
set ::eqpres
} {/.* customer .* nation AS n1 .* nation AS 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;
} {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}}
|