[HIVE-8856] Multiple joins must have conditionals in same order

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 0.14.0
• Assignee: Ashutosh Chauhan
• Reporter: Ryan Pridgeon
• Created: 13/Nov/14 14:19
• Updated: 13/Nov/14 19:43
• Resolved: 13/Nov/14 16:12

Description

SELECT
COUNT
FROM
TBL_A,TBL_B,TBL_C
WHERE
A.key1 = B.key1 AND A.key2 = B.key2
AND
A.key2 = C.key2 AND A.Key1 = B.key1

Where key1 is a string and key2 is a double.

Note: This effects explicit joins as well

A look at the query plan reveals the following:

Map Join Operator
condition map:
Inner Join 0 to 1
Inner Join 0 to 2
condition expressions:
0

{prdct_id} {bu_cd}
1 {prdct_id}

{bu_cd}
2 {prdct_id} {bu_cd}

keys:
0 UDFToDouble(prdct_id) (type: double), bu_cd (type: double)
1 UDFToDouble(prdct_id) (type: double), bu_cd (type: double)
2 bu_cd (type: double), UDFToDouble(prdct_id) (type: double)

The ordering of keys within a join should not dictate it's type. This is something the query optimizer should handle prior to making the plan. This way users do not have to worry about ordrering their conditionals. At the very least it should fail, instead it silently converts them to nulls and returns 0.

Issue Links

• duplicates: HIVE-8298 Incorrect results for n-way join when join expressions are not in same order across joins