[HIVE-19648] Multiple left outer join in single HQL query is giving wrong result .

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Beeline

Environment

BEELINE HIVE 

• Assignee: Unassigned
• Reporter: aditya
• Due: 22/May/18
• Created: 22/May/18 12:20
• Updated: 27/Feb/24 22:23

Description

INPUT :-
EMPLOYMENT
PARTY_ID
1
2
3
4
5
6
WORKPHONE
PARTY_ID , Telephone_Num , PRIMARY_IND , CD
1,100,Y,A
2,200,Y,A
4,300,N,A
5,400,N,C

select
distinct,
E.PARTY_ID ,
WP1.Telephone_Num ,
WP2.Telephone_Num ,
WP3.Telephone_Num ,
WP4.Telephone_Num ,
WP5.Telephone_Num ,
WP6.Telephone_Num
FROM
EMPLOYMENT E
LEFT OUTER JOIN WORKPHONE WP1
ON (E.PARTY_ID = WP1.PARTY_ID
AND WP1.PRIMARY_IND = 'Y'
AND WP1.CD = 'B')
LEFT OUTER JOIN WORKPHONE WP2
ON (E.PARTY_ID = WP2.PARTY_ID
AND WP2.CD = 'C')
LEFT OUTER JOIN WORKPHONE WP3
ON (E.PARTY_ID = WP3.PARTY_ID
AND WP3.CD = 'A' )
LEFT OUTER JOIN WORKPHONE WP4
ON (E.PARTY_ID = WP4.PARTY_ID
AND WP4.CD = 'D )
LEFT OUTER JOIN WORKPHONE WP5
ON (E.PARTY_ID = WP5.PARTY_ID
AND WP5.CD = 'E' )
LEFT OUTER JOIN WORKPHONE WP6
ON (E.PARTY_ID = WP6.PARTY_ID
AND WP6.CD = 'F') ;

Expected output :-
E.PARTY_ID ,Telephone_Num1,Telephone_Num2,Telephone_Num3,Telephone_Num4,Telephone_Num5,Telephone_Num6
1,100,null,null,null,null,null
2,200,null,null,null,null,null
3,null,null,null,null,null,null
4,null,null,null,null,null,null
5,null,null,400,null,null,null
6,null,null,null,null,null,null

Actual output :-
E.PARTY_ID ,Telephone_Num1,Telephone_Num2,Telephone_Num3,Telephone_Num4,Telephone_Num5,Telephone_Num6
1,null,null,null,null,null,null
2,null,null,null,null,null,null
3,null,null,null,null,null,null
4,null,null,null,null,null,null
5,null,null,null,null,null,null
6,null,null,null,null,null,null

However when we are chaging the position of the tables , it is showing correct result :-

select
distinct,
E.PARTY_ID ,
WP1.Telephone_Num ,
WP2.Telephone_Num ,
WP3.Telephone_Num ,
WP4.Telephone_Num ,
WP5.Telephone_Num ,
WP6.Telephone_Num
FROM
EMPLOYMENT E
LEFT OUTER JOIN WORKPHONE WP1
ON (E.PARTY_ID = WP1.PARTY_ID
AND WP1.PRIMARY_IND = 'Y'
AND WP1.CD = 'B')
LEFT OUTER JOIN WORKPHONE WP2
ON (E.PARTY_ID = WP2.PARTY_ID
AND WP2.CD = 'C')
LEFT OUTER JOIN WORKPHONE WP4
ON (E.PARTY_ID = WP4.PARTY_ID
AND WP4.CD = 'D )
LEFT OUTER JOIN WORKPHONE WP5
ON (E.PARTY_ID = WP5.PARTY_ID
AND WP5.CD = 'E' )
LEFT OUTER JOIN WORKPHONE WP6
ON (E.PARTY_ID = WP6.PARTY_ID
AND WP6.CD = 'F')
LEFT OUTER JOIN WORKPHONE WP3
ON (E.PARTY_ID = WP3.PARTY_ID
AND WP3.CD = 'A' ) ;

Result is coming as expected .

Note :- When we have matching value in the last left outer joined table , result is coming as expected .However when the last table in the left outer join has no matched value it is displaying null value for each column .