--! qt:disabled:HIVE-24680
--! qt:dataset:srcpart
--! qt:dataset:srcbucket
--! qt:dataset:src1
--! qt:dataset:src
set hive.llap.execution.mode=auto;
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
-- SORT_QUERY_RESULTS

-- union10.q

-- union case: all subqueries are a map-reduce jobs, 3 way union, same input for all sub-queries, followed by filesink

create table tmptable_n9(key string, value int);

explain 
insert overwrite table tmptable_n9
  select unionsrc.key, unionsrc.value FROM (select 'tst1_n93' as key, count(1) as value from src s1
                                        UNION DISTINCT  
                                            select 'tst2_n58' as key, count(1) as value from src s2
                                        UNION DISTINCT
                                            select 'tst3_n22' as key, count(1) as value from src s3) unionsrc;


insert overwrite table tmptable_n9
  select unionsrc.key, unionsrc.value FROM (select 'tst1_n93' as key, count(1) as value from src s1
                                        UNION DISTINCT  
                                            select 'tst2_n58' as key, count(1) as value from src s2
                                        UNION DISTINCT
                                            select 'tst3_n22' as key, count(1) as value from src s3) unionsrc;


select * from tmptable_n9 x sort by x.key;


-- union12.q

 
-- union case: all subqueries are a map-reduce jobs, 3 way union, different inputs for all sub-queries, followed by filesink

create table tmptable12(key string, value int);

explain 
insert overwrite table tmptable12
  select unionsrc.key, unionsrc.value FROM (select 'tst1_n93' as key, count(1) as value from src s1
                                        UNION DISTINCT  
                                            select 'tst2_n58' as key, count(1) as value from src1 s2
                                        UNION DISTINCT
                                            select 'tst3_n22' as key, count(1) as value from srcbucket s3) unionsrc;


insert overwrite table tmptable12
  select unionsrc.key, unionsrc.value FROM (select 'tst1_n93' as key, count(1) as value from src s1
                                        UNION DISTINCT  
                                            select 'tst2_n58' as key, count(1) as value from src1 s2
                                        UNION DISTINCT
                                            select 'tst3_n22' as key, count(1) as value from srcbucket s3) unionsrc;

select * from tmptable12 x sort by x.key;
-- union13.q

-- SORT_BEFORE_DIFF
-- union case: both subqueries are a map-only jobs, same input, followed by filesink

explain 
  select unionsrc.key, unionsrc.value FROM (select s1.key as key, s1.value as value from src s1 UNION DISTINCT  
                                            select s2.key as key, s2.value as value from src s2) unionsrc;

select unionsrc.key, unionsrc.value FROM (select s1.key as key, s1.value as value from src s1 UNION DISTINCT  
                                          select s2.key as key, s2.value as value from src s2) unionsrc;
-- union17.q

CREATE TABLE DEST1_n96(key STRING, value STRING) STORED AS TEXTFILE;
CREATE TABLE DEST2_n26(key STRING, val1 STRING, val2 STRING) STORED AS TEXTFILE;

-- SORT_BEFORE_DIFF
-- union case:map-reduce sub-queries followed by multi-table insert

explain 
FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST1_n96 SELECT unionsrc.key, COUNT(DISTINCT SUBSTR(unionsrc.value,5)) GROUP BY unionsrc.key
INSERT OVERWRITE TABLE DEST2_n26 SELECT unionsrc.key, unionsrc.value, COUNT(DISTINCT SUBSTR(unionsrc.value,5)) GROUP BY unionsrc.key, unionsrc.value;

FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST1_n96 SELECT unionsrc.key, COUNT(DISTINCT SUBSTR(unionsrc.value,5)) GROUP BY unionsrc.key
INSERT OVERWRITE TABLE DEST2_n26 SELECT unionsrc.key, unionsrc.value, COUNT(DISTINCT SUBSTR(unionsrc.value,5)) GROUP BY unionsrc.key, unionsrc.value;

SELECT DEST1_n96.* FROM DEST1_n96;
SELECT DEST2_n26.* FROM DEST2_n26;
-- union18.q

CREATE TABLE DEST118(key STRING, value STRING) STORED AS TEXTFILE;
CREATE TABLE DEST218(key STRING, val1 STRING, val2 STRING) STORED AS TEXTFILE;

