[HIVE-3892] auto sort merge join does not work for sub-queries

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.11.0
• Component/s: Query Processor
• Assignee: Namit Jain
• Reporter: Namit Jain
• Created: 14/Jan/13 09:37
• Updated: 24/Mar/14 20:50
• Resolved: 07/Feb/13 06:52

Description

set hive.enforce.bucketing = true;
set hive.enforce.sorting = true;
set hive.exec.reducers.max = 1;

CREATE TABLE tbl1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS;
CREATE TABLE tbl2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS;

insert overwrite table tbl1
select * from src where key < 10;

insert overwrite table tbl2
select * from src where key < 10;

set hive.auto.sort.merge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.auto.sort.merge.join.bigtbl.matcher = org.apache.hadoop.hive.ql.optimizer.SizeSortMergeJoinBigTableMatcher;

– The join is being performed in sub-query. It should be converted to a sort-merge join
explain
select count from (
select a.key as key, a.value as val1, b.value as val2 from tbl1 a join tbl2 b on a.key = b.key
) subq1;

Even after HIVE-3403, the test above does not convert to a sort-merge join.

Issue Links

• depends upon: HIVE-3403 user should not specify mapjoin to perform sort-merge bucketed join