set hive.stats.dbclass=fs;
CREATE TABLE src_union_1 (key int, value string) PARTITIONED BY (ds string);
CREATE INDEX src_union_1_key_idx ON TABLE src_union_1(key) AS 'COMPACT' WITH DEFERRED REBUILD;

CREATE TABLE src_union_2 (key int, value string) PARTITIONED BY (ds string, part_1 string);
CREATE INDEX src_union_2_key_idx ON TABLE src_union_2(key) AS 'COMPACT' WITH DEFERRED REBUILD;

CREATE TABLE src_union_3(key int, value string) PARTITIONED BY (ds string, part_1 string, part_2 string);
CREATE INDEX src_union_3_key_idx ON TABLE src_union_3(key) AS 'COMPACT' WITH DEFERRED REBUILD;

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;

SET hive.exec.pre.hooks=;
SET hive.exec.post.hooks=;
SET hive.semantic.analyzer.hook=;
SET hive.merge.mapfiles=false;
SET hive.merge.mapredfiles=false;

INSERT OVERWRITE TABLE src_union_1 PARTITION (ds='1') SELECT * FROM src;
ALTER INDEX src_union_1_key_idx ON src_union_1 PARTITION (ds='1') REBUILD;

INSERT OVERWRITE TABLE src_union_2 PARTITION (ds='2', part_1='1') SELECT * FROM src;
INSERT OVERWRITE TABLE src_union_2 PARTITION (ds='2', part_1='2') SELECT * FROM src;
ALTER INDEX src_union_2_key_idx ON src_union_2 PARTITION (ds='2', part_1='1') REBUILD;
ALTER INDEX src_union_2_key_idx ON src_union_2 PARTITION (ds='2', part_1='2') REBUILD;

INSERT OVERWRITE TABLE src_union_3 PARTITION (ds='3', part_1='1', part_2='2:3+4') SELECT * FROM src;
INSERT OVERWRITE TABLE src_union_3 PARTITION (ds='3', part_1='2', part_2='2:3+4') SELECT * FROM src;
ALTER INDEX src_union_3_key_idx ON src_union_3 PARTITION (ds='3', part_1='1', part_2='2:3+4') REBUILD;
ALTER INDEX src_union_3_key_idx ON src_union_3 PARTITION (ds='3', part_1='2', part_2='2:3+4') REBUILD;

EXPLAIN SELECT key, value, ds FROM src_union_1 WHERE key=86 and ds='1';
EXPLAIN SELECT key, value, ds FROM src_union_2 WHERE key=86 and ds='2';
EXPLAIN SELECT key, value, ds FROM src_union_3 WHERE key=86 and ds='3';

SELECT key, value, ds FROM src_union_1 WHERE key=86 AND ds ='1';
SELECT key, value, ds FROM src_union_2 WHERE key=86 AND ds ='2';
SELECT key, value, ds FROM src_union_3 WHERE key=86 AND ds ='3';

EXPLAIN SELECT count(1) from src_union_1 WHERE ds ='1';
EXPLAIN SELECT count(1) from src_union_2 WHERE ds ='2';
EXPLAIN SELECT count(1) from src_union_3 WHERE ds ='3';

SELECT count(1) from src_union_1 WHERE ds ='1';
SELECT count(1) from src_union_2 WHERE ds ='2';
SELECT count(1) from src_union_3 WHERE ds ='3';

CREATE VIEW src_union_view PARTITIONED ON (ds) as
SELECT key, value, ds FROM (
SELECT key, value, ds FROM src_union_1
UNION ALL
SELECT key, value, ds FROM src_union_2
UNION ALL
SELECT key, value, ds FROM src_union_3
) subq;

EXPLAIN SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='1';
EXPLAIN SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='2';
EXPLAIN SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='3';
EXPLAIN SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds IS NOT NULL order by ds;

SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='1';
SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='2';
SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='3';
SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds IS NOT NULL order by ds;

EXPLAIN SELECT count(1) from src_union_view WHERE ds ='1';
EXPLAIN SELECT count(1) from src_union_view WHERE ds ='2';
EXPLAIN SELECT count(1) from src_union_view WHERE ds ='3';

SELECT count(1) from src_union_view WHERE ds ='1';
SELECT count(1) from src_union_view WHERE ds ='2';
SELECT count(1) from src_union_view WHERE ds ='3';

INSERT OVERWRITE TABLE src_union_3 PARTITION (ds='4', part_1='1', part_2='2:3+4') SELECT * FROM src;
ALTER INDEX src_union_3_key_idx ON src_union_3 PARTITION (ds='4', part_1='1', part_2='2:3+4') REBUILD;

EXPLAIN SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='4';
SELECT key, value, ds FROM src_union_view WHERE key=86 AND ds ='4';

EXPLAIN SELECT count(1) from src_union_view WHERE ds ='4';
SELECT count(1) from src_union_view WHERE ds ='4';