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]