hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jesus Camacho Rodriguez (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (HIVE-19648) Multiple left outer join in single HQL query is giving wrong result .
Date Tue, 22 May 2018 16:15:00 GMT

    [ https://issues.apache.org/jira/browse/HIVE-19648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16484222#comment-16484222
] 

Jesus Camacho Rodriguez edited comment on HIVE-19648 at 5/22/18 4:14 PM:
-------------------------------------------------------------------------

[~adityafedex], which version have you used to reproduce? Master? Thanks


was (Author: jcamachorodriguez):
[~adityafedex], which version have you used to reproduce? Master?

> 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
>            Priority: Major
>
> *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)

Mime
View raw message