Hive
  1. Hive
  2. HIVE-832

Disallow OUTER JOIN with partition in WHERE clause in strict mode

    Details

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

      Description

      There is a wide-range poorly-understood issue in how WHERE and ON clauses work in the case of outer joins.

      The WHERE filter is applied after the join. After an outer join, the INNER table (the left table in a RIGHT OUTER, or the left table in the LEFT OUTER) has all columns set to NULL for all rows. As a result, if ANY of them are cited in the WHERE clause, they will equal NULL. This almost always causes the join to effectively be an INNER join, even though the user typed the word O-U-T-E-R, probably for good reason.

      In strict mode, we should not allow ANY COLUMNS from the inner table to appear in the WHERE clause.

        Activity

        Hide
        Namit Jain added a comment -

        Yes, but there was a usecase when this was being used to do perform subtraction (A - B).

        Show
        Namit Jain added a comment - Yes, but there was a usecase when this was being used to do perform subtraction (A - B).
        Hide
        Adam Kramer added a comment -

        I think if that's going on (seems like a valid use case upon reflection, but kind of a hack), requiring safe mode = off and/or throwing a warning is reasonable.

        Show
        Adam Kramer added a comment - I think if that's going on (seems like a valid use case upon reflection, but kind of a hack), requiring safe mode = off and/or throwing a warning is reasonable.

          People

          • Assignee:
            Unassigned
            Reporter:
            Adam Kramer
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development