[HIVE-3633] sort-merge join does not work with sub-queries

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.11.0
• Component/s: Query Processor
• Assignee: Namit Jain Assign to me
• Reporter: Namit Jain
• Created: 29/Oct/12 06:17
• Updated: 16/May/13 21:10
• Resolved: 19/Dec/12 01:21

Description

Consider the following query:

create table smb_bucket_1(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 6 BUCKETS STORED AS TEXTFILE;
create table smb_bucket_2(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 6 BUCKETS STORED AS TEXTFILE;

– load the above tables

set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format = org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

explain
select count from
(
select /+mapjoin(a)/ a.key as key1, b.key as key2, a.value as value1, b.value as value2
from smb_bucket_1 a join smb_bucket_2 b on a.key = b.key)
subq;

The above query does not use sort-merge join. This would be very useful as we automatically convert the queries to use sorting and bucketing properties for join.

Issue Links

• is depended upon by: HIVE-3763 Aggregation followed by a sort-merge join requires a new MR job
• is depended upon by: HIVE-3403 user should not specify mapjoin to perform sort-merge bucketed join
• relates to: HIVE-3784 de-emphasize mapjoin hint