-- union case:map-reduce sub-queries followed by multi-table insert 

explain 
FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST118 SELECT unionsrc.key, unionsrc.value
INSERT OVERWRITE TABLE DEST218 SELECT unionsrc.key, unionsrc.value, unionsrc.value;

FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST118 SELECT unionsrc.key, unionsrc.value
INSERT OVERWRITE TABLE DEST218 SELECT unionsrc.key, unionsrc.value, unionsrc.value;

SELECT DEST118.* FROM DEST118 SORT BY DEST118.key, DEST118.value;
SELECT DEST218.* FROM DEST218 SORT BY DEST218.key, DEST218.val1, DEST218.val2;
-- union19.q


CREATE TABLE DEST119(key STRING, value STRING) STORED AS TEXTFILE;
CREATE TABLE DEST219(key STRING, val1 STRING, val2 STRING) STORED AS TEXTFILE;

-- union case:map-reduce sub-queries followed by multi-table insert

explain 
FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST119 SELECT unionsrc.key, count(unionsrc.value) group by unionsrc.key
INSERT OVERWRITE TABLE DEST219 SELECT unionsrc.key, unionsrc.value, unionsrc.value;

FROM (select 'tst1_n93' as key, cast(count(1) as string) as value from src s1
                         UNION DISTINCT  
      select s2.key as key, s2.value as value from src s2) unionsrc
INSERT OVERWRITE TABLE DEST119 SELECT unionsrc.key, count(unionsrc.value) group by unionsrc.key
INSERT OVERWRITE TABLE DEST219 SELECT unionsrc.key, unionsrc.value, unionsrc.value;

SELECT DEST119.* FROM DEST119 SORT BY DEST119.key, DEST119.value;
SELECT DEST219.* FROM DEST219 SORT BY DEST219.key, DEST219.val1, DEST219.val2;


-- union22.q

-- SORT_QUERY_RESULTS

create table dst_union22_n0(k1 string, k2 string, k3 string, k4 string) partitioned by (ds string);
create table dst_union22_delta_n0(k0 string, k1 string, k2 string, k3 string, k4 string, k5 string) partitioned by (ds string);

insert overwrite table dst_union22_n0 partition (ds='1')
select key, value, key , value from src;

insert overwrite table dst_union22_delta_n0 partition (ds='1')
select key, key, value, key, value, value from src;

set hive.merge.mapfiles=false;

set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=8000;

-- Since the inputs are small, it should be automatically converted to mapjoin

explain extended
insert overwrite table dst_union22_n0 partition (ds='2')
select * from
(
select k1 as k1, k2 as k2, k3 as k3, k4 as k4 from dst_union22_delta_n0 where ds = '1' and k0 <= 50
UNION DISTINCT
select a.k1 as k1, a.k2 as k2, b.k3 as k3, b.k4 as k4
from dst_union22_n0 a left outer join (select * from dst_union22_delta_n0 where ds = '1' and k0 > 50) b on
a.k1 = b.k1 and a.ds='1'
where a.k1 > 20
)
subq;

insert overwrite table dst_union22_n0 partition (ds='2')
select * from
(
select k1 as k1, k2 as k2, k3 as k3, k4 as k4 from dst_union22_delta_n0 where ds = '1' and k0 <= 50
UNION DISTINCT
select a.k1 as k1, a.k2 as k2, b.k3 as k3, b.k4 as k4
from dst_union22_n0 a left outer join (select * from dst_union22_delta_n0 where ds = '1' and k0 > 50) b on
a.k1 = b.k1 and a.ds='1'
where a.k1 > 20
)
subq;

select * from dst_union22_n0 where ds = '2';
-- union23.q

explain
select s.key2, s.value2
from (
  select transform(key, value) using 'cat' as (key2, value2)
  from src
  UNION DISTINCT 
  select key as key2, value as value2 from src) s;

select s.key2, s.value2
from (
  select transform(key, value) using 'cat' as (key2, value2)
  from src
  UNION DISTINCT 
  select key as key2, value as value2 from src) s;

-- union24.q

-- SORT_QUERY_RESULTS

