James Xu created SPARK-61694:
--------------------------------

             Summary: Allow scalar subquery merging in plans with CTEs
                 Key: SPARK-61694
                 URL: https://issues.apache.org/jira/browse/SPARK-61694
             Project: Spark
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: James Xu


Problem:
MergeSubplans currently skips every plan that contains a CTE, even when the 
plan also contains repeated uncorrelated scalar subqueries that could otherwise 
be merged. This disables scalar-subquery merging for the entire query rather 
than only around the existing CTE, potentially repeating scans and aggregations.

For example:

SELECT
  (SELECT count(*) FROM t WHERE k = 1),
  (SELECT max(id) FROM t WHERE k = 1),
  (SELECT count(*) FROM v a JOIN v b ON a.id = b.id)

The two scalar subqueries over t can normally share a merged subplan. If v is 
converted to a materialized CTE by 
spark.sql.optimizer.convertViewToMaterializedCTE.enabled, the generated WithCTE 
causes MergeSubplans to leave the whole plan unchanged, so those unrelated 
subqueries over t are no longer merged.

Root cause:
MergeSubplans creates new CTERelationDef instances and wraps the rewritten plan 
in a new WithCTE. If the input already contains a WithCTE, this can create 
nested CTE scopes whose new outer definitions refer to definitions owned by an 
inner WithCTE. ReplaceCTERefWithRepartition can then encounter references 
before their definitions are available. SPARK-56481 added the conservative 
containsPattern(CTE) guard to avoid this failure.

Proposed improvement:
Allow scalar-subquery merging when CTEs are already present by making 
definition attachment and reference resolution CTE-scope aware. New definitions 
could be merged into an existing WithCTE, or otherwise attached while 
preserving definition ownership and dependency order, instead of skipping the 
plan. The implementation must avoid cross-scope references and support existing 
CTEs wrapped by operators such as Limit.

Tests should cover:
- Repeated scalar subqueries alongside an existing materialized CTE.
- Unrelated scalar subqueries alongside a view converted to a materialized CTE.
- Existing and newly created CTE definitions with dependencies between them.
- Existing CTEs below wrappers such as Limit, including the SPARK-56481 
regression shape.




--
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