Created
June 19, 2022 02:57
-
-
Save BohuTANG/4c214f176d2340a0ab53066524ce6d80 to your computer and use it in GitHub Desktop.
tpcc-databend
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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); |
Q1(OK):
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate < '1998-12-01'
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus LIMIT 1;
Q2(OOM):
select
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = :1
and p_type like '%:2'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = ':3'
and ps_supplycost =
(
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = ':3'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey LIMIT 100;
Q3(OK):
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'AUTOMOBILE'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < '1995-03-13'
and l_shipdate > '1995-03-13'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate limit 10;
Q4(Error):
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= '1995-01-01'
and o_orderdate < date_add(to_date('1995-01-01'), 3, month)
and exists
(
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
Error:
ERROR 1105 (HY000): Code: 1015, displayText = Projection column: "L_QUANTITY"_13 not exists in ["\"O_ORDERPRIORITY\"_5", "\"O_SHIPPRIORITY\"_7", "\"O_COMMENT\"_8", "\"O_ORDERKEY\"_0", "\"O_TOTALPRICE\"_3", "\"O_CLERK\"_6", "\"O_ORDERDATE\"_4", "\"O_CUSTKEY\"_1", "\"O_ORDERSTATUS\"_2"], there are bugs! (while in processor thread 0).
Q5(Error):
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'MIDDLE EAST'
and o_orderdate >= '1994-01-01'
and o_orderdate < date_add(to_date('1994-01-01'), 1, year)
group by
n_name
order by
revenue desc;
Error:
ERROR 1105 (HY000): Code: 1058, displayText = downcast column error, column type: "Const(String)", expected column: "common_datavalues::columns::string::StringColumn" (while in processor thread 0).
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).
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
docker pull ghcr.io/databloom-ai/tpch-docker:main
docker run -it -v "$(pwd)":/data ghcr.io/databloom-ai/tpch-docker:main dbgen -vf -s 1