test/tpch01.test

   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 TABLE part USING INDEX bootleg_pti *SEARCH TABLE lineitem USING INDEX lpki2*/}
 169 do_test tpch01-1.1b {
 170   set ::eqpres
 171 } {/.* customer .* nation AS n1 .*/}
 172 do_test tpch01-1.1c {
 173   set ::eqpres
 174 } {/.* supplier .* nation AS 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 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
 200 finish_test