[HIVE-4697] Subqueries with IN and NOT IN

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Component/s: Query Processor
• Assignee: Matthew Weaver
• Reporter: Matthew Weaver
• Created: 10/Jun/13 20:11
• Updated: 10/Jun/13 21:03
• Resolved: 10/Jun/13 21:03

Description

Functional Requirements

• Support WHERE x IN (<column subquery>);
  • <column subquery> returns one column, any number of rows.
• Support WHERE x NOT IN (<column subquery>);
• Support same types of subqueries in HAVING.
  • E.g.

    SELECT key FROM t1 GROUP BY key 
    HAVING COUNT(value) IN (SELECT p FROM t2);  

• Correlated subqueries not supported, for now at least
  • But still need to check for correlation, and bail if it occurs.
  • Correlated subquery:
    • A subquery that references a table that appears in a containing query (MySQL), thus requiring subquery evaluation to look outside its scope.
    • The subquery depends on the outer query for its values, so the subquery must be executed once for each row of the outer query. Also known as repeating Subqueries.

Tasks

• Rewrite IN (<column-subquery>) as a LEFT SEMI JOIN.
  • Not ready for public consumption. In particular, no check for correlated terms.
  • With test queries.
• Add check for correlated terms, return informative error message.
• Rewrite WHERE NOT IN (<column-subquery>) as a LEFT OUTER JOIN.
  • Return rows that don't match the right side
• Rewrite subqueries in HAVING, using LEFT SEMI JOIN and LEFT OUTER JOIN as above.

Issue Links

• duplicates: HIVE-784 Support uncorrelated subqueries in the WHERE clause