[HIVE-16791] Tez engine giving inaccurate results on SMB Map joins while map-join and shuffle join gets correct results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: Hive, HiveServer2
• Assignee: Deepak Jaiswal
• Reporter: Saumil Mayani
• Created: 30/May/17 20:48
• Updated: 29/Jul/17 02:24
• Resolved: 29/Jul/17 02:24

Description

SMB Join gives incorrect results.

SMB-Join
set hive.execution.engine=tez;
set hive.enforce.sortmergebucketmapjoin=false;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=500000;

OK
2016	1	11999639
2016	2	18955110
2017	2	22217437
Time taken: 92.647 seconds, Fetched: 3 row(s)

MAP-JOIN
set hive.execution.engine=tez;
set hive.enforce.sortmergebucketmapjoin=false;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=50000000;
OK
2016	1	26586093
2016	2	17724062
2017	2	8862031
Time taken: 17.49 seconds, Fetched: 3 row(s)

Shuffle Join
set hive.execution.engine=tez;
set hive.enforce.sortmergebucketmapjoin=false;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.auto.convert.sortmerge.join=false;
set hive.auto.convert.join=false;
set hive.auto.convert.join.noconditionaltask.size=50000000;

OK
2016	1	26586093
2016	2	17724062
2017	2	8862031
Time taken: 38.575 seconds, Fetched: 3 row(s)

Issue Links

• duplicates: HIVE-16965 SMB join may produce incorrect results