pan3793 opened a new pull request, #58661: URL: https://github.com/apache/spark/pull/58661
### What changes were proposed in this pull request? Extend the CTE syntax with the PostgreSQL-style materialization option: ```sql WITH name [ ( col, ... ) ] [ AS ] [ [ NOT ] MATERIALIZED ] ( query ) ``` - `MATERIALIZED`: the CTE is never inlined. It is evaluated once and shared by all references through the existing shuffle-reuse path (`ReplaceCTERefWithRepartition` + exchange reuse), even when it is deterministic or referenced only once. - `NOT MATERIALIZED`: the CTE is always inlined into every reference, even when it is non-deterministic and referenced more than once. - Neither keyword: unchanged. `InlineCTE` inlines a CTE that is deterministic, referenced once, or correlated to an outer query, and keeps the rest. A `MATERIALIZED` CTE cannot reference the outer query, directly or through another CTE it references, since its result would depend on the outer row. That fails analysis with the new `UNSUPPORTED_FEATURE.MATERIALIZED_CTE_WITH_OUTER_REFERENCE` condition. Where Spark already inlines CTEs during analysis (multi-insert, commands that are not `CTEInChildren`, and the legacy inline and precedence configs), the option is ignored, since it only affects planning. Implementation: - Grammar: `namedQuery` accepts `(NOT? MATERIALIZED)?` after the optional `AS`. `MATERIALIZED` is already a non-reserved keyword. - `UnresolvedWith.cteRelations` is now a `Seq[CTERelation]` (name, plan, `maxDepth`, `materialized`) instead of a 3-tuple, so the new field has a name at every use site. - `CTERelationDef` gets `materialized: Option[Boolean]`, set by `CTESubstitution` and the single-pass `Resolver`. `InlineCTE.shouldInline` honors it in the optimizer; `alwaysInline` mode (used by `CheckAnalysis` to restore the plan shape) still inlines, and the internal `forceSkipInline` flag from SPARK-58006 keeps precedence. - New `MaterializedCTECheck` rejects a `MATERIALIZED` CTE whose definition, or any CTE it references (those get inlined into it), contains an outer reference or an outer-scope subquery reference. `CheckAnalysis` runs it before inlining, and the single-pass `ResolverRunner` runs it as a single-pass-only resolution check, so both analyzers agree. - Docs: `sql-ref-syntax-qry-select-cte.md` describes the option. ### Why are the changes needed? Spark inlines every deterministic CTE, so a CTE referenced several times is planned and executed several times unless the physical subtrees happen to be identical. Users have no way to ask for a single evaluation short of caching. PostgreSQL solves this with `MATERIALIZED` / `NOT MATERIALIZED` (https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION); this adds the same syntax. The semantics match, except that a correlated `MATERIALIZED` CTE is rejected instead of being evaluated once per outer row. ### Does this PR introduce _any_ user-facing change? Yes, new SQL syntax. Queries without the option behave as before. ### How was this patch tested? - `PlanParserSuite`: parsing of the option, with and without `AS`, combined with column aliases and `RECURSIVE` options, and `materialized` still usable as a CTE name. - `InlineCTESuite`: `MATERIALIZED` keeps a single-reference deterministic CTE, `NOT MATERIALIZED` inlines a multi-reference non-deterministic CTE, `alwaysInline` still inlines. - `AnalysisErrorSuite`: the outer-reference check for a direct `OuterReference`, an outer-scope subquery reference, and an outer reference in another CTE that the `MATERIALIZED` CTE references. - `CTEInlineSuite` (AQE on and off): end-to-end plan shape (`RepartitionOperation`, `ReusedExchangeExec`) and results for both options, a `MATERIALIZED` CTE inside a subquery, and the outer-reference error with its query context, both direct and through another CTE from the same or an enclosing WITH clause. - Golden files: `cte.sql` (syntax and error cases, including the outer reference through another CTE), `explain.sql` / `explain-aqe.sql` (physical plan with a materialized CTE). ### Was this patch authored or co-authored using generative AI tooling? Generated-by: Claude Fable 5.1 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
