[SPARK-35553] Improve correlated subqueries

• Type: Umbrella
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.0, 3.3.0, 3.4.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Allison Wang
• Created: 28/May/21 04:36
• Updated: 29/Sep/22 15:18

Description

This umbrella ticket is used to keep track of correlated subquery improvements and bug fixes. 

The goal is to gradually remove the restrictions in CheckAnalysis for correlated subqueries.

Sub-Tasks

• Improve the subquery decorrelation framework: Resolved
• Support outer references in Aggregate: Open
• Support subqueries with correlated non-equality predicates: Resolved
• Support set operators to be on correlation paths: Resolved
• Support ORDER BY and LIMIT to be on the correlation path: Resolved
• Allow Project to host outer references in scalar subqueries: Resolved
• Optimize OneRowRelation subqueries: Resolved
• Enable DecorrelateInnerQuery for IN/EXISTS subqueries: Resolved
• Unify the logic to handle COUNT bug for scalar and lateral subqueries: Open
• Handle the COUNT bug for correlated IN/EXISTS subquery: Resolved
• ResolveSubquery should skip unary node UnresolvedHaving: Open
• Join can become unresolved after PullupCorrelatedPredicates: Open
• CollapseProject should not collapse correlated scalar subqueries: Resolved
• Do not collapse Project with Aggregate when correlated subqueries are present in the project list: Resolved
• Disallow distinct aggregate in lateral subqueries with unsupported correlated predicates: Resolved
• Allow safe up-cast expressions in correlated equality predicates: Resolved
• Check unsupported data type when decorrelating subqueries: Resolved
• Refactor checkCorrelationsInSubquery: Resolved
• Always inline expressions in OptimizeOneRowRelationSubquery: Resolved
• Unexpected operators when rewriting scalar subqueries with non-deterministic expressions: Resolved
• Allow Generate with no required child output to host outer references: Resolved
• Should not handle the COUNT bug when the GROUP BY clause of a correlated scalar subquery is non-empty: Resolved
• Correctness COUNT bug in correlated scalar subselect with `COUNT(*) is null`: Resolved
• Improve scalar subquery logic plan when result are literal: Open
• Correlated EXISTS subqueries in join ON condition unsupported and fail with internal error: Resolved
• Loosen `correlated scalar subqueries must be aggregated` error by doing runtime check for scalar subqueries output rowcount: Open
• Scalar subquery with group-by and non-equality predicate incorrectly allowed, wrong results: Resolved
• Support scalar subquery with group-by on column equal to constant: Resolved