--! qt:dataset:tpch_0_001.customer
--! qt:dataset:tpch_0_001.lineitem
--! qt:dataset:tpch_0_001.nation
--! qt:dataset:tpch_0_001.orders
--! qt:dataset:tpch_0_001.part
--! qt:dataset:tpch_0_001.partsupp
--! qt:dataset:tpch_0_001.region
--! qt:dataset:tpch_0_001.supplier


use tpch_0_001;

set hive.transpose.aggr.join=true;
set hive.transpose.aggr.join.unique=true;
set hive.mapred.mode=nonstrict;

create view q18_tmp_cached as
select
	l_orderkey,
	sum(l_quantity) as t_sum_quantity
from
	lineitem
where
	l_orderkey is not null
group by
	l_orderkey;


explain cbo select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
	customer,
	orders,
	q18_tmp_cached t,
	lineitem l
where
c_custkey = o_custkey
and o_orderkey = t.l_orderkey
and o_orderkey is not null
and t.t_sum_quantity > 300
and o_orderkey = l.l_orderkey
and l.l_orderkey is not null
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;


select 'add constraints';

alter table orders add constraint pk_o primary key (o_orderkey) disable novalidate rely;
alter table customer add constraint pk_c primary key (c_custkey) disable novalidate rely;
alter table lineitem add constraint pk_l primary key (l_orderkey,l_linenumber) disable novalidate rely;

alter table lineitem change column L_ORDERKEY L_ORDERKEY int constraint li_ok_nn not null disable novalidate rely;

alter table lineitem add constraint li_o foreign key  (l_orderkey) references orders (o_orderkey) disable novalidate rely;
alter table orders   add constraint or_c foreign key  (o_custkey) references customer (c_custkey) disable novalidate rely;

explain cbo select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
	customer,
	orders,
	q18_tmp_cached t,
	lineitem l
where
c_custkey = o_custkey
and o_orderkey = t.l_orderkey
and o_orderkey is not null
and t.t_sum_quantity > 300
and o_orderkey = l.l_orderkey
and l.l_orderkey is not null
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;


select 'with extended costmodel';
set hive.cbo.costmodel.extended=true;

explain cbo select
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
from
	customer,
	orders,
	q18_tmp_cached t,
	lineitem l
where
c_custkey = o_custkey
and o_orderkey = t.l_orderkey
and o_orderkey is not null
and t.t_sum_quantity > 300
and o_orderkey = l.l_orderkey
and l.l_orderkey is not null
group by
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice
order by
o_totalprice desc,
o_orderdate
limit 100;