# 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