github-actions[bot] commented on code in PR #68499:
URL: https://github.com/apache/doris/pull/68499#discussion_r4623634266


##########
fe/fe-core/src/main/java/org/apache/doris/nereids/spm/SPMPlanner.java:
##########
@@ -0,0 +1,1503 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.spm;
+
+import org.apache.doris.common.AnalysisException;
+import org.apache.doris.common.Pair;
+import org.apache.doris.common.UserException;
+import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.parser.NereidsParser;
+import org.apache.doris.nereids.spm.builder.SPMPlan2SQLBuilder;
+import org.apache.doris.nereids.spm.manager.BaselineManager;
+import org.apache.doris.nereids.spm.manager.SessionBaselineStore;
+import org.apache.doris.nereids.spm.matcher.SPMFrozenTreeReplacer;
+import org.apache.doris.nereids.spm.matcher.SPMPlaceholderReplacer;
+import org.apache.doris.nereids.spm.placeholder.SPMPlaceholderBuilder;
+import org.apache.doris.nereids.spm.placeholder.SpmConstList;
+import org.apache.doris.nereids.spm.placeholder.SpmConstVar;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.commands.Command;
+import org.apache.doris.nereids.trees.plans.commands.ExplainCommand;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCatalogRelation;
+import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.qe.SessionVariable;
+import org.apache.doris.qe.SqlModeHelper;
+import org.apache.doris.qe.VariableMgr;
+
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Creates baseline metadata and attempts query replay. Creation parameterizes 
supported literal positions in bind
+ * and plan trees with one builder, optimizes the plan, and stores either 
decompiled frozen SQL or the original SQL
+ * for fallback planning.
+ *
+ * Replay uses a structural hash, exact canonical digest and tree comparison 
to select candidates and collect
+ * replacements. It tries frozen SQL first, then the parameterized fallback 
tree when available. Output labels, row
+ * limits, ordering and metadata are checked before the caller plans the 
returned unbound tree. No usable match
+ * returns null so the caller can plan the original query.
+ */
+public class SPMPlanner {
+
+    private static final Logger LOG = LogManager.getLogger(SPMPlanner.class);
+
+    /** Id of the baseline used by the last successful rewrite (-1 when none). 
*/
+    private long usedBaselineId = -1;
+
+    /**
+     * The matched baseline OBJECT (see verifyReplayMetadata): the post-plan
+     * validation must keep the fingerprint of the SAME incarnation the match 
used, so a
+     * concurrent DROP / refresh cannot make it silently skip the check.
+     */
+    private BaselinePlan usedBaseline;
+
+    /**
+     * Returns the id of the baseline used by the last successful rewrite.
+     *
+     * Returns: the baseline id, or -1 when the last rewrite did not hit a 
baseline
+     */
+    public long getUsedBaselineId() {
+        return usedBaselineId;
+    }
+
+    /** The matched baseline object (see verifyReplayMetadata). */
+    public BaselinePlan getUsedBaseline() {
+        return usedBaseline;
+    }
+
+    // query rewrite (called from StmtExecutor / EXPLAIN).
+
+    /**
+     * Finds SESSION candidates before GLOBAL candidates and attempts replay 
using extracted marker values. Returns
+     * a usable unbound plan with caller labels, limits and SET_VAR hints, or 
null if no candidate can be used. The
+     * epoch-millisecond deadline is checked before matching each candidate; 
it is not a hard interruption of
+     * parsing, matching or substitution.
+     */
+    public LogicalPlan tryRewritePlan(LogicalPlan userPlan, long deadline) {
+        if (System.currentTimeMillis() > deadline) {
+            LOG.info("SPM tryRewritePlan: timeout before matching, degrade to 
the original plan");
+            return null;
+        }
+        ConnectContext ctx = ConnectContext.get();
+        SessionBaselineStore sessionStore = ctx == null ? null : 
ctx.getSessionBaselineStore();
+        // Fast path: with no baseline anywhere (global or session) there is 
nothing to
+        // match, so skip the whole-tree digest rendering below - it walks 
every literal
+        // of the whole query and is the most expensive part of a rewrite 
attempt.
+        if (!BaselineManager.getInstance().hasBaselines()
+                && (sessionStore == null || sessionStore.isEmpty())) {
+            return null;
+        }
+        // SELECT... INTO OUTFILE writes to a destination that lives OUTSIDE 
the plan
+        // expressions: the generic match cannot see a different path / format 
and the
+        // rewritten tree keeps the CAPTURED sink fields, so a replay would 
export to the
+        // baseline's destination. SPM refuses to rewrite such statements (the 
original
+        // query runs normally, which is always correct).
+        if (SPMPlanTreeSupport.containsFileSink(userPlan)) {
+            LOG.info("SPM tryRewritePlan: the statement writes to a file sink 
(OUTFILE); keeping"
+                    + " the original plan");
+            return null;
+        }
+        // Matching includes the caller's hints against bindSql. Replay uses 
planSql, whose SET_VAR hints
+        // can differ or be absent after capture. Remove captured settings and 
attach the caller's own ones
+        // at both return sites so planning the replacement keeps the query's 
statement-scoped assignments.
+        // Level 1/2 use the FULL-QUERY value-free digest (Plan.toSpmDigest 
renders every
+        // literal as "?", so the digest is value-independent). The digest is 
computed on a
+        // namespace-qualified COPY: "FROM t" is keyed as the CURRENT 
catalog/db's t, so a
+        // baseline captured under db1 can never match the same text executed 
under db2
+        // (the frozen planSql is fully qualified and would silently keep 
running against
+        // db1.t). Explicitly qualified references stay verbatim on both 
sides, and so do
+        // references to a CTE alias: those bind inside the query's own WITH 
clause and are
+        // therefore namespace-independent.
+        LogicalPlan matchPlan = SPMPlanTreeSupport.namespaceQualified(userPlan,
+                captureCatalogName(ctx), captureDatabaseName(ctx));
+        String queryDigest = 
SPMPlanTreeSupport.canonicalSpmDigest(matchPlan.toSpmDigest());
+        long queryHash = SPMUtils.hashOf(queryDigest);
+        // SESSION-scope baselines of the current connection are consulted 
BEFORE the
+        // global ones, so a session baseline can override a global baseline 
for this
+        // session only; each candidate list is already priority-ordered.
+        List<BaselinePlan> candidates = new ArrayList<>();
+        if (sessionStore != null) {
+            candidates.addAll(sessionStore.findCandidateBaselines(queryDigest, 
queryHash));
+        }
+        candidates.addAll(
+                
BaselineManager.getInstance().findCandidateBaselines(queryDigest, queryHash, 
deadline));
+        if (candidates.isEmpty()) {
+            return null;
+        }
+        // View guard (computed lazily on the first structural match, so the 
per-query
+        // hot path pays nothing): the replay of a matched baseline is planned 
before the
+        // normal authorization pass and resolves a view to its base tables, so
+        // authorization would check the base tables instead of the view - a 
view-only
+        // user is denied on the base tables, while a base-table user passes 
the same
+        // view query without any view check. View queries keep the original 
plan.
+        boolean viewChecked = false;
+        boolean viewReferenced = false;
+        // Schema-identity guard state (computed lazily, once per query, only 
after the
+        // first structural match): see the check inside the loop.
+        boolean schemaChecked = false;
+        String currentSchemaFingerprint = null;
+        SPMPlanTreeSupport.ReplayContracts userContracts = new 
SPMPlanTreeSupport.ReplayContracts(matchPlan);
+        for (BaselinePlan candidate : candidates) {
+            if (System.currentTimeMillis() > deadline) {
+                LOG.info("SPM tryRewritePlan: timeout before matching baseline 
{}, "
+                        + "degrade to the original plan", candidate.getId());
+                return null;
+            }
+            // A permissive capture can freeze an implicit ANY_VALUE. Strict 
callers must
+            // analyse their own grouping expressions before that permissive 
result can be used.
+            if ((candidate.getCreatorSqlMode() & 
SqlModeHelper.MODE_ONLY_FULL_GROUP_BY) == 0

Review Comment:
   [P2] Fence strict-cast settings before replaying folded conversions
   
   Capture `SELECT * REPLACE(CAST('abc' AS INT) AS k) FROM t` with 
`enable_strict_cast=false`, then run the identical query after enabling strict 
casts:
   ```text
   Capture: Project[CAST('abc' AS INT) AS k] -> Scan(t)
   Frozen:  Project[NULL AS k] -> Scan(t)
   ```
   REPLACE stays concrete, so FoldConstantRuleOnFE converts the invalid cast to 
NULL using the creator's setting. This eligibility guard only fences 
ONLY_FULL_GROUP_BY; the unchanged raw tree/digest and table fingerprint still 
match under strict casts. Replay has no CAST left to fail and returns NULL 
instead of the caller's required error. Preserve/check result-affecting capture 
settings or decline this freeze so the caller re-evaluates its conversion.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/spm/manager/BaselineManager.java:
##########
@@ -0,0 +1,10122 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.spm.manager;
+
+import org.apache.doris.catalog.Env;
+import org.apache.doris.catalog.InternalSchema;
+import org.apache.doris.common.FeConstants;
+import org.apache.doris.common.FeNameFormat;
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.analyzer.UnboundRelation;
+import org.apache.doris.nereids.spm.BaselinePlan;
+import org.apache.doris.nereids.spm.BaselineScope;
+import org.apache.doris.nereids.spm.BaselineSource;
+import org.apache.doris.nereids.spm.BaselineStatus;
+import org.apache.doris.nereids.spm.SPMPlanTreeSupport;
+import org.apache.doris.nereids.spm.SPMPlanner;
+import org.apache.doris.nereids.spm.SPMUtils;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.qe.MasterOpExecutor;
+import org.apache.doris.qe.QueryState;
+import org.apache.doris.qe.SqlModeHelper;
+import org.apache.doris.qe.VariableMgr;
+import org.apache.doris.statistics.repository.ResultRow;
+import org.apache.doris.statistics.util.StatisticsUtil;
+
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.time.Instant;
+import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
+import java.util.Set;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.concurrent.atomic.AtomicBoolean;
+import java.util.concurrent.atomic.AtomicLong;
+import java.util.concurrent.locks.ReentrantReadWriteLock;
+import java.util.stream.Collectors;
+
+/**
+ * Manages GLOBAL baselines, durable mutations, snapshot publication and 
candidate lookup. The live store uses the
+ * baseline table, an id-reservation table and a 
high-water-mark/mutation-clock table. A bounded cache holds
+ * complete hot hash buckets; a copy-on-write hash map provides the coarse 
lookup filter.
+ *
+ * GLOBAL DDL is routed to the master. Allocation reconciles durable row and 
reservation watermarks and confirms
+ * reservations before inserting rows. Collision handling, tombstones, 
mutation windows and publication fences
+ * handle delayed writes and leadership changes.
+ *
+ * writerLock serializes local mutation protocols. stateLock protects 
in-memory publication, and the row cache has
+ * its own lock. Internal-table I/O stays outside stateLock. Matching first 
checks the volatile hash map and cache;
+ * periodic refresh publishes durable changes across FEs.
+ */
+public class BaselineManager {
+
+    private static final Logger LOG = 
LogManager.getLogger(BaselineManager.class);
+
+    /** Singleton. */
+    private static final BaselineManager INSTANCE = new BaselineManager();
+
+    // internal-table persistence (__internal_schema.spm_baselines).
+
+    /** Fully qualified internal table (FeConstants.INTERNAL_DB_NAME == 
"__internal_schema"). */
+    private static final String SPM_BASELINES_TABLE =
+            FeConstants.INTERNAL_DB_NAME + "." + 
InternalSchema.SPM_BASELINES_TBL_NAME;
+
+    /** The append-only id reservation table (see 
InternalSchema.SPM_BASELINES_SEQ_TBL_NAME). */
+    private static final String SPM_BASELINES_SEQ_TABLE =
+            FeConstants.INTERNAL_DB_NAME + "." + 
InternalSchema.SPM_BASELINES_SEQ_TBL_NAME;
+
+    /** The compact id high-water-mark table (see 
InternalSchema.SPM_BASELINES_HWM_TBL_NAME). */
+    private static final String SPM_BASELINES_HWM_TABLE =
+            FeConstants.INTERNAL_DB_NAME + "." + 
InternalSchema.SPM_BASELINES_HWM_TBL_NAME;
+
+    /** Column order follows InternalSchema.SPM_BASELINES_SCHEMA (unpaged 
selection). */
+    private static final String SNAPSHOT_COLUMNS =
+            "SELECT `id`, `bind_sql`, `bind_sql_digest`,"
+            + " `bind_sql_hash`, `plan_sql`, `query_id`, `cost`, 
`query_time_ms`, `source`,"
+            + " `status`, " + storedMicros("`create_time`") + ", " + 
storedMicros("`update_time`")
+            + ", `sql_mode`, `plan_sql_mode`,"
+            + " `plan_frozen`, `schema_fingerprint`, `plan_sql_digest` FROM ";
+
+    /**
+     * Shared page ordering over every stored scalar column. Rows that tie on 
id, update time, status and SQL text
+     * can still differ in parser modes, fingerprints, costs or other replay 
metadata. Ordering all columns makes
+     * inclusive-id pagination stable; fully identical rows remain 
interchangeable.
+     */
+    private static final String SNAPSHOT_ORDER_BY = " ORDER BY `id`, 
`update_time`, `status`,"
+            + " `bind_sql_digest`, `plan_sql`, `bind_sql`, `bind_sql_hash`, 
`query_id`,"
+            + " `cost`, `query_time_ms`, `source`, `create_time`, `sql_mode`, 
`plan_sql_mode`,"
+            + " `plan_frozen`, `schema_fingerprint`, `plan_sql_digest`";
+
+    private static final String SELECT_ALL_ORDERED_SQL =
+            SNAPSHOT_COLUMNS + SPM_BASELINES_TABLE + SNAPSHOT_ORDER_BY;
+
+    /**
+     * One continuation page of a whole-table snapshot: every row with id >=
+     * ${lastId}, ordered by id and SKIPPING the first ${offset} rows of that
+     * range. The offset is what keeps an id group larger than one page 
readable: a
+     * repeated opposite-status ALTER failure leaves one more row under the id 
every time,
+     * so the group can outgrow SNAPSHOT_PAGE_SIZE rows - a jump past it would
+     * omit the rows behind the first page, possibly the newest durable 
status. The order
+     * must remain a TOTAL order within one id (see SELECT_ALL_ORDERED_SQL) or 
a
+     * tie order that differs between the two queries can make the offset skip 
a row of
+     * the interrupted group (content tie-breakers added).
+     */
+    private static final String SELECT_PAGE_SQL = SNAPSHOT_COLUMNS + 
SPM_BASELINES_TABLE
+            + " WHERE `id` >= ${lastId}" + SNAPSHOT_ORDER_BY
+            + " LIMIT ${pageSize} OFFSET ${offset}";
+
+    /**
+     * Rows per snapshot page (see readPersistedSnapshot). Bounds what ONE
+     * internal query has to return, so a growing table can no longer make the 
whole
+     * snapshot read fail against a fixed timeout.
+     */
+    private static final int SNAPSHOT_PAGE_SIZE = 2000;
+
+    /**
+     * Fence re-reads a paginated snapshot read is allowed before it fails 
closed (see
+     * readStableRawSnapshot): one DDL overlapping the loop then converges on 
the
+     * retry, while a table that never stays stable must not be published as a 
snapshot.
+     */
+    private static final int SNAPSHOT_STABILITY_ATTEMPTS = 3;
+
+    /**
+     * The persistence-layer id watermark (see reserveAllocatedId): read
+     *  before every id allocation. MAX over an aggregate is a light 
single-row query.
+     */
+    private static final String SELECT_MAX_ID_SQL = "SELECT MAX(`id`) FROM " + 
SPM_BASELINES_TABLE;
+
+    /**
+     * The compact id high-water mark: a tiny append-only table whose rows 
carry the
+     * newest allocated id AND the newest MUTATION CLOCK tick (see 
SELECT_CLOCK_SQL). The
+     * append-only HISTORY table (InternalSchema.SPM_BASELINES_SEQ_TBL_NAME) 
grows by one
+     * row per create forever, so its MAX(baseline_id) - the only unbounded 
read on the create
+     * path - was replaced: every allocation also records itself here (pruning 
the
+     * superseded rows), and a pre-upgrade cluster only pays the legacy full 
read ONCE
+     * (see readPersistedWatermark).
+     */
+    private static final String SELECT_HWM_SQL = "SELECT MAX(`watermark`) FROM 
"
+            + SPM_BASELINES_HWM_TABLE;
+
+    /**
+     * Maximum age in seconds for an open mutation-window row to participate 
in the snapshot fence. An abandoned
+     * row eventually expires so a failed FE cannot block snapshots 
indefinitely.
+     * The bound covers opening, pruning and visibility probes before the row 
statement, plus that statement's
+     * write timeout. After those probes, beginRowMutation also checks that 
enough lifetime remains before
+     * dispatching the statement. Successful completion explicitly closes the 
writer's window.
+     */
+    private static final long MUTATION_WINDOW_MAX_AGE_SECONDS = 120L;
+
+    /**
+     * Maximum allowed lead of a stored update timestamp over wall time, in 
microseconds. Status writers use this
+     * bound when incrementing an already-ahead timestamp so stored ordering 
remains within the shared fence
+     * lifetime assumptions.
+     */
+    private static final long MAX_STORED_UPDATE_LEAD_MICROS = 60_000_000L;
+
+    /**
+     * How many low bits of a mutation tick carry the window's FE identity (see
+     * mutationTickSalt): enough range that two FEs writing in the same 
millisecond almost never
+     * collide, small enough that the millisecond stays in the high bits (the 
clock's
+     * cluster-wide ordering: the remaining 43 positive bits cover roughly 279 
years of millis).
+     *
+     * The salt only makes a collision UNLIKELY; beginRowMutation therefore 
VERIFIES that the
+     * marker it appended is the only one carrying its tick (see 
MUTATION_TICK_OPEN_ATTEMPTS).
+     */
+    private static final int MUTATION_TICK_FE_BITS = 20;
+
+    /**
+     * How many fresh ticks one window open may try when the marker it 
appended shares its tick
+     * with another FE's window (see beginRowMutation): a random salt is not a 
guarantee, and two
+     * windows sharing one identity would let either writer's exact-row close 
delete the OTHER's
+     * still-open marker - turning an in-flight row statement into a quiet 
clock.
+     */
+    private static final int MUTATION_TICK_OPEN_ATTEMPTS = 3;
+
+    /** The value mask of MUTATION_TICK_FE_BITS (see nextMutationTick). */
+    private static final long MUTATION_TICK_FE_MASK = (1L << 
MUTATION_TICK_FE_BITS) - 1;
+
+    /**
+     * The bounded MUTATION CLOCK of the paginated snapshot fence (see
+     * readStableRawSnapshot): MAX(tick) plus the slot's row count / tick sum. 
Every
+     * spm_baselines mutation (create / delete / status flip) appends a clock 
row with a
+     * strictly increasing tick BEFORE its own statement, so the token changes 
whenever
+     * the durable table changes - without any table-wide aggregate on the 
read path. The
+     * count / sum stay as tie-breakers for an append whose tick collides with 
the current
+     * maximum (skewed clocks): an append raises MAX(tick) or the pair (count, 
sum), so a
+     * fence read around a paginated snapshot observes the mutation and 
retries instead of
+     * publishing a mixed state (a residual collision of two same-instant 
appends from
+     * different FEs converges on the next refresh, like the fence's 
second-precision
+     * predecessor).
+     *
+     * The fourth column is the OPEN-WINDOW marker: MAX(pending) over the rows 
written
+     * within MUTATION_WINDOW_MAX_AGE_SECONDS. Any unclosed window poisons the 
fence (that
+     * is the point - a window is only closed by its own completion write, see
+     * beginRowMutation / endRowMutation), but a window WHOSE FE DIED before 
its completion
+     * became a PERMANENT poison: pending stayed non-null forever and every 
snapshot read
+     * failed, so the store could never load again. A mutation's own WINDOW - 
the pre-write
+     * budget AND the row statement it fences, see 
MUTATION_WINDOW_MAX_AGE_SECONDS, whose
+     * bound beginRowMutation verifies before dispatching anything - fits 
inside the window
+     * bound, so a begin row older than it cannot still be mid-statement and 
is ignored.
+     */
+    private static final String SELECT_CLOCK_SQL = "SELECT MAX(`tick`), 
COUNT(*),"
+            + " SUM(`tick`), MAX(CASE WHEN `update_time` > DATE_SUB(NOW(), 
INTERVAL "
+            + MUTATION_WINDOW_MAX_AGE_SECONDS + " SECOND) THEN `pending` ELSE 
0 END)"
+            + " FROM " + SPM_BASELINES_HWM_TABLE;
+
+    /**
+     * The slot's newest watermark AND clock tick; the pre-mutation build of 
the mutation
+     * window (and the completion append) reads it to keep the recorded 
watermark untouched
+     * while advancing the clock (see beginRowMutation). One bounded keyed 
read.
+     */
+    private static final String SELECT_SLOT_STATE_SQL = "SELECT 
MAX(`watermark`),"
+            + " MAX(`tick`) FROM " + SPM_BASELINES_HWM_TABLE;
+
+    /**
+     * Whether the OPEN mutation window just appended is READABLE: the row 
carrying the very
+     * tick this process wrote in pending (see beginRowMutation). A plain 
append can
+     * report SQL OK while its row is still unreadable - and with the window 
marker only
+     * readable LOCALLY, a successor's fence read (SELECT_CLOCK_SQL, the 
MATCHES path of
+     * updateStatus) sees a QUIET clock while this process's row statement is 
already in
+     * flight: the statement then publishes AFTER the successor accepted a 
matching read
+     * and reversed it. The bounded read-back makes the statement's dispatch
+     * conditional on the window being SHARED.
+     */
+    private static final String SELECT_PENDING_TICK_SQL = "SELECT COUNT(*) 
FROM "
+            + SPM_BASELINES_HWM_TABLE
+            + " WHERE `tick` = ${tick} AND `pending` = ${tick}";
+
+    /** Append one high-water-mark / clock row (see SELECT_HWM_SQL, 
SELECT_CLOCK_SQL). */
+    private static final String INSERT_HWM_SQL = "INSERT INTO " + 
SPM_BASELINES_HWM_TABLE
+            + " (`watermark`, `update_time`, `tick`, `pending`)"
+            + " VALUES (${lastId}, NOW(), ${tick}, ${pending})";
+
+    /**
+     * Best-effort pruning of completed high-watermark rows superseded by the 
latest watermark or tick. Higher
+     * watermarks and open mutation-window rows are retained. Open rows are 
shared evidence of in-flight mutations
+     * and only their own completion may close them. The pending = 0 predicate 
also leaves legacy NULL rows
+     * untouched.
+     */
+    private static final String PRUNE_HWM_SQL = "DELETE FROM " + 
SPM_BASELINES_HWM_TABLE
+            + " WHERE `watermark` < ${lastId}"
+            + " AND `pending` = 0";
+
+    /** The same-watermark half of PRUNE_HWM_SQL (rows superseded by the newer 
tick). */
+    private static final String PRUNE_HWM_SAME_WATERMARK_SQL = "DELETE FROM "
+            + SPM_BASELINES_HWM_TABLE
+            + " WHERE `watermark` = ${lastId} AND `tick` < ${tick}"
+            + " AND `pending` = 0";
+
+    /**
+     * Deletes the completed mutation's own open-window row using both its 
tick and pending token. General
+     * watermark pruning keeps open rows, so only this writer's completion 
closes this window. Tick generation uses
+     * a random FE salt; beginRowMutation checks for readable collisions 
before dispatching the row mutation.
+     */
+    private static final String PRUNE_HWM_OPEN_SQL = "DELETE FROM " + 
SPM_BASELINES_HWM_TABLE
+            + " WHERE `tick` = ${windowTick} AND `pending` = ${windowTick}";
+
+    /**
+     * The scoped CONFIRMATION of SELECT_HWM_SQL (see readCompactIdWatermark): 
the
+     * highest reservation VISIBLE beyond the recorded mark. The sequence 
table's key
+     * starts with (id, baseline_id), so the read scans only the rows past the 
mark - a
+     * healthy cluster has none, and a stale record never hides a reservation 
whose
+     * HWM write was lost or is still unreadable.
+     */
+    private static final String SELECT_SEQ_TAIL_SQL = "SELECT 
MAX(`baseline_id`) FROM "
+            + SPM_BASELINES_SEQ_TABLE + " WHERE `id` = 1 AND `baseline_id` > 
${floor}";
+
+    /**
+     * The id high-water mark that OUTLIVES the rows (see
+     * InternalSchema.SPM_BASELINES_SEQ_TBL_NAME): MAX(baseline_id) over the 
append-only
+     * reservation rows. Kept as the ONE-TIME legacy fallback of
+     * readPersistedWatermark (a cluster created before the compact high-water
+     * mark table exists); the per-create path reads the bounded
+     * SELECT_HWM_SQL instead. The baselines table's own MAX(id) falls back to 
a
+     * lower value as soon as its highest row is DROPped, and an id reused for 
a DIFFERENT
+     * baseline would let a delayed DROP BASELINE PLAN IF EXISTS N retry 
delete the
+     * new baseline.
+     */
+    private static final String SELECT_SEQ_ID_SQL = "SELECT MAX(`baseline_id`) 
FROM "
+            + SPM_BASELINES_SEQ_TABLE;
+
+    /** Appends one reservation row (the id just allocated). Append-only: MAX 
never falls. */
+    private static final String INSERT_SEQ_ID_SQL = "INSERT INTO " + 
SPM_BASELINES_SEQ_TABLE
+            + " (`id`, `baseline_id`, `bind_sql_digest`, `plan_sql_hash`, 
`reserve_time`,"
+            + " `unconfirmed`, `dropped`)"
+            + " VALUES (1, ${lastId}, '${bindSqlDigest}', ${planSqlHash}, 
'${reserveTime}',"
+            + " ${unconfirmed}, 0)";
+
+    /**
+     * Appends a DROP TOMBSTONE: the identity of a baseline this FE just
+     * removed, with dropped = 1. A demoted master's in-flight status INSERT 
can
+     * commit AFTER the DROP deleted the row - its conditional precondition 
ran against
+     * the pre-DROP snapshot, and Doris cannot re-check it at durable commit - 
and the
+     * revived row would make the dropped baseline ACTIVE again on every 
loader. The
+     * tombstone is APPEND-ONLY and survives that commit: a load that sees a 
baseline row
+     * matching a tombstone's (id, bind_sql_digest, plan_sql_hash) treats it 
as deleted
+     * (and repairs it away). Ids are never reused (the sequence watermark), 
so a matching
+     * tombstone always describes this very incarnation.
+     */
+    private static final String INSERT_SEQ_DROPPED_SQL = "INSERT INTO " + 
SPM_BASELINES_SEQ_TABLE
+            + " (`id`, `baseline_id`, `bind_sql_digest`, `plan_sql_hash`, 
`reserve_time`,"
+            + " `unconfirmed`, `dropped`)"
+            + " VALUES (${tombstoneId}, ${lastId}, '${bindSqlDigest}', 
${planSqlHash},"
+            + " '${reserveTime}', 0, 1)";
+
+    /**
+     * The id key of one DROP TOMBSTONE (see INSERT_SEQ_DROPPED_SQL): a 
dedicated NEGATIVE
+     * id space offset by the baseline id, disjoint from every other row of 
the sequence
+     * table (the historical reservations and the watermark carry the constant 
id = 1, the
+     * compact identity rows carry COMPACT_SEQ_ID_BASE + a per-identity hash).
+     *
+     * Keying the tombstones by the BASELINE id is what BOUNDS the tombstone 
read: the
+     * duplicate key is id, so id IN (...) seeks exactly the requested 
identities
+     * instead of scanning the whole append-only history once per id chunk. 
The previous
+     * dropped = 1 AND baseline_id IN (...) form constrained neither key 
column, so with a
+     * large CREATE / DROP history every chunk re-read the entire table and 
eventually
+     * exhausted the fixed query timeout - and BOTH the refresh and the 
confirmed reads go
+     * through it, so GLOBAL SHOW / management failed with them.
+     */
+    private static final long TOMBSTONE_SEQ_ID_BASE = -1L;
+
+    /**
+     * Reads the DROP TOMBSTONES of the GIVEN ids (see INSERT_SEQ_DROPPED_SQL,
+     * the append-only sequence table retains every dropped = 1 row
+     * forever, so an unrestricted read built a HashSet of the FULL historical 
drop set on
+     * every load / refresh - with a small active set and heavy CREATE / DROP 
churn the
+     * read grew without bound and, once it timed out, follower caches stopped
+     * incorporating later GLOBAL changes. The read is scoped to the ids the 
caller is
+     * actually filtering (the snapshot / point-read ids), chunked to keep 
each statement
+     * bounded, and KEYED by the tombstone id space (see 
TOMBSTONE_SEQ_ID_BASE): the
+     * id IN (...) seek is what keeps each chunk's cost independent of the 
history's
+     * length.
+     */
+    private static final String SELECT_SEQ_DROPPED_SQL = "SELECT 
`baseline_id`, `bind_sql_digest`,"
+            + " `plan_sql_hash` FROM " + SPM_BASELINES_SEQ_TABLE
+            + " WHERE `id` IN (${tombstoneIds}) AND `baseline_id` IN (${ids})"
+            + " AND `dropped` = 1";
+
+    /** How many ids one scoped tombstone read carries (see 
SELECT_SEQ_DROPPED_SQL). */
+    private static final int DROPPED_MARKER_ID_CHUNK = 256;
+
+    /**
+     * The LATEST identity-carrying row of one baseline: the durable half of 
the
+     * unresolved-create fence (see
+     * resolveDurablePendingCreate). BOTH kinds of rows fence while their 
write's
+     * outcome is unresolved:
+     *   unconfirmed = 1: the marker of a create whose INSERT outcome was
+     *       AMBIGUOUS;
+     *   a PLAIN reservation (unconfirmed = 0): the row every create appends
+     *       BEFORE its INSERT. Its separate ambiguous marker can fail / lag 
(that write
+     *       is best effort), and a cross-FE retry that sees neither the 
baseline row nor
+     *       the marker then allocated a SECOND id whose committed row later 
published a
+     *       duplicate; the pre-INSERT reservation is the one record that 
always exists,
+     *       so it fences too - but only while its row is not readable and its 
age is
+     *       inside the fence bound.
+     *   dropped = 1: a tombstone (a completed DROP or a condemned abandoned
+     *       write) RESOLVED the identity - no fence, the key may be created 
again.
+     * The NEWEST IDENTITY wins (highest baseline_id): ids name the successive
+     * incarnations of one key. Timestamp ordering alone cannot distinguish 
all interleavings: a DROP of
+     * id N followed by a re-create as N+1 within the same stored instant used 
to sort
+     * N's dropped = 1 tombstone BEFORE N+1's reservation, so 
resolveDurablePendingCreate
+     * read the key as resolved, skipped the pending fence of N+1's 
committed-but-
+     * unreadable INSERT, and allocated N+2 (both ENABLED rows could then 
publish).
+     * Within one identity the latest state wins: a tombstone (appended after 
the
+     * reservation) means resolved; the equal-timestamp order is tombstone, 
then marker,
+     * then the plain row.
+     */
+    private static final String SELECT_PENDING_SEQ_SQL = "SELECT 
`baseline_id`, "
+            + storedMicros("`reserve_time`") + ", `unconfirmed`, `dropped` 
FROM "
+            + SPM_BASELINES_SEQ_TABLE + " WHERE `bind_sql_digest` = 
'${bindSqlDigest}'"
+            + " AND `plan_sql_hash` = ${planSqlHash}"
+            + " ORDER BY `baseline_id` DESC, `reserve_time` DESC, `dropped` 
DESC,"
+            + " `unconfirmed` ASC LIMIT 1";
+
+    /**
+     * The base of the synthetic id every COMPACT identity row carries (see
+     * INSERT_COMPACT_SEQ_SQL). The sequence table's id column is its 
DUPLICATE key and
+     * every historical row carries the constant 1: the identity read filtered 
on
+     * bind_sql_digest / plan_sql_hash alone, which no sort order can bound - 
the table is
+     * append-only and grows with every create / drop forever, so one identity 
read grew
+     * into a full scan of the whole history. Each state change ALSO appends a 
compact
+     * copy under this stable per-identity id, so the read seeks the key 
prefix and sees
+     * only that identity's few surviving rows (the base keeps them clear of 
the
+     * historical id = 1 rows and of the compact HWM rows of the OTHER tables).
+     */
+    private static final long COMPACT_SEQ_ID_BASE = 2L;
+
+    /** Appends the compact copy of one identity state (see 
COMPACT_SEQ_ID_BASE). */
+    private static final String INSERT_COMPACT_SEQ_SQL = "INSERT INTO " + 
SPM_BASELINES_SEQ_TABLE
+            + " (`id`, `baseline_id`, `bind_sql_digest`, `plan_sql_hash`, 
`reserve_time`,"
+            + " `unconfirmed`, `dropped`)"
+            + " VALUES (${compactId}, ${lastId}, '${bindSqlDigest}', 
${planSqlHash},"
+            + " '${reserveTime}', ${unconfirmed}, ${dropped})";
+
+    /**
+     * Best-effort prune of the compact rows the just-written one supersedes
+     * (see COMPACT_SEQ_ID_BASE): without it one identity's slot grows with 
every state
+     * change. Correctness never depends on it - the read takes the MAX within 
the slot -
+     * so failures are swallowed like PRUNE_HWM_SQL's. A compact id is a 
64-bit HASH, so
+     * two identities may share one slot: their rows carry their own digest / 
hash
+     * predicates and the read falls back to the history when the slot's 
newest row is
+     * another identity's.
+     */
+    private static final String PRUNE_COMPACT_SEQ_SQL = "DELETE FROM "
+            + SPM_BASELINES_SEQ_TABLE + " WHERE `id` = ${compactId} AND 
`baseline_id` < ${lastId}";

Review Comment:
   [P2] Bound compact state within one baseline allocation
   
   Repeated successful CREATE GLOBAL BASELINE PLAN of an unchanged baseline 
reaches `resolveDurablePendingCreate`, adopts its readable plain reservation 
and still calls `retireSeqPendingMarker`. The production retirement deletes 
only unconfirmed rows, then appends a fresh plain compact row for the same 
baseline_id. This prune removes only lower ids, so every prior same-id row 
survives in the DUPLICATE KEY table. `SELECT_COMPACT_SEQ_BATCH_SQL` returns all 
of them before Java retains the newest; one idempotently recreated baseline 
therefore accumulates unbounded storage/read/sort work in refresh, SHOW and 
cold-match identity reads. Skip unchanged resolution appends or safely prune 
superseded same-id states while preserving the winning fences.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/spm/ShowBaselinePlansCommand.java:
##########
@@ -0,0 +1,366 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.plans.commands.spm;
+
+import org.apache.doris.analysis.StmtType;
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.Env;
+import org.apache.doris.catalog.PrimitiveType;
+import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.common.AnalysisException;
+import org.apache.doris.common.DdlException;
+import org.apache.doris.common.ErrorCode;
+import org.apache.doris.common.ErrorReport;
+import org.apache.doris.mysql.privilege.PrivPredicate;
+import org.apache.doris.nereids.parser.Origin;
+import org.apache.doris.nereids.spm.BaselinePlan;
+import org.apache.doris.nereids.spm.manager.BaselineManager;
+import org.apache.doris.nereids.trees.plans.PlanType;
+import org.apache.doris.nereids.trees.plans.commands.ShowCommand;
+import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
+import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.qe.ShowResultSet;
+import org.apache.doris.qe.ShowResultSetMetaData;
+import org.apache.doris.qe.StmtExecutor;
+
+import com.google.common.annotations.VisibleForTesting;
+import com.google.common.collect.Lists;
+
+import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Optional;
+
+/**
+ * SHOW BASELINE PLANS command (design doc 6.8 / 6.9 / 6.17).
+ *
+ * Syntax:
+ *
+ *   SHOW BASELINE PLANS [LIKE 'pattern' | WHERE expression]
+ *
+ * Execution logic: merge the SESSION-scope baselines of the current connection
+ * (SessionBaselineStore) with the GLOBAL ones from
+ * BaselineManager.getInstance().getAllBaselines(), order by id and render 
them as a
+ * ShowResultSet. The first 12 columns mirror the spm_baselines internal table 
(design doc
+ * 6.14.1; `query_id` is the audit_log correlation id of the statement that 
produced the
+ * baseline); the trailing `scope` column is synthesized (GLOBAL / SESSION) 
because the
+ * scope is implied by the owning store / the id range (BaselineScope.ofId) 
and is not
+ * persisted in the table.
+ *
+ * Phase 1 filtering: a non-null pattern (from LIKE or a simplified
+ * `WHERE source = 'x'` / `WHERE status = 'x'` clause) is matched in Java 
against the
+ * source / status / scope columns (exact) and against bindSql / planSql 
(substring).
+ */
+public class ShowBaselinePlansCommand extends ShowCommand {
+
+    private static final DateTimeFormatter DATETIME_FORMAT =
+            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
+
+    /** SHOW result set columns: the first 12 columns are one-to-one with the
+     * spm_baselines internal table (see InternalSchema.SPM_BASELINES_SCHEMA); 
the
+     * trailing `scope` column is synthesized (GLOBAL / SESSION, also 
derivable from the
+     * id range - BaselineScope.ofId). */
+    private static final ShowResultSetMetaData META_DATA = 
ShowResultSetMetaData.builder()
+            .addColumn(new Column("id", 
ScalarType.createType(PrimitiveType.BIGINT)))
+            .addColumn(new Column("bind_sql", 
ScalarType.createType(PrimitiveType.STRING)))
+            .addColumn(new Column("bind_sql_digest", 
ScalarType.createType(PrimitiveType.STRING)))
+            .addColumn(new Column("bind_sql_hash", 
ScalarType.createType(PrimitiveType.BIGINT)))
+            .addColumn(new Column("plan_sql", 
ScalarType.createType(PrimitiveType.STRING)))
+            .addColumn(new Column("query_id", ScalarType.createVarchar(64)))
+            .addColumn(new Column("cost", 
ScalarType.createType(PrimitiveType.DOUBLE)))
+            .addColumn(new Column("query_time_ms", 
ScalarType.createType(PrimitiveType.BIGINT)))
+            .addColumn(new Column("source", ScalarType.createVarchar(16)))
+            .addColumn(new Column("status", ScalarType.createVarchar(16)))
+            .addColumn(new Column("create_time", 
ScalarType.createType(PrimitiveType.DATETIME)))
+            .addColumn(new Column("update_time", 
ScalarType.createType(PrimitiveType.DATETIME)))
+            .addColumn(new Column("scope", ScalarType.createVarchar(16)))
+            .build();
+
+    /** Optional LIKE / WHERE filter value; null shows everything. */
+    private final String pattern;
+
+    /** Exact-match filter column (id / bind_sql_digest / bind_sql / plan_sql /
+     * source / status / scope) extracted from a WHERE col = val clause; null 
when
+     * the filter is only a LIKE / substring pattern. */
+    private final String filterColumn;
+
+    /** Exact-match filter value for filterColumn; null when unused. */
+    private final String filterValue;
+
+    public ShowBaselinePlansCommand(String pattern) {
+        this(pattern, null, null);
+    }
+
+    public ShowBaselinePlansCommand(String pattern, String filterColumn, 
String filterValue) {
+        super(PlanType.SHOW_BASELINE_PLANS_COMMAND);
+        this.pattern = pattern;
+        this.filterColumn = filterColumn;
+        this.filterValue = filterValue;
+    }
+
+    public String getPattern() {
+        return pattern;
+    }
+
+    /** Builds the LIKE matcher of one operand; null means "LIKE omitted". */
+    static java.util.regex.Pattern buildLikeMatcher(String pattern)
+            throws AnalysisException {
+        if (pattern == null) {
+            return null;
+        }
+        // SHOW BASELINE PLANS searches STORED SQL TEXT. The shared 
MySQL-pattern helper
+        // (PatternMatcher) rejects the literal characters every statement 
contains
+        // ('*', '=', '('), so LIKE '%SELECT * FROM%' failed with an analysis 
error, and
+        // its '%' compiles to a regex '.' that does not span the NEWLINES the 
stored SQL
+        // is printed with. Escape every regex metacharacter and apply the 
MySQL
+        // wildcards explicitly: '%' -> any characters (DOTALL), '_' -> one 
character,
+        // '\' escapes the NEXT wildcard character ('\_' / '\%' are LITERALS, 
exactly
+        // like the regular LIKE matcher), whole-value, case-insensitive.
+        StringBuilder regex = new StringBuilder();
+        for (int i = 0; i < pattern.length(); i++) {
+            char c = pattern.charAt(i);
+            if (c == '\\' && i + 1 < pattern.length()
+                    && (pattern.charAt(i + 1) == '%' || pattern.charAt(i + 1) 
== '_')) {
+                // the SQL literal parser preserves the backslash; consuming 
it together
+                // with the wildcard keeps a search for '%my\_table%' matching 
the stored
+                // my_table (the old loop emitted the backslash literally and 
turned the
+                // '_' into a wildcard, so the row was missed while unrelated 
text with a
+                // backslash could match)
+                char escaped = pattern.charAt(++i);
+                regex.append('\\').append(escaped);
+            } else if (c == '%') {
+                regex.append(".*");
+            } else if (c == '_') {
+                regex.append('.');
+            } else {
+                if ("\\^$.|?*+()[]{}".indexOf(c) >= 0) {
+                    regex.append('\\');
+                }
+                regex.append(c);
+            }
+        }
+        return java.util.regex.Pattern.compile(regex.toString(),
+                java.util.regex.Pattern.DOTALL | 
java.util.regex.Pattern.CASE_INSENSITIVE);
+    }
+
+    @Override
+    public Optional<Origin> getOrigin() {
+        return super.getOrigin();
+    }
+
+    @Override
+    public ShowResultSetMetaData getMetaData() {
+        return META_DATA;
+    }
+
+    @Override
+    public ShowResultSet doRun(ConnectContext ctx, StmtExecutor executor) 
throws Exception {
+        // SPM management commands require ADMIN
+        if (!Env.getCurrentEnv().getAccessManager().checkGlobalPriv(
+                ConnectContext.get(), PrivPredicate.ADMIN)) {
+            
ErrorReport.reportAnalysisException(ErrorCode.ERR_SPECIFIC_ACCESS_DENIED_ERROR, 
"ADMIN");
+        }
+
+        // SESSION-scope baselines of this connection first, then the GLOBAL 
ones; ids are
+        // self-describing by range (BaselineScope.ofId), so sorting by id 
keeps the
+        // deterministic order and puts the SESSION rows (>= 2^62) after the 
GLOBAL ones
+        List<BaselinePlan> all = Lists.newArrayList();
+        all.addAll(ctx.getSessionBaselineStore().getAllBaselines());
+        // GLOBAL rows must be authoritative: getAllBaselines() only STARTS the
+        // asynchronous load and returns the current cache, so right after 
startup / a
+        // promotion (which clears it) SHOW reported ZERO rows although 
durable baselines
+        // existed, and - with the cache merely "loaded" - a GLOBAL DDL 
completed on the
+        // MASTER stayed invisible (a completed DROP stayed listed) until this 
follower's
+        // next refresh daemon cycle. confirmGlobalRowsForShow() does the 
module's
+        // confirmed durable read instead: master sync, generation fence, 
fresh snapshot
+        // apply, retryable error when the rows cannot be read (never a stale 
table).
+        //
+        // The WHERE scope = 'SESSION' form is applied BEFORE the confirmed 
read: those
+        // rows live only in this connection's in-memory store, so the result 
can never
+        // contain a GLOBAL row - a master handoff (whose confirmed read is 
retryable)
+        // must not fail a SHOW whose answer does not depend on the master at 
all.
+        if (!isSessionScopeOnlyFilter()) {
+            BaselineManager.getInstance().confirmGlobalRowsForShow();

Review Comment:
   [P2] Return the confirmed rows instead of scanning the store twice
   
   On a loaded durable follower, `confirmGlobalRowsForShow()` syncs the journal 
and reads a complete `readPersistedSnapshot()` before returning. The following 
`getAllBaselines()` unconditionally reads another complete snapshot because 
production hash reads are available; it does not consume the first row set. 
Each pass walks every 2,000-row page plus clock/tombstone/identity probes under 
writerLock. Every such SHOW thus pays two complete distributed scans and delays 
forwarded-DDL reconciliation for both passes as the store grows. Return and 
filter the first confirmed snapshot's rows through one API.



##########
regression-test/suites/spm/test_spm_r65_review.groovy:
##########
@@ -0,0 +1,334 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_spm_r65_review", "spm") {
+
+    // Round-65 review fixes with a SQL surface. Every block captures a 
baseline and
+    // replays it with the SPM fallback switch pinned CLOSED, so a broken 
frozen text or a
+    // dropped rewrite surfaces as an ERROR instead of silently re-running the 
original
+    // query.
+    //
+    //  - #1  the checker's internal LAG frame must not be serialized: the 
frozen text is
+    //        re-planned on replay and rejects an explicit LAG frame; the IN 
list also
+    //        proves the retained SpmConstList values are replannable (the old 
probe
+    //        skipped validation and the replay failed with "WindowFrame for 
LAG() must
+    //        be null").
+    //  - #3  COUNT(DISTINCT a,b) keeps the tuple identity: the frozen SQL 
must not add a
+    //        second DISTINCT over the already-deduplicated stage (the replay 
returned 1
+    //        instead of 2 for two tuples sharing one component value).
+    //  - #5  the LATERAL VIEW generator name collides with the source column 
`X`
+    //        case-insensitively: the frozen projection must not export two 
ambiguous
+    //        bare references.
+    //  - #6  GROUPING_ID is registered from its virtual expression: a 
congruent grouping
+    //        key (a_b) must not make the frozen SQL return the grand-total 
value.
+    //  - #7  SET_VAR hints carry in analyzer order (CTE definitions before 
the consuming
+    //        query): the replay must end in the caller's effective time zone.
+    //  - #8  every NULL-aware hash key survives decompilation (the frozen NOT 
IN must not
+    //        drop a secondary key's NULL semantics).
+    //  - #9  a nonmovable function above a child LIMIT stays in place (the 
replay must
+    //        not evaluate the discarded row).
+    //  - #12 a TVF-only baseline (EMPTY schema fingerprint) can be DROPPED: 
the
+    //        IFNULL(...)='' predicate used to be rejected by 
DeleteFromCommand.
+    //  - #15 inherited CTEs expand in their defining scope (the replay header 
must not
+    //        come from the nested shadowing definition).
+    //  - #16 a GROUPING SETS output list is aligned like an aggregate's (the 
replay must
+    //        expose the caller's headers with the caller's values).
+    //  - #18 keep-all CTE consumers below a UNION are recorded (a supported
+    //        materialized-CTE shape must still hit and replay).
+
+    sql """set enable_spm_rewrite = true"""
+    sql """set enable_spm_fallback = false"""
+
+    // ==================== setup: tables (drop before use, keep after) 
====================
+    sql """DROP TABLE IF EXISTS spm_r65b_t"""
+    sql """
+        CREATE TABLE spm_r65b_t (k INT, v INT)
+        DUPLICATE KEY(k)
+        DISTRIBUTED BY HASH(k) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_t VALUES (1, 10), (2, 20), (3, 30)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_d"""
+    sql """
+        CREATE TABLE spm_r65b_d (a INT, b INT)
+        DUPLICATE KEY(a)
+        DISTRIBUTED BY HASH(a) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_d VALUES (1, 7), (2, 7)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_lv"""
+    sql """
+        CREATE TABLE spm_r65b_lv (X INT, arr ARRAY<INT>, k INT)
+        DUPLICATE KEY(X)
+        DISTRIBUTED BY HASH(X) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_lv VALUES (5, [1, 2], 1)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_g"""
+    sql """
+        CREATE TABLE spm_r65b_g (a INT, b INT, a_b INT)
+        DUPLICATE KEY(a)
+        DISTRIBUTED BY HASH(a) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_g VALUES (1, 2, 3)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_tz"""
+    sql """
+        CREATE TABLE spm_r65b_tz (k BIGINT)
+        DUPLICATE KEY(k)
+        DISTRIBUTED BY HASH(k) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_tz VALUES (0)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_l"""
+    sql """
+        CREATE TABLE spm_r65b_l (a INT, b INT, z INT)
+        DUPLICATE KEY(a)
+        DISTRIBUTED BY HASH(a) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_l VALUES (1, 2, 1)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_r"""
+    sql """
+        CREATE TABLE spm_r65b_r (c INT, d INT, f INT)
+        DUPLICATE KEY(c)
+        DISTRIBUTED BY HASH(c) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_r VALUES (NULL, NULL, 2)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_bt"""
+    sql """
+        CREATE TABLE spm_r65b_bt (k INT, v INT)
+        DUPLICATE KEY(k)
+        DISTRIBUTED BY HASH(k) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_bt VALUES (1, 1), (2, -1)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_u"""
+    sql """
+        CREATE TABLE spm_r65b_u (q INT)
+        DUPLICATE KEY(q)
+        DISTRIBUTED BY HASH(q) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_u VALUES (3), (4)"""
+
+    sql """DROP TABLE IF EXISTS spm_r65b_c"""
+    sql """
+        CREATE TABLE spm_r65b_c (k INT, v INT)
+        DUPLICATE KEY(k)
+        DISTRIBUTED BY HASH(k) BUCKETS 1
+        PROPERTIES("replication_num" = "1")
+    """
+    sql """INSERT INTO spm_r65b_c VALUES (1, 10), (2, 20), (3, 30)"""
+
+    // Global baselines are cluster-wide state and other SPM suites may run 
their own in
+    // parallel: every SHOW here is scoped to THIS suite's tables.
+    def ourTables = ["spm_r65b_t", "spm_r65b_d", "spm_r65b_lv", "spm_r65b_g", 
"spm_r65b_tz",
+                     "spm_r65b_bt", "spm_r65b_c"]
+    def ownBaselines = {
+        sql("""SHOW BASELINE PLANS""").findAll { row ->
+            ourTables.any { table -> row[1].toString().contains(table) }
+        }
+    }
+    def dropOwnBaselines = {
+        ownBaselines().each { row ->
+            sql """DROP BASELINE PLAN ${row[0]}"""
+        }
+    }
+    def explainOf = { String stmt ->
+        sql("""EXPLAIN ${stmt}""").toString()
+    }
+    def createGlobal = { String bind, String plan ->
+        List<List<Object>> created = sql("""CREATE GLOBAL BASELINE PLAN 
'${bind}' WITH '${plan}'""")
+        assertEquals(1, created.size(), "CREATE should return one row, got: 
${created}")
+        return Long.parseLong(created[0][0].toString())
+    }
+
+    dropOwnBaselines()
+
+    // ==================== #1: LAG() with an IN-list placeholder 
====================
+    String lagBind = "SELECT lag(k) OVER (ORDER BY k) AS p FROM spm_r65b_t 
WHERE k IN (1, 2)"
+    long lagId = createGlobal(lagBind, lagBind)
+    assertTrue(explainOf(lagBind).contains("SPM baseline hit: id=${lagId}"),
+            "the LAG query must hit its baseline: " + explainOf(lagBind))
+    order_qt_r65_lag_identical """
+        SELECT lag(k) OVER (ORDER BY k) AS p FROM spm_r65b_t WHERE k IN (1, 2) 
ORDER BY p

Review Comment:
   [P2] Execute the matched query in the LAG result checks
   
   `lagBind`/`lagVariant` and their hit-EXPLAIN assertions omit statement-level 
ORDER BY, but these output queries add `ORDER BY p`. LogicalSort changes the 
full-query digest/hash, so the result checks miss this baseline and execute 
ordinary planning, even with rewrite enabled and fallback disabled. A valid but 
wrong-valued frozen LAG replay can therefore pass both output checks; only 
EXPLAIN exercises the hit. Pass the exact matched strings to `order_qt` (which 
already sorts returned rows outside SQL), or capture/assert the sorted shape 
too. The lateral/grouping result checks below repeat the mismatch. This is 
separate from the previously repaired Round23 JDBC-label check.



##########
fe/fe-core/src/main/java/org/apache/doris/nereids/spm/builder/SPMPlan2SQLBuilder.java:
##########
@@ -0,0 +1,4674 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.spm.builder;
+
+import org.apache.doris.analysis.TableScanParams;
+import org.apache.doris.analysis.TableSnapshot;
+import org.apache.doris.catalog.Column;
+import org.apache.doris.catalog.OlapTable;
+import org.apache.doris.catalog.Partition;
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.parser.NereidsParser;
+import org.apache.doris.nereids.properties.DistributionSpec;
+import org.apache.doris.nereids.properties.DistributionSpecReplicated;
+import org.apache.doris.nereids.trees.TableSample;
+import org.apache.doris.nereids.trees.expressions.AggregateExpression;
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.CTEId;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.IsNull;
+import org.apache.doris.nereids.trees.expressions.MarkJoinSlotReference;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.WindowExpression;
+import org.apache.doris.nereids.trees.expressions.functions.Function;
+import 
org.apache.doris.nereids.trees.expressions.functions.NoneMovableFunction;
+import 
org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.agg.GroupConcat;
+import 
org.apache.doris.nereids.trees.expressions.functions.agg.MultiDistinctGroupConcat;
+import org.apache.doris.nereids.trees.expressions.functions.generator.Unnest;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.GroupingId;
+import 
org.apache.doris.nereids.trees.expressions.functions.scalar.GroupingScalarFunction;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.If;
+import 
org.apache.doris.nereids.trees.expressions.functions.table.TableValuedFunction;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.trees.plans.AggPhase;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.LimitPhase;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.algebra.SetOperation.Qualifier;
+import org.apache.doris.nereids.trees.plans.physical.AbstractPhysicalJoin;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalAssertNumRows;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEAnchor;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEConsumer;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCTEProducer;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalCatalogRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalDistribute;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalEmptyRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalExcept;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalFileScan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalFilter;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalGenerate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalIntersect;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalLazyMaterialize;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalLazyMaterializeFileScan;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalLazyMaterializeOlapScan;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalLazyMaterializeTVFScan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalLimit;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalNestedLoopJoin;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalOlapScan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalOneRowRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalPartitionTopN;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalProject;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalQuickSort;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalRecursiveUnion;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalRecursiveUnionAnchor;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalRecursiveUnionProducer;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalRepeat;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalResultSink;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalSetOperation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalSink;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalStorageLayerAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalTVFRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalTopN;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalUnion;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalWindow;
+import 
org.apache.doris.nereids.trees.plans.physical.PhysicalWorkTableReference;
+import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
+
+import com.google.common.collect.Lists;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.TreeMap;
+import java.util.TreeSet;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import java.util.stream.Collectors;
+
+/**
+ * Decompiles supported physical plans into SQL that can be parsed and planned 
for baseline replay. SQL blocks and
+ * distribution hints retain selected plan structure; replay still runs 
ordinary analysis and optimization.
+ * Unsupported operators or expression forms raise 
UnsupportedOperationException so baseline creation can retain
+ * the original plan SQL.
+ *
+ * The renderer tracks ExprId references, exported column names, CTE 
definitions and live output columns.
+ * Execution-only stages are folded where supported, while wrappers preserve 
semantic clause boundaries and
+ * assertions.
+ */
+public class SPMPlan2SQLBuilder extends PlanVisitor<SQLRelation, Void> {
+
+    private static final Logger LOG = 
LogManager.getLogger(SPMPlan2SQLBuilder.class);
+
+    /** Generated output names ("c_" + position) produced by re-export labels 
and dedupe renames. */
+    private static final Pattern GENERATED_NAME_PATTERN = 
Pattern.compile("\\bc_\\d+\\b");
+
+    /** JOIN distribution HINT prefix constants. */
+    private static final String HINT_JOIN_BROADCAST = "BROADCAST";
+    private static final String HINT_JOIN_SHUFFLE = "SHUFFLE";
+
+    /**
+     * Re-export labels a hoist step created one level below ("X AS c_N" 
items): maps the
+     * generated label back to the ExprId of the item it re-exports, so a 
later hoist step
+     * can re-export the SAME label through one more projection level (tpcds 
q78: the
+     * ORDER BY hoisted out of the 16-item projection references 
c_14/c_15/c_16, and the
+     * 11-item projection above it must therefore re-export them).
+     */
+    private final Map<String, ExprId> reExportedLabels = new HashMap<>();
+
+    /** Expression printer (carries the columnNames mapping of SQLRelation). */
+    private final SPMExprSqlBuilder exprSqlBuilder = new SPMExprSqlBuilder();
+
+    /**
+     * Per-decompile alias sequence for LATERAL VIEW clauses whose output slot 
has no
+     * user qualifier (reset in toSQL).
+     */
+    private int lateralViewSeq = 0;
+
+    /**
+     * CTE body relations registered by the producer and referenced through 
generated WITH aliases by consumers.
+     */
+    private final Map<CTEId, SQLRelation> cteBodies = new HashMap<>();
+
+    /** CTEId -> the generated alias (t_N) that references the WITH 
definition. */
+    private final Map<CTEId, String> cteAliases = new HashMap<>();
+
+    /**
+     * The WITH entries (alias AS (body)) in producer visit order - the 
definition
+     * order of the emitted WITH clause. They are attached to the ROOT 
relation in
+     * toSQL: every consumer lies inside the statement, so the definitions are
+     * visible everywhere regardless of how deeply the CTE anchors are nested 
in the
+     * physical tree (attaching at each anchor instead would place a 
definition inside
+     * one join branch while a consumer sits in another branch). Producers are 
visited
+     * in dependency order - a consumer requires its producer to be registered 
already -
+     * so the list is a valid SQL definition order (a CTE used by another CTE 
is
+     * defined before it).
+     */
+    private final List<String> cteDefinitions = new ArrayList<>();
+
+    /**
+     * Recursive CTE output column names keyed by CTE name. Registered from 
the anchor
+     * branch of a PhysicalRecursiveUnion; used to name the recursive 
work-table self
+     * reference and the outer reference so the decompiled WITH RECURSIVE stays
+     * re-parseable.
+     */
+    private final Map<String, List<String>> recursiveCteColumns = new 
HashMap<>();
+
+    /**
+     * Local (partial) aggregate output column -> the aggregate input 
expression it
+     * aggregates, e.g. local partial_sum(x)#N registers N -> x. A global
+     * aggregate references such a column as sum(partial_sum(x)#N); the global
+     * decompile rewrites it back to sum(x) so the decompiled SQL stays a 
single
+     * logical aggregate (no partial_ intermediate functions).
+     */
+    private final Map<ExprId, Expression> localAggParams = new HashMap<>();
+
+    /**
+     * Buffer columns produced by the distinct-dedup branch: their defining 
partial
+     * aggregate consumed a DATA column (e.g. DISTINCT_LOCAL's 
partial_count(key)),
+     * not another partial buffer. In a DISTINCT_GLOBAL merge stage a 
count(...) over
+     * such a buffer is the user's count(DISTINCT key).
+     *
+     * A plan may mix plain aggregates with a distinct one (TPCDS q28:
+     * avg(x), count(x), count(DISTINCT x)): the plain count rides along the 
same
+     * DISTINCT_GLOBAL stage, but its buffer is a merge chain (its defining 
partial
+     * aggregated another partial buffer, e.g. 
partial_count(partial_count(x))), so it
+     * must NOT be rendered with DISTINCT. Both chains otherwise resolve to 
the same
+     * data column, which is why the provenance has to be recorded while the
+     * intermediate stages are eliminated.
+     */
+    private final Set<ExprId> distinctMergeBuffers = new HashSet<>();
+
+    /**
+     * Table id to (partition id to the name the CAPTURE resolved), 
snapshotted while the
+     * planning locks were held (see 
SPMOptimizer.OptimizeResult.getPartitionNames). Empty when
+     * the caller has none (a direct / test-side decompile).
+     */
+    private final Map<Long, Map<Long, 
org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin>>
+            capturedPartitionNames;
+
+    /**
+     * Outermost projection reached through unary nodes, stopping at an 
aggregate or branching node. It emits the
+     * final project list. Intermediate projections carry child references and 
their own expressions, filtered by
+     * live-column analysis when available.
+     */
+    private final Set<Plan> outputProjects =
+            Collections.newSetFromMap(new java.util.IdentityHashMap<>());
+
+    /**
+     * Identity map: physical plan node -> the ExprIds of its output columns 
that upper
+     * layers actually consume (the final result columns plus every column 
referenced by
+     * a decompiled expression above). An entry ABSENT, or mapped to null, 
means "no
+     * pruning for this node and everything below it" (the pre-pruning 
behaviour).
+     *
+     * The decompiled planSql inflates when every intermediate projection 
re-emits the
+     * whole child column set (a deep TPCDS join stack re-lists hundreds of 
c_N columns
+     * per level). This map drives live-column pruning: each SELECT list is 
filtered to
+     * the live columns only, so a column that is never referenced above and 
is not part
+     * of the final result disappears from every intermediate projection. 
Equivalence is
+     * preserved because only dead columns are dropped.
+     */
+    private final Map<Plan, Set<ExprId>> neededOutputs =
+            new java.util.IdentityHashMap<>();
+
+    /**
+     * CTEId -> the CTE body output columns referenced by every inlined 
consumer (the
+     * consumer output slots mapped back to their producer slots). Filled 
while the main
+     * query is propagated top-down; the CTE producer subtree is propagated 
afterwards
+     * with exactly this column set, so a big CTE body (a deep TPCDS join 
stack) only
+     * keeps the columns its consumers actually read.
+     */
+    private final Map<CTEId, Set<ExprId>> cteConsumerNeeds = new HashMap<>();
+    /**
+     * CTE ids with at least one consumer that must keep every body column (see
+     * propagateNeed): a consumer under a NON-prunable node (e.g. a UNION 
branch) receives
+     * need = null, and without this obligation the producer was pruned to the 
OTHER
+     * consumer's columns alone while the union branch still emitted its y 
reference -
+     * an invalid frozen text that the creation probe accepted whenever an 
IN-list marker
+     * skipped validation.
+     */
+    private final Set<CTEId> cteKeepAllConsumers = new HashSet<>();
+
+    /**
+     * Per-decompile generated-column-name state: when a decompiled output 
column has no
+     * clean user alias it is exported under a generated c_N name (c_1, 
c_2,...),
+     * assigned in decompile order and memoized by the output ExprId so every 
reference
+     * to the same column prints the same alias. A fresh per-decompile 
sequence (instead
+     * of embedding the analyzer's ExprId) keeps the generated names small, 
readable and
+     * independent of how many internal ExprIds the optimizer allocated.
+     */
+    private final Map<ExprId, String> generatedColumnNames = new HashMap<>();
+    private int generatedColumnSeq = 0;
+
+    /**
+     * Normalized names already visible in the CURRENT decompile: user aliases 
and the
+     * preserved source columns of the relations a generated name gets 
exported next to.
+     * A generated c_N must avoid them - the counter alone only keeps the
+     * GENERATED names apart, so a source column literally named c_1 (or a 
group-by
+     * item / user alias of that name) could end up next to sum(v) AS c_1 or
+     * MARK_SLOT c_1: two ExprIds registered under one visible name make the
+     * enclosing projection / result sink read an AMBIGUOUS column from the 
derived
+     * relation after reload. Project / window outputs repair duplicates 
afterwards
+     * (dedupeSelectOutputNames); the join (MARK_SLOT / explicit projection) 
and aggregate
+     * exports do not, so they reserve here instead.
+     */
+    private final Set<String> reservedOutputNames = new HashSet<>();
+
+    /**
+     * Builds a renderer WITHOUT an under-lock partition-name snapshot (direct 
/ test-side
+     * decompile): a pinned partition is then named from the live catalog.
+     */
+    public SPMPlan2SQLBuilder() {
+        this(java.util.Map.of());
+    }
+
+    /**
+     * Builds a renderer carrying the partition names the CAPTURE resolved 
while the planning locks
+     * were held (see SPMOptimizer.OptimizeResult.getPartitionNames).
+     *
+     * Parameter partitionNames: table id to (partition id to the ADMITTED 
pin), or null
+     */
+    public SPMPlan2SQLBuilder(
+            Map<Long, Map<Long, 
org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin>>
+                    partitionNames) {
+        this.capturedPartitionNames = partitionNames == null
+                ? java.util.Map.of() : partitionNames;
+    }
+
+    /** Fuses freeze guards while retaining session, subquery, reserved-name 
error precedence. */
+    private static void rejectUnsupportedExpressions(Plan plan) {
+        Expression[] unsupported = new Expression[2];
+        
org.apache.doris.nereids.spm.SPMPlanTreeSupport.<RuntimeException>walkPlans(plan,
 node -> {
+            for (Expression expr : node.getExpressions()) {
+                inspectFreezeExpression(expr, expr, unsupported);
+            }
+        });
+        if (unsupported[0] != null) {
+            throw new UnsupportedOperationException("SPM cannot freeze an 
expression"
+                    + " carrying a subquery (its SQL has no subquery 
rendering): " + unsupported[0]);
+        }
+        if (unsupported[1] != null) {
+            throw new UnsupportedOperationException("SPM cannot freeze an 
expression"
+                    + " that calls the reserved internal name of a subquery 
marker: " + unsupported[1]);
+        }
+    }
+
+    private static void inspectFreezeExpression(Expression expression, 
Expression root,
+            Expression[] unsupported) {
+        if (expression instanceof 
org.apache.doris.nereids.trees.expressions.SessionVarGuardExpr) {
+            throw new UnsupportedOperationException("SPM cannot freeze an 
expression"
+                    + " carrying a session-variable guard: " + root);
+        }
+        if (unsupported[0] == null
+                && expression instanceof 
org.apache.doris.nereids.trees.expressions.SubqueryExpr) {
+            unsupported[0] = root;
+        }
+        if (unsupported[1] == null && expression instanceof Function
+                && SPMExprSqlBuilder.INTERNAL_PSEUDO_FUNCTION_NAMES.contains(
+                        ((Function) 
expression).getName().toLowerCase(java.util.Locale.ROOT))) {
+            unsupported[1] = root;
+        }
+        for (Expression child : expression.children()) {
+            inspectFreezeExpression(child, root, unsupported);
+        }
+    }
+
+    /**
+     * Marks the OUTERMOST projection of the decompiled tree: the first 
PhysicalProject
+     * reached from the root along single-child pass-through nodes (ResultSink 
/ Sort /
+     * Distribute /...). Walking stops at an aggregate - an aggregate that 
feeds the
+     * result carries the final SELECT list itself, so there is no outermost 
projection
+     * and every projection below it is an intermediate one (full child 
output).
+     */
+    private void markOutputProject(Plan plan) {
+        Plan current = plan;
+        while (current != null) {
+            if (current instanceof PhysicalProject) {
+                outputProjects.add(current);
+                return;
+            }
+            if (current instanceof PhysicalHashAggregate || current.arity() != 
1) {
+                return;
+            }
+            current = current.child(0);
+        }
+    }
+
+    // live-column analysis (dead-column pruning).
+
+    /**
+     * Whether live-column requirements can be propagated through this node. 
Supported linear, join, aggregate and
+     * CTE-anchor shapes propagate requirements; other nodes retain all 
descendant columns. CTE consumers record
+     * producer-column requirements separately.
+     */
+    private static boolean isPrunableNode(Plan node) {
+        return node instanceof PhysicalProject
+                || node instanceof PhysicalWindow
+                || node instanceof PhysicalHashJoin
+                || node instanceof PhysicalNestedLoopJoin
+                || node instanceof PhysicalHashAggregate
+                || node instanceof PhysicalFilter
+                || node instanceof PhysicalTopN
+                || node instanceof PhysicalQuickSort
+                || node instanceof PhysicalLimit
+                || node instanceof PhysicalDistribute
+                || node instanceof PhysicalLazyMaterialize
+                || node instanceof PhysicalLazyMaterializeOlapScan
+                || node instanceof PhysicalPartitionTopN
+                || node instanceof PhysicalAssertNumRows
+                || node instanceof PhysicalResultSink
+                || node instanceof PhysicalCTEAnchor;
+    }
+
+    /** Pre-analysis entry: fills neededOutputs top-down from the root. */
+    private void computeNeeded(Plan root) {
+        neededOutputs.clear();
+        cteConsumerNeeds.clear();
+        cteKeepAllConsumers.clear();
+        Set<ExprId> rootNeed = new HashSet<>();
+        for (Slot slot : root.getOutput()) {
+            rootNeed.add(slot.getExprId());
+        }
+        propagateNeed(root, rootNeed);
+    }
+
+    /**
+     * Top-down live-column propagation. need is the set of output ExprIds of
+     * node that upper layers consume; null means "keep everything" (never 
prune
+     * below). Every parent hands each child the child's output columns that 
must stay
+     * alive: the columns the parent passes through and the columns the 
parent's own
+     * decompiled expressions reference.
+     */
+    private void propagateNeed(Plan node, Set<ExprId> need) {
+        if (node == null) {
+            return;
+        }
+        if (node instanceof PhysicalCTEConsumer) {
+            // a CTE consumer has no children; record which body columns its 
live output
+            // columns map back to, so the producer subtree can be pruned to 
exactly them.
+            // need = null means "keep everything": the consumer sits under a 
non-prunable
+            // node (a UNION branch, a recursive union), so it must record a 
KEEP-ALL
+            // obligation - otherwise the producer was pruned to the OTHER 
consumers'
+            // columns while this consumer still referenced its own (see
+            // cteKeepAllConsumers).
+            PhysicalCTEConsumer consumer = (PhysicalCTEConsumer) node;
+            if (need == null) {
+                cteKeepAllConsumers.add(consumer.getCteId());
+                return;
+            }
+            Set<ExprId> bodyNeeds = cteConsumerNeeds.computeIfAbsent(
+                    consumer.getCteId(), k -> new HashSet<>());
+            for (Slot slot : consumer.getOutput()) {
+                if (need.contains(slot.getExprId())) {
+                    bodyNeeds.add(consumer.getProducerSlot(slot).getExprId());
+                }
+            }
+            return;
+        }
+        if (need == null || !isPrunableNode(node)) {
+            // keep everything on this node and below (no pruning boundary)
+            for (Plan child : node.children()) {
+                propagateNeed(child, null);
+            }
+            return;
+        }
+        Set<ExprId> nodeNeed = new HashSet<>(need);
+        neededOutputs.put(node, nodeNeed);
+
+        if (node instanceof PhysicalCTEAnchor) {
+            // child(0) is the CTE producer whose body every consumer inlines. 
The main
+            // query (child(1)) is propagated FIRST so every consumer records 
which body
+            // columns it reads; the body subtree is then pruned to exactly 
those columns.
+            // A body with no consumer (or one whose consumers never resolve) 
keeps every
+            // column.
+            PhysicalCTEAnchor<? extends Plan, ? extends Plan> anchor =
+                    (PhysicalCTEAnchor<? extends Plan, ? extends Plan>) node;
+            if (anchor.child(1) != null) {
+                propagateNeed(anchor.child(1), nodeNeed);
+            }
+            Plan producer = anchor.child(0);
+            if (producer == null) {
+                return;
+            }
+            if (producer instanceof PhysicalCTEProducer) {
+                PhysicalCTEProducer<? extends Plan> cteProducer =
+                        (PhysicalCTEProducer<? extends Plan>) producer;
+                Set<ExprId> bodyNeed = 
cteConsumerNeeds.get(cteProducer.getCteId());
+                if (cteKeepAllConsumers.contains(cteProducer.getCteId())) {
+                    // at least one consumer keeps every column: the body must 
NOT be
+                    // pruned (a UNION branch's y reference would otherwise 
dangle)
+                    propagateNeed(cteProducer.child(0), null);
+                } else if (bodyNeed != null && !bodyNeed.isEmpty()) {
+                    propagateNeed(cteProducer.child(0), bodyNeed);
+                } else {
+                    propagateNeed(cteProducer.child(0), null);
+                }
+            } else {
+                propagateNeed(producer, null);
+            }
+            return;
+        }
+        if (node instanceof PhysicalProject) {
+            propagateProjectNeed((PhysicalProject<? extends Plan>) node, 
nodeNeed);
+        } else if (node instanceof PhysicalHashJoin || node instanceof 
PhysicalNestedLoopJoin) {
+            propagateJoinNeed((AbstractPhysicalJoin<? extends Plan, ? extends 
Plan>) node, nodeNeed);
+        } else if (node instanceof PhysicalHashAggregate) {
+            propagateAggregateNeed((PhysicalHashAggregate<? extends Plan>) 
node, nodeNeed);
+        } else if (node instanceof PhysicalWindow) {
+            propagateWindowNeed((PhysicalWindow<? extends Plan>) node, 
nodeNeed);
+        } else if (node instanceof PhysicalFilter) {
+            PhysicalFilter<? extends Plan> filter = (PhysicalFilter<? extends 
Plan>) node;
+            Set<ExprId> childNeed = new HashSet<>(nodeNeed);
+            addExprSlots(filter.getPredicate(), childNeed);
+            propagateNeed(filter.child(0), childNeed);
+        } else if (node instanceof PhysicalTopN) {
+            PhysicalTopN<? extends Plan> topN = (PhysicalTopN<? extends Plan>) 
node;
+            Set<ExprId> childNeed = new HashSet<>(nodeNeed);
+            for (org.apache.doris.nereids.properties.OrderKey key : 
topN.getOrderKeys()) {
+                addExprSlots(key.getExpr(), childNeed);
+            }
+            propagateNeed(topN.child(0), childNeed);
+        } else if (node instanceof PhysicalQuickSort) {
+            PhysicalQuickSort<? extends Plan> sort = (PhysicalQuickSort<? 
extends Plan>) node;
+            Set<ExprId> childNeed = new HashSet<>(nodeNeed);
+            for (org.apache.doris.nereids.properties.OrderKey key : 
sort.getOrderKeys()) {
+                addExprSlots(key.getExpr(), childNeed);
+            }
+            propagateNeed(sort.child(0), childNeed);
+        } else {
+            // pure pass-through nodes (limit / distribute / lazy materialize 
/...):
+            // their output slots are the child slots, so the same need flows 
down
+            for (Plan child : node.children()) {
+                propagateNeed(child, nodeNeed);
+            }
+        }
+    }
+
+    /**
+     * PhysicalProject: live pass-through columns plus the columns referenced 
by the
+     * project's own expressions whose output is itself live.
+     */
+    private void propagateProjectNeed(PhysicalProject<? extends Plan> project, 
Set<ExprId> need) {
+        Plan child = project.child(0);
+        if (child == null) {
+            return;
+        }
+        Set<ExprId> childNeed = new HashSet<>();
+        boolean finalProject = outputProjects.contains(project);
+        if (finalProject) {
+            // the outermost projection emits its full projection list: every 
referenced
+            // child column must stay alive
+            for (NamedExpression projectExpr : project.getProjects()) {
+                addExprSlots(projectExpr, childNeed);
+            }
+        } else {
+            // pass-through: the child columns that are still live above this 
projection
+            Set<ExprId> childOut = outputIdSet(child);
+            for (ExprId id : need) {
+                if (childOut.contains(id)) {
+                    childNeed.add(id);
+                }
+            }
+            // plus the columns referenced by this projection's own live 
expressions
+            for (NamedExpression projectExpr : project.getProjects()) {
+                if (need.contains(projectExpr.getExprId())) {
+                    addExprSlots(projectExpr, childNeed);
+                }
+            }
+        }
+        propagateNeed(child, childNeed);
+    }
+
+    /**
+     * Join: the preserved output columns flow to the side that produces them; 
the
+     * hash / other / mark conjuncts are always decompiled, so every column 
they
+     * reference stays alive on its own side.
+     */
+    private void propagateJoinNeed(AbstractPhysicalJoin<? extends Plan, ? 
extends Plan> join,
+            Set<ExprId> need) {
+        Plan left = join.left();
+        Plan right = join.right();
+        if (left == null || right == null) {
+            return;
+        }
+        Set<ExprId> leftOut = outputIdSet(left);
+        Set<ExprId> rightOut = outputIdSet(right);
+        Set<ExprId> conjRefs = new HashSet<>();
+        for (Expression conjunct : join.getHashJoinConjuncts()) {
+            addExprSlots(conjunct, conjRefs);
+        }
+        for (Expression conjunct : join.getOtherJoinConjuncts()) {
+            addExprSlots(conjunct, conjRefs);
+        }
+        for (Expression conjunct : join.getMarkJoinConjuncts()) {
+            addExprSlots(conjunct, conjRefs);
+        }
+        Set<ExprId> leftNeed = new HashSet<>();
+        Set<ExprId> rightNeed = new HashSet<>();
+        for (ExprId id : need) {
+            if (leftOut.contains(id)) {
+                leftNeed.add(id);
+            }
+            if (rightOut.contains(id)) {
+                rightNeed.add(id);
+            }
+        }
+        for (ExprId id : conjRefs) {
+            if (leftOut.contains(id)) {
+                leftNeed.add(id);
+            }
+            if (rightOut.contains(id)) {
+                rightNeed.add(id);
+            }
+        }
+        propagateNeed(left, leftNeed);
+        propagateNeed(right, rightNeed);
+    }
+
+    /**
+     * Aggregate: the global stage keeps its whole output list (GROUP BY keys 
and
+     * aggregate functions), but the input columns its expressions reference 
must stay
+     * alive below. Local / intermediate execution stages are pass-through 
nodes.
+     */
+    private void propagateAggregateNeed(PhysicalHashAggregate<? extends Plan> 
agg, Set<ExprId> need) {
+        AggPhase phase = agg.getAggPhase();
+        if (phase.isLocal() || isIntermediateAggStage(agg)) {
+            for (Plan child : agg.children()) {
+                propagateNeed(child, need);
+            }
+            return;
+        }
+        Plan child = agg.child(0);
+        if (child == null) {
+            return;
+        }
+        Set<ExprId> childNeed = new HashSet<>();
+        for (Expression groupBy : agg.getGroupByExpressions()) {
+            addExprSlots(groupBy, childNeed);
+        }
+        for (NamedExpression output : agg.getOutputExpressions()) {
+            addAggregateOutputRefs(output, childNeed);
+        }
+        propagateNeed(child, childNeed);
+    }
+
+    /**
+     * Window: live pass-through columns plus the input columns of the live 
window
+     * expressions.
+     */
+    private void propagateWindowNeed(PhysicalWindow<? extends Plan> window, 
Set<ExprId> need) {
+        Plan child = window.child(0);
+        if (child == null) {
+            return;
+        }
+        Set<ExprId> childNeed = new HashSet<>();
+        Set<ExprId> childOut = outputIdSet(child);
+        for (ExprId id : need) {
+            if (childOut.contains(id)) {
+                childNeed.add(id);
+            }
+        }
+        for (NamedExpression windowExpr : window.getWindowExpressions()) {
+            if (need.contains(windowExpr.getExprId())) {
+                addExprSlots(windowExpr, childNeed);
+            }
+        }
+        propagateNeed(child, childNeed);
+    }
+
+    /**
+     * The columns referenced by one global-aggregate output expression, with 
every
+     * local partial-buffer reference resolved down to its data columns 
(mirrors
+     * appendAggSelect / resolveBufferSlots so the pruned SELECT lists keep 
exactly the
+     * columns the decompiled aggregate prints).
+     */
+    private void addAggregateOutputRefs(NamedExpression output, Set<ExprId> 
out) {
+        Expression inner = output instanceof Alias ? ((Alias) output).child() 
: output;
+        if (inner instanceof SlotReference) {
+            // group-by key pass-through
+            out.add(((SlotReference) inner).getExprId());
+            return;
+        }
+        if (inner instanceof AggregateExpression) {
+            AggregateExpression aggExpr = (AggregateExpression) inner;
+            List<Expression> args = aggExpr.getFunction().children().isEmpty()
+                    ? new ArrayList<>(aggExpr.children()) : 
aggExpr.getFunction().children();
+            for (Expression arg : args) {
+                addResolvedExprSlots(arg, out);
+            }
+            return;
+        }
+        addExprSlots(inner, out);
+    }
+
+    /**
+     * Collects every slot of expr, resolving partial-buffer slots through
+     * localAggParams down to their data columns (mirrors resolveBufferSlots).
+     */
+    private void addResolvedExprSlots(Expression expr, Set<ExprId> out) {
+        if (expr instanceof SlotReference) {
+            Expression param = localAggParams.get(((SlotReference) 
expr).getExprId());
+            if (param != null) {
+                addResolvedExprSlots(param, out);
+                return;
+            }
+            out.add(((SlotReference) expr).getExprId());
+            return;
+        }
+        for (Expression childExpr : expr.children()) {
+            addResolvedExprSlots(childExpr, out);
+        }
+    }
+
+    /** Output ExprId set of a plan node. */
+    private static Set<ExprId> outputIdSet(Plan node) {
+        Set<ExprId> ids = new HashSet<>();
+        for (Slot slot : node.getOutput()) {
+            ids.add(slot.getExprId());
+        }
+        return ids;
+    }
+
+    /** Adds every slot ExprId used by an expression. */
+    private static void addExprSlots(Expression expr, Set<ExprId> out) {
+        if (expr == null) {
+            return;
+        }
+        collectAllSlotIds(expr, out);
+    }
+
+    /**
+     * Pre-walk that fills localAggParams bottom-up so the live-column analysis
+     * (which runs before the decompile walk) can resolve partial-buffer 
references. The
+     * decompile walk re-fills the same map while it descends (harmless 
duplicate).
+     */
+    private void collectLocalAggParams(Plan node) {
+        for (Plan child : node.children()) {
+            collectLocalAggParams(child);
+        }
+        if (node instanceof PhysicalHashAggregate) {
+            PhysicalHashAggregate<? extends Plan> agg = 
(PhysicalHashAggregate<? extends Plan>) node;
+            if (agg.getAggPhase().isLocal() || isIntermediateAggStage(agg)) {
+                recordLocalAggStage(agg);
+            }
+        }
+    }
+
+    /**
+     * Registers one local (partial) aggregate stage's buffer outputs (see the 
field
+     * comment of localAggParams).
+     */
+    private void recordLocalAggStage(PhysicalHashAggregate<? extends Plan> 
agg) {
+        for (NamedExpression output : agg.getOutputExpressions()) {
+            Expression inner = output instanceof Alias ? ((Alias) 
output).child() : output;
+            if (inner instanceof AggregateExpression && 
isPartialAggregate((AggregateExpression) inner)) {
+                Expression rawParam = 
extractPartialParam((AggregateExpression) inner);
+                if (agg.getGroupByExpressions().isEmpty()
+                        && isDistinctMergeContribution(((AggregateExpression) 
inner).children())) {
+                    distinctMergeBuffers.add(output.getExprId());
+                }
+                Expression param = rawParam;
+                if (param == null) {
+                    // count(*) has no data argument (extractPartialParam -> 
null). Map the
+                    // buffer to the partial expression itself so the enclosing
+                    // merge-finalize count(*) resolves its argument back to 
the star, which
+                    // appendAggSelect collapses into count(*) 
(isNestedNoArgCount) - without
+                    // this the raw buffer slot (named e.g. 
"partial_count(*)") would leak
+                    // into the decompiled SQL as the invalid 
count(partial_count(*)).
+                    param = inner;
+                }
+                while (param instanceof SlotReference) {
+                    Expression resolved = localAggParams.get(((SlotReference) 
param).getExprId());
+                    if (resolved == null) {
+                        break;
+                    }
+                    param = resolved;
+                }
+                localAggParams.put(output.getExprId(), param);
+            }
+        }
+    }
+
+    /**
+     * Whether an output of an eliminated DISTINCT_LOCAL stage is the 
distinct-dedup
+     * contribution (see distinctMergeBuffers).
+     *
+     * The distinction has to be read from the aggregate expression's own 
children -
+     * the physical input of the buffer - because the merge function's 
argument is
+     * normalized to the data column for EVERY buffer:
+     *
+     * merge-chain buffer (plain aggregate riding along): its input is another
+     *   partial buffer, already recorded in localAggParams, or a count-star 
function
+     *   with no argument at all;
+     * distinct-dedup buffer: its input is the dedup KEY data column, either 
as the
+     *   bare slot (partial_count(key)) or wrapped as the partial function's 
argument
+     *   (count(key)).
+     */
+    private boolean isDistinctMergeContribution(List<Expression> exprChildren) 
{
+        if (exprChildren.isEmpty()) {
+            return false;
+        }
+        Expression bufferArg = exprChildren.get(0);
+        if (bufferArg instanceof SlotReference) {
+            return !localAggParams.containsKey(((SlotReference) 
bufferArg).getExprId());
+        }
+        List<Expression> argChildren = bufferArg.children();
+        if (argChildren.isEmpty()) {
+            // e.g. the count(*) star buffer: raw-row counting, never the 
distinct merge
+            return false;
+        }
+        for (Expression child : argChildren) {
+            if (!(child instanceof SlotReference)
+                    || localAggParams.containsKey(((SlotReference) 
child).getExprId())) {
+                return false;
+            }
+        }
+        return true;
+    }
+
+    /**
+     * The live column filter for one explicit SELECT list: the list a node 
emits is
+     * pruned to the node's needed output columns (no entry in the map, or a 
null need,
+     * keeps the whole list - e.g. mock plan trees and unpruned subtrees).
+     */
+    private void filterLiveSelects(Plan node, List<Pair<ExprId, String>> 
selects) {
+        Set<ExprId> need = neededOutputs.get(node);
+        if (need == null) {
+            return;
+        }
+        selects.removeIf(p -> !need.contains(p.key()));
+    }
+
+    /**
+     * Decompile entry: physical plan -> planSql.
+     *
+     * Parameter plan: the optimal physical plan
+     * Returns: planSql (standard SQL text)
+     */
+    public String toSQL(Plan plan) {
+        // An alias-UDF expansion computed under the DEFINITION's stored 
session
+        // variables (decimalOverflowScale / enable_decimal256 /...) carries a
+        // SessionVarGuardExpr; the SQL text has no way to express that guard, 
so
+        // printing only its child would replan the arithmetic under the LATER 
caller's
+        // variables and could change its type, scale or value while the same 
bind SQL
+        // still matches. Reject freezing instead (CREATE keeps the user 
planSql /
+        // falls back to the parameterized-plan-tree path).
+        // A residual SUBQUERY has no SQL rendering here (SPMExprSqlBuilder 
emits the digest
+        // pseudo form, which a user function of the same name could legally 
satisfy), and SPM's
+        // pseudo names are reserved - both are refusals, so the baseline 
stays on the
+        // parameterized-plan-tree path (see rejectUnsupportedExpressions).
+        rejectUnsupportedExpressions(plan);
+        // reset the per-decompile alias / generated-name sequences: t_N and 
c_N only
+        // need to be unique WITHIN the one produced SQL, so numbering 
restarts here and
+        // the decompiled text stays compact across calls
+        SQLRelation.resetAliasCounter();
+        cteBodies.clear();
+        cteAliases.clear();
+        cteDefinitions.clear();
+        generatedColumnNames.clear();
+        generatedColumnSeq = 0;
+        reservedOutputNames.clear();
+        lateralViewSeq = 0;
+        outputProjects.clear();
+        localAggParams.clear();
+        distinctMergeBuffers.clear();
+        markOutputProject(plan);
+        collectLocalAggParams(plan);
+        computeNeeded(plan);
+        SQLRelation relation = plan.accept(this, null);
+        // attach the collected CTE definitions (WITH) to the outermost 
relation: they
+        // are collected in producer dependency order and are visible to the 
whole
+        // statement, which is exactly the scope a CTE anchor binds
+        if (!cteDefinitions.isEmpty()) {
+            List<String> merged = relation.getCte() == null
+                    ? new ArrayList<>() : new ArrayList<>(relation.getCte());
+            merged.addAll(cteDefinitions);
+            relation.setCte(merged);
+        }
+        // PhysicalAssertNumRows carries no node of its own in the frozen 
text: it renders
+        // as the ASSERT_ROWS relation prefix wherever its parent references 
the input
+        // relation (visitPhysicalAssertNumRows), and the parser rebuilds the 
assertion.
+        //
+        // At the ROOT there is no parent to consume that prefix: toSQL of the
+        // relation never renders assertRows, so SELECT * FROM ASSERT_ROWS 
(SELECT...) a
+        // froze as an ordinary filtered SELECT - CREATE's probe planned it 
happily and an
+        // identical replay returned BOTH rows where native execution raises 
the row-count
+        // error. The boundary is therefore materialized HERE, in the only 
statement form the
+        // grammar has for it: the ASSERT_ROWS relation under a plain SELECT *.
+        return renderStatement(relation);
+    }
+
+    /** Materializes a pending assertion when a relation is emitted as a 
standalone SELECT. */
+    private String renderStatement(SQLRelation relation) {
+        return relation.isAssertRows() ? "SELECT * FROM " + 
relation.toRelationSQL() : relation.toSQL();
+    }
+
+    /**
+     * Handles a child node.
+     *
+     * Parameter plan: child physical plan
+     * Returns: SQLRelation of the child plan
+     */
+    private SQLRelation process(Plan plan) {
+        return plan.accept(this, null);
+    }
+
+    // default: unsupported operators.
+
+    @Override
+    public SQLRelation visit(Plan plan, Void context) {
+        throw new UnsupportedOperationException(
+                "SPMPlan2SQLBuilder does not support plan: " + 
plan.getClass().getSimpleName());
+    }
+
+    // pass-through mode.
+    /**
+     * PhysicalStorageLayerAggregate: a scan-shaped shortcut that returns 
COUNT / MIN / MAX
+     * for the wrapped scan from table or footer metadata instead of reading 
the data.
+     * AggregateStrategies keeps the enclosing aggregate (or the constant-only 
project) on
+     * TOP of the shortcut and keeps every expression in terms of the wrapped 
relation's
+     * slots, so decompiling the wrapped relation publishes exactly those 
slots and the
+     * enclosing operators render the same SQL aggregate (count(*) / min(x) 
/...) over the
+     * same table. Replaying that SQL re-derives an equivalent plan; there is 
no clause that
+     * describes the shortcut itself and none is needed, because the shortcut 
is an
+     * execution strategy of the aggregate rather than a different result.
+     */
+    @Override
+    public SQLRelation visitPhysicalStorageLayerAggregate(
+            PhysicalStorageLayerAggregate storageLayerAggregate, Void context) 
{
+        return visitPhysicalRelation(storageLayerAggregate.getRelation(), 
context);
+    }
+
+    /**
+     * PhysicalDistribute: a data exchange node with no SQL equivalent; 
returns the child.
+     */
+    @Override
+    public SQLRelation visitPhysicalDistribute(PhysicalDistribute<? extends 
Plan> distribute, Void context) {
+        return process(distribute.child(0));
+    }
+
+    /**
+     * PhysicalLazyMaterialize: an optimizer lazy-column-materialization 
wrapper with no
+     * SQL equivalent; returns the child.
+     */
+    @Override
+    public SQLRelation visitPhysicalLazyMaterialize(
+            PhysicalLazyMaterialize<? extends Plan> lazy, Void context) {
+        return process(lazy.child(0));
+    }
+
+    /**
+     * PhysicalLazyMaterializeOlapScan: an OlapScan wrapped with lazy column
+     * materialization; decompiled as a normal scan.
+     */
+    @Override
+    public SQLRelation visitPhysicalLazyMaterializeOlapScan(
+            PhysicalLazyMaterializeOlapScan scan, Void context) {
+        return visitPhysicalRelation(scan, context);
+    }
+
+    /**
+     * PhysicalLazyMaterializeFileScan: a file scan wrapped with lazy column
+     * materialization; decompiled as a normal scan (including its scan 
modifiers -
+     * the wrapper subclasses PhysicalFileScan, so the TABLESAMPLE / snapshot 
/ scan
+     * parameter rendering applies unchanged).
+     */
+    @Override
+    public SQLRelation visitPhysicalLazyMaterializeFileScan(
+            PhysicalLazyMaterializeFileScan scan, Void context) {
+        return visitPhysicalRelation(scan, context);
+    }
+
+    /**
+     * PhysicalResultSink (and sinks in general): result collection nodes with 
no SQL
+     * equivalent; passed through. For the RESULT sink the final output column 
ORDER is
+     * forced to the sink's output list (the user SELECT order) - the physical 
aggregate
+     * / projection below may emit columns in a different order (e.g. group-by 
keys
+     * before aggregates), which would reorder the user-visible result columns.
+     */
+    @Override
+    public SQLRelation visitPhysicalSink(PhysicalSink<? extends Plan> sink, 
Void context) {
+        if (sink instanceof PhysicalResultSink) {
+            SQLRelation child = process(sink.child(0));
+            List<Slot> outputs = sink.getOutput();
+            if (outputs.isEmpty()) {
+                return child;
+            }
+            // Final output columns in the USER SELECT order. Each output 
column is
+            // emitted as its in-scope reference (child.getColumnNames), and - 
when the
+            // reference is a decompile-internal alias (c_N) that hides the 
original
+            // column label - re-labelled with the ResultSink output slot's 
name
+            // (output.getName): that label is exactly what a normal execution 
of the
+            // original query shows in the result header (a plain alias such as
+            // "d_week_seq1", a table column name, or the expression text of an
+            // un-aliased expression column), so the frozen planSql keeps the 
SAME output
+            // labels from the result sink rather than internal generated 
references.
+            List<Pair<ExprId, String>> ordered = new ArrayList<>();
+            boolean anyRenamed = false;
+            for (Slot output : outputs) {
+                String ref = child.getColumnNames().get(output.getExprId());
+                if (ref == null) {
+                    ref = output.getName();
+                }
+                String display = output.getName();
+                if (display == null || display.isEmpty() || 
display.equals(ref)) {
+                    ordered.add(Pair.of(output.getExprId(), ref));
+                    continue;
+                }
+                // Re-expose the column under its original label: "c_3 AS 
d_week_seq1"
+                // (or "AS round(...)" for an un-aliased expression column, 
whose header
+                // text is the expression itself). The quoted name never 
participates in
+                // name resolution of the frozen SQL body, so quoting is 
always safe.
+                ordered.add(Pair.of(output.getExprId(), ref + " AS " + 
quoteIdentifier(display)));
+                anyRenamed = true;
+            }
+            if (child.getSelects().isEmpty()) {
+                // Without an explicit projection, the FROM columns are 
referenceable.
+                // A scan emits star columns in schema order even when the 
sink requests a
+                // different projection. Apply the sink order in place, 
preserving ORDER BY / LIMIT,
+                // UNLESS a re-labelled alias would SHADOW a name the ORDER BY 
references
+                // "c_3 AS b" next to "ORDER BY b" rebinds the sort to the
+                // alias although the clause was rendered against the base 
column b. Then
+                // the relabel moves to the wrapper below, where the clause 
keeps its own
+                // query block and the aliases cannot capture it.
+                String inPlaceOrderBy = child.getOrderBy();
+                if (inPlaceOrderBy.isEmpty()
+                        || !orderByShadowedByRelabel(child, inPlaceOrderBy, 
ordered)) {
+                    child.setSelects(ordered);
+                    return child;
+                }
+            }
+            // Explicit projection already emitted (e.g. a bare aggregate
+            // "sum(...) AS revenue" or a pass-through projection). 
Overwriting it in
+            // place would replace the expression with a bare name that is not 
resolvable
+            // in the FROM scope, so:
+            //  if the emitted columns already carry the original labels in 
the user
+            //    SELECT order, keep the projection untouched;
+            //  otherwise wrap the child in a subquery and select the ordered
+            //    (re-labelled) columns from it.
+            if (!anyRenamed && sameExprIdOrder(child.getSelects(), ordered)) {
+                return child;
+            }
+            SQLRelation outer = new SQLRelation();
+            String alias = outer.newAlias();
+            // A TOP-LEVEL ORDER BY / LIMIT pair is a semantic user clause: 
matching
+            // deliberately ignores its LIMIT value, and mergeLimits adopts 
the user's value
+            // by position - but a limit left INSIDE the derived table can 
never be reached
+            // by that positional merge (the frozen root would have no Limit 
node next to
+            // the user's Limit), so a matched LIMIT 200 query kept the 
captured 100-row
+            // cap. This wrapper is a PURE output relabelling (it neither 
filters nor
+            // aggregates), so hoisting the pair onto it is semantically 
identical and
+            // makes the frozen root a Limit node again.
+            // The ORDER BY moves WITH the LIMIT: a derived-table ORDER BY 
WITHOUT its
+            // LIMIT is only a hint the optimizer is free to drop, and the 
outer SELECT
+            // would then return an arbitrary LIMIT slice (a q02-style query 
replayed as
+            // unordered rows). Clear both BEFORE rendering the child text - 
toSQL
+            // captures them into the string.
+            String sinkOrderBy = child.getOrderBy();
+            String sinkLimit = child.getLimit();
+            // The order and cap belong to the input checked by ASSERT_ROWS.
+            if (!child.isAssertRows() && (!sinkOrderBy.isEmpty() || 
!sinkLimit.isEmpty())) {
+                // Independently of the LIMIT: "SELECT a FROM t ORDER BY b + 1"
+                // (no LIMIT at all) arrives here as a Sort under the final 
relabel wrapper,
+                // and leaving the ORDER BY inside the derived table was NOT 
safe - the
+                // frozen text is re-planned at replay, and a sort that the 
user asked for
+                // but that sits one level down came back as unordered rows 
(Nereids drops
+                // an inner sort it considers redundant). The wrapper is a 
pure output
+                // relabelling, so the user clause belongs on it. Hidden sort 
keys the
+                // child's SELECT list does not export are added to it first 
(see
+                // hoistableOrderBy); a key that cannot be exported, or that a 
relabelled
+                // wrapper alias would capture, keeps the clause inside the 
child - its own
+                // query block still resolves it. ORDER BY and LIMIT
+                // always move TOGETHER: a limit without its order picks 
arbitrary rows.
+                String hoisted = sinkOrderBy.isEmpty() ? ""
+                        : hoistableOrderBy(child, sinkOrderBy, ordered, true);
+                if (hoisted != null) {
+                    outer.setOrderBy(hoisted);
+                    outer.setLimit(sinkLimit);
+                    child.setOrderBy("");
+                    child.setLimit("");
+                }
+            }
+            // A relabelled ASSERT_ROWS child keeps its assertion: toSQL of the
+            // relation never renders the relation PREFIX (only toRelationSQL 
does), so the
+            // wrapper used to drop the single-row assertion of
+            // SELECT * FROM ASSERT_ROWS (SELECT k+1 FROM t WHERE k>0) a - 
CREATE's probe planned
+            // the plain SELECT happily and an identical replay returned BOTH 
rows where native
+            // execution raises the row-count error. The relation prefix is 
the only statement
+            // form the grammar has for it, so it is rendered HERE with this 
wrapper's alias.
+            outer.setFrom((child.isAssertRows()
+                    ? "ASSERT_ROWS (" + child.toSQL() + ") "
+                    : "(" + child.toSQL() + ") ") + alias);
+            outer.setSelects(ordered);
+            return outer;
+        }
+        return visit((Plan) sink, context);
+    }
+
+    /** Whether the projection ExprIds appear in the same order as ordered. */
+    private static boolean sameExprIdOrder(List<Pair<ExprId, String>> 
projection,
+            List<Pair<ExprId, String>> ordered) {
+        if (projection.size() != ordered.size()) {
+            return false;
+        }
+        for (int i = 0; i < projection.size(); i++) {
+            if (projection.get(i).key() != ordered.get(i).key()) {
+                return false;
+            }
+        }
+        return true;
+    }
+
+    /**
+     * Returns a valid unquoted identifier unchanged and otherwise 
backtick-quotes the whole name, doubling
+     * embedded backticks. A name containing a minus sign or a dot must remain 
one identifier rather than becoming
+     * an expression or multiple components.
+     */
+    public static String quoteIdentifier(String name) {
+        if (name == null) {
+            return null;
+        }
+        if (name.matches("[A-Za-z_][A-Za-z0-9_]*") && 
NereidsParser.isValidUnquotedIdentifier(name)) {
+            return name;
+        }
+        return "`" + name.replace("`", "``") + "`";
+    }
+
+    /**
+     * Fully-qualified name of a table with every COMPONENT quoted separately
+     * (catalog.db.table). getNameWithFullQualifiers FLATTENS a legal quoted
+     * component such as t.a into "internal.db.t.a", and splitting that on 
every dot
+     * would emit FOUR identifiers instead of the intended three-part name 
with t.a
+     * quoted as ONE component - a manually created frozen baseline carrying 
that broken
+     * text then fails re-analysis after a reload, and no raw fallback tree 
exists for a
+     * frozen row. The metadata components are taken structurally, never 
re-split.
+     */
+    static String quoteQualifiedTableName(org.apache.doris.catalog.TableIf 
table) {
+        StringBuilder sb = new StringBuilder();
+        for (String component : table.getFullQualifiers()) {
+            if (sb.length() > 0) {
+                sb.append('.');
+            }
+            sb.append(quoteIdentifier(component));
+        }
+        return sb.toString();
+    }
+
+    // Scan (wrapped as subquery or inline).
+
+    /**
+     * Renders a catalog relation with a qualified table name, supported scan 
modifiers and registered output
+     * references. Temporary table names are refused so creation can retain 
user SQL for session-local resolution.
+     */
+    @Override
+    public SQLRelation visitPhysicalRelation(PhysicalRelation relation, Void 
context) {
+        if (!(relation instanceof PhysicalCatalogRelation)) {
+            throw new UnsupportedOperationException(
+                    "SPMPlan2SQLBuilder does not support relation: " + 
relation.getClass().getSimpleName());
+        }
+        PhysicalCatalogRelation catalogRelation = (PhysicalCatalogRelation) 
relation;
+        // A TEMPORARY table's catalog object carries the CREATOR session's 
internal name
+        // (<sessionId>_#TEMP._<name>), not the text the user typed. Emitting 
it into the
+        // frozen planSql would persist a session-scoped identifier: another 
session
+        // running the same "FROM t" text matches the same bind key 
(catalog.db.t), and
+        // Database.getTableNullable accepts the already-marked internal name 
unchanged,
+        // so the replay would read the CREATOR's (possibly still live) 
temporary table
+        // instead of its own t. Never render it - the GLOBAL create / capture 
path
+        // rejects temporary relations before freezing (SPMPlanner), and a 
SESSION-scope
+        // freeze (where the session's own temp table is the intended target) 
falls back
+        // to the raw user text instead.
+        if (catalogRelation.getTable().isTemporary()) {
+            throw new UnsupportedOperationException(
+                    "SPMPlan2SQLBuilder does not support temporary tables: the 
physical relation"
+                    + " carries the creator session's internal name");
+        }
+        SQLRelation sqlRelation = new SQLRelation();
+        // Emit the fully qualified name (catalog.db.table) so the frozen 
planSql resolves
+        // the same table when it is replayed from a session whose current 
database (or
+        // catalog) differs from the one used at CREATE time (cross-db queries,
+        // information_schema,...). Tables without a database (e.g. 
FunctionGenTable)
+        // keep the bare name. Every COMPONENT is backtick-quoted separately 
when it is
+        // not a plain identifier, so a metadata name containing operators is 
re-parsed
+        // as an identifier instead of an expression, and a legal quoted 
component such
+        // as t.a keeps its boundary (see quoteQualifiedTableName). Scan 
modifiers
+        // (partition selection, TABLESAMPLE, snapshot, scan parameters) 
follow the name
+        // in grammar order.
+        String table = catalogRelation.getTable().getDatabase() == null
+                ? quoteIdentifier(catalogRelation.getTable().getName())
+                : quoteQualifiedTableName(catalogRelation.getTable());
+        sqlRelation.setFrom(table + renderScanModifiers(relation));
+        // Remember WHICH table this scan reads: two occurrences of one table 
are a self
+        // join even when their FROM texts differ (each occurrence may carry 
its own
+        // PARTITION / TABLESAMPLE pin), and the join must wrap both sides 
then.
+        sqlRelation.setRelationIdentity(table);
+        // Register output columns: ExprId -> real column name. Internal 
system columns
+        // (e.g. rowid columns a join may request from the scan) are execution 
details
+        // and are never registered so they cannot leak into projections / ON 
clauses.
+        for (Slot slot : relation.getOutput()) {
+            if (isSystemColumnName(slot.getName())) {
+                continue;
+            }
+            // registered as executable SQL text: a special-character column 
(a-b)
+            // must be backtick-quoted, otherwise a frozen projection SELECT 
a-b
+            // re-parses as the subtraction a - b and returns a different value
+            sqlRelation.registerRef(slot.getExprId(), 
quoteIdentifier(slot.getName()));
+        }
+        return sqlRelation;
+    }
+
+    // table-valued functions.
+
+    /**
+     * PhysicalTVFRelation: a table-valued function in FROM (e.g.
+     * numbers('number' = '5')). The function's own SQL text is used because a 
TVF
+     * argument list is a property list, not a normal expression list; the 
output
+     * columns keep their names.
+     */
+    @Override
+    public SQLRelation visitPhysicalTVFRelation(PhysicalTVFRelation 
tvfRelation, Void context) {
+        SQLRelation relation = new SQLRelation();
+        relation.setFrom(renderTableValuedFunction(tvfRelation.getFunction()));
+        for (Slot slot : tvfRelation.getOutput()) {
+            relation.registerRef(slot.getExprId(), 
quoteIdentifier(slot.getName()));
+        }
+        return relation;
+    }
+
+    /**
+     * Renders one TVF call safely. TableValuedFunction.computeToSql 
concatenates each
+     * concrete key/value between apostrophes WITHOUT escaping, so a valid 
property such
+     * as an S3 object key containing an apostrophe or backslash would freeze 
malformed
+     * SQL: with a placeholder-bearing predicate the row is classified as 
frozen, no
+     * fallback tree is rebuilt, and after refresh/restart the baseline 
silently stops
+     * applying. The property map is serialized in a deterministic key order 
with the
+     * same default-mode-safe quoting as every other SPM-emitted string 
literal (the
+     * stored text is always re-parsed under MODE_DEFAULT).
+     */
+    private static String renderTableValuedFunction(TableValuedFunction 
function) {
+        String args = new 
TreeMap<>(function.getTVFProperties().getMap()).entrySet().stream()
+                .map(kv -> quoteSqlString(kv.getKey()) + " = " + 
quoteSqlString(kv.getValue()))
+                .collect(Collectors.joining(", "));
+        return quoteIdentifier(function.getName()) + "(" + args + ")";
+    }
+
+    /** PhysicalLazyMaterializeTVFScan: a TVF scan wrapped by lazy 
materialization. */
+    @Override
+    public SQLRelation visitPhysicalLazyMaterializeTVFScan(
+            PhysicalLazyMaterializeTVFScan scan, Void context) {
+        return visitPhysicalTVFRelation(scan, context);
+    }
+
+    /**
+     * Renders the scan modifiers that the frozen SQL must carry, in the order 
the grammar
+     * accepts them after the table name
+     * (optScanParams, materializedViewName, tableSnapshot, 
specifiedPartition, sample):
+     *
+     * olap scans: @paramType(...) parameters (binlog reads), the partition 
list
+     *     when the scan reads a strict non-empty subset of the table 
partitions, and
+     *     TABLESAMPLE;
+     * file scans: @paramType(...) parameters, FOR VERSION/TIME AS OF
+     *     snapshots and TABLESAMPLE.
+     *
+     * Dropping any of them would silently change what the frozen planSql 
reads: a
+     * "FROM t PARTITION(p1)" baseline would replay over every partition, a 
snapshot read
+     * would run against the moving head of the table, and a sampled scan 
would return the
+     * full table.
+     *
+     * The partition list is emitted only when the user pinned it (a pruned 
scan re-derives
+     * its selection by replay); a frozen text without the pin simply does not 
match the
+     * pinned user query (a safe miss, never a replay over the wrong 
partitions).
+     *
+     * Two scan states are deliberately NOT emitted. The selected index is an 
optimizer
+     * choice (a rollup is a consistent copy, so the choice carries no 
semantics) that
+     * cannot be told apart from a user-written INDEX clause; freezing it 
would pin the
+     * optimization and stop the plain user query from matching. A 
user-written INDEX
+     * pin only survives in the user tree, so such queries never match a 
frozen text that
+     * dropped the pin.
+     *
+     * A user-written INDEX pin is NOT lost to this asymmetry: the pin is a
+     * SEMANTIC choice (on an aggregate-key table the forced rollup returns 
one aggregated
+     * row per rollup key while the base table returns one per record), the 
matching key
+     * carries it (UnboundRelation.toDigest renders "INDEX index_name", so 
only callers
+     * with the same pin match), and this method cannot reproduce it. The 
CREATE therefore
+     * declines the FREEZE for a statement that pins an index at all
+     * (SPMPlanTreeSupport.pinsExplicitIndex): the user planSql is kept, 
plan_frozen stays
+     * false, and the rewrite replays the parameterized tree, whose own 
analysis re-applies
+     * the pin - so the frozen-text path below is never asked to render one.
+     */
+    private String renderScanModifiers(PhysicalRelation relation) {
+        StringBuilder modifiers = new StringBuilder();
+        if (relation instanceof PhysicalOlapScan) {
+            PhysicalOlapScan scan = (PhysicalOlapScan) relation;
+            modifiers.append(renderScanParams(scan.getScanParams()));
+            modifiers.append(renderPartitionSelection(scan));
+            modifiers.append(renderTabletSelection(scan));
+            modifiers.append(renderTableSample(scan.getTableSample()));
+        } else if (relation instanceof PhysicalFileScan) {
+            PhysicalFileScan scan = (PhysicalFileScan) relation;
+            modifiers.append(renderScanParams(scan.getScanParams()));
+            modifiers.append(renderTableSnapshot(scan.getTableSnapshot()));
+            modifiers.append(renderTableSample(scan.getTableSample()));
+        }
+        return modifiers.toString();
+    }
+
+    /**
+     * Whether the CAPTURE admitted one pinned partition in the TEMPORARY 
namespace (see
+     * recordPartitionNames), and whether the live table still agrees.
+     *
+     * A pin without a snapshot (a table the capture never described) keeps 
the live answer, so
+     * an old baseline is unaffected. A snapshot that DISAGREES with the live 
namespace declines
+     * the freeze - the pin can no longer be described by the caller's own 
text.
+     *
+     * Parameter table: the scanned OLAP table
+     * Parameter pins: the captured pins of that table, or null
+     * Parameter partitionId: the pinned partition
+     * Returns: whether the pinned partition is TEMPORARY
+     */
+    private static boolean snapshotTemporary(OlapTable table,
+            Map<Long, 
org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin> pins,
+            long partitionId) {
+        boolean live = table.isTemporaryPartition(partitionId);
+        org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin pin =
+                pins == null ? null : pins.get(partitionId);
+        if (pin == null) {
+            return live;
+        }
+        if (pin.isTemporary() != live) {
+            throw new UnsupportedOperationException(
+                    "SPM decompile: the pinned partition " + partitionId + " 
of table "
+                            + table.getName() + " moved between the TEMPORARY 
and the formal"
+                            + " namespace after the capture (the admitted pin 
was "
+                            + (pin.isTemporary() ? "TEMPORARY" : "formal") + 
"); keeping the"
+                            + " user planSql so the replay re-resolves the 
pin");
+        }
+        return pin.isTemporary();
+    }
+
+    /**
+     * OLAP partition selection: the ids of a user-written PARTITION(...) /
+     * TEMPORARY PARTITION(...) list are frozen as that very clause - including
+     * when the pin happens to cover every partition that exists right now (a 
cardinality
+     * test would drop the clause and a later ADD PARTITION would let the 
pinned query
+     * silently read the new partition) and including the temporary namespace 
(a temp pin
+     * replayed as a formal PARTITION(name) binds the wrong partition or fails 
to bind).
+     * Partition pruning also shrinks selectedPartitionIds without any user 
pin,
+     * so only the manual provenance is ever rendered; a pruned scan 
re-derives its
+     * selection by replay. Ids are sorted so the frozen text is deterministic 
(the
+     * matcher compares the selection as a set).
+     */
+    private String renderPartitionSelection(PhysicalOlapScan scan) {
+        List<Long> pinnedIds = scan.getManuallySpecifiedPartitions();
+        if (pinnedIds.isEmpty()) {
+            return "";
+        }
+        OlapTable table = scan.getTable();
+        List<Long> sortedIds = new ArrayList<>(pinnedIds);
+        Collections.sort(sortedIds);
+        Map<Long, 
org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin> tablePins =
+                capturedPartitionNames.get(table.getId());
+        // The NAMESPACE comes from the CAPTURE: a temporary pin promoted to a
+        // formal partition (use_temp_partition_name=true) between the unlock 
and this render made
+        // the live check say "formal", the frozen text said PARTITION(name), 
and an identical
+        // replay bound the formal partition - or nothing - while the caller's 
own query reads
+        // the re-created TEMPORARY one.
+        boolean temporary = snapshotTemporary(table, tablePins, 
sortedIds.get(0));
+        StringBuilder partition = new StringBuilder(temporary ? " TEMPORARY 
PARTITION(" : " PARTITION(");
+        for (int i = 0; i < sortedIds.size(); i++) {
+            if (i > 0) {
+                partition.append(", ");
+            }
+            if (snapshotTemporary(table, tablePins, sortedIds.get(i)) != 
temporary) {
+                throw new UnsupportedOperationException(
+                        "SPM decompile: a partition pin mixing temporary and 
formal partitions"
+                                + " has no single-clause rendering");
+            }
+            long partitionId = sortedIds.get(i);
+            Partition partitionMeta = table.getPartition(partitionId);
+            org.apache.doris.nereids.spm.SPMPlanTreeSupport.PartitionPin pin =
+                    tablePins == null ? null : tablePins.get(partitionId);
+            String capturedName = pin == null ? null : pin.getName();
+            if (capturedName != null) {
+                // The ADMITTED name wins: the applied pin is a SET OF IDS, 
while the
+                // stored bind text (and every later caller matching it) names 
the partition it
+                // wrote. Reading the LIVE name here would freeze a RENAME 
PARTITION that landed
+                // between the capture and this render, so an identical caller 
pinning the OLD
+                // name - now a different, newly added partition - would be 
answered from the
+                // renamed one. A partition whose live name already moved 
makes the CREATE decline
+                // the freeze instead of freezing a text the bind cannot 
describe.
+                if (partitionMeta == null || 
!capturedName.equals(partitionMeta.getName())) {
+                    throw new UnsupportedOperationException(
+                            "SPM decompile: the pinned partition " + 
partitionId + " of table "
+                                    + table.getName() + " was renamed / 
dropped after the capture"
+                                    + " ('" + capturedName + "' is the 
admitted name); keeping"
+                                    + " the user planSql so the replay 
re-resolves the pin");
+                }
+                partition.append(quoteIdentifier(capturedName));
+                continue;
+            }
+            if (partitionMeta == null) {
+                throw new UnsupportedOperationException(
+                        "SPM decompile: the pinned partition " + partitionId + 
" of table "
+                                + table.getName() + " no longer exists and the 
capture did not"
+                                + " snapshot its name");
+            }
+            partition.append(quoteIdentifier(partitionMeta.getName()));
+        }
+        return partition.append(')').toString();
+    }
+
+    /**
+     * OLAP tablet pin: TABLET(id,...) is frozen when the user wrote it. Bucket
+     * pruning also fills selectedTabletIds, so only the manual provenance is
+     * rendered; without the clause a replayed scan would read every tablet of 
the
+     * selected partitions and could return rows the captured query excluded. 
Ids are
+     * sorted (the matcher compares the tablet list as a set).
+     */
+    private static String renderTabletSelection(PhysicalOlapScan scan) {
+        List<Long> pinnedIds = scan.getManuallySpecifiedTabletIds();
+        if (pinnedIds.isEmpty()) {
+            return "";
+        }
+        List<Long> sortedIds = new ArrayList<>(pinnedIds);
+        Collections.sort(sortedIds);
+        StringBuilder tablet = new StringBuilder(" TABLET(");
+        for (int i = 0; i < sortedIds.size(); i++) {
+            if (i > 0) {
+                tablet.append(", ");
+            }
+            tablet.append(sortedIds.get(i));
+        }
+        return tablet.append(')').toString();
+    }
+
+    /**
+     * TABLESAMPLE(n PERCENT | n ROWS) [REPEATABLE seed]: both olap and file 
scans keep the
+     * user's sample. Dropping it would replay over the full table and return 
rows the
+     * captured plan never sampled in.
+     */
+    private static String renderTableSample(Optional<TableSample> tableSample) 
{
+        if (!tableSample.isPresent()) {
+            return "";
+        }
+        TableSample sample = tableSample.get();
+        StringBuilder modifiers = new StringBuilder(" TABLESAMPLE(")
+                .append(sample.sampleValue)
+                .append(sample.isPercent ? " PERCENT)" : " ROWS)");
+        if (sample.seek >= 0) {
+            modifiers.append(" REPEATABLE ").append(sample.seek);
+        }
+        return modifiers.toString();
+    }
+
+    /**
+     * FOR VERSION AS OF / FOR TIME AS OF: a time-travel read must stay pinned 
to the
+     * captured version, otherwise the replay reads the current table 
contents. A numeric
+     * version is emitted as a literal, every other value (and all times) as a 
string.
+     */
+    private static String renderTableSnapshot(Optional<TableSnapshot> 
tableSnapshot) {
+        if (!tableSnapshot.isPresent()) {
+            return "";
+        }
+        TableSnapshot snapshot = tableSnapshot.get();
+        if (snapshot.getType() == TableSnapshot.VersionType.TIME) {
+            return " FOR TIME AS OF " + quoteSqlString(snapshot.getValue());
+        }
+        String value = snapshot.getValue();
+        if (value.matches("[0-9]+")) {
+            return " FOR VERSION AS OF " + value;
+        }
+        return " FOR VERSION AS OF " + quoteSqlString(value);
+    }
+
+    /**
+     * The @paramType(...) read parameters (incremental / branch / tag / 
options /
+     * snapshot / reset): the map form when the parameters carry key/value 
pairs, otherwise
+     * the bare identifier list form. Dropping them would replay a different 
(e.g.
+     * non-incremental) read than the captured one.
+     */
+    private static String renderScanParams(Optional<TableScanParams> 
scanParams) {
+        if (!scanParams.isPresent()) {
+            return "";
+        }
+        TableScanParams params = scanParams.get();
+        StringBuilder modifiers = new StringBuilder(" 
@").append(params.getParamType()).append('(');
+        Map<String, String> mapParams = params.getMapParams();
+        if (!mapParams.isEmpty()) {
+            boolean first = true;
+            for (Map.Entry<String, String> param : mapParams.entrySet()) {
+                if (!first) {
+                    modifiers.append(", ");
+                }
+                first = false;
+                modifiers.append(quoteIdentifier(param.getKey()))
+                        .append(" = ")
+                        .append(quoteSqlString(param.getValue()));
+            }
+        } else {
+            List<String> listParams = params.getListParams();
+            for (int i = 0; i < listParams.size(); i++) {
+                if (i > 0) {
+                    modifiers.append(", ");
+                }
+                modifiers.append(quoteIdentifier(listParams.get(i)));
+            }
+        }
+        return modifiers.append(')').toString();
+    }
+
+    /**
+     * A single-quoted SQL string literal: embedded single quotes are doubled 
AND
+     * backslashes are doubled, so the value cannot terminate the literal, 
change the frozen
+     * SQL structure, or decode to a DIFFERENT value. The DEFAULT sql_mode 
treats a backslash
+     * as an escape introducer (a semantic value such as release\next would 
otherwise
+     * reparse as release + newline + ext under that mode and select another 
external ref),
+     * so the literal doubles it; the SPM re-parses pin the DEFAULT mode (see
+     * SPMPlanner.parseStoredSelect), which makes the round trip exact under 
default AND
+     * NO_BACKSLASH_ESCAPES sessions alike.
+     */
+    static String quoteSqlString(String value) {
+        return "'" + value.replace("\\", "\\\\").replace("'", "''") + "'";
+    }
+
+    // Generate (LATERAL VIEW).
+
+    /**
+     * PhysicalGenerate: LATERAL VIEW clauses over the child relation - one 
clause per
+     * generator. The parser wraps every user-written LATERAL VIEW... into its
+     * own single-generator node, while MergeGenerates (for two independent
+     * views) folds stacked nodes into one node carrying several generators; 
the
+     * executor rolls several functions over each child row, i.e. a cartesian 
expansion,
+     * which is exactly what stacked LATERAL VIEWs express (MergeGenerates 
only merges
+     * when the upper view does not reference the lower view's output). 
LATERAL VIEW
+     * clauses attach to the child's COMPLETE query block: the child's WHERE / 
GROUP BY /
+     * HAVING / ORDER BY / LIMIT clauses must stay inside the lateral-view 
input (a
+     * derived table with LIMIT 10 limits the input, not the exploded rows). 
The
+     * generator output columns are registered on the same relation so parent 
operators
+     * can reference them; the alias is taken from the output slot's qualifier 
when it
+     * survived analysis, otherwise a per-decompile lv_N alias is used. 
Generate
+     * conjuncts (if any) are appended to the relation's WHERE.
+     */
+    @Override
+    public SQLRelation visitPhysicalGenerate(PhysicalGenerate<? extends Plan> 
generate, Void context) {
+        SQLRelation childRelation = process(generate.child(0));
+        List<Function> generators = generate.getGenerators();
+        List<Slot> outputs = generate.getGeneratorOutput();
+        if (generators.isEmpty() || generators.size() != outputs.size()) {
+            // post-binding plans keep exactly one output slot per generator 
(multi-column
+            // generators carry their columns in the expand alias project 
above)
+            throw new UnsupportedOperationException("SPM decompile generate: 
generator/output arity mismatch "
+                    + generators.size() + "/" + outputs.size());
+        }
+        // The LATERAL VIEW must attach to the child's COMPLETE query block. 
Attaching it
+        // to the bare FROM fragment (getFrom) would keep the child's WHERE / 
GROUP BY /
+        // HAVING / ORDER BY / LIMIT clauses on the OUTER relation, where they 
apply AFTER
+        // the explode: a derived table with LIMIT 10 would limit the exploded 
rows
+        // instead of the lateral-view input, a GROUP BY would regroup the 
generator
+        // output, and frozen replay could return rows the captured plan 
filtered out.
+        // A FROM-less child (e.g. LATERAL VIEW over "SELECT 1 AS x") needs 
the wrapper
+        // for the same reason.
+        SQLRelation relation;
+        String baseSql;
+        if (childRelation.getFrom().isEmpty() || childRelation.hasOwnBlock()) {

Review Comment:
   [P1] Keep input row assertions below lateral expansion
   
   For t(k INT, arr ARRAY<INT>) containing (1,[10]),(2,[]), capture and replay 
identical `SELECT k+1 AS c,lv.x AS x FROM ASSERT_ROWS (SELECT k,arr FROM t) a 
LATERAL VIEW explode(arr) lv AS x`:
   ```text
   Captured: Project -> Generate -> AssertNumRows(EQ1) -> Scan
   Frozen:   Project -> AssertNumRows(EQ1) -> Generate -> Scan
   ```
   This condition misses `childRelation.isAssertRows()`, so the else branch 
reuses the asserted scan and adds LATERAL VIEW to its FROM. The parent then 
serializes the assertion around the expanded SQL. The original must fail on two 
input rows, but explode leaves one row and replay returns (2,10). The 
planning-only probe, cap/order guards and fingerprints all accept it. 
Consume/wrap the asserted input before attaching LATERAL VIEW; cover both 
contracting and expanding generators.



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

Reply via email to