set hive.mapred.supports.subdirectories=true;
set hive.optimize.skewjoin.compiletime = true;
set hive.auto.convert.join=true;
    
CREATE TABLE T1(key STRING, val STRING)	
SKEWED BY (key) ON ((2)) STORED AS TEXTFILE;	
       
LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1;	
     
CREATE TABLE T2(key STRING, val STRING) STORED AS TEXTFILE;	
       
LOAD DATA LOCAL INPATH '../../data/files/T2.txt' INTO TABLE T2;	
     
-- copy from skewjoinopt11
-- test compile time skew join and auto map join	 
-- This test is to verify the skew join compile optimization when the join is followed
-- by a union. Both sides of a union consist of a join, which should have used
-- skew join compile time optimization.
-- adding an order by at the end to make the results deterministic

EXPLAIN	 
select * from	
(      
  select a.key, a.val as val1, b.val as val2 from T1 a join T2 b on a.key = b.key
    union all 	
  select a.key, a.val as val1, b.val as val2 from T1 a join T2 b on a.key = b.key
) subq1; 
  
select * from	
(      
  select a.key, a.val as val1, b.val as val2 from T1 a join T2 b on a.key = b.key
    union all 	
  select a.key, a.val as val1, b.val as val2 from T1 a join T2 b on a.key = b.key
) subq1
ORDER BY key, val1, val2;