Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-19648

Multiple left outer join in single HQL query is giving wrong result .

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Beeline
    • Labels:
      None
    • Environment:

      BEELINE HIVE 

      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 .

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              adityafedex aditya
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Due:
                Created:
                Updated: