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

Q6(OK):

select
   sum(l_extendedprice * l_discount) as revenue 
from
   lineitem 
where
   l_shipdate >= '1994-01-01' 
   and l_shipdate < date_add(to_date('1994-01-01'), 1, year) 
   and l_discount between 0.06 - 0.01 and 0.06 + 0.01 
   and l_quantity < 24;

Q7:

select
	supp_nation,
	cust_nation,
	l_year,
	sum(volume) as revenue
from
	(
	select
	n1.n_name as supp_nation,
	n2.n_name as cust_nation,
	extract(year from l_shipdate) as l_year,
	l_extendedprice * (1 - l_discount) as volume
	from
	supplier,
	lineitem,
	orders,
	customer,
	nation n1,
	nation n2
	where
	s_suppkey = l_suppkey
	and o_orderkey = l_orderkey
	and c_custkey = o_custkey
	and s_nationkey = n1.n_nationkey
	and c_nationkey = n2.n_nationkey
	and ((n1.n_name = 'JAPAN' and n2.n_name = 'INDIA')
	or (n1.n_name = 'INDIA' and n2.n_name = 'JAPAN'))
	and l_shipdate between '1995-01-01' and '1996-12-31'
	) as shipping
group by
	supp_nation,
	cust_nation,
	l_year
order by
	supp_nation,
	cust_nation,
	l_year;

