James Xu created SPARK-61957:
--------------------------------

             Summary: CTE predicate pushdown throws NoSuchElementException with 
nested materialized CTEs
                 Key: SPARK-61957
                 URL: https://issues.apache.org/jira/browse/SPARK-61957
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: James Xu


h2. Problem

{{PushdownPredicatesAndPruneColumnsForCTEDef}} throws {{NoSuchElementException: 
key not found}} when a non-inlined ({{MATERIALIZED}}) CTE lives in a nested 
{{WithCTE}} scope and a predicate referencing it is pushed into an outer CTE 
definition.

Reproducer (fails on master with {{key not found: 2}}):

{code:sql}
CREATE TABLE t2 AS SELECT id, id % 10 AS k FROM range(100);
CREATE TABLE small AS SELECT id FROM range(5);

WITH u0 AS MATERIALIZED (SELECT id FROM t2),
     u AS MATERIALIZED (SELECT id, k FROM t2 WHERE k < 6)
SELECT (WITH c AS MATERIALIZED (SELECT id FROM small)
        SELECT count(*) FROM u WHERE id > (SELECT min(id) FROM c)) x,
       (WITH c AS MATERIALIZED (SELECT id FROM small)
        SELECT max(id) FROM u WHERE id > (SELECT min(id) FROM c)) y
FROM u0 LIMIT 1;
{code}

h2. Root Cause

In {{gatherPredicatesAndAttributes}}, each definition's precedence is its index 
inside its own {{WithCTE}} ({{zipWithIndex}}), but the forward-reference guard 
compares those indices across scopes. A predicate that reads an inner 
definition (index 0 in its nested {{WithCTE}}) is therefore treated as visible 
from an outer definition (index 1 in the outer {{WithCTE}}) and pushed there, 
outside the inner definition's scope. On the next rule iteration, the planted 
{{CTERelationRef}} is visited before its definition is registered, and the 
{{cteMap}} lookup throws.

h2. Proposed Fix

Number definitions in visiting order ({{cteMap.size}} at registration time) 
instead of per-{{WithCTE}} index. A definition visited later is either a 
genuine later sibling (a real forward reference) or one in a nested scope 
(invisible to the earlier definition); both are then excluded by the existing 
guard.

{code:scala}
      case WithCTE(child, cteDefs) =>
        cteDefs.foreach { cteDef =>
          gatherPredicatesAndAttributes(cteDef.child, cteMap)
          cteMap.put(cteDef.id, (cteDef, cteMap.size, Seq.empty, 
AttributeSet.empty))
        }
        gatherPredicatesAndAttributes(child, cteMap)
{code}

This crash predates SPARK-58950; that PR (view-to-CTE conversion) only makes it 
reachable from plain view queries. Filed separately so the fix can be tracked 
and backported on its own. Analysis and reproducers by Peter Toth during the 
SPARK-58950 review.




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to