| # 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 |
