/ Check-in [eb5cef83]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases to cover TPC-H Q8.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eb5cef8351d12c0f8550dac96ee7a6e495975b5a
User & Date: drh 2013-09-11 14:57:11
Context
2013-09-12
00:54
Fix harmless compiler warning. check-in: 2b510614 user: mistachkin tags: trunk
00:40
Merge updates from trunk. Closed-Leaf check-in: fca799f0 user: mistachkin tags: vsix2013
2013-09-11
14:57
Add test cases to cover TPC-H Q8. check-in: eb5cef83 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: 48ed8b56 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added 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  +} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
          169  +do_test tpch01-1.1b {
          170  +  set ::eqpres
          171  +} {/.* customer .* nation AS n1 .* nation AS n2 .*/}
          172  +
          173  +do_eqp_test tpch01-1.2 {
          174  +select
          175  +    c_custkey,    c_name,    sum(l_extendedprice * (1 - l_discount)) as revenue,
          176  +    c_acctbal,    n_name,    c_address,    c_phone,    c_comment
          177  +from
          178  +    customer,    orders,    lineitem,    nation
          179  +where
          180  +    c_custkey = o_custkey    and l_orderkey = o_orderkey
          181  +    and o_orderdate >=  '1994-08-01'    and o_orderdate < date('1994-08-01', '+3 month')
          182  +    and l_returnflag = 'R'    and c_nationkey = n_nationkey
          183  +group by
          184  +    c_custkey,    c_name,    c_acctbal,    c_phone,    n_name, c_address,    c_comment
          185  +order by
          186  +    revenue desc;
          187  +} {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}}