[HIVE-15757] Allow EXISTS/NOT EXISTS correlated subquery with aggregates

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Query Planning
• Labels: pull-request-available sub-query
• Assignee: Krisztian Kasa
• Reporter: Vineet Garg
• Created: 31/Jan/17 00:56
• Updated: 17/Nov/22 08:55
• Resolved: 10/Mar/21 03:56

Description

Currently HIVE doesn't allow correlated EXISTS/NOT EXISTS subqueries which has aggregate without group by e.g.

 select *
from src b 
where exists 
  (select count(*) 
  from src a 
  where b.value = a.value  and a.key = b.key and a.value > 'val_9'
  ) ;

Such queries could be rewritten to replace EXISTS/NOT EXISTS predicate with true or false based on aggregate.

Issue Links

• contains: HIVE-16432 Improve plan for subquery with EXISTS
• relates to: HIVE-24935 Remove outdated check for correlated exists subqueries with full aggregate
• links to: GitHub Pull Request #2039