create table src2_n2 as select key, count(1) as count from src group by key;
create table src3 as select * from src2_n2;
create table src4 as select * from src2_n2;
create table src5_n1 as select * from src2_n2;


set hive.merge.mapfiles=false;
set hive.merge.mapredfiles=false;


explain extended
select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select key, count from src4  where key < 10
  UNION DISTINCT
  select key, count(1) as count from src5_n1 where key < 10 group by key
)s
;

select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select key, count from src4  where key < 10
  UNION DISTINCT
  select key, count(1) as count from src5_n1 where key < 10 group by key
)s
;

explain extended
select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select a.key as key, b.count as count from src4 a join src5_n1 b on a.key=b.key where a.key < 10
)s
;

select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select a.key as key, b.count as count from src4 a join src5_n1 b on a.key=b.key where a.key < 10
)s
;

explain extended
select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select a.key as key, count(1) as count from src4 a join src5_n1 b on a.key=b.key where a.key < 10 group by a.key
)s
;

select s.key, s.count from (
  select key, count from src2_n2  where key < 10
  UNION DISTINCT
  select key, count from src3  where key < 10
  UNION DISTINCT
  select a.key as key, count(1) as count from src4 a join src5_n1 b on a.key=b.key where a.key < 10 group by a.key
)s
;
-- union25.q

create table tmp_srcpart_n0 like srcpart;

insert overwrite table tmp_srcpart_n0 partition (ds='2008-04-08', hr='11')
select key, value from srcpart where ds='2008-04-08' and hr='11';

explain
create table tmp_unionall_n0 as
SELECT count(1) as counts, key, value
FROM
(
  SELECT key, value FROM srcpart a WHERE a.ds='2008-04-08' and a.hr='11'

    UNION DISTINCT

  SELECT key, key as value FROM (
    SELECT distinct key FROM (
      SELECT key, value FROM tmp_srcpart_n0 a WHERE a.ds='2008-04-08' and a.hr='11'
        UNION DISTINCT
      SELECT key, value FROM tmp_srcpart_n0 b WHERE b.ds='2008-04-08' and b.hr='11'
    )t
  ) master_table
) a GROUP BY key, value
;

set hive.stats.fetch.column.stats=false;
-- union26.q

-- SORT_QUERY_RESULTS

set hive.auto.convert.join.noconditionaltask.size=20000;

EXPLAIN
SELECT 
count(1) as counts,
key,
value
FROM
(

SELECT
a.key, a.value
FROM srcpart a JOIN srcpart b 
ON a.ds='2008-04-08' and a.hr='11' and b.ds='2008-04-08' and b.hr='12'
AND a.key = b.key 

UNION DISTINCT

select key, value 
FROM srcpart LATERAL VIEW explode(array(1,2,3)) myTable AS myCol
WHERE ds='2008-04-08' and hr='11'
) a
group by key, value
;

SELECT 
count(1) as counts,
key,
value
FROM
(

SELECT
a.key, a.value
FROM srcpart a JOIN srcpart b 
ON a.ds='2008-04-08' and a.hr='11' and b.ds='2008-04-08' and b.hr='12'
AND a.key = b.key 

UNION DISTINCT

select key, value 
FROM srcpart LATERAL VIEW explode(array(1,2,3)) myTable AS myCol
WHERE ds='2008-04-08' and hr='11'
) a
group by key, value
;

set hive.stats.fetch.column.stats=true;

SELECT 
count(1) as counts,
key,
value
FROM
(

SELECT
a.key, a.value
FROM srcpart a JOIN srcpart b 
ON a.ds='2008-04-08' and a.hr='11' and b.ds='2008-04-08' and b.hr='12'
AND a.key = b.key 

UNION DISTINCT

select key, value 
FROM srcpart LATERAL VIEW explode(array(1,2,3)) myTable AS myCol
WHERE ds='2008-04-08' and hr='11'
) a
group by key, value
;

SELECT 
count(1) as counts,
key,
value
FROM
(

SELECT
a.key, a.value
FROM srcpart a JOIN srcpart b 
ON a.ds='2008-04-08' and a.hr='11' and b.ds='2008-04-08' and b.hr='12'
AND a.key = b.key 

UNION DISTINCT

select key, value 
FROM srcpart LATERAL VIEW explode(array(1,2,3)) myTable AS myCol
WHERE ds='2008-04-08' and hr='11'
) a
group by key, value
;
-- union27.q

