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

["aditya (JIRA)" <ji...@apache.org>]

aditya created HIVE-19648:
-----------------------------

             Summary: Multiple left outer join in single HQL query is giving wrong result .
                 Key: HIVE-19648
                 URL: https://issues.apache.org/jira/browse/HIVE-19648
             Project: Hive
          Issue Type: Bug
          Components: Beeline
         Environment: BEELINE HIVE 
            Reporter: aditya


*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 .

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)