Hive
  1. Hive
  2. HIVE-7555

inner join is being resolves as cartesian product

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      CentOS

      Description

      I believe this is a bug, because I do not seem to be able to find a way around the following stackoverflow question,

      http://stackoverflow.com/questions/25020190/hive-query-returns-cartesian-product-instead-of-inner-join

      The issue is as follows (repeated from SO for convenience).
      This is type of query I am sending to HIVE:

      SELECT BigTable.nicefield,LargeTable.*
      FROM LargeTable INNER JOIN BigTable
      ON (
      LargeTable.joinfield1of4 = BigTable.joinfield1of4
      AND LargeTable.joinfield2of4 = BigTable.joinfield2of4
      )
      WHERE LargeTable.joinfield3of4=20140726 AND LargeTable.joinfield4of4=15 AND BigTable.joinfield3of4=20140726 AND BigTable.joinfield4of4=15
      AND LargeTable.filterfiled1of2=123456
      AND LargeTable.filterfiled2of2=98765
      AND LargeTable.joinfield2of4=12
      AND LargeTable.joinfield1of4='iwanttolikehive'

      It returns `2418025` rows. The issue is that

      SELECT *
      FROM LargeTable
      WHERE joinfield3of4=20140726 AND joinfield4of4=15
      AND filterfiled1of2=123456
      AND filterfiled2of2=98765
      AND joinfield2of4=12
      AND joinfield1of4='iwanttolikehive'

      returns `1555` rows, and so does:

      SELECT *
      FROM BigTable
      WHERE joinfield3of4=20140726 AND joinfield4of4=15
      AND joinfield2of4=12
      AND joinfield1of4='iwanttolikehive'

      Note that *1555^2 = 2418025*.

      Feel free to discard this issue if it is not a bug, but please provide a solution on SO.

      Thank you.

        Activity

        Hide
        J. Tipan Verella added a comment -

        My apologies,
        This was in fact an issue with the query, as documented on the stackoverflow.com page.

        Show
        J. Tipan Verella added a comment - My apologies, This was in fact an issue with the query, as documented on the stackoverflow.com page.

          People

          • Assignee:
            Unassigned
            Reporter:
            J. Tipan Verella
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development