-- SORT_BEFORE_DIFF
create table jackson_sev_same as select * from src;
create table dim_pho as select * from src;
create table jackson_sev_add as select * from src;
explain select b.* from jackson_sev_same a join (select * from dim_pho UNION DISTINCT select * from jackson_sev_add)b on a.key=b.key and b.key=97;
select b.* from jackson_sev_same a join (select * from dim_pho UNION DISTINCT select * from jackson_sev_add)b on a.key=b.key and b.key=97;
-- union28.q

create table union_subq_union_n0(key int, value string);

explain
insert overwrite table union_subq_union_n0 
select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value, count(1) from src group by key, value
    UNION DISTINCT
    select key, value, count(1) from src group by key, value
  ) subq
) a
;

insert overwrite table union_subq_union_n0 
select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value, count(1) from src group by key, value
    UNION DISTINCT
    select key, value, count(1) from src group by key, value
  ) subq
) a
;

select * from union_subq_union_n0 order by key, value limit 20;
-- union29.q

create table union_subq_union29(key int, value string);

explain
insert overwrite table union_subq_union29 
select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value from src 
    UNION DISTINCT
    select key, value from src
  ) subq
) a
;

insert overwrite table union_subq_union29 
select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value from src 
    UNION DISTINCT
    select key, value from src
  ) subq
) a
;

select * from union_subq_union29 order by key, value limit 20;
-- union3.q

-- SORT_BEFORE_DIFF

explain
SELECT *
FROM (
  SELECT 1 AS id
  FROM (SELECT * FROM src LIMIT 1) s1
  UNION DISTINCT
  SELECT 2 AS id
  FROM (SELECT * FROM src LIMIT 1) s1
  UNION DISTINCT
  SELECT 3 AS id
  FROM (SELECT * FROM src LIMIT 1) s2
  UNION DISTINCT
  SELECT 4 AS id
  FROM (SELECT * FROM src LIMIT 1) s2
  CLUSTER BY id
) a;


CREATE TABLE union_out (id int);

insert overwrite table union_out 
SELECT *
FROM (
  SELECT 1 AS id
  FROM (SELECT * FROM src LIMIT 1) s1
  UNION DISTINCT
  SELECT 2 AS id
  FROM (SELECT * FROM src LIMIT 1) s1
  UNION DISTINCT
  SELECT 3 AS id
  FROM (SELECT * FROM src LIMIT 1) s2
  UNION DISTINCT
  SELECT 4 AS id
  FROM (SELECT * FROM src LIMIT 1) s2
  CLUSTER BY id
) a;

select * from union_out;
-- union30.q

create table union_subq_union30(key int, value string);

explain
insert overwrite table union_subq_union30 
select * from (

select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value, count(1) from src group by key, value
    UNION DISTINCT
    select key, value, count(1) from src group by key, value
  ) subq
) a

UNION DISTINCT

select key, value from src
) aa
;

insert overwrite table union_subq_union30 
select * from (

select * from (
  select key, value from src 
  UNION DISTINCT 
  select key, value from 
  (
    select key, value, count(1) from src group by key, value
    UNION DISTINCT
    select key, value, count(1) from src group by key, value
  ) subq
) a

UNION DISTINCT

select key, value from src
) aa
;

select * from union_subq_union30 order by key, value limit 20;
-- union31.q

-- SORT_QUERY_RESULTS

drop table t1_n93;
drop table t2_n58;


create table t1_n93 as select * from src where key < 10;
create table t2_n58 as select * from src where key < 10;

create table t3_n22(key string, cnt int);
create table t4_n11(value string, cnt int);

explain
from
(select * from t1_n93
 UNION DISTINCT
 select * from t2_n58
) x
insert overwrite table t3_n22
  select key, count(1) group by key
insert overwrite table t4_n11
  select value, count(1) group by value;

from
(select * from t1_n93
 UNION DISTINCT
 select * from t2_n58
) x
insert overwrite table t3_n22
  select key, count(1) group by key
insert overwrite table t4_n11
  select value, count(1) group by value;

