tpcc-databend

tpcc-databend

CREATE DATABASE IF NOT EXISTS tpch;
USE tpch;
CREATE TABLE IF NOT EXISTS nation  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       VARCHAR NOT NULL,
                            N_REGIONKEY  INT NOT NULL,
                            N_COMMENT    VARCHAR);

CREATE TABLE IF NOT EXISTS region  ( R_REGIONKEY  INT NOT NULL,
       	               R_NAME       VARCHAR NOT NULL,
                       R_COMMENT    VARCHAR);

CREATE TABLE IF NOT EXISTS part  ( P_PARTKEY     INT NOT NULL,
                          P_NAME        VARCHAR NOT NULL,
                          P_MFGR        VARCHAR NOT NULL,
                          P_BRAND       VARCHAR NOT NULL,
                          P_TYPE        VARCHAR NOT NULL,
                          P_SIZE        INT NOT NULL,
                          P_CONTAINER   VARCHAR NOT NULL,
                          P_RETAILPRICE FLOAT NOT NULL,
                          P_COMMENT     VARCHAR NOT NULL);

CREATE TABLE IF NOT EXISTS supplier  ( S_SUPPKEY     INT NOT NULL,
                             S_NAME        VARCHAR NOT NULL,
                             S_ADDRESS     VARCHAR NOT NULL,
                             S_NATIONKEY   INT NOT NULL,
                             S_PHONE       VARCHAR NOT NULL,
                             S_ACCTBAL     FLOAT NOT NULL,
                             S_COMMENT     VARCHAR NOT NULL);

CREATE TABLE IF NOT EXISTS partsupp ( PS_PARTKEY     INT NOT NULL,
                             PS_SUPPKEY     INT NOT NULL,
                             PS_AVAILQTY    INT NOT NULL,
                             PS_SUPPLYCOST  FLOAT NOT NULL,
                             PS_COMMENT     VARCHAR NOT NULL);

CREATE TABLE IF NOT EXISTS customer  ( C_CUSTKEY     INT NOT NULL,
                             C_NAME        VARCHAR NOT NULL,
                             C_ADDRESS     VARCHAR NOT NULL,
                             C_NATIONKEY   INT NOT NULL,
                             C_PHONE       VARCHAR NOT NULL,
                             C_ACCTBAL     FLOAT   NOT NULL,
                             C_MKTSEGMENT  VARCHAR NOT NULL,
                             C_COMMENT     VARCHAR NOT NULL);

CREATE TABLE IF NOT EXISTS orders  ( O_ORDERKEY       INT NOT NULL,
                           O_CUSTKEY        INT NOT NULL,
                           O_ORDERSTATUS    VARCHAR NOT NULL,
                           O_TOTALPRICE     FLOAT NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  VARCHAR NOT NULL,  
                           O_CLERK          VARCHAR NOT NULL, 
                           O_SHIPPRIORITY   INT NOT NULL,
                           O_COMMENT        VARCHAR NOT NULL);

CREATE TABLE IF NOT EXISTS lineitem ( L_ORDERKEY    INT NOT NULL,
                             L_PARTKEY     INT NOT NULL,
                             L_SUPPKEY     INT NOT NULL,
                             L_LINENUMBER  INT NOT NULL,
                             L_QUANTITY    FLOAT NOT NULL,
                             L_EXTENDEDPRICE  FLOAT NOT NULL,
                             L_DISCOUNT    FLOAT NOT NULL,
                             L_TAX         FLOAT NOT NULL,
                             L_RETURNFLAG  VARCHAR NOT NULL,
                             L_LINESTATUS  VARCHAR NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT VARCHAR NOT NULL,
                             L_SHIPMODE     VARCHAR NOT NULL,
                             L_COMMENT      VARCHAR NOT NULL);

Q21(OOM):

select
   s_name,
   count(*) as numwait 
from
   supplier,
   lineitem l1,
   orders,
   nation 
where
   s_suppkey = l1.l_suppkey 
   and o_orderkey = l1.l_orderkey 
   and o_orderstatus = 'F' 
   and l1.l_receiptdate > l1.l_commitdate 
   and exists 
   (
      select
         * 
      from
         lineitem l2 
      where
         l2.l_orderkey = l1.l_orderkey 
         and l2.l_suppkey <> l1.l_suppkey 
   )
   and not exists 
   (
      select
         * 
      from
         lineitem l3 
      where
         l3.l_orderkey = l1.l_orderkey 
         and l3.l_suppkey <> l1.l_suppkey 
         and l3.l_receiptdate > l3.l_commitdate 
   )
   and s_nationkey = n_nationkey 
   and n_name = 'EGYPT' 
group by
   s_name 
order by
   numwait desc,
   s_name limit 100;

Q22(Unsupport):

select
   cntrycode,
   count(*) as numcust,
   sum(c_acctbal) as totacctbal 
from
   (
      select
         substring(c_phone 
      from
         1 for 2) as cntrycode,
         c_acctbal 
      from
         customer 
      where
         substring(c_phone 
      from
         1 for 2) in 
         (
            '20',
            '40',
            '22',
            '30',
            '39',
            '42',
            '21'
         )
         and c_acctbal > ( 
         select
            avg(c_acctbal) 
         from
            customer 
         where
            c_acctbal > 0.00 
            and substring(c_phone 
         from
            1 for 2) in 
            (
               '20',
               '40',
               '22',
               '30',
               '39',
               '42',
               '21'
            )
) 
            and not exists 
            (
               select
                  * 
               from
                  orders 
               where
                  o_custkey = c_custkey 
            )
   )
   as custsale 
group by
   cntrycode 
order by
   cntrycode;

Error:

ERROR 1105 (HY000): Code: 1015, displayText = Projection column: "O_SHIPPRIORITY"_26 not exists in ["\"C_CUSTKEY\"_0", "\"C_ADDRESS\"_2", "\"C_ACCTBAL\"_5", "\"avg(c_acctbal)\"_16", "2", "1", "\"C_MKTSEGMENT\"_6", "\"C_NATIONKEY\"_3", "substring(\"C_PHONE\"_4, 1, 2)", "\"C_PHONE\"_4", "\"C_COMMENT\"_7", "\"C_NAME\"_1"], there are bugs! (while in processor thread 7).