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);

Q19(OOM):

select
   sum(l_extendedprice* (1 - l_discount)) as revenue 
from
   lineitem,
   part 
where
   (
      p_partkey = l_partkey 
      and p_brand = 'Brand#52' 
      and p_container in 
      (
         'SM CASE',
         'SM BOX',
         'SM PACK',
         'SM PKG'
      )
      and l_quantity >= 4 
      and l_quantity <= 4 + 10 
      and p_size between 1 and 5 
      and l_shipmode in 
      (
         'AIR',
         'AIR REG'
      )
      and l_shipinstruct = 'DELIVER IN PERSON' 
   )
   or 
   (
      p_partkey = l_partkey 
      and p_brand = 'Brand#11' 
      and p_container in 
      (
         'MED BAG',
         'MED BOX',
         'MED PKG',
         'MED PACK'
      )
      and l_quantity >= 18 
      and l_quantity <= 18 + 10 
      and p_size between 1 and 10 
      and l_shipmode in 
      (
         'AIR',
         'AIR REG'
      )
      and l_shipinstruct = 'DELIVER IN PERSON' 
   )
   or 
   (
      p_partkey = l_partkey 
      and p_brand = 'Brand#51' 
      and p_container in 
      (
         'LG CASE',
         'LG BOX',
         'LG PACK',
         'LG PKG'
      )
      and l_quantity >= 29 
      and l_quantity <= 29 + 10 
      and p_size between 1 and 15 
      and l_shipmode in 
      (
         'AIR',
         'AIR REG'
      )
      and l_shipinstruct = 'DELIVER IN PERSON' 
   )
;

Q20(Unsupport):

select
   s_name,
   s_address 
from
   supplier,
   nation 
where
   s_suppkey in 
   (
      select
         ps_suppkey 
      from
         partsupp 
      where
         ps_partkey in 
         (
            select
               p_partkey 
            from
               part 
            where
               p_name like 'green%' 
         )
         and ps_availqty > ( 
         select
            0.5 * sum(l_quantity) 
         from
            lineitem 
         where
            l_partkey = ps_partkey 
            and l_suppkey = ps_suppkey 
            and l_shipdate >= '1993-01-01' 
            and l_shipdate < date_add(to_date('1993-01-01'), 1, year) ) 
   )
   and s_nationkey = n_nationkey 
   and n_name = 'ALGERIA' 
order by
   s_name;

Error:

ERROR 1105 (HY000): Code: 1002, displayText = Unsupported expr: InSubquery { span: [IN(80..82), LParen(87..88), SELECT(95..101), Ident(111..121), FROM(129..133), Ident(143..151), WHERE(159..164), Ident(174..184), IN(185..187), LParen(198..199), SELECT(212..218), Ident(234..243), FROM(257..261), Ident(277..281), WHERE(295..300), Ident(316..322), LIKE(323..327), QuotedString(328..336), RParen(347..348), AND(358..361), Ident(362..373), Gt(374..375), LParen(376..377), SELECT(388..394), LiteralFloat(407..410), Multiply(411..412), 

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).