set hive.explain.user=true;
set hive.optimize.index.filter=true;
set hive.auto.convert.join=true;
set hive.vectorized.execution.enabled=true;

drop table if exists x1_store_sales;
drop table if exists x1_date_dim;
drop table if exists x1_item;

create table x1_store_sales 
(
	ss_item_sk	int
)
partitioned by (ss_sold_date_sk int)
stored as orc;

create table x1_date_dim
(
	d_date_sk	int,
	d_month_seq	int,
	d_year		int,
	d_moy		int
)
stored as orc;

create table x1_item
(
	i_item_sk	int,
	i_category	char(10),
	i_current_price	decimal(7,2)
)
stored as orc;

insert into x1_date_dim values	(1,1,2000,2),
				(1,2,2001,2);
insert into x1_store_sales partition (ss_sold_date_sk=1) values (1);
insert into x1_store_sales partition (ss_sold_date_sk=2) values (2);

insert into x1_item values (1,2,1),(1,2,1),(2,2,1);

alter table x1_store_sales partition (ss_sold_date_sk=1) update statistics set(
'numRows'='123456',
'rawDataSize'='1234567');

alter table x1_date_dim update statistics set(
'numRows'='28',
'rawDataSize'='81449');

alter table x1_item update statistics set(
'numRows'='18',
'rawDataSize'='32710');

-- note: it is important that the below query uses DPP!

explain 
select   count(*) cnt
 from
     x1_store_sales s
     ,x1_date_dim d
     ,x1_item i
 where  
	1=1
 	and s.ss_sold_date_sk = d.d_date_sk
 	and s.ss_item_sk = i.i_item_sk
 	and d.d_month_seq in	 
 	     (select distinct (d_month_seq)
 	      from x1_date_dim
               where d_year = 2000 and d_year*d_moy > 200000
 	        and d_moy = 2 )
 	and i.i_current_price > 
            (select min(j.i_current_price) 
 	     from x1_item j 
 	     where j.i_category = i.i_category)

 group by d.d_month_seq
 order by cnt 
 limit 100;


select   count(*) cnt
 from
     x1_store_sales s
     ,x1_date_dim d
     ,x1_item i
 where  
	1=1
 	and s.ss_sold_date_sk = d.d_date_sk
 	and s.ss_item_sk = i.i_item_sk
 	and d.d_month_seq in	 
 	     (select distinct (d_month_seq)
 	      from x1_date_dim
               where d_year = 2000 and d_year*d_moy > 200000
 	        and d_moy = 2 )
 	and i.i_current_price > 
            (select min(j.i_current_price) 
 	     from x1_item j 
 	     where j.i_category = i.i_category)

 group by d.d_month_seq
 order by cnt 
 limit 100;