ERROR 1105 (HY000): Code: 1005, displayText = error: 
   --> SQL:27:49
   |
 1 | select
   | ------ while parsing `SELECT ...`
   .
 5 | sum(volume) as revenue
 6 | from
 7 | (
 8 | select
   | ------ while parsing `SELECT ...`
   .
18 | nation n1,
19 | nation n2
20 | where
21 | s_suppkey = l_suppkey
   | --------- while parsing expression
22 | and o_orderkey = l_orderkey
23 | and c_custkey = o_custkey
24 | and s_nationkey = n1.n_nationkey
25 | and c_nationkey = n2.n_nationkey
26 | and ((n1.n_name = 'JAPAN' and n2.n_name = 'I

Q8:

select
	o_year,
	sum(case
		when nation = 'INDIA' then volume
		else 0
	end) / sum(volume) as mkt_share
from
	(
	select
	extract(year from 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 = 'ASIA'
	and s_nationkey = n2.n_nationkey
	and o_orderdate between '1995-01-01' and '1996-12-31'
	and p_type = 'SMALL PLATED COPPER'
	) as all_nations
group by
	o_year
order by
	o_year;

OK.

Q9:

select
   nation,
   o_year,
   sum(amount) as sum_profit 
from
   (
      select
         n_name as nation,
         extract(year 
      from
         o_orderdate) as o_year,
         l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount 
      from
         part,
         supplier,
         lineitem,
         partsupp,
         orders,
         nation 
      where
         s_suppkey = l_suppkey 
         and ps_suppkey = l_suppkey 
         and ps_partkey = l_partkey 
         and p_partkey = l_partkey 
         and o_orderkey = l_orderkey 
         and s_nationkey = n_nationkey 
         and p_name like '%dim%' 
   )
   as profit 
group by
   nation,
   o_year 
order by
   nation,
   o_year desc;

OK.

Q10:

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 >= '1993-08-01' 
   and o_orderdate < date_add(to_date('1993-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 limit 20;

OK.

Q11:

select
   ps_partkey,
   sum(ps_supplycost * ps_availqty) as value 
from
   partsupp,
   supplier,
   nation 
where
   ps_suppkey = s_suppkey 
   and s_nationkey = n_nationkey 
   and n_name = 'MOZAMBIQUE' 
group by
   ps_partkey 
having
   sum(ps_supplycost * ps_availqty) > ( 
   select
      sum(ps_supplycost * ps_availqty) * 0.0001000000 
   from
      partsupp, supplier, nation 
   where
      ps_suppkey = s_suppkey 
      and s_nationkey = n_nationkey 
      and n_name = 'MOZAMBIQUE' ) 
   order by
      value desc;

OK

Q12:

select
   l_shipmode,
   sum(
   case
      when
         o_orderpriority = '1-URGENT' 
         or o_orderpriority = '2-HIGH' 
      then
         1 
      else
         0 
   end
) as high_line_count, sum(
   case
      when
         o_orderpriority <> '1-URGENT' 
         and o_orderpriority <> '2-HIGH' 
      then
         1 
      else
         0 
   end
) as low_line_count 
from
   orders, lineitem 
where
   o_orderkey = l_orderkey 
   and l_shipmode in 
   (
      'RAIL', 'FOB'
   )
   and l_commitdate < l_receiptdate 
   and l_shipdate < l_commitdate 
   and l_receiptdate >= '1997-01-01' 
   and l_receiptdate < date_add(to_date('1997-01-01'), 1, year) 
group by
   l_shipmode 
order by
   l_shipmode;

OK

Q13:

select
   c_count,
   count(*) as custdist 
from
   (
      select
         c_custkey,
         count(o_orderkey) as c_count 
      from
         customer 
         left outer join
            orders 
            on c_custkey = o_custkey 
            and o_comment not like '%pending%deposits%' 
      group by
         c_custkey 
   )
   c_orders 
group by
   c_count 
order by
   custdist desc,
   c_count desc;

Error:

ERROR 1105 (HY000): Code: 1002, displayText = Outer join only support equi-join.

Q14:

select
   100.00 * sum(
   case
      when
         p_type like 'PROMO%' 
      then
         l_extendedprice * (1 - l_discount) 
      else
         0 
   end
) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 
from
   lineitem, part 
where
   l_partkey = p_partkey 
   and l_shipdate >= '1996-12-01' 
   and l_shipdate < date_add(to_date('1996-12-01'), 1, month);

OK

Q15(Unsupport):

create view revenue0 (supplier_no, total_revenue) as 
select
   l_suppkey,
   sum(l_extendedprice * (1 - l_discount)) 
from
   lineitem 
where
   l_shipdate >= '1997-07-01' 
   and l_shipdate < date_add('1997-07-01', interval '3' month) 
group by
   l_suppkey 
   select
      s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue 
   from
      supplier,
      revenue0 
   where
      s_suppkey = supplier_no 
      and total_revenue = 
      (
         select
            max(total_revenue) 
         from
            revenue0 
      )
   order by
      s_suppkey drop view revenue0

Q16:

select
   p_brand,
   p_type,
   p_size,
   count(distinct ps_suppkey) as supplier_cnt 
from
   partsupp,
   part 
where
   p_partkey = ps_partkey 
   and p_brand <> 'Brand#34' 
   and p_type not like 'LARGE BRUSHED%' 
   and p_size in 
   (
      48,
      19,
      12,
      4,
      41,
      7,
      21,
      39
   )
   and ps_suppkey not in 
   (
      select
         s_suppkey 
      from
         supplier 
      where
         s_comment like '%Customer%Complaints%' 
   )
group by
   p_brand,
   p_type,
   p_size 
order by
   supplier_cnt desc,
   p_brand,
   p_type,
   p_size;

Error:

ERROR 1105 (HY000): Code: 1002, displayText = Unsupported expr: InSubquery { span: [NOT(342..345), IN(346..348), LParen(353..354), SELECT(361..367), Ident(377..386), FROM(394..398), Ident(408..416), WHERE(424..429), Ident(439..448), LIKE(449..453), QuotedString(454..477), RParen(482..483)], expr: ColumnRef { span: [Ident(331..341)], database: None, table: None, column: Identifier { name: "ps_suppkey", quote: None, span: Ident(331..341) } }, subquery: Query { span: [SELECT(361..367), Ident(377..386), FROM(394..398), Ident(408.

Q17(OOM):

select
   sum(l_extendedprice) / 7.0 as avg_yearly 
from
   lineitem,
   part 
where
   p_partkey = l_partkey 
   and p_brand = 'Brand#44' 
   and p_container = 'WRAP PKG' 
   and l_quantity < ( 
   select
      0.2 * avg(l_quantity) 
   from
      lineitem 
   where
      l_partkey = p_partkey );

Q18:

select
   c_name,
   c_custkey,
   o_orderkey,
   o_orderdate,
   o_totalprice,
   sum(l_quantity) 
from
   customer,
   orders,
   lineitem 
where
   o_orderkey in 
   (
      select
         l_orderkey 
      from
         lineitem 
      group by
         l_orderkey 
      having
         sum(l_quantity) > 314 
   )
   and c_custkey = o_custkey 
   and o_orderkey = l_orderkey 
group by
   c_name,
   c_custkey,
   o_orderkey,
   o_orderdate,
   o_totalprice 
order by
   o_totalprice desc,
   o_orderdate limit 100;

Error:

ERROR 1105 (HY000): Code: 1002, displayText = Unsupported expr: InSubquery { span: [IN(162..164), LParen(169..170), SELECT(177..183), Ident(193..203), FROM(211..215), Ident(225..233), GROUP(241..246), BY(247..249), Ident(259..269), HAVING(277..283), Ident(293..296), LParen(296..297), Ident(297..307), RParen(307..308), Gt(309..310), LiteralInteger(311..314), RParen(319..320)], expr: ColumnRef { span: [Ident(151..161)], database: None, table: None, column: Identifier { name: "o_orderkey", quote: None, span: Ident(151..161) } },

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