[SPARK-38155] Disallow distinct aggregate in lateral subqueries with unsupported correlated predicates

• 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: 09/Feb/22 05:31
• Updated: 22/Feb/22 07:40
• Resolved: 22/Feb/22 07:40

Description

Block lateral subqueries in CheckAnalysis that contain DISTINCT aggregate and correlated non-equality predicates. This can lead to incorrect results as DISTINCT will be rewritten as Aggregate during the optimization phase.

For example

CREATE VIEW t1(c1, c2) AS VALUES (0, 1)
CREATE VIEW t2(c1, c2) AS VALUES (1, 2), (2, 2)
SELECT * FROM t1 JOIN LATERAL (SELECT DISTINCT c2 FROM t2 WHERE c1 > t1.c1)

The correct results should be (0, 1, 2) but currently, it gives  be[(0, 1, 2), (0, 1, 2)].

Issue Links

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