[SPARK-46526] Limit over certain correlated subqueries results in Nosuchelement exception

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.4.0
• Fix Version/s: 4.0.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Andrey Gubichev
• Reporter: Andrey Gubichev
• Created: 27/Dec/23 17:05
• Updated: 07/Feb/24 03:54
• Resolved: 07/Feb/24 03:54

Description

The types of queries that result in errors are:

• have LIMIT in the subquery
• predicate with correlated references does not depend on the inner query (references exclusively outer table).

For example:

SELECT COUNT(DISTINCT(t1a))
FROM t1
WHERE t1d IN (SELECT t2d
              FROM   t2
              WHERE t1a IS NOT NULL
              LIMIT 10);
 

Here, WHERE t1a IS NOT NULL can be conceptually lifted to the join that connects inner and outer query. 

Currently, this query results in an error ("no such element exception").

Issue Links

• links to: GitHub Pull Request #44514