blob: 6688a213e6bbfff9ad0fa7277114dcde2ea48dcf [file] [log] [blame]
-- Enable when DRILL-6914 is fixed
set `exec.hashjoin.enable.runtime_filter` = true;
set `exec.hashjoin.runtime_filter.max.waiting.time` = 10000;
set `planner.enable_broadcast_join` = true;
set `planner.broadcast_threshold` = 10000000;
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
supplier s,
lineitem l,
orders o,
customer c,
nation n1,
nation 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;
reset `exec.hashjoin.enable.runtime_filter`;
reset `exec.hashjoin.runtime_filter.max.waiting.time`;
reset `planner.enable_broadcast_join`;
reset `planner.broadcast_threshold`;