ql/src/test/queries/clientpositive/ppd_union_view.q

--! qt:dataset:src
set hive.mapred.mode=nonstrict;
-- test predicate pushdown on a view with a union

drop view v;

create table t1_new_n0 (key string, value string) partitioned by (ds string);

insert overwrite table t1_new_n0 partition (ds = '2011-10-15')
select 'key1', 'value1' from src tablesample (1 rows);

insert overwrite table t1_new_n0 partition (ds = '2011-10-16')
select 'key2', 'value2' from src tablesample (1 rows);

create table t1_old (keymap string, value string) partitioned by (ds string);

insert overwrite table t1_old partition (ds = '2011-10-13')
select 'keymap3', 'value3' from src tablesample (1 rows);

insert overwrite table t1_old partition (ds = '2011-10-14')
select 'keymap4', 'value4' from src tablesample (1 rows);

create table t1_mapping (key string, keymap string) partitioned by (ds string);

insert overwrite table t1_mapping partition (ds = '2011-10-13')
select 'key3', 'keymap3' from src tablesample (1 rows);

insert overwrite table t1_mapping partition (ds = '2011-10-14')
select 'key4', 'keymap4' from src tablesample (1 rows);


create view t1_n113 partitioned on (ds) as
select * from
(
select key, value, ds from t1_new_n0
union all
select key, value, t1_old.ds from t1_old join t1_mapping
on t1_old.keymap = t1_mapping.keymap and
   t1_old.ds = t1_mapping.ds
) subq;

explain extended
select * from t1_n113 where ds = '2011-10-13';

select * from t1_n113 where ds = '2011-10-13';

select * from t1_n113 where ds = '2011-10-14';

explain extended
select * from t1_n113 where ds = '2011-10-15';

select * from t1_n113 where ds = '2011-10-15';
select * from t1_n113 where ds = '2011-10-16';