[SPARK-36115] Handle the COUNT bug for correlated IN/EXISTS subquery

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Allison Wang
• Created: 13/Jul/21 04:36
• Updated: 02/Jun/24 20:52
• Resolved: 02/Jun/24 20:52

Description

Correlated IN/EXISTS subqueries are also subject to the COUNT bug which is not handled.

create view t1(c1, c2) as values (0, 1), (1, 2)
create view t2(c1, c2) as values (0, 2), (0, 3)

-- Example 1: IN subquery
select * from t1 where c1 in (select count(*) + 1 from t2 where t1.c1 = t2.c1)
-- Correct answer: (1, 2)
+---+---+
|c1 |c2 |
+---+---+
+---+---+

-- Example 2: EXISTS subquery
select * from t1 where exists (select count(*) from t2 where t1.c1 = t2.c1)
-- Correct answer: [(0, 1), (1, 2)]
+---+---+
|c1 |c2 |
+---+---+
|0  |1  |
+---+---+