[HIVE-24929] Allow correlated exists subqueries with windowing clause

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Query Planning
• Labels: pull-request-available
• Assignee: Krisztian Kasa
• Reporter: Krisztian Kasa
• Created: 24/Mar/21 08:58
• Updated: 17/Nov/22 08:48
• Resolved: 25/Mar/21 18:41

Description

Currently queries which has windowing clause with subqueries are not supported by Hive: Hive rewrites subqueries to joins and the rewritten plan would lead to incorrect results such cases.
However this restriction can be lifted in case of Exists/Not exists subqueries since those cases we don not interested in the result of the window function call but the existence of any record.

select id, int_col
from alltypesagg a
where exists
  (select sum(int_col) over (partition by bool_col)
   from alltypestiny b
   where a.id = b.id);

select id, int_col from alltypestiny t
where not exists
  (select sum(int_col) over (partition by bool_col)
   from alltypesagg a where t.id = a.int_col);

Issue Links

• links to: GitHub Pull Request #2112