diff options
Diffstat (limited to 'exec/java-exec/src/test/resources/queries')
23 files changed, 724 insertions, 0 deletions
diff --git a/exec/java-exec/src/test/resources/queries/tpch.json b/exec/java-exec/src/test/resources/queries/tpch.json new file mode 100644 index 000000000..59894398e --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch.json @@ -0,0 +1,54 @@ +[ + + tpch1: " + +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 + cp.`tpch/lineitem.parquet` +where + l_shipdate <= date '1998-12-01' - interval ':1' day +group by + l_returnflag, + l_linestatus +order by + l_returnflag, + l_linestatus +LIMIT 1; + + ", + tpch1a: " + +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 + cp.`tpch/lineitem.parquet` +group by + l_returnflag, + l_linestatus +order by + l_returnflag, + l_linestatus +LIMIT 1; + + " + +]
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/01.sql b/exec/java-exec/src/test/resources/queries/tpch/01.sql new file mode 100644 index 000000000..b10733967 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/01.sql @@ -0,0 +1,24 @@ +-- using 1395599672 as a seed to the RNG
+
+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
+ cp.`tpch/lineitem.parquet`
+-- where
+-- l_shipdate <= date '1998-12-01' - interval '120' day (3)
+group by
+ l_returnflag,
+ l_linestatus
+
+order by
+ l_returnflag,
+ l_linestatus;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/02.sql b/exec/java-exec/src/test/resources/queries/tpch/02.sql new file mode 100644 index 000000000..d6e234696 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/02.sql @@ -0,0 +1,48 @@ +-- tpch2 using 1395599672 as a seed to the RNG
+select
+ s.s_acctbal,
+ s.s_name,
+ n.n_name,
+ p.p_partkey,
+ p.p_mfgr,
+ s.s_address,
+ s.s_phone,
+ s.s_comment
+from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+where
+ p.p_partkey = ps.ps_partkey
+ and s.s_suppkey = ps.ps_suppkey
+ and p.p_size = 41
+ and p.p_type like '%NICKEL'
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+ and ps.ps_supplycost = (
+
+ select
+ min(ps.ps_supplycost)
+
+ from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+ where
+ p.p_partkey = ps.ps_partkey
+ and s.s_suppkey = ps.ps_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+ )
+
+order by
+ s.s_acctbal desc,
+ n.n_name,
+ s.s_name,
+ p.p_partkey
+limit 100;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/03.sql b/exec/java-exec/src/test/resources/queries/tpch/03.sql new file mode 100644 index 000000000..f856e64af --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/03.sql @@ -0,0 +1,27 @@ +-- tpch3 using 1395599672 as a seed to the RNG
+select
+ l.l_orderkey,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue,
+ o.o_orderdate,
+ o.o_shippriority
+
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+
+where
+ c.c_mktsegment = 'HOUSEHOLD'
+ and c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+-- and o.o_orderdate < date '1995-03-25'
+-- and l.l_shipdate > date '1995-03-25'
+
+group by
+ l.l_orderkey,
+ o.o_orderdate,
+ o.o_shippriority
+order by
+ revenue desc,
+ o.o_orderdate
+limit 10;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/04.sql b/exec/java-exec/src/test/resources/queries/tpch/04.sql new file mode 100644 index 000000000..3c5957209 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/04.sql @@ -0,0 +1,24 @@ +-- tpch4 using 1395599672 as a seed to the RNG
+select
+ o_orderpriority,
+ count(*) as order_count
+from
+ cp.`tpch/orders.parquet`
+
+where
+-- o_orderdate >= date '1996-10-01'
+-- and o_orderdate < date '1996-10-01' + interval '3' month
+-- and
+ exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet`
+ where
+ l_orderkey = o_orderkey
+ and l_commitdate < l_receiptdate
+ )
+group by
+ o_orderpriority
+order by
+ o_orderpriority;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/05.sql b/exec/java-exec/src/test/resources/queries/tpch/05.sql new file mode 100644 index 000000000..ae82d39ef --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/05.sql @@ -0,0 +1,28 @@ +-- tpch5 using 1395599672 as a seed to the RNG
+select
+ n.n_name,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue
+
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+
+where
+ c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+ and l.l_suppkey = s.s_suppkey
+ and c.c_nationkey = s.s_nationkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+-- and o.o_orderdate >= date '1997-01-01'
+-- and o.o_orderdate < date '1997-01-01' + interval '1' year
+group by
+ n.n_name
+
+order by
+ revenue desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/06.sql b/exec/java-exec/src/test/resources/queries/tpch/06.sql new file mode 100644 index 000000000..f1780b63c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/06.sql @@ -0,0 +1,12 @@ +-- tpch6 using 1395599672 as a seed to the RNG
+
+select
+ sum(l_extendedprice * l_discount) as revenue
+from
+ cp.`tpch/lineitem.parquet`
+where
+-- l_shipdate >= date '1997-01-01'
+-- and l_shipdate < date '1997-01-01' + interval '1' year
+-- and
+ l_discount between 0.03 - 0.01 and 0.03 + 0.01
+ and l_quantity < 24;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/07.sql b/exec/java-exec/src/test/resources/queries/tpch/07.sql new file mode 100644 index 000000000..94bd51c35 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/07.sql @@ -0,0 +1,40 @@ +-- tpch7 using 1395599672 as a seed to the RNG
+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.l_shipdate) as l_year,
+ l.l_extendedprice * (1 - l.l_discount) as volume
+ from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/nation.parquet` n1,
+ cp.`tpch/nation.parquet` n2
+ where
+ s.s_suppkey = l.l_suppkey
+ and o.o_orderkey = l.l_orderkey
+ and c.c_custkey = o.o_custkey
+ and s.s_nationkey = n1.n_nationkey
+ and c.c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'EGYPT' and n2.n_name = 'UNITED STATES')
+ or (n1.n_name = 'UNITED STATES' and n2.n_name = 'EGYPT')
+ )
+-- and l.l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+group by
+ supp_nation,
+ cust_nation,
+ l_year
+order by
+ supp_nation,
+ cust_nation,
+ l_year;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/08.sql b/exec/java-exec/src/test/resources/queries/tpch/08.sql new file mode 100644 index 000000000..363b5a527 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/08.sql @@ -0,0 +1,38 @@ +-- tpch8 using 1395599672 as a seed to the RNG
+select
+ o_year,
+ sum(case
+ when nation = 'EGYPT' then volume
+ else 0
+ end) / sum(volume) as mkt_share
+from
+ (
+ select
+ extract(year from o.o_orderdate) as o_year,
+ l.l_extendedprice * (1 - l.l_discount) as volume,
+ n2.n_name as nation
+ from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/nation.parquet` n1,
+ cp.`tpch/nation.parquet` n2,
+ cp.`tpch/region.parquet` r
+ where
+ p.p_partkey = l.l_partkey
+ and s.s_suppkey = l.l_suppkey
+ and l.l_orderkey = o.o_orderkey
+ and o.o_custkey = c.c_custkey
+ and c.c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r.r_regionkey
+ and r.r_name = 'MIDDLE EAST'
+ and s.s_nationkey = n2.n_nationkey
+ and o.o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p.p_type = 'PROMO BRUSHED COPPER'
+ ) as all_nations
+group by
+ o_year
+order by
+ o_year;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/09.sql b/exec/java-exec/src/test/resources/queries/tpch/09.sql new file mode 100644 index 000000000..84aca9825 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/09.sql @@ -0,0 +1,33 @@ +-- tpch9 using 1395599672 as a seed to the RNG
+select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+from
+ (
+ select
+ n_name as nation,
+ extract(year from o_orderdate) as o_year,
+ l.l_extendedprice * (1 - l.l_discount) - ps.ps_supplycost * l.l_quantity as amount
+ from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/nation.parquet` n
+ where
+ s.s_suppkey = l.l_suppkey
+ and ps.ps_suppkey = l.l_suppkey
+ and ps.ps_partkey = l.l_partkey
+ and p.p_partkey = l.l_partkey
+ and o.o_orderkey = l.l_orderkey
+ and s.s_nationkey = n.n_nationkey
+ and p.p_name like '%yellow%'
+ ) as profit
+group by
+ nation,
+ o_year
+order by
+ nation,
+ o_year desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/10.sql b/exec/java-exec/src/test/resources/queries/tpch/10.sql new file mode 100644 index 000000000..dbf1aedbb --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/10.sql @@ -0,0 +1,33 @@ +-- tpch10 using 1395599672 as a seed to the RNG
+select
+ c.c_custkey,
+ c.c_name,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue,
+ c.c_acctbal,
+ n.n_name,
+ c.c_address,
+ c.c_phone,
+ c.c_comment
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/nation.parquet` n
+where
+ c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+ and o.o_orderdate >= date '1994-03-01'
+ and o.o_orderdate < date '1994-03-01' + interval '3' month
+ and l.l_returnflag = 'R'
+ and c.c_nationkey = n.n_nationkey
+group by
+ c.c_custkey,
+ c.c_name,
+ c.c_acctbal,
+ c.c_phone,
+ n.n_name,
+ c.c_address,
+ c.c_comment
+order by
+ revenue desc
+limit 20;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/11.sql b/exec/java-exec/src/test/resources/queries/tpch/11.sql new file mode 100644 index 000000000..6064454ad --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/11.sql @@ -0,0 +1,28 @@ +-- tpch11 using 1395599672 as a seed to the RNG
+select
+ ps.ps_partkey,
+ sum(ps.ps_supplycost * ps.ps_availqty) as `value`
+from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+where
+ ps.ps_suppkey = s.s_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'JAPAN'
+group by
+ ps.ps_partkey having
+ sum(ps.ps_supplycost * ps.ps_availqty) > (
+ select
+ sum(ps.ps_supplycost * ps.ps_availqty) * 0.0001000000
+ from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+ where
+ ps.ps_suppkey = s.s_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'JAPAN'
+ )
+order by
+ `value` desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/12.sql b/exec/java-exec/src/test/resources/queries/tpch/12.sql new file mode 100644 index 000000000..41bc5dd75 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/12.sql @@ -0,0 +1,29 @@ +-- tpch12 using 1395599672 as a seed to the RNG
+select
+ l.l_shipmode,
+ sum(case
+ when o.o_orderpriority = '1-URGENT'
+ or o.o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o.o_orderpriority <> '1-URGENT'
+ and o.o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+from
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+where
+ o.o_orderkey = l.l_orderkey
+ and l.l_shipmode in ('TRUCK', 'REG AIR')
+ and l.l_commitdate < l.l_receiptdate
+ and l.l_shipdate < l.l_commitdate
+-- and l.l_receiptdate >= date '1994-01-01'
+-- and l.l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+ l.l_shipmode
+order by
+ l.l_shipmode;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/13.sql b/exec/java-exec/src/test/resources/queries/tpch/13.sql new file mode 100644 index 000000000..ae3f691c9 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/13.sql @@ -0,0 +1,22 @@ +-- tpch13 using 1395599672 as a seed to the RNG
+select
+ c_count,
+ count(*) as custdist
+from
+ (
+ select
+ c.c_custkey,
+ count(o.o_orderkey)
+ from
+ cp.`tpch/customer.parquet` c
+ left outer join cp.`tpch/orders.parquet` o
+ on c.c_custkey = o.o_custkey
+ and o.o_comment not like '%special%requests%'
+ group by
+ c.c_custkey
+ ) as orders (c_custkey, c_count)
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/14.sql b/exec/java-exec/src/test/resources/queries/tpch/14.sql new file mode 100644 index 000000000..91cd938e3 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/14.sql @@ -0,0 +1,14 @@ +-- tpch14 using 1395599672 as a seed to the RNG
+select
+ 100.00 * sum(case
+ when p.p_type like 'PROMO%'
+ then l.l_extendedprice * (1 - l.l_discount)
+ else 0
+ end) / sum(l.l_extendedprice * (1 - l.l_discount)) as promo_revenue
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ l.l_partkey = p.p_partkey
+ and l.l_shipdate >= date '1994-08-01'
+ and l.l_shipdate < date '1994-08-01' + interval '1' month;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/15.sql b/exec/java-exec/src/test/resources/queries/tpch/15.sql new file mode 100644 index 000000000..251631d74 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/15.sql @@ -0,0 +1,34 @@ +-- tpch15 using 1395599672 as a seed to the RNG
+create view revenue0 (supplier_no, total_revenue) as
+ select
+ l_suppkey,
+ sum(l_extendedprice * (1 - l_discount))
+ from
+ cp.`tpch/lineitem.parquet`
+ where
+ l_shipdate >= date '1993-05-01'
+ and l_shipdate < date '1993-05-01' + interval '3' month
+ group by
+ l_suppkey;
+
+select
+ s.s_suppkey,
+ s.s_name,
+ s.s_address,
+ s.s_phone,
+ r.total_revenue
+from
+ cp.`tpch/supplier.parquet` s,
+ revenue0 r
+where
+ s.s_suppkey = r.supplier_no
+ and r.total_revenue = (
+ select
+ max(total_revenue)
+ from
+ revenue0
+ )
+order by
+ s.s_suppkey;
+
+drop view revenue0;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/16.sql b/exec/java-exec/src/test/resources/queries/tpch/16.sql new file mode 100644 index 000000000..96c5fd40c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/16.sql @@ -0,0 +1,31 @@ +-- tpch16 using 1395599672 as a seed to the RNG
+select
+ p.p_brand,
+ p.p_type,
+ p.p_size,
+ count(distinct ps.ps_suppkey) as supplier_cnt
+from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/part.parquet` p
+where
+ p.p_partkey = ps.ps_partkey
+ and p.p_brand <> 'Brand#21'
+ and p.p_type not like 'MEDIUM PLATED%'
+ and p.p_size in (38, 2, 8, 31, 44, 5, 14, 24)
+ and ps.ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ cp.`tpch/supplier.parquet`
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+group by
+ p.p_brand,
+ p.p_type,
+ p.p_size
+order by
+ supplier_cnt desc,
+ p.p_brand,
+ p.p_type,
+ p.p_size;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/17.sql b/exec/java-exec/src/test/resources/queries/tpch/17.sql new file mode 100644 index 000000000..24e2044b1 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/17.sql @@ -0,0 +1,18 @@ +-- tpch17 using 1395599672 as a seed to the RNG
+select
+ sum(l.l_extendedprice) / 7.0 as avg_yearly
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#13'
+ and p.p_container = 'JUMBO CAN'
+ and l.l_quantity < (
+ select
+ 0.2 * avg(l2.l_quantity)
+ from
+ cp.`tpch/lineitem.parquet` l2
+ where
+ l2.l_partkey = p.p_partkey
+ );
diff --git a/exec/java-exec/src/test/resources/queries/tpch/18.sql b/exec/java-exec/src/test/resources/queries/tpch/18.sql new file mode 100644 index 000000000..6fcddeb66 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/18.sql @@ -0,0 +1,34 @@ +-- tpch18 using 1395599672 as a seed to the RNG
+select
+ c.c_name,
+ c.c_custkey,
+ o.o_orderkey,
+ o.o_orderdate,
+ o.o_totalprice,
+ sum(l.l_quantity)
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+where
+ o.o_orderkey in (
+ select
+ l_orderkey
+ from
+ cp.`tpch/lineitem.parquet`
+ group by
+ l_orderkey having
+ sum(l_quantity) > 313
+ )
+ and c.c_custkey = o.o_custkey
+ and o.o_orderkey = l.l_orderkey
+group by
+ c.c_name,
+ c.c_custkey,
+ o.o_orderkey,
+ o.o_orderdate,
+ o.o_totalprice
+order by
+ o.o_totalprice desc,
+ o.o_orderdate
+limit 100;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/19.sql b/exec/java-exec/src/test/resources/queries/tpch/19.sql new file mode 100644 index 000000000..15ceb91a0 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/19.sql @@ -0,0 +1,36 @@ +-- tpch19 using 1395599672 as a seed to the RNG
+select
+ sum(l.l_extendedprice* (1 - l.l_discount)) as revenue
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#41'
+ and p.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l.l_quantity >= 2 and l.l_quantity <= 2 + 10
+ and p.p_size between 1 and 5
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#13'
+ and p.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ and l.l_quantity >= 14 and l.l_quantity <= 14 + 10
+ and p.p_size between 1 and 10
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#55'
+ and p.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l.l_quantity >= 23 and l.l_quantity <= 23 + 10
+ and p.p_size between 1 and 15
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ );
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/20.sql b/exec/java-exec/src/test/resources/queries/tpch/20.sql new file mode 100644 index 000000000..e1980340d --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/20.sql @@ -0,0 +1,38 @@ +-- tpch20 using 1395599672 as a seed to the RNG
+select
+ s.s_name,
+ s.s_address
+from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+where
+ s.s_suppkey in (
+ select
+ ps.ps_suppkey
+ from
+ cp.`tpch/partsupp.parquet` ps
+ where
+ ps. ps_partkey in (
+ select
+ p.p_partkey
+ from
+ cp.`tpch/part.parquet` p
+ where
+ p.p_name like 'antique%'
+ )
+ and ps.ps_availqty > (
+ select
+ 0.5 * sum(l.l_quantity)
+ from
+ cp.`tpch/lineitem.parquet` l
+ where
+ l.l_partkey = ps.ps_partkey
+ and l.l_suppkey = ps.ps_suppkey
+ and l.l_shipdate >= date '1993-01-01'
+ and l.l_shipdate < date '1993-01-01' + interval '1' year
+ )
+ )
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'KENYA'
+order by
+ s.s_name;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/21.sql b/exec/java-exec/src/test/resources/queries/tpch/21.sql new file mode 100644 index 000000000..7b50df64c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/21.sql @@ -0,0 +1,41 @@ +-- tpch21 using 1395599672 as a seed to the RNG
+select
+ s.s_name,
+ count(*) as numwait
+from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l1,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/nation.parquet` n
+where
+ s.s_suppkey = l1.l_suppkey
+ and o.o_orderkey = l1.l_orderkey
+ and o.o_orderstatus = 'F'
+ and l1.l_receiptdate > l1.l_commitdate
+ and exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet` l2
+ where
+ l2.l_orderkey = l1.l_orderkey
+ and l2.l_suppkey <> l1.l_suppkey
+ )
+ and not exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet` l3
+ where
+ l3.l_orderkey = l1.l_orderkey
+ and l3.l_suppkey <> l1.l_suppkey
+ and l3.l_receiptdate > l3.l_commitdate
+ )
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'BRAZIL'
+group by
+ s.s_name
+order by
+ numwait desc,
+ s.s_name
+limit 100;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/22.sql b/exec/java-exec/src/test/resources/queries/tpch/22.sql new file mode 100644 index 000000000..052633e41 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/22.sql @@ -0,0 +1,38 @@ +-- tpch22 using 1395599672 as a seed to the RNG
+select
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone from 1 for 2) as cntrycode,
+ c_acctbal
+ from
+ cp.`tpch/customer.parquet` c
+ where
+ substring(c_phone from 1 for 2) in
+ ('24', '31', '11', '16', '21', '20', '34')
+ and c_acctbal > (
+ select
+ avg(c_acctbal)
+ from
+ cp.`tpch/customer.parquet`
+ where
+ c_acctbal > 0.00
+ and substring(c_phone from 1 for 2) in
+ ('24', '31', '11', '16', '21', '20', '34')
+ )
+ and not exists (
+ select
+ *
+ from
+ cp.`tpch/orders.parquet` o
+ where
+ o.o_custkey = c.c_custkey
+ )
+ ) as custsale
+group by
+ cntrycode
+order by
+ cntrycode;
|