select * from t3_n22;
select * from t4_n11;

create table t5_n4(c1 string, cnt int);
create table t6_n3(c1 string, cnt int);

explain
from
(
 select key as c1, count(1) as cnt from t1_n93 group by key
   UNION DISTINCT
 select key as c1, count(1) as cnt from t2_n58 group by key
) x
insert overwrite table t5_n4
  select c1, sum(cnt) group by c1
insert overwrite table t6_n3
  select c1, sum(cnt) group by c1;

from
(
 select key as c1, count(1) as cnt from t1_n93 group by key
   UNION DISTINCT
 select key as c1, count(1) as cnt from t2_n58 group by key
) x
insert overwrite table t5_n4
  select c1, sum(cnt) group by c1
insert overwrite table t6_n3
  select c1, sum(cnt) group by c1;

select * from t5_n4;
select * from t6_n3;


create table t9_n1 as select key, count(1) as cnt from src where key < 10 group by key;

create table t7_n4(c1 string, cnt int);
create table t8_n2(c1 string, cnt int);

explain
from
(
 select key as c1, count(1) as cnt from t1_n93 group by key
   UNION DISTINCT
 select key as c1, cnt from t9_n1
) x
insert overwrite table t7_n4
  select c1, count(1) group by c1
insert overwrite table t8_n2
  select c1, count(1) group by c1;

from
(
 select key as c1, count(1) as cnt from t1_n93 group by key
   UNION DISTINCT
 select key as c1, cnt from t9_n1
) x
insert overwrite table t7_n4
  select c1, count(1) group by c1
insert overwrite table t8_n2
  select c1, count(1) group by c1;

select * from t7_n4;
select * from t8_n2;
-- union32.q

-- SORT_QUERY_RESULTS

-- This tests various union queries which have columns on one side of the query
-- being of double type and those on the other side another

-- Test simple union with double
EXPLAIN
SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key FROM t1_n93
UNION DISTINCT
SELECT CAST(key AS BIGINT) AS key FROM t2_n58) a;

SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key FROM t1_n93
UNION DISTINCT
SELECT CAST(key AS BIGINT) AS key FROM t2_n58) a
;

-- Test union with join on the left
EXPLAIN
SELECT * FROM 
(SELECT CAST(a.key AS BIGINT) AS key FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key
UNION DISTINCT
SELECT CAST(key AS DOUBLE) AS key FROM t2_n58) a
;

SELECT * FROM 
(SELECT CAST(a.key AS BIGINT) AS key FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key
UNION DISTINCT
SELECT CAST(key AS DOUBLE) AS key FROM t2_n58) a
;

-- Test union with join on the right
EXPLAIN
SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key FROM t2_n58
UNION DISTINCT
SELECT CAST(a.key AS BIGINT) AS key FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key) a
;

SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key FROM t2_n58
UNION DISTINCT
SELECT CAST(a.key AS BIGINT) AS key FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key) a
;

-- Test union with join on the left selecting multiple columns
EXPLAIN
SELECT * FROM 
(SELECT CAST(a.key AS BIGINT) AS key, CAST(b.key AS STRING) AS value FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key
UNION DISTINCT
SELECT CAST(key AS DOUBLE) AS key, CAST(key AS STRING) AS value FROM t2_n58) a
;

SELECT * FROM 
(SELECT CAST(a.key AS BIGINT) AS key, CAST(b.key AS CHAR(20)) AS value FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key
UNION DISTINCT
SELECT CAST(key AS DOUBLE) AS key, CAST(key AS STRING) AS value FROM t2_n58) a
;

-- Test union with join on the right selecting multiple columns
EXPLAIN
SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key, CAST(key AS STRING) AS value FROM t2_n58
UNION DISTINCT
SELECT CAST(a.key AS BIGINT) AS key, CAST(b.key AS VARCHAR(20)) AS value FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key) a
;

SELECT * FROM 
(SELECT CAST(key AS DOUBLE) AS key, CAST(key AS STRING) AS value FROM t2_n58
UNION DISTINCT
SELECT CAST(a.key AS BIGINT) AS key, CAST(b.key AS VARCHAR(20)) AS value FROM t1_n93 a JOIN t2_n58 b ON a.key = b.key) a
;
-- union33.q

