[SPARK-36656] CollapseProject should not collapse correlated scalar subqueries

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.3.0
• Fix Version/s: 3.3.0
• Component/s: SQL
• Assignee: Allison Wang
• Reporter: Allison Wang
• Created: 03/Sep/21 00:01
• Updated: 24/Sep/21 08:52
• Resolved: 24/Sep/21 08:52

Description

Currently, the optimizer rule `CollapseProject` inlines expressions generated from correlated scalar subqueries, which can create unnecessary left outer joins.

select c1, s, s * 10 from (
select c1, (select first(c2) from t2 where t1.c1 = t2.c1) s from t1)

// Before
Project [c1, s, (s * 10)]
+- Project [c1, scalar-subquery [c1] AS s]
   :  +- Aggregate [c1], [first(c2), c1] 
   :      +- LocalRelation [c1, c2]
   +- LocalRelation [c1, c2]

// After (scalar subqueries are inlined)
Project [c1, scalar-subquery [c1], (scalar-subquery [c1] * 10)]
:  +- Aggregate [c1], [first(c2), c1] 
:      +- LocalRelation [c1, c2]
:  +- Aggregate [c1], [first(c2), c1] 
:      +- LocalRelation [c1, c2]
+- LocalRelation [c1, c2]

Then this query will have two LeftOuter joins created. We should only collapse projects after correlated subqueries are rewritten as joins.

Issue Links

• links to: [Github] Pull Request #33903 (allisonwang-db)
• links to: [Github] Pull Request #33903 (allisonwang-db)