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

Multiple joins must have conditionals in same order

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: 0.14.0
    • Component/s: None
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ashutoshc Ashutosh Chauhan
                Reporter:
                rnpridgeon Ryan Pridgeon
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: