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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix tpch01 18 19do_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 127do_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 TABLE part USING INDEX bootleg_pti *SEARCH TABLE lineitem USING INDEX lpki2*/} 169do_test tpch01-1.1b { 170 set ::eqpres 171} {/.* customer .* nation AS n1 .*/} 172do_test tpch01-1.1c { 173 set ::eqpres 174} {/.* supplier .* nation AS n2 .*/} 175 176do_eqp_test tpch01-1.2 { 177select 178 c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, 179 c_acctbal, n_name, c_address, c_phone, c_comment 180from 181 customer, orders, lineitem, nation 182where 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 186group by 187 c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment 188order by 189 revenue desc; 190} { 191 QUERY PLAN 192 |--SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?) 193 |--SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) 194 |--SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?) 195 |--SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?) 196 |--USE TEMP B-TREE FOR GROUP BY 197 `--USE TEMP B-TREE FOR ORDER BY 198} 199 200finish_test 201