-- SORT_BEFORE_DIFF
-- This tests that a UNION DISTINCT with a map only subquery on one side and a 
-- subquery involving two map reduce jobs on the other runs correctly.

drop table if exists test_src;

CREATE TABLE test_src (key STRING, value STRING);

EXPLAIN INSERT OVERWRITE TABLE test_src 
SELECT key, value FROM (
	SELECT key, value FROM src 
	WHERE key = 0
UNION DISTINCT
 	SELECT key, cast(COUNT(*) as string) AS value FROM src
 	GROUP BY key
)a;
 
INSERT OVERWRITE TABLE test_src 
SELECT key, value FROM (
	SELECT key, value FROM src 
	WHERE key = 0
UNION DISTINCT
 	SELECT key, cast(COUNT(*) as string) AS value FROM src
 	GROUP BY key
)a;
 
SELECT COUNT(*) FROM test_src;
 
EXPLAIN INSERT OVERWRITE TABLE test_src 
SELECT key, value FROM (
	SELECT key, cast(COUNT(*) as string) AS value FROM src
 	GROUP BY key
UNION DISTINCT
 	SELECT key, value FROM src 
	WHERE key = 0
)a;
 
INSERT OVERWRITE TABLE test_src 
SELECT key, value FROM (
	SELECT key, cast(COUNT(*) as string) AS value FROM src
 	GROUP BY key
UNION DISTINCT
 	SELECT key, value FROM src 
	WHERE key = 0
)a;
 
SELECT COUNT(*) FROM test_src;
 -- union34.q

create table src10_1 (key string, value string);
create table src10_2 (key string, value string);
create table src10_3 (key string, value string);
create table src10_4 (key string, value string);

from (select * from src tablesample (10 rows)) a
insert overwrite table src10_1 select *
insert overwrite table src10_2 select *
insert overwrite table src10_3 select *
insert overwrite table src10_4 select *;

analyze table src10_1 compute statistics;
analyze table src10_2 compute statistics;
analyze table src10_3 compute statistics;
analyze table src10_4 compute statistics;

set hive.auto.convert.join=true;
-- When we convert the Join of sub1 and sub0 into a MapJoin,
-- we can use a single MR job to evaluate this entire query.
explain
SELECT * FROM (
  SELECT sub1.key,sub1.value FROM (SELECT * FROM src10_1) sub1 JOIN (SELECT * FROM src10_2) sub0 ON (sub0.key = sub1.key)
  UNION DISTINCT
  SELECT key,value FROM (SELECT * FROM (SELECT * FROM src10_3) sub2 UNION DISTINCT SELECT * FROM src10_4 ) alias0
) alias1;

SELECT * FROM (
  SELECT sub1.key,sub1.value FROM (SELECT * FROM src10_1) sub1 JOIN (SELECT * FROM src10_2) sub0 ON (sub0.key = sub1.key)
  UNION DISTINCT
  SELECT key,value FROM (SELECT * FROM (SELECT * FROM src10_3) sub2 UNION DISTINCT SELECT * FROM src10_4 ) alias0
) alias1;

set hive.auto.convert.join=false;
-- When we do not convert the Join of sub1 and sub0 into a MapJoin,
-- we need to use two MR jobs to evaluate this query.
-- The first job is for the Join of sub1 and sub2. The second job
-- is for the UNION DISTINCT and ORDER BY.
explain
SELECT * FROM (
  SELECT sub1.key,sub1.value FROM (SELECT * FROM src10_1) sub1 JOIN (SELECT * FROM src10_2) sub0 ON (sub0.key = sub1.key)
  UNION DISTINCT
  SELECT key,value FROM (SELECT * FROM (SELECT * FROM src10_3) sub2 UNION DISTINCT SELECT * FROM src10_4 ) alias0
) alias1;

SELECT * FROM (
  SELECT sub1.key,sub1.value FROM (SELECT * FROM src10_1) sub1 JOIN (SELECT * FROM src10_2) sub0 ON (sub0.key = sub1.key)
  UNION DISTINCT
  SELECT key,value FROM (SELECT * FROM (SELECT * FROM src10_3) sub2 UNION DISTINCT SELECT * FROM src10_4 ) alias0
) alias1;