From e5c7033b71a8c0302fb3fa48790157f6cd70585e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sun, 5 Apr 2026 00:56:20 +0900 Subject: [PATCH] Implement FIRST/LAST and compound navigation for RPR Add FIRST(expr [, offset]) and LAST(expr [, offset]) navigation functions for DEFINE clauses. FIRST references the match start row, LAST references the current row. Includes slot swap elision, per-context DEFINE re-evaluation for match_start-dependent variables, and planner-level absorption disabling when FIRST or LAST-with-offset is present. Add compound navigation: PREV(FIRST()), NEXT(FIRST()), PREV(LAST()), NEXT(LAST()) per SQL standard 5.6.4. The parser flattens nested RPRNavExpr into a single compound node with two offsets. The executor computes the target position in two steps with range validation at each step. ruleutils restores the nested syntax for deparsing. Extend tuplestore trim to handle FIRST navigation via hasFirstNav/navFirstOffset, allowing mark advance based on oldest active context's matchStartRow. Compound offsets are computed by the unified nav_offset_walker in a single expression tree walk. Add "Nav Mark Lookahead" to EXPLAIN for FIRST-based navigation. --- doc/src/sgml/func/func-window.sgml | 53 ++ src/backend/commands/explain.c | 68 ++- src/backend/executor/execExpr.c | 55 +- src/backend/executor/execExprInterp.c | 155 ++++- src/backend/executor/execRPR.c | 152 ++++- src/backend/executor/nodeWindowAgg.c | 343 +++++++++--- src/backend/nodes/nodeFuncs.c | 3 + src/backend/optimizer/plan/createplan.c | 387 +++++++++++-- src/backend/optimizer/plan/rpr.c | 6 +- src/backend/parser/parse_func.c | 70 ++- src/backend/parser/parse_rpr.c | 112 +++- src/backend/utils/adt/ruleutils.c | 81 ++- src/backend/utils/adt/windowfuncs.c | 56 ++ src/include/catalog/pg_proc.dat | 12 + src/include/executor/execExpr.h | 8 +- src/include/nodes/execnodes.h | 12 +- src/include/nodes/parsenodes.h | 17 +- src/include/nodes/plannodes.h | 30 +- src/include/nodes/primnodes.h | 37 +- src/include/optimizer/rpr.h | 12 +- src/test/regress/expected/rpr.out | 652 +++++++++++++++++++++- src/test/regress/expected/rpr_base.out | 257 ++++++++- src/test/regress/expected/rpr_explain.out | 425 +++++++++++++- src/test/regress/sql/rpr.sql | 374 +++++++++++++ src/test/regress/sql/rpr_base.sql | 136 ++++- src/test/regress/sql/rpr_explain.sql | 242 +++++++- src/tools/pgindent/typedefs.list | 5 + 27 files changed, 3509 insertions(+), 251 deletions(-) diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml index 1b9b993a817..ab80690f7be 100644 --- a/doc/src/sgml/func/func-window.sgml +++ b/doc/src/sgml/func/func-window.sgml @@ -337,10 +337,63 @@ + + + + first + + first ( value anyelement [, offset bigint ] ) + anyelement + + + Returns the column value at the row offset + rows after the match start row; + returns NULL if the target row is beyond the current row. + offset defaults to 0 if omitted, referring to the + match start row itself. + offset must be a non-negative integer. + offset must not be NULL. + Can only be used in a DEFINE clause. + + + + + + + last + + last ( value anyelement [, offset bigint ] ) + anyelement + + + Returns the column value at the row offset + rows before the current row within the match; + returns NULL if the target row is before the match start row. + offset defaults to 0 if omitted, referring to the + current row itself. + offset must be a non-negative integer. + offset must not be NULL. + Can only be used in a DEFINE clause. + + + + + PREV and NEXT may wrap + FIRST or LAST for compound + navigation. For example, + PREV(FIRST(val, 2), 3) fetches the value at + 3 rows before the row that is 2 rows after the match start. + The reverse nesting (FIRST/LAST + wrapping PREV/NEXT) is not + permitted. Same-category nesting (e.g., + PREV inside PREV) is also + prohibited. + + The SQL standard defines a FROM FIRST or FROM LAST diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 1848de9de7a..221d9a49e0d 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -3255,14 +3255,66 @@ show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es) pfree(patternStr); } - /* Show navigation offsets for tuplestore trim */ - if (wagg->navMaxOffset == RPR_NAV_OFFSET_RETAIN_ALL) - ExplainPropertyText("Nav Mark Lookback", "retain all", es); - else if (wagg->navMaxOffset == RPR_NAV_OFFSET_NEEDS_EVAL) - ExplainPropertyText("Nav Mark Lookback", "runtime", es); - else - ExplainPropertyInteger("Nav Mark Lookback", NULL, - wagg->navMaxOffset, es); + /* + * Show navigation offsets for tuplestore trim. For EXPLAIN ANALYZE, + * use the executor-resolved values (which may differ from the plan + * when NEEDS_EVAL was resolved to FIXED or RETAIN_ALL at init). + */ + { + RPRNavOffsetKind maxKind = wagg->navMaxOffsetKind; + int64 maxOffset = wagg->navMaxOffset; + RPRNavOffsetKind firstKind = wagg->navFirstOffsetKind; + int64 firstOffset = wagg->navFirstOffset; + + if (es->analyze) + { + maxKind = planstate->navMaxOffsetKind; + maxOffset = planstate->navMaxOffset; + firstKind = planstate->navFirstOffsetKind; + firstOffset = planstate->navFirstOffset; + } + + switch (maxKind) + { + case RPR_NAV_OFFSET_NEEDS_EVAL: + ExplainPropertyText("Nav Mark Lookback", "runtime", es); + break; + case RPR_NAV_OFFSET_RETAIN_ALL: + ExplainPropertyText("Nav Mark Lookback", "retain all", es); + break; + case RPR_NAV_OFFSET_FIXED: + ExplainPropertyInteger("Nav Mark Lookback", NULL, + maxOffset, es); + break; + default: + elog(ERROR, "unrecognized RPR nav offset kind: %d", + maxKind); + break; + } + + if (wagg->hasFirstNav) + { + switch (firstKind) + { + case RPR_NAV_OFFSET_NEEDS_EVAL: + ExplainPropertyText("Nav Mark Lookahead", "runtime", + es); + break; + case RPR_NAV_OFFSET_RETAIN_ALL: + ExplainPropertyText("Nav Mark Lookahead", "retain all", + es); + break; + case RPR_NAV_OFFSET_FIXED: + ExplainPropertyInteger("Nav Mark Lookahead", NULL, + firstOffset, es); + break; + default: + elog(ERROR, "unrecognized RPR nav offset kind: %d", + firstKind); + break; + } + } + } } } diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index dbed4f48a0f..6349a564a98 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -1225,12 +1225,16 @@ ExecInitExprRec(Expr *node, ExprState *state, case T_RPRNavExpr: { /* - * RPR navigation functions (PREV/NEXT) are compiled into - * EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE opcodes instead of - * a normal function call. The SET opcode swaps - * ecxt_outertuple to the target row, the argument expression - * is compiled normally (reads from the swapped slot), and the - * RESTORE opcode restores the original slot. + * RPR navigation functions (PREV/NEXT/FIRST/LAST) are + * compiled into EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE + * opcodes instead of a normal function call. The SET opcode + * swaps ecxt_outertuple to the target row, the argument + * expression is compiled normally (reads from the swapped + * slot), and the RESTORE opcode restores the original slot. + * + * Default offset when offset_arg is NULL: PREV/NEXT: 1 + * (physical offset from currentpos) FIRST/LAST: 0 (logical + * offset from match boundary) */ RPRNavExpr *nav = (RPRNavExpr *) node; WindowAggState *winstate; @@ -1243,25 +1247,52 @@ ExecInitExprRec(Expr *node, ExprState *state, scratch.d.rpr_nav.winstate = winstate; scratch.d.rpr_nav.kind = nav->kind; - if (nav->offset_arg != NULL) + if (nav->kind >= RPR_NAV_PREV_FIRST) { /* - * Allocate storage for the runtime offset value. The - * offset expression is compiled below so it runs before - * EEOP_RPR_NAV_SET. + * Compound navigation: allocate array of 2 for inner [0] + * and outer [1] offsets. */ + Datum *offset_values = palloc_array(Datum, 2); + bool *offset_isnulls = palloc_array(bool, 2); + + /* Inner offset (default 0 for FIRST/LAST) */ + if (nav->offset_arg != NULL) + ExecInitExprRec(nav->offset_arg, state, + &offset_values[0], &offset_isnulls[0]); + else + { + offset_values[0] = Int64GetDatum(0); + offset_isnulls[0] = false; + } + + /* Outer offset (default 1 for PREV/NEXT) */ + if (nav->compound_offset_arg != NULL) + ExecInitExprRec(nav->compound_offset_arg, state, + &offset_values[1], &offset_isnulls[1]); + else + { + offset_values[1] = Int64GetDatum(1); + offset_isnulls[1] = false; + } + + scratch.d.rpr_nav.offset_value = offset_values; + scratch.d.rpr_nav.offset_isnull = offset_isnulls; + } + else if (nav->offset_arg != NULL) + { + /* Simple navigation with explicit offset */ Datum *offset_value = palloc_object(Datum); bool *offset_isnull = palloc_object(bool); - /* Compile the offset expression into the temp storage */ ExecInitExprRec(nav->offset_arg, state, offset_value, offset_isnull); - scratch.d.rpr_nav.offset_value = offset_value; scratch.d.rpr_nav.offset_isnull = offset_isnull; } else { + /* Simple navigation with default offset */ scratch.d.rpr_nav.offset_value = NULL; scratch.d.rpr_nav.offset_isnull = NULL; } diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index e41faa95be3..2ec579732cc 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -5942,7 +5942,35 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans, } /* - * Evaluate RPR PREV/NEXT navigation: swap slot to target row. + * Extract compound (outer) offset from step data. + * For compound nav, offset_value is an array: [0]=inner, [1]=outer. + * Returns the outer offset; errors on NULL or negative. + * Default is 1 (like PREV/NEXT implicit offset). + */ +static int64 +rpr_nav_get_compound_offset(ExprEvalStep *op) +{ + int64 val; + + Assert(op->d.rpr_nav.offset_value != NULL); + + if (op->d.rpr_nav.offset_isnull[1]) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("row pattern navigation offset must not be null"))); + + val = DatumGetInt64(op->d.rpr_nav.offset_value[1]); + + if (val < 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("row pattern navigation offset must not be negative"))); + + return val; +} + +/* + * Evaluate RPR navigation (PREV/NEXT/FIRST/LAST): swap slot to target row. * * Saves the current outertuple into winstate for later restore, computes * the target row position, fetches the corresponding slot from the @@ -5963,27 +5991,35 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) winstate->nav_saved_outertuple = econtext->ecxt_outertuple; /* - * Determine the unsigned offset. For 2-arg PREV/NEXT the offset - * expression has already been evaluated into offset_value. NULL or - * negative offsets are errors per the SQL standard (ISO/IEC 9075-2, - * Subclause 5.6.2). + * Determine the inner offset. NULL or negative offsets are errors per + * the SQL standard. + * + * Default offset when offset_arg is NULL: PREV/NEXT: 1 (standard 5.6.2) + * FIRST/LAST and compound: 0 for inner, 1 for outer */ if (op->d.rpr_nav.offset_value != NULL) { if (*op->d.rpr_nav.offset_isnull) ereport(ERROR, (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), - errmsg("PREV/NEXT offset must not be null"))); + errmsg("row pattern navigation offset must not be null"))); offset = DatumGetInt64(*op->d.rpr_nav.offset_value); if (offset < 0) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("PREV/NEXT offset must not be negative"))); + errmsg("row pattern navigation offset must not be negative"))); } else - offset = 1; + { + /* Default offset: 1 for simple PREV/NEXT, 0 otherwise */ + if (op->d.rpr_nav.kind == RPR_NAV_PREV || + op->d.rpr_nav.kind == RPR_NAV_NEXT) + offset = 1; + else + offset = 0; + } /* * Calculate target position based on navigation direction. On overflow, @@ -5999,8 +6035,107 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) if (pg_add_s64_overflow(winstate->currentpos, offset, &target_pos)) target_pos = -1; break; + case RPR_NAV_FIRST: + /* FIRST: offset from match_start, clamped to currentpos */ + if (pg_add_s64_overflow(winstate->nav_match_start, offset, &target_pos)) + target_pos = -1; + else if (target_pos > winstate->currentpos) + target_pos = -1; /* beyond current match range */ + break; + case RPR_NAV_LAST: + /* LAST: offset backward from currentpos, clamped to match_start */ + if (pg_sub_s64_overflow(winstate->currentpos, offset, &target_pos)) + target_pos = -1; + else if (target_pos < winstate->nav_match_start) + target_pos = -1; /* before match_start */ + break; + + case RPR_NAV_PREV_FIRST: + case RPR_NAV_NEXT_FIRST: + { + int64 compound_offset; + int64 inner_pos; + + /* Inner: match_start + offset */ + if (pg_add_s64_overflow(winstate->nav_match_start, offset, &inner_pos)) + { + target_pos = -1; + break; + } + if (inner_pos > winstate->currentpos || inner_pos < 0) + { + target_pos = -1; + break; + } + + /* Outer offset */ + compound_offset = rpr_nav_get_compound_offset(op); + + /* Apply outer: PREV subtracts, NEXT adds */ + if (op->d.rpr_nav.kind == RPR_NAV_PREV_FIRST) + { + if (pg_sub_s64_overflow(inner_pos, compound_offset, &target_pos)) + target_pos = -1; + } + else + { + if (pg_add_s64_overflow(inner_pos, compound_offset, &target_pos)) + target_pos = -1; + } + } + break; + + case RPR_NAV_PREV_LAST: + case RPR_NAV_NEXT_LAST: + { + int64 compound_offset; + int64 inner_pos; + + /* Inner: currentpos - offset */ + if (pg_sub_s64_overflow(winstate->currentpos, offset, &inner_pos)) + { + target_pos = -1; + break; + } + if (inner_pos < winstate->nav_match_start) + { + target_pos = -1; + break; + } + + /* Outer offset */ + compound_offset = rpr_nav_get_compound_offset(op); + + /* Apply outer: PREV subtracts, NEXT adds */ + if (op->d.rpr_nav.kind == RPR_NAV_PREV_LAST) + { + if (pg_sub_s64_overflow(inner_pos, compound_offset, &target_pos)) + target_pos = -1; + } + else + { + if (pg_add_s64_overflow(inner_pos, compound_offset, &target_pos)) + target_pos = -1; + } + } + break; + default: + elog(ERROR, "unrecognized RPR navigation kind: %d", + op->d.rpr_nav.kind); + break; } + /* + * Slot swap elision: if target_pos is the current row, skip the + * tuplestore fetch and slot swap entirely. This benefits LAST(expr), + * PREV(expr, 0), NEXT(expr, 0), and similar cases. + * + * We must still set nav_saved_outertuple (done above) so that + * EEOP_RPR_NAV_RESTORE is a harmless no-op. + */ + if (target_pos == winstate->currentpos) + return; + /* Fetch target row slot (returns nav_null_slot if out of range) */ target_slot = ExecRPRNavGetSlot(winstate, target_pos); @@ -6015,9 +6150,11 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) } /* - * Evaluate RPR PREV/NEXT navigation: restore slot to original row. + * Evaluate RPR navigation: restore slot to original row. * * Restores econtext->ecxt_outertuple from the saved slot in winstate. + * When slot swap was elided (target == currentpos), this is a harmless + * no-op since saved and current slots are identical. * The caller is responsible for updating any local slot cache. */ void diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c index 995acdd7be5..60f0d8b2fa1 100644 --- a/src/backend/executor/execRPR.c +++ b/src/backend/executor/execRPR.c @@ -48,6 +48,7 @@ * - src/include/nodes/plannodes.h (plan node definitions) * - src/include/nodes/execnodes.h (execution state definitions) * - src/include/optimizer/rpr.h (types and constants) + * - src/backend/optimizer/plan/createplan.c (nav offset computation) * * ============================================================================ * @@ -609,22 +610,69 @@ * result = ExecEvalExpr(defineClause[i]) * varMatched[i] = (not null and true) * - * To support row navigation operators such as PREV() and NEXT(), + * To support row navigation operators (PREV, NEXT, FIRST, LAST), * a 1-slot model is used: only ecxt_outertuple is set to the current - * row. PREV/NEXT navigation is handled by EEOP_RPR_NAV_SET/RESTORE - * opcodes emitted during DEFINE expression compilation: + * row. Navigation is handled by EEOP_RPR_NAV_SET/RESTORE opcodes + * emitted during DEFINE expression compilation: * * NAV_SET: save ecxt_outertuple, swap in target row via nav_slot * (evaluate): argument expression reads from swapped slot * NAV_RESTORE: restore original ecxt_outertuple * + * Compound navigation (PREV(FIRST()), NEXT(FIRST()), PREV(LAST()), + * NEXT(LAST())) is flattened by the parser into a single RPRNavExpr + * with a compound kind (RPR_NAV_PREV_FIRST, etc.). The executor + * computes the target position in two steps: first the inner reference + * point (match_start + N or currentpos - N) with match-range validation, + * then the outer adjustment (± M) with partition-range validation. + * If either step is out of range, the result is NULL. + * * nav_slot caches the last fetched position (nav_slot_pos) to avoid - * redundant tuplestore lookups when multiple PREV/NEXT calls target + * redundant tuplestore lookups when multiple navigation calls target * the same row. * * The varMatched array is referenced later in Phase 1 (Match). * - * VI-4. ExecRPRProcessRow(): 3-Phase Processing + * VI-4. Per-Context Re-evaluation (match_start-dependent variables) + * + * DEFINE variables that depend on match_start (those containing FIRST, + * LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST) + * are identified at plan time via defineMatchStartDependent. The shared + * evaluation in nfa_evaluate_row() uses the head context's matchStartRow + * for FIRST/LAST base position. + * + * When processing a context whose matchStartRow differs from the shared + * value, nfa_reevaluate_dependent_vars() temporarily sets nav_match_start + * to that context's matchStartRow and re-evaluates only the dependent + * variables. No restore is needed because contexts are ordered by + * matchStartRow (ascending), so no later context shares the head's value. + * + * VI-5. Tuplestore Mark and Trim (nodeWindowAgg.c) + * + * Navigation functions require access to past rows via the tuplestore. + * To allow tuplestore_trim() to free rows that are no longer reachable, + * the planner computes two offsets (see compute_nav_offsets): + * + * navMaxOffset (Nav Mark Lookback): + * Maximum backward reach from currentpos. Contributed by PREV, + * LAST-with-offset, and compound PREV_LAST/NEXT_LAST. + * Mark position: currentpos - navMaxOffset. + * + * navFirstOffset (Nav Mark Lookahead): + * Minimum forward offset from match_start. Contributed by FIRST + * and compound PREV_FIRST/NEXT_FIRST. Can be negative when + * compound PREV_FIRST looks before match_start. + * Mark position: oldest_context->matchStartRow + navFirstOffset. + * + * The actual mark is set to: min(lookback_mark, lookahead_mark). + * This ensures all rows reachable by any navigation function are retained. + * + * When offsets contain non-constant expressions (Param), the planner sets + * navMaxOffsetKind/navFirstOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL and the + * executor evaluates them at init time. On overflow, the kind is set to + * RPR_NAV_OFFSET_RETAIN_ALL, disabling trim for that dimension. + * + * VI-6. ExecRPRProcessRow(): 3-Phase Processing * * NFA processing for a single row is divided into three phases: * @@ -707,6 +755,21 @@ * * VIII-3. Absorption Conditions * + * Planner-time prerequisites (all must hold for absorption to be enabled): + * + * (a) SKIP PAST LAST ROW. SKIP TO NEXT ROW creates overlapping + * contexts that cannot be safely absorbed. + * (b) Unbounded frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED + * FOLLOWING). Limited frames apply differently to each context, + * breaking the monotonicity principle. + * (c) No match_start-dependent navigation in DEFINE. FIRST, + * LAST-with-offset, and compound navigation referencing match_start + * (PREV_FIRST, NEXT_FIRST, PREV_LAST/NEXT_LAST with offset) + * cause different contexts to evaluate to different values for the + * same row, breaking monotonicity. + * + * Runtime conditions (evaluated per context pair): + * * (1) The pattern is marked as isAbsorbable (see IV-5) * (2) allStatesAbsorbable of the target context is true * (3) An earlier context "covers" all states of the target @@ -979,6 +1042,19 @@ * Only INITIAL is supported, searching only for matches starting at each * row position pos. * + * X-4. Bounded Frame Handling + * + * When the frame is bounded (e.g., ROWS BETWEEN CURRENT ROW AND 5 + * FOLLOWING), ExecRPRProcessRow receives hasLimitedFrame=true and + * frameOffset indicating the upper bound. After the advance phase, + * any context whose match has exceeded the frame boundary + * (currentPos - matchStartRow >= frameOffset + 1) is finalized early. + * This prevents matches from extending beyond the window frame. + * + * Note that bounded frames also disable context absorption at the + * planner level (see VIII-3(b)), since the frame boundary breaks the + * monotonicity assumption required for correct absorption. + * * Chapter XI Worked Example: Full Execution Trace * ============================================================================ * @@ -1330,6 +1406,14 @@ * nfa_advance_var execRPR.c VAR handling * nfa_add_state_unique execRPR.c Deduplication * nfa_states_covered execRPR.c Absorption check + * nfa_reevaluate_dependent_vars execRPR.c Per-context re-eval + * ExecRPRGetHeadContext execRPR.c Context lookup + * ExecRPRFreeContext execRPR.c Context deallocation + * ExecRPRCleanupDeadContexts execRPR.c Dead context cleanup + * ExecRPRFinalizeAllContexts execRPR.c Partition-end finalize + * ExecRPRRecordContextSuccess execRPR.c Stats: match success + * ExecRPRRecordContextFailure execRPR.c Stats: match failure + * compute_nav_offsets createplan.c Trim offset computation * * Appendix B. Data Structure Relationship Diagram * ============================================================================ @@ -2989,6 +3073,56 @@ ExecRPRRecordContextFailure(WindowAggState *winstate, int64 failedLen) } } +/* + * nfa_reevaluate_dependent_vars + * Re-evaluate match_start-dependent DEFINE variables for a specific + * context whose matchStartRow differs from the shared evaluation's + * nav_match_start. + * + * Only variables in defineMatchStartDependent are re-evaluated. The + * current row's slot (ecxt_outertuple) must already be set up by + * nfa_evaluate_row(). + */ +static void +nfa_reevaluate_dependent_vars(WindowAggState *winstate, RPRNFAContext *ctx, + int64 currentPos) +{ + ExprContext *econtext = winstate->ss.ps.ps_ExprContext; + int64 saved_match_start = winstate->nav_match_start; + int64 saved_pos = winstate->currentpos; + int varIdx = 0; + ListCell *lc; + + /* Temporarily set nav_match_start and currentpos for FIRST/LAST */ + winstate->nav_match_start = ctx->matchStartRow; + winstate->currentpos = currentPos; + + /* Invalidate nav_slot cache since match_start changed */ + winstate->nav_slot_pos = -1; + + foreach(lc, winstate->defineClauseList) + { + if (bms_is_member(varIdx, winstate->defineMatchStartDependent)) + { + ExprState *exprState = (ExprState *) lfirst(lc); + Datum result; + bool isnull; + + result = ExecEvalExpr(exprState, econtext, &isnull); + winstate->nfaVarMatched[varIdx] = (!isnull && DatumGetBool(result)); + } + + varIdx++; + if (varIdx >= list_length(winstate->defineVariableList)) + break; + } + + /* Restore original match_start, currentpos, and invalidate cache */ + winstate->nav_match_start = saved_match_start; + winstate->currentpos = saved_pos; + winstate->nav_slot_pos = -1; +} + /* * ExecRPRProcessRow * @@ -3003,6 +3137,7 @@ ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos, { RPRNFAContext *ctx; bool *varMatched = winstate->nfaVarMatched; + bool hasDependent = !bms_is_empty(winstate->defineMatchStartDependent); /* Allow query cancellation once per row for simple/low-state patterns */ CHECK_FOR_INTERRUPTS(); @@ -3029,6 +3164,13 @@ ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos, } } + /* + * If this context has a different matchStartRow than the one used in + * the shared evaluation, re-evaluate match_start-dependent variables + * with this context's matchStartRow. + */ + if (hasDependent && ctx->matchStartRow != winstate->nav_match_start) + nfa_reevaluate_dependent_vars(winstate, ctx, currentPos); nfa_match(winstate, ctx, varMatched); ctx->lastProcessedRow = currentPos; } diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 9787ef7756f..cdbe356abd7 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -34,6 +34,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "common/int.h" #include "catalog/objectaccess.h" #include "catalog/pg_aggregate.h" #include "catalog/pg_collation_d.h" @@ -245,8 +246,8 @@ static void update_reduced_frame(WindowObject winobj, int64 pos); static bool nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched); /* Forward declarations - navigation offset evaluation */ -static bool collect_prev_offset_walker(Node *node, List **offsets); static void eval_nav_max_offset(WindowAggState *winstate, List *defineClause); +static void eval_nav_first_offset(WindowAggState *winstate, List *defineClause); /* * Not null info bit array consists of 2-bit items @@ -932,28 +933,7 @@ eval_windowaggregates(WindowAggState *winstate) * head, so that tuplestore can discard unnecessary rows. */ if (agg_winobj->markptr >= 0) - { - int64 markpos = winstate->frameheadpos; - - if (rpr_is_defined(winstate)) - { - /* - * If RPR is used, PREV may need to look at rows before the frame - * head. Adjust mark by navMaxOffset if known, otherwise retain - * from position 0. - */ - if (winstate->navMaxOffset >= 0) - { - if (markpos > winstate->navMaxOffset) - markpos -= winstate->navMaxOffset; - else - markpos = 0; - } - else - markpos = 0; - } - WinSetMarkPosition(agg_winobj, markpos); - } + WinSetMarkPosition(agg_winobj, winstate->frameheadpos); /* * Now restart the aggregates that require it. @@ -1279,15 +1259,15 @@ prepare_tuplestore(WindowAggState *winstate) if (winstate->nav_winobj) { /* - * Allocate mark and read pointers for PREV/NEXT navigation. + * Allocate mark and read pointers for RPR navigation. * - * If navMaxOffset >= 0, we advance the mark to (currentpos - - * navMaxOffset) as rows are processed, allowing tuplestore_trim() to - * free rows that are no longer reachable. + * If navMaxOffsetKind == RPR_NAV_OFFSET_FIXED, we advance the mark + * based on (currentpos - navMaxOffset) and optionally + * (nfaContext->matchStartRow + navFirstOffset), allowing + * tuplestore_trim() to free rows that are no longer reachable. * - * If navMaxOffset < 0 (RPR_NAV_OFFSET_NEEDS_EVAL or - * RPR_NAV_OFFSET_RETAIN_ALL), the mark stays at 0, retaining the - * entire partition in the tuplestore. + * RPR_NAV_OFFSET_NEEDS_EVAL is resolved at executor init; by this + * point it is either FIXED or RETAIN_ALL. */ winstate->nav_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); @@ -2527,18 +2507,40 @@ ExecWindowAgg(PlanState *pstate) /* * Advance RPR navigation mark pointer if possible, so that - * tuplestore_trim() can free rows no longer reachable by PREV. + * tuplestore_trim() can free rows no longer reachable by navigation. */ if (winstate->nav_winobj && winstate->rpPattern != NULL && - winstate->navMaxOffset >= 0) + winstate->navMaxOffsetKind == RPR_NAV_OFFSET_FIXED) { int64 navmarkpos; + /* Backward reach from PREV/LAST/compound PREV_LAST/NEXT_LAST */ if (winstate->currentpos > winstate->navMaxOffset) navmarkpos = winstate->currentpos - winstate->navMaxOffset; else navmarkpos = 0; + + /* + * If FIRST is used, also consider match_start + navFirstOffset. + * The oldest active context (nfaContext) has the smallest + * matchStartRow. + */ + if (winstate->hasFirstNav && + winstate->navFirstOffsetKind == RPR_NAV_OFFSET_FIXED && + winstate->nfaContext != NULL) + { + int64 firstreach; + + if (winstate->navFirstOffset > -winstate->nfaContext->matchStartRow) + firstreach = winstate->nfaContext->matchStartRow + + winstate->navFirstOffset; + else + firstreach = 0; + if (firstreach < navmarkpos) + navmarkpos = firstreach; + } + if (navmarkpos > winstate->nav_winobj->markpos) WinSetMarkPosition(winstate->nav_winobj, navmarkpos); } @@ -2779,6 +2781,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->nav_null_slot = ExecStoreAllNullTuple(winstate->nav_null_slot); winstate->nav_saved_outertuple = NULL; + winstate->nav_match_start = 0; } /* @@ -2988,10 +2991,20 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->rpSkipTo = node->rpSkipTo; /* Set up row pattern recognition PATTERN clause (compiled NFA) */ winstate->rpPattern = node->rpPattern; - /* Set up max PREV offset for tuplestore trim */ + /* Set up nav offsets for tuplestore trim */ + winstate->navMaxOffsetKind = node->navMaxOffsetKind; winstate->navMaxOffset = node->navMaxOffset; - if (winstate->navMaxOffset == RPR_NAV_OFFSET_NEEDS_EVAL) + if (winstate->navMaxOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL) eval_nav_max_offset(winstate, node->defineClause); + winstate->hasFirstNav = node->hasFirstNav; + winstate->navFirstOffsetKind = node->navFirstOffsetKind; + winstate->navFirstOffset = node->navFirstOffset; + if (winstate->hasFirstNav && + winstate->navFirstOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL) + eval_nav_first_offset(winstate, node->defineClause); + + /* Copy match_start dependency bitmapset for per-context evaluation */ + winstate->defineMatchStartDependent = bms_copy(node->defineMatchStartDependent); /* Calculate NFA state size and allocate cycle detection bitmap */ if (node->rpPattern != NULL) @@ -3903,84 +3916,254 @@ put_notnull_info(WindowObject winobj, int64 pos, int argno, bool isnull) } /* - * collect_prev_offset_walker - * Walk expression tree to collect PREV offset_arg expressions. + * eval_nav_offset_helper + * Evaluate an offset expression at executor init time for trim + * optimization. Returns the offset value, or 0 for NULL/negative + * (these will cause a runtime error during actual navigation, so the + * trim value is irrelevant). + */ +static int64 +eval_nav_offset_helper(WindowAggState *winstate, Expr *offset_expr, + int64 defaultOffset) +{ + ExprContext *econtext = winstate->ss.ps.ps_ExprContext; + ExprState *estate; + Datum val; + bool isnull; + int64 offset; + + if (offset_expr == NULL) + return defaultOffset; + + estate = ExecInitExpr(offset_expr, (PlanState *) winstate); + val = ExecEvalExprSwitchContext(estate, econtext, &isnull); + + if (isnull) + return 0; + + offset = DatumGetInt64(val); + if (offset < 0) + return 0; + + return offset; +} + +typedef struct +{ + WindowAggState *winstate; + int64 maxOffset; + bool overflow; /* true if overflow detected */ +} EvalNavMaxContext; + +/* + * eval_nav_max_offset_walker + * Walk expression tree evaluating backward-reach offsets at runtime. + * + * Handles simple PREV, LAST-with-offset, and compound PREV_LAST/NEXT_LAST. */ static bool -collect_prev_offset_walker(Node *node, List **offsets) +eval_nav_max_offset_walker(Node *node, void *ctx) { + EvalNavMaxContext *context = (EvalNavMaxContext *) ctx; + if (node == NULL) return false; + /* Short-circuit if overflow already detected */ + if (context->overflow) + return false; + if (IsA(node, RPRNavExpr)) { RPRNavExpr *nav = (RPRNavExpr *) node; + int64 reach = 0; + + if (nav->kind == RPR_NAV_PREV) + { + reach = eval_nav_offset_helper(context->winstate, + nav->offset_arg, 1); + } + else if (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL) + { + reach = eval_nav_offset_helper(context->winstate, + nav->offset_arg, 0); + } + else if (nav->kind == RPR_NAV_PREV_LAST || + nav->kind == RPR_NAV_NEXT_LAST) + { + int64 inner = eval_nav_offset_helper(context->winstate, + nav->offset_arg, 0); + int64 outer = eval_nav_offset_helper(context->winstate, + nav->compound_offset_arg, 1); - if (nav->kind == RPR_NAV_PREV && nav->offset_arg != NULL) - *offsets = lappend(*offsets, nav->offset_arg); + if (nav->kind == RPR_NAV_PREV_LAST) + { + if (pg_add_s64_overflow(inner, outer, &reach)) + { + context->overflow = true; + return false; + } + } + else + reach = (inner > outer) ? inner - outer : 0; + } - /* Don't walk into RPRNavExpr children */ - return false; + if (reach > context->maxOffset) + context->maxOffset = reach; + + return false; /* don't walk into children */ } - return expression_tree_walker(node, collect_prev_offset_walker, offsets); + return expression_tree_walker(node, eval_nav_max_offset_walker, ctx); } /* * eval_nav_max_offset - * Evaluate non-constant PREV offsets at executor init time. + * Evaluate non-constant backward-reach offsets at executor init time. + * + * Called when the planner set navMaxOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL + * because some offset in PREV, LAST-with-offset, or compound PREV_LAST/ + * NEXT_LAST contains a parameter or non-foldable expression. * - * Called when the planner set navMaxOffset to RPR_NAV_OFFSET_NEEDS_EVAL - * because some PREV offset contains a parameter or non-foldable expression. - * Walks the original defineClause expression trees, compiles and evaluates - * each PREV offset_arg, and stores the maximum in winstate->navMaxOffset. + * On overflow, sets navMaxOffsetKind to RPR_NAV_OFFSET_RETAIN_ALL so that + * tuplestore trim is disabled for backward navigation. */ static void eval_nav_max_offset(WindowAggState *winstate, List *defineClause) { - ExprContext *econtext = winstate->ss.ps.ps_ExprContext; - List *offsets = NIL; + EvalNavMaxContext ctx; ListCell *lc; - int64 maxOffset = 0; - /* Collect all PREV offset expressions from DEFINE clause */ + ctx.winstate = winstate; + ctx.maxOffset = 0; + ctx.overflow = false; + foreach(lc, defineClause) { TargetEntry *te = (TargetEntry *) lfirst(lc); - collect_prev_offset_walker((Node *) te->expr, &offsets); + eval_nav_max_offset_walker((Node *) te->expr, &ctx); } - /* Evaluate each offset and find maximum */ - foreach(lc, offsets) + if (ctx.overflow) { - Expr *offset_expr = (Expr *) lfirst(lc); - ExprState *estate; - Datum val; - bool isnull; - int64 offset; + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL; + winstate->navMaxOffset = 0; + } + else + { + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->navMaxOffset = ctx.maxOffset; + } +} - estate = ExecInitExpr(offset_expr, (PlanState *) winstate); - val = ExecEvalExprSwitchContext(estate, econtext, &isnull); +typedef struct +{ + WindowAggState *winstate; + int64 minOffset; + bool found; +} EvalNavFirstContext; - /* - * NULL or negative offsets will cause a runtime error when PREV is - * actually evaluated. For trim purposes, treat them as 0. - */ - if (isnull) - continue; +/* + * eval_nav_first_offset_walker + * Walk expression tree evaluating forward-from-match_start offsets. + * + * Handles simple FIRST and compound PREV_FIRST/NEXT_FIRST. + */ +static bool +eval_nav_first_offset_walker(Node *node, void *ctx) +{ + EvalNavFirstContext *context = (EvalNavFirstContext *) ctx; - offset = DatumGetInt64(val); - if (offset < 0) - continue; + if (node == NULL) + return false; + + if (IsA(node, RPRNavExpr)) + { + RPRNavExpr *nav = (RPRNavExpr *) node; + int64 combined = INT64_MAX; - if (offset > maxOffset) - maxOffset = offset; + if (nav->kind == RPR_NAV_FIRST) + { + context->found = true; + combined = eval_nav_offset_helper(context->winstate, + nav->offset_arg, 0); + } + else if (nav->kind == RPR_NAV_PREV_FIRST || + nav->kind == RPR_NAV_NEXT_FIRST) + { + int64 inner = eval_nav_offset_helper(context->winstate, + nav->offset_arg, 0); + int64 outer = eval_nav_offset_helper(context->winstate, + nav->compound_offset_arg, 1); + + context->found = true; + if (nav->kind == RPR_NAV_PREV_FIRST) + { + /* + * combined = inner - outer. Both are non-negative, so the + * result >= -INT64_MAX, which cannot underflow int64. + */ + combined = inner - outer; + } + else + { + /* + * NEXT_FIRST: combined = inner + outer. This can overflow, + * but the result is always >= 0, so it never updates + * minOffset (which tracks the minimum). Clamp to INT64_MAX + * on overflow. + */ + if (pg_add_s64_overflow(inner, outer, &combined)) + combined = INT64_MAX; + } + } + + if (combined < context->minOffset) + context->minOffset = combined; + + return false; } - winstate->navMaxOffset = maxOffset; + return expression_tree_walker(node, eval_nav_first_offset_walker, ctx); +} + +/* + * eval_nav_first_offset + * Evaluate non-constant forward-from-match_start offsets at executor + * init time. + * + * Called when the planner set navFirstOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL + * because some offset in FIRST or compound PREV_FIRST/NEXT_FIRST contains + * a parameter or non-foldable expression. + */ +static void +eval_nav_first_offset(WindowAggState *winstate, List *defineClause) +{ + EvalNavFirstContext ctx; + ListCell *lc; + + ctx.winstate = winstate; + ctx.minOffset = INT64_MAX; + ctx.found = false; + + foreach(lc, defineClause) + { + TargetEntry *te = (TargetEntry *) lfirst(lc); - list_free(offsets); + eval_nav_first_offset_walker((Node *) te->expr, &ctx); + } + + if (ctx.found && ctx.minOffset < INT64_MAX) + { + winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->navFirstOffset = ctx.minOffset; + } + else + { + winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->navFirstOffset = 0; + } } /* @@ -4210,8 +4393,14 @@ update_reduced_frame(WindowObject winobj, int64 pos) /* * Evaluate variables for this row - done only once, shared by all - * contexts + * contexts. + * + * Set nav_match_start to the head context's matchStartRow for + * FIRST/LAST navigation. Match_start-dependent variables (FIRST, + * LAST-with-offset) are re-evaluated per-context in ExecRPRProcessRow + * when matchStartRow differs. */ + winstate->nav_match_start = targetCtx->matchStartRow; rowExists = nfa_evaluate_row(winobj, currentPos, winstate->nfaVarMatched); /* No more rows in partition? Finalize all contexts */ @@ -4299,8 +4488,8 @@ register_result: * varMatched[i] = true if variable i matched at current row. * * Uses 1-slot model: only ecxt_outertuple is set to the current row. - * PREV/NEXT navigation is handled by EEOP_RPR_NAV_SET/RESTORE opcodes - * during expression evaluation, which temporarily swap the slot. + * PREV/NEXT/FIRST/LAST navigation is handled by EEOP_RPR_NAV_SET/RESTORE + * opcodes during expression evaluation, which temporarily swap the slot. */ static bool nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched) diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d2f19584070..66d37b78898 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2207,6 +2207,8 @@ expression_tree_walker_impl(Node *node, return true; if (expr->offset_arg && WALK(expr->offset_arg)) return true; + if (expr->compound_offset_arg && WALK(expr->compound_offset_arg)) + return true; } break; case T_SubscriptingRef: @@ -3146,6 +3148,7 @@ expression_tree_mutator_impl(Node *node, FLATCOPY(newnode, nav, RPRNavExpr); MUTATE(newnode->arg, nav->arg, Expr *); MUTATE(newnode->offset_arg, nav->offset_arg, Expr *); + MUTATE(newnode->compound_offset_arg, nav->compound_offset_arg, Expr *); return (Node *) newnode; } break; diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 8ee3ccf6d0d..02d511269ab 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -16,6 +16,7 @@ */ #include "postgres.h" +#include "common/int.h" #include "access/sysattr.h" #include "access/transam.h" #include "catalog/pg_class.h" @@ -292,6 +293,7 @@ static WindowAgg *make_windowagg(List *tlist, WindowClause *wc, List *runCondition, RPSkipTo rpSkipTo, RPRPattern *compiledPattern, List *defineClause, + Bitmapset *defineMatchStartDependent, List *qual, bool topWindow, Plan *lefttree); static Group *make_group(List *tlist, List *qual, int numGroupCols, @@ -2462,19 +2464,72 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path) } /* - * nav_max_offset_walker - * Walk expression tree to find the maximum PREV offset. + * NavOffsetContext - context for compute_nav_offsets walker. * - * Only PREV is relevant for tuplestore trim since it looks backward; - * NEXT looks forward and never references already-trimmed rows. + * Collects both backward reach (PREV, LAST-with-offset, compound + * PREV_LAST/NEXT_LAST) and forward-from-match-start reach (FIRST, + * compound PREV_FIRST/NEXT_FIRST) in a single tree walk. + */ +typedef struct NavOffsetContext +{ + int64 maxOffset; /* max PREV/LAST backward offset (>= 0) */ + bool maxNeedsEval; /* non-constant PREV/LAST offset found */ + bool maxOverflow; /* constant offset overflow detected */ + int64 firstOffset; /* min FIRST offset (>= 0), or -1 if none */ + bool hasFirst; /* any FIRST node found */ + bool firstNeedsEval; /* non-constant FIRST offset found */ +} NavOffsetContext; + +/* + * Helper: extract constant offset from an expression, handling NULL/negative. + * If expr is NULL, returns defaultOffset. + * Returns true if constant, false if non-constant (Param, cast, etc.). + */ +static bool +extract_const_offset(Expr *expr, int64 defaultOffset, int64 *result) +{ + if (expr == NULL) + { + *result = defaultOffset; + return true; + } + + if (IsA(expr, Const)) + { + Const *c = (Const *) expr; + + if (c->constisnull) + *result = 0; /* runtime error; safe placeholder */ + else + { + *result = DatumGetInt64(c->constvalue); + if (*result < 0) + *result = 0; /* runtime error; safe placeholder */ + } + return true; + } + + return false; /* non-constant */ +} + +/* + * nav_offset_walker + * Expression tree walker for compute_nav_offsets. + * + * For each RPRNavExpr found, extract its constant offset(s) and update the + * NavOffsetContext with the maximum backward reach (maxOffset) and minimum + * forward reach (firstOffset). Handles simple navigation (PREV, NEXT, + * FIRST, LAST) and compound forms (PREV_FIRST, NEXT_FIRST, PREV_LAST, + * NEXT_LAST) by combining inner and outer offsets. * - * Returns true (to stop walking) if a non-constant PREV offset is found, - * in which case *maxOffset is set to -1. Otherwise accumulates the - * maximum constant offset value. + * Non-constant offsets set maxNeedsEval or firstNeedsEval. Overflow sets + * maxOverflow or firstOverflow for RETAIN_ALL fallback. */ static bool -nav_max_offset_walker(Node *node, int64 *maxOffset) +nav_offset_walker(Node *node, void *ctx) { + NavOffsetContext *context = (NavOffsetContext *) ctx; + if (node == NULL) return false; @@ -2482,81 +2537,294 @@ nav_max_offset_walker(Node *node, int64 *maxOffset) { RPRNavExpr *nav = (RPRNavExpr *) node; - /* Only PREV looks backward; NEXT is irrelevant for trim */ - if (nav->kind == RPR_NAV_PREV) + /* + * Simple PREV(v, N) and LAST(v, N): backward reach from currentpos. + * LAST without offset = currentpos, no backward reach. NEXT: forward + * only, irrelevant for trim. + */ + if (nav->kind == RPR_NAV_PREV || + (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL)) { - int64 offset; - - if (nav->offset_arg == NULL) + if (!context->maxNeedsEval) { - /* 1-arg form: implicit offset of 1 */ - offset = 1; + int64 offset; + + if (extract_const_offset(nav->offset_arg, 1, &offset)) + { + if (offset > context->maxOffset) + context->maxOffset = offset; + } + else + context->maxNeedsEval = true; } - else if (IsA(nav->offset_arg, Const)) + } + + /* + * Simple FIRST(v, N): forward reach from match_start. Smaller N means + * older rows needed. + */ + if (nav->kind == RPR_NAV_FIRST) + { + context->hasFirst = true; + + if (!context->firstNeedsEval) { - Const *c = (Const *) nav->offset_arg; + int64 offset; - if (c->constisnull) + if (extract_const_offset(nav->offset_arg, 0, &offset)) { - /* - * NULL offset causes a runtime error, so this path is - * never actually reached during execution. Use 0 as a - * safe placeholder for planning purposes. - */ - offset = 0; + if (offset < context->firstOffset) + context->firstOffset = offset; } else + context->firstNeedsEval = true; + } + } + + /* + * Compound PREV_LAST / NEXT_LAST: base = currentpos. PREV_LAST(v, N, + * M): target = currentpos - N - M → lookback = N + M NEXT_LAST(v, + * N, M): target = currentpos - N + M → lookback = max(N - M, 0) + */ + if (nav->kind == RPR_NAV_PREV_LAST || + nav->kind == RPR_NAV_NEXT_LAST) + { + if (!context->maxNeedsEval) + { + int64 inner, + outer, + combined; + + if (extract_const_offset(nav->offset_arg, 0, &inner) && + extract_const_offset(nav->compound_offset_arg, 1, &outer)) { - offset = DatumGetInt64(c->constvalue); - if (offset < 0) - offset = 0; /* negative offset causes runtime error */ + if (nav->kind == RPR_NAV_PREV_LAST) + { + if (pg_add_s64_overflow(inner, outer, &combined)) + { + context->maxOverflow = true; + return false; + } + } + else + combined = (inner > outer) ? inner - outer : 0; + + if (combined > context->maxOffset) + context->maxOffset = combined; } + else + context->maxNeedsEval = true; } - else + } + + /* + * Compound PREV_FIRST / NEXT_FIRST: base = match_start. PREV_FIRST(v, + * N, M): target = match_start + N - M NEXT_FIRST(v, N, M): target = + * match_start + N + M The combined offset (N±M) from match_start can + * be negative, meaning rows before match_start are needed. + */ + if (nav->kind == RPR_NAV_PREV_FIRST || + nav->kind == RPR_NAV_NEXT_FIRST) + { + context->hasFirst = true; + + if (!context->firstNeedsEval) { - /* - * Non-constant offset (Param, stable function, etc.). The - * parser guarantees offset is a runtime constant, so it can - * be evaluated at executor init time. - */ - *maxOffset = RPR_NAV_OFFSET_NEEDS_EVAL; - return true; /* stop walking */ + int64 inner, + outer, + combined; + + if (extract_const_offset(nav->offset_arg, 0, &inner) && + extract_const_offset(nav->compound_offset_arg, 1, &outer)) + { + if (nav->kind == RPR_NAV_PREV_FIRST) + { + /* + * combined = inner - outer. Both are non-negative, + * so the result >= -INT64_MAX, which cannot underflow + * int64. No overflow check needed. + */ + combined = inner - outer; + } + else + { + /* + * NEXT_FIRST: combined = inner + outer. This can + * overflow, but the result is always >= 0, so it + * never updates firstOffset (which tracks the + * minimum). Clamp to INT64_MAX on overflow. + */ + if (pg_add_s64_overflow(inner, outer, &combined)) + combined = INT64_MAX; + } + + if (combined < context->firstOffset) + context->firstOffset = combined; + } + else + context->firstNeedsEval = true; } + } - if (offset > *maxOffset) - *maxOffset = offset; + /* Don't walk into RPRNavExpr children */ + return false; + } + + return expression_tree_walker(node, nav_offset_walker, ctx); +} + +/* + * compute_nav_offsets + * Compute navigation offsets for tuplestore trim in a single pass. + * + * Walks all DEFINE clause expressions once, computing: + * - maxOffset: max backward reach from PREV, LAST-with-offset, + * compound PREV_LAST/NEXT_LAST + * - hasFirst/firstOffset: min forward-from-match-start reach from + * FIRST, compound PREV_FIRST/NEXT_FIRST + */ +static void +compute_nav_offsets(List *defineClause, + RPRNavOffsetKind *maxKind, int64 *maxResult, + bool *hasFirst, + RPRNavOffsetKind *firstKind, int64 *firstResult) +{ + NavOffsetContext ctx; + ListCell *lc; + + ctx.maxOffset = 0; + ctx.maxNeedsEval = false; + ctx.maxOverflow = false; + ctx.firstOffset = INT64_MAX; /* sentinel: no FIRST found yet */ + ctx.hasFirst = false; + ctx.firstNeedsEval = false; + + foreach(lc, defineClause) + { + TargetEntry *te = (TargetEntry *) lfirst(lc); + + nav_offset_walker((Node *) te->expr, &ctx); + } + + /* Max backward offset */ + if (ctx.maxOverflow) + { + *maxKind = RPR_NAV_OFFSET_RETAIN_ALL; + *maxResult = 0; + } + else if (ctx.maxNeedsEval) + { + *maxKind = RPR_NAV_OFFSET_NEEDS_EVAL; + *maxResult = 0; + } + else + { + *maxKind = RPR_NAV_OFFSET_FIXED; + *maxResult = ctx.maxOffset; + } + + /* First offset (can be negative for compound PREV_FIRST) */ + *hasFirst = ctx.hasFirst; + if (ctx.hasFirst) + { + if (ctx.firstNeedsEval) + { + *firstKind = RPR_NAV_OFFSET_NEEDS_EVAL; + *firstResult = 0; + } + else if (ctx.firstOffset == INT64_MAX) + { + *firstKind = RPR_NAV_OFFSET_FIXED; + *firstResult = 0; /* only implicit FIRST(v) */ + } + else + { + *firstKind = RPR_NAV_OFFSET_FIXED; + *firstResult = ctx.firstOffset; /* may be negative */ } + } + else + { + *firstKind = RPR_NAV_OFFSET_FIXED; + *firstResult = 0; + } +} - /* Don't walk into RPRNavExpr children - offset_arg already handled */ +/* + * has_match_start_dependency + * Check if an expression tree contains navigation that depends on + * match_start: FIRST, LAST-with-offset, or compound PREV_FIRST/ + * NEXT_FIRST/PREV_LAST/NEXT_LAST with offset. Such expressions + * require per-context re-evaluation during NFA processing. + * + * LAST without offset always resolves to currentpos and is + * match_start-independent. + */ +static bool +has_match_start_dependency(Node *node, void *context) +{ + if (node == NULL) return false; + + if (IsA(node, RPRNavExpr)) + { + RPRNavExpr *nav = (RPRNavExpr *) node; + + if (nav->kind == RPR_NAV_FIRST) + return true; + if (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL) + return true; + + /* Compound kinds with FIRST base depend on match_start */ + if (nav->kind == RPR_NAV_PREV_FIRST || + nav->kind == RPR_NAV_NEXT_FIRST) + return true; + + /* + * PREV_LAST/NEXT_LAST: inner is LAST, which uses currentpos. + * match_start-dependent only if inner has offset (clamped to + * match_start). + */ + if ((nav->kind == RPR_NAV_PREV_LAST || + nav->kind == RPR_NAV_NEXT_LAST) && + nav->offset_arg != NULL) + return true; + + /* Check children (arg may contain further nav expressions) */ + return has_match_start_dependency((Node *) nav->arg, context); } - return expression_tree_walker(node, nav_max_offset_walker, maxOffset); + return expression_tree_walker(node, has_match_start_dependency, NULL); } /* - * compute_nav_max_offset - * Compute the maximum PREV offset from DEFINE clause expressions. + * compute_match_start_dependent + * Build a Bitmapset of DEFINE variable indices whose expressions + * depend on match_start (contain FIRST, LAST-with-offset, or + * compound PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST with offset). * - * Returns the maximum constant offset found, or -1 if any PREV offset - * cannot be determined statically. NEXT offsets are ignored since they - * look forward and don't affect tuplestore trim. + * Variables in this set require per-context re-evaluation during NFA + * processing, because different contexts may have different match_start + * values. */ -static int64 -compute_nav_max_offset(List *defineClause) +static Bitmapset * +compute_match_start_dependent(List *defineClause) { - int64 maxOffset = 0; + Bitmapset *result = NULL; ListCell *lc; + int varIdx = 0; foreach(lc, defineClause) { TargetEntry *te = (TargetEntry *) lfirst(lc); - if (nav_max_offset_walker((Node *) te->expr, &maxOffset)) - return maxOffset; /* NEEDS_EVAL or RETAIN_ALL */ + if (has_match_start_dependency((Node *) te->expr, NULL)) + result = bms_add_member(result, varIdx); + + varIdx++; } - return maxOffset; + return result; } /* @@ -2586,6 +2854,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) List *defineVariableList = NIL; List *filteredDefineClause = NIL; RPRPattern *compiledPattern = NULL; + Bitmapset *matchStartDependent = NULL; /* @@ -2648,11 +2917,15 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) buildDefineVariableList(wc->defineClause, &defineVariableList); filteredDefineClause = wc->defineClause; + /* Identify match_start-dependent DEFINE variables */ + matchStartDependent = compute_match_start_dependent(wc->defineClause); + /* Compile and optimize RPR patterns */ compiledPattern = buildRPRPattern(wc->rpPattern, defineVariableList, wc->rpSkipTo, - wc->frameOptions); + wc->frameOptions, + !bms_is_empty(matchStartDependent)); } /* And finally we can make the WindowAgg node */ @@ -2670,6 +2943,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) wc->rpSkipTo, compiledPattern, filteredDefineClause, + matchStartDependent, best_path->qual, best_path->topwindow, subplan); @@ -6742,6 +7016,7 @@ make_windowagg(List *tlist, WindowClause *wc, List *runCondition, RPSkipTo rpSkipTo, RPRPattern *compiledPattern, List *defineClause, + Bitmapset *defineMatchStartDependent, List *qual, bool topWindow, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); @@ -6776,8 +7051,14 @@ make_windowagg(List *tlist, WindowClause *wc, node->defineClause = defineClause; - /* Compute max PREV offset for tuplestore trim optimization */ - node->navMaxOffset = compute_nav_max_offset(defineClause); + /* Store pre-computed match_start dependency bitmapset */ + node->defineMatchStartDependent = defineMatchStartDependent; + + /* Compute nav offsets for tuplestore trim optimization */ + compute_nav_offsets(defineClause, + &node->navMaxOffsetKind, &node->navMaxOffset, + &node->hasFirstNav, + &node->navFirstOffsetKind, &node->navFirstOffset); plan->targetlist = tlist; plan->lefttree = lefttree; diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c index c0e9d134aa9..767a214016c 100644 --- a/src/backend/optimizer/plan/rpr.c +++ b/src/backend/optimizer/plan/rpr.c @@ -1893,7 +1893,8 @@ buildDefineVariableList(List *defineClause, List **defineVariableList) */ RPRPattern * buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList, - RPSkipTo rpSkipTo, int frameOptions) + RPSkipTo rpSkipTo, int frameOptions, + bool hasMatchStartDependent) { RPRPattern *result; RPRPatternNode *optimized; @@ -1947,7 +1948,8 @@ buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList, hasLimitedFrame = (frameOptions & FRAMEOPTION_ROWS) && !(frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING); - if (rpSkipTo == ST_PAST_LAST_ROW && !hasLimitedFrame) + if (rpSkipTo == ST_PAST_LAST_ROW && !hasLimitedFrame && + !hasMatchStartDependent) { /* Runtime conditions met - check structural absorbability */ computeAbsorbability(result); diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index e14ff4dc494..aa45a98713f 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -757,35 +757,79 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, if (retset) check_srf_call_placement(pstate, last_srf, location); - /* next() and prev() are only allowed in a WINDOW DEFINE clause */ + /* + * RPR navigation functions (PREV/NEXT/FIRST/LAST) are only meaningful + * inside a WINDOW DEFINE clause. + * + * Outside DEFINE, these polymorphic placeholders can shadow column access + * via functional notation (e.g., last(f) meaning f.last). For the 1-arg + * form, try column projection first; if that succeeds, use it instead. + * Otherwise, report a clear parser error. + */ if (fdresult == FUNCDETAIL_NORMAL && pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE && (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT || - funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8)) + funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 || + funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT || + funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8)) + { + /* 1-arg form: try column projection before erroring out */ + if (nargs == 1 && !agg_star && !agg_distinct && over == NULL && + list_length(funcname) == 1) + { + Node *projection; + + projection = ParseComplexProjection(pstate, + strVal(linitial(funcname)), + linitial(fargs), + location); + if (projection) + return projection; + } + + /* Not a column projection — report error */ ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("%s can only be used in a DEFINE clause", NameListToString(funcname)), parser_errposition(pstate, location))); + } /* build the appropriate output structure */ if (fdresult == FUNCDETAIL_NORMAL && + pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE && (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT || - funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8)) + funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 || + funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT || + funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8)) { /* - * PREV() and NEXT() are compiled into EEOP_RPR_NAV_SET / - * EEOP_RPR_NAV_RESTORE opcodes instead of a normal function call. - * Represent them as RPRNavExpr nodes so that later stages can - * identify them without relying on funcid comparisons. + * RPR navigation functions (PREV/NEXT/FIRST/LAST) are compiled into + * EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE opcodes instead of a normal + * function call. Represent them as RPRNavExpr nodes so that later + * stages can identify them without relying on funcid comparisons. */ - bool is_next = (funcid == F_NEXT_ANYELEMENT || - funcid == F_NEXT_ANYELEMENT_INT8); - bool has_offset = (funcid == F_PREV_ANYELEMENT_INT8 || - funcid == F_NEXT_ANYELEMENT_INT8); - RPRNavExpr *navexpr = makeNode(RPRNavExpr); + RPRNavKind kind; + bool has_offset; + RPRNavExpr *navexpr; + + if (funcid == F_PREV_ANYELEMENT || funcid == F_PREV_ANYELEMENT_INT8) + kind = RPR_NAV_PREV; + else if (funcid == F_NEXT_ANYELEMENT || funcid == F_NEXT_ANYELEMENT_INT8) + kind = RPR_NAV_NEXT; + else if (funcid == F_FIRST_ANYELEMENT || funcid == F_FIRST_ANYELEMENT_INT8) + kind = RPR_NAV_FIRST; + else + kind = RPR_NAV_LAST; + + has_offset = (funcid == F_PREV_ANYELEMENT_INT8 || + funcid == F_NEXT_ANYELEMENT_INT8 || + funcid == F_FIRST_ANYELEMENT_INT8 || + funcid == F_LAST_ANYELEMENT_INT8); + + navexpr = makeNode(RPRNavExpr); - navexpr->kind = is_next ? RPR_NAV_NEXT : RPR_NAV_PREV; + navexpr->kind = kind; navexpr->arg = (Expr *) linitial(fargs); navexpr->offset_arg = has_offset ? (Expr *) lsecond(fargs) : NULL; navexpr->resulttype = rettype; diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index d1e02e52e53..05070cb04bb 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -339,8 +339,8 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, /* * Transform the DEFINE expression. We must NOT add the whole * expression to the query targetlist, because it may contain - * RPRNavExpr nodes (PREV/NEXT) that can only be evaluated inside the - * owning WindowAgg. + * RPRNavExpr nodes (PREV/NEXT/FIRST/LAST) that can only be evaluated + * inside the owning WindowAgg. * * Instead, we transform the expression directly and only ensure that * the individual Var nodes it references are present in the @@ -429,14 +429,21 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, /* * check_rpr_nav_expr * Validate a single RPRNavExpr node by walking its arg and offset_arg - * subtrees in a single pass each. Checks for nested PREV/NEXT, missing + * subtrees in a single pass each. Checks for illegal nesting, missing * column references, and non-constant offset expressions. + * + * Nesting rules (SQL standard 5.6.4): + * - PREV/NEXT wrapping FIRST/LAST: allowed (compound navigation) + * - FIRST/LAST wrapping PREV/NEXT: prohibited + * - Same-category nesting (PREV inside PREV, FIRST inside FIRST, etc.): + * prohibited */ typedef struct { - bool has_nav; /* RPRNavExpr found (nesting) */ + int nav_count; /* number of RPRNavExpr nodes found */ bool has_column_ref; /* Var found */ -} NavCheckResult; + RPRNavKind inner_kind; /* kind of first (outermost) nested RPRNavExpr */ +} NavCheckResult; static bool nav_check_walker(Node *node, void *context) @@ -446,7 +453,11 @@ nav_check_walker(Node *node, void *context) if (node == NULL) return false; if (IsA(node, RPRNavExpr)) - result->has_nav = true; + { + if (result->nav_count == 0) + result->inner_kind = ((RPRNavExpr *) node)->kind; + result->nav_count++; + } if (IsA(node, Var)) result->has_column_ref = true; @@ -457,16 +468,93 @@ static void check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate) { NavCheckResult result; + bool outer_is_physical = (nav->kind == RPR_NAV_PREV || + nav->kind == RPR_NAV_NEXT); /* Check arg subtree: nesting + column reference in one walk */ memset(&result, 0, sizeof(result)); (void) nav_check_walker((Node *) nav->arg, &result); - if (result.has_nav) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("PREV and NEXT cannot be nested"), - parser_errposition(pstate, nav->location))); + if (result.nav_count > 0) + { + bool inner_is_physical = (result.inner_kind == RPR_NAV_PREV || + result.inner_kind == RPR_NAV_NEXT); + + if (outer_is_physical && !inner_is_physical) + { + /* + * PREV/NEXT wrapping FIRST/LAST: compound navigation per SQL + * standard 5.6.4. Flatten the nested RPRNavExpr into a single + * compound node. The inner RPRNavExpr must be the direct arg of + * the outer; expressions like PREV(val + FIRST(v)) are not valid + * compound navigation. + */ + RPRNavExpr *inner; + + /* Reject triple-or-deeper nesting (e.g. PREV(FIRST(PREV(x)))) */ + if (result.nav_count > 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern navigation cannot be nested more than two levels deep"), + errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."), + parser_errposition(pstate, nav->location))); + + if (!IsA(nav->arg, RPRNavExpr)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern navigation operation must be a direct argument of the outer navigation"), + errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."), + parser_errposition(pstate, nav->location))); + inner = (RPRNavExpr *) nav->arg; + + /* Determine compound kind */ + if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_FIRST) + nav->kind = RPR_NAV_PREV_FIRST; + else if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_LAST) + nav->kind = RPR_NAV_PREV_LAST; + else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_FIRST) + nav->kind = RPR_NAV_NEXT_FIRST; + else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_LAST) + nav->kind = RPR_NAV_NEXT_LAST; + + /* Move outer offset to compound_offset_arg */ + nav->compound_offset_arg = nav->offset_arg; + + /* Move inner offset and arg up */ + nav->offset_arg = inner->offset_arg; + nav->arg = inner->arg; + + /* No further nesting check needed - already validated */ + return; + } + else if (!outer_is_physical && inner_is_physical) + { + /* FIRST/LAST wrapping PREV/NEXT: prohibited by standard */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("FIRST and LAST cannot contain PREV or NEXT"), + errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."), + parser_errposition(pstate, nav->location))); + } + else if (outer_is_physical && inner_is_physical) + { + /* PREV/NEXT wrapping PREV/NEXT: prohibited */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("PREV and NEXT cannot contain PREV or NEXT"), + errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."), + parser_errposition(pstate, nav->location))); + } + else + { + /* FIRST/LAST wrapping FIRST/LAST: prohibited */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("FIRST and LAST cannot contain FIRST or LAST"), + errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."), + parser_errposition(pstate, nav->location))); + } + } if (!result.has_column_ref) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -483,7 +571,7 @@ check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate) contain_volatile_functions((Node *) nav->offset_arg)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("PREV/NEXT offset must be a run-time constant"), + errmsg("row pattern navigation offset must be a run-time constant"), parser_errposition(pstate, nav->location))); } } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a4fe725646c..467736d9146 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -10106,16 +10106,83 @@ get_rule_expr(Node *node, deparse_context *context, case T_RPRNavExpr: { RPRNavExpr *nav = (RPRNavExpr *) node; + const char *outer_func = NULL; + const char *inner_func; - appendStringInfoString(buf, - nav->kind == RPR_NAV_PREV ? "PREV(" : "NEXT("); - get_rule_expr((Node *) nav->arg, context, showimplicit); - if (nav->offset_arg != NULL) + switch (nav->kind) { - appendStringInfoString(buf, ", "); - get_rule_expr((Node *) nav->offset_arg, context, showimplicit); + case RPR_NAV_PREV: + inner_func = "PREV("; + break; + case RPR_NAV_NEXT: + inner_func = "NEXT("; + break; + case RPR_NAV_FIRST: + inner_func = "FIRST("; + break; + case RPR_NAV_LAST: + inner_func = "LAST("; + break; + case RPR_NAV_PREV_FIRST: + outer_func = "PREV("; + inner_func = "FIRST("; + break; + case RPR_NAV_PREV_LAST: + outer_func = "PREV("; + inner_func = "LAST("; + break; + case RPR_NAV_NEXT_FIRST: + outer_func = "NEXT("; + inner_func = "FIRST("; + break; + case RPR_NAV_NEXT_LAST: + outer_func = "NEXT("; + inner_func = "LAST("; + break; + default: + elog(ERROR, "unrecognized RPR navigation kind: %d", + nav->kind); + inner_func = NULL; /* keep compiler quiet */ + break; + } + + if (outer_func != NULL) + { + /* + * Compound: PREV(FIRST(arg [, inner_offset]) [, + * outer_offset]) + */ + appendStringInfoString(buf, outer_func); + appendStringInfoString(buf, inner_func); + get_rule_expr((Node *) nav->arg, context, showimplicit); + if (nav->offset_arg != NULL) + { + appendStringInfoString(buf, ", "); + get_rule_expr((Node *) nav->offset_arg, context, + showimplicit); + } + appendStringInfoChar(buf, ')'); + if (nav->compound_offset_arg != NULL) + { + appendStringInfoString(buf, ", "); + get_rule_expr((Node *) nav->compound_offset_arg, + context, showimplicit); + } + appendStringInfoChar(buf, ')'); + } + else + { + /* Simple: FUNC(arg [, offset]) */ + appendStringInfoString(buf, inner_func); + get_rule_expr((Node *) nav->arg, context, showimplicit); + if (nav->offset_arg != NULL) + { + appendStringInfoString(buf, ", "); + get_rule_expr((Node *) nav->offset_arg, context, + showimplicit); + } + appendStringInfoChar(buf, ')'); } - appendStringInfoChar(buf, ')'); } break; diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index 091260d2cce..420a4962395 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -785,3 +785,59 @@ window_next_offset(PG_FUNCTION_ARGS) errmsg("next() can only be used in a DEFINE clause"))); PG_RETURN_NULL(); /* not reached */ } + +/* + * first + * Catalog placeholder for RPR's FIRST navigation operator. + * See window_prev() for details. + */ +Datum +window_first(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("first() can only be used in a DEFINE clause"))); + PG_RETURN_NULL(); /* not reached */ +} + +/* + * last + * Catalog placeholder for RPR's LAST navigation operator. + * See window_prev() for details. + */ +Datum +window_last(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("last() can only be used in a DEFINE clause"))); + PG_RETURN_NULL(); /* not reached */ +} + +/* + * first(value, offset) + * Catalog placeholder for RPR's FIRST navigation operator with offset. + * See window_prev() for details. + */ +Datum +window_first_offset(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("first() can only be used in a DEFINE clause"))); + PG_RETURN_NULL(); /* not reached */ +} + +/* + * last(value, offset) + * Catalog placeholder for RPR's LAST navigation operator with offset. + * See window_prev() for details. + */ +Datum +window_last_offset(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("last() can only be used in a DEFINE clause"))); + PG_RETURN_NULL(); /* not reached */ +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 8e95169b7b0..6d70ed23aeb 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10984,6 +10984,18 @@ { oid => '8129', descr => 'next value at offset', proname => 'next', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', proargtypes => 'anyelement int8', prosrc => 'window_next_offset' }, +{ oid => '8130', descr => 'first value in match', + proname => 'first', provolatile => 's', prorettype => 'anyelement', + proargtypes => 'anyelement', prosrc => 'window_first' }, +{ oid => '8132', descr => 'first value in match at offset', + proname => 'first', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', + proargtypes => 'anyelement int8', prosrc => 'window_first_offset' }, +{ oid => '8131', descr => 'last value in match', + proname => 'last', provolatile => 's', prorettype => 'anyelement', + proargtypes => 'anyelement', prosrc => 'window_last' }, +{ oid => '8133', descr => 'last value in match at offset', + proname => 'last', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', + proargtypes => 'anyelement int8', prosrc => 'window_last_offset' }, # functions for range types { oid => '3832', descr => 'I/O', diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index fac37c96896..834800a4062 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -699,10 +699,10 @@ typedef struct ExprEvalStep struct { WindowAggState *winstate; - RPRNavKind kind; /* PREV or NEXT */ - Datum *offset_value; /* 2-arg: runtime offset value, or - * NULL */ - bool *offset_isnull; /* 2-arg: runtime offset null flag */ + RPRNavKind kind; /* navigation kind (simple or compound) */ + Datum *offset_value; /* offset value(s), or NULL */ + bool *offset_isnull; /* offset null flag(s) */ + /* For compound nav: offset_value[0] = inner, [1] = outer */ } rpr_nav; /* for EEOP_AGG_*DESERIALIZE */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index ff6d7d70a60..602b72a6e0d 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -2638,6 +2638,9 @@ typedef struct WindowAggState Size nfaStateSize; /* pre-calculated RPRNFAState size */ bool *nfaVarMatched; /* per-row cache: varMatched[varId] for varId * < numDefines */ + Bitmapset *defineMatchStartDependent; /* DEFINE vars needing per-context + * evaluation + * (match_start-dependent) */ bitmapword *nfaVisitedElems; /* elemIdx visited bitmap for cycle * detection */ int nfaVisitedNWords; /* number of bitmapwords in @@ -2692,12 +2695,17 @@ typedef struct WindowAggState TupleTableSlot *temp_slot_2; /* RPR navigation */ - int64 navMaxOffset; /* max PREV offset; see RPR_NAV_OFFSET_* */ + RPRNavOffsetKind navMaxOffsetKind; /* status of navMaxOffset */ + int64 navMaxOffset; /* max backward nav offset (when FIXED) */ + bool hasFirstNav; /* FIRST() present in DEFINE */ + RPRNavOffsetKind navFirstOffsetKind; /* status of navFirstOffset */ + int64 navFirstOffset; /* min FIRST() offset (when FIXED) */ struct WindowObjectData *nav_winobj; /* winobj for RPR nav fetch */ int64 nav_slot_pos; /* position cached in nav_slot, or -1 */ - TupleTableSlot *nav_slot; /* slot for PREV/NEXT target row */ + TupleTableSlot *nav_slot; /* slot for PREV/NEXT/FIRST/LAST target row */ TupleTableSlot *nav_saved_outertuple; /* saved slot during nav swap */ TupleTableSlot *nav_null_slot; /* all NULL slot */ + int64 nav_match_start; /* match_start for FIRST/LAST nav */ /* RPR current match result */ bool rpr_match_valid; /* true if a match result is set */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b92663687a6..1fdf62575cb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -586,9 +586,24 @@ typedef enum RPSkipTo ST_NONE, /* no AFTER MATCH clause; default for non-RPR * windows */ ST_NEXT_ROW, /* SKIP TO NEXT ROW */ - ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */ + ST_PAST_LAST_ROW /* SKIP TO PAST LAST ROW */ } RPSkipTo; +/* + * RPRNavOffsetKind - status of navigation offset for tuplestore trim. + * + * The planner computes navMaxOffset/navFirstOffset for tuplestore mark + * optimization. This enum tracks whether the value is a resolved constant, + * needs runtime evaluation, or cannot be determined (retain all rows). + */ +typedef enum RPRNavOffsetKind +{ + RPR_NAV_OFFSET_FIXED, /* resolved constant; use the offset value */ + RPR_NAV_OFFSET_NEEDS_EVAL, /* non-constant offset; evaluate at executor + * init */ + RPR_NAV_OFFSET_RETAIN_ALL /* cannot determine; retain all rows (no trim) */ +} RPRNavOffsetKind; + /* * RPRPatternNodeType - Row Pattern Recognition pattern node types */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 27a2e7b48c7..93ce505f0d4 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -1387,14 +1387,34 @@ typedef struct WindowAgg List *defineClause; /* - * Maximum PREV offset for tuplestore mark optimization. >= 0: statically - * determined max offset (mark = currentpos - offset). - * RPR_NAV_OFFSET_NEEDS_EVAL: has non-constant offset; evaluate at - * executor init. RPR_NAV_OFFSET_RETAIN_ALL: must retain entire partition - * (no trim possible). + * Bitmapset of DEFINE variable indices whose expressions depend on + * match_start (contain FIRST, LAST-with-offset, or compound + * PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST with offset). Variables in + * this set require per-context re-evaluation during NFA processing. */ + Bitmapset *defineMatchStartDependent; + + /* + * Navigation offset status and values for tuplestore mark optimization. + * See RPRNavOffsetKind in nodes/parsenodes.h. + * + * navMaxOffset: maximum backward reach from currentpos (contributed by + * PREV, LAST-with-offset, compound PREV_LAST/NEXT_LAST). Only valid when + * navMaxOffsetKind == RPR_NAV_OFFSET_FIXED. + * + * navFirstOffset: minimum forward offset from match_start (contributed by + * FIRST, compound PREV_FIRST/NEXT_FIRST). Can be negative for compound + * PREV_FIRST. Only valid when navFirstOffsetKind == RPR_NAV_OFFSET_FIXED + * and hasFirstNav == true. + */ + RPRNavOffsetKind navMaxOffsetKind; int64 navMaxOffset; + /* true if FIRST-based navigation (FIRST, PREV_FIRST, NEXT_FIRST) is used */ + bool hasFirstNav; + RPRNavOffsetKind navFirstOffsetKind; + int64 navFirstOffset; + /* * false for all apart from the WindowAgg that's closest to the root of * the plan diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 94723a3b909..0afcfacd5d2 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -651,27 +651,50 @@ typedef struct WindowFuncRunCondition /* * RPRNavExpr * - * Represents a PREV() or NEXT() navigation call in an RPR DEFINE clause. + * Represents a PREV/NEXT/FIRST/LAST navigation call in an RPR DEFINE clause. * At expression compile time this is translated into EEOP_RPR_NAV_SET / * EEOP_RPR_NAV_RESTORE opcodes rather than a normal function call. * - * kind: RPR_NAV_PREV or RPR_NAV_NEXT - * arg: the expression to evaluate against the target row - * offset_arg: optional explicit offset expression (2-arg form); NULL for - * the 1-arg form which uses an implicit offset of 1 + * Simple navigation (PREV/NEXT/FIRST/LAST): + * kind: RPR_NAV_PREV, RPR_NAV_NEXT, RPR_NAV_FIRST, or RPR_NAV_LAST + * arg: the expression to evaluate against the target row + * offset_arg: optional explicit offset expression (2-arg form); NULL for + * the 1-arg form (implicit offset: 1 for PREV/NEXT, 0 for + * FIRST/LAST) + * + * Compound navigation (PREV/NEXT wrapping FIRST/LAST): + * kind: RPR_NAV_PREV_FIRST, PREV_LAST, NEXT_FIRST, NEXT_LAST + * arg: the expression to evaluate against the final target row + * offset_arg: inner offset (FIRST/LAST), NULL = implicit default + * compound_offset_arg: outer offset (PREV/NEXT), NULL = implicit default + * + * Compound target computation: + * PREV_FIRST: (match_start + inner) - outer + * NEXT_FIRST: (match_start + inner) + outer + * PREV_LAST: (currentpos - inner) - outer + * NEXT_LAST: (currentpos - inner) + outer */ typedef enum RPRNavKind { RPR_NAV_PREV, RPR_NAV_NEXT, + RPR_NAV_FIRST, + RPR_NAV_LAST, + /* compound: outer(inner(arg)) */ + RPR_NAV_PREV_FIRST, + RPR_NAV_PREV_LAST, + RPR_NAV_NEXT_FIRST, + RPR_NAV_NEXT_LAST } RPRNavKind; typedef struct RPRNavExpr { Expr xpr; - RPRNavKind kind; /* PREV or NEXT */ + RPRNavKind kind; /* navigation kind */ Expr *arg; /* argument expression */ - Expr *offset_arg; /* offset expression, or NULL for 1-arg form */ + Expr *offset_arg; /* offset expression, or NULL for default */ + Expr *compound_offset_arg; /* outer offset for compound nav, or + * NULL if simple */ Oid resulttype; /* result type (same as arg's type) */ /* OID of collation of result */ Oid resultcollid pg_node_attr(query_jumble_ignore); diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h index 00a28abe2b4..0a14cfad79b 100644 --- a/src/include/optimizer/rpr.h +++ b/src/include/optimizer/rpr.h @@ -55,19 +55,11 @@ #define RPRElemIsFin(e) ((e)->varId == RPR_VARID_FIN) #define RPRElemCanSkip(e) ((e)->min == 0) -/* - * navMaxOffset sentinel values. - * Non-negative values represent a statically determined maximum PREV offset. - */ -#define RPR_NAV_OFFSET_NEEDS_EVAL (-1) /* has non-constant PREV offset; - * evaluate at executor init */ -#define RPR_NAV_OFFSET_RETAIN_ALL (-2) /* must retain entire partition - * (e.g., future FIRST/LAST) */ - extern List *collectPatternVariables(RPRPatternNode *pattern); extern void buildDefineVariableList(List *defineClause, List **defineVariableList); extern RPRPattern *buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList, - RPSkipTo rpSkipTo, int frameOptions); + RPSkipTo rpSkipTo, int frameOptions, + bool hasMatchStartDependent); #endif /* OPTIMIZER_RPR_H */ diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index c02dbd4c08d..04ec25d4cf5 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1040,9 +1040,10 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > PREV(PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: PREV and NEXT cannot contain PREV or NEXT LINE 7: DEFINE A AS price > PREV(PREV(price)) ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- Nested NEXT SELECT price FROM stock WINDOW w AS ( @@ -1052,9 +1053,10 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(NEXT(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: PREV and NEXT cannot contain PREV or NEXT LINE 7: DEFINE A AS price > NEXT(NEXT(price)) ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- PREV nested inside NEXT SELECT price FROM stock WINDOW w AS ( @@ -1064,9 +1066,10 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: PREV and NEXT cannot contain PREV or NEXT LINE 7: DEFINE A AS price > NEXT(PREV(price)) ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- PREV nested inside expression inside NEXT SELECT price FROM stock WINDOW w AS ( @@ -1076,9 +1079,10 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(price * PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: PREV and NEXT cannot contain PREV or NEXT LINE 7: DEFINE A AS price > NEXT(price * PREV(price)) ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- Triple nesting: error reported at outermost PREV SELECT price FROM stock WINDOW w AS ( @@ -1088,9 +1092,10 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > PREV(PREV(PREV(price))) ); -ERROR: PREV and NEXT cannot be nested +ERROR: PREV and NEXT cannot contain PREV or NEXT LINE 7: DEFINE A AS price > PREV(PREV(PREV(price))) ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- No column reference in PREV/NEXT argument -- PREV(1): constant only, no column reference SELECT price FROM stock @@ -1137,7 +1142,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS PREV(price, price) > 0 ); -ERROR: PREV/NEXT offset must be a run-time constant +ERROR: row pattern navigation offset must be a run-time constant LINE 7: DEFINE A AS PREV(price, price) > 0 ^ -- Non-constant offset: volatile function as offset @@ -1149,7 +1154,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS PREV(price, random()::int) > 0 ); -ERROR: PREV/NEXT offset must be a run-time constant +ERROR: row pattern navigation offset must be a run-time constant LINE 7: DEFINE A AS PREV(price, random()::int) > 0 ^ -- Non-constant offset: subquery as offset @@ -1442,7 +1447,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS PREV(price, -1) IS NOT NULL ); -ERROR: PREV/NEXT offset must not be negative +ERROR: row pattern navigation offset must not be negative -- 2-arg PREV/NEXT: NULL offset (typed) SELECT company, tdate, price, first_value(price) OVER w FROM stock @@ -1452,7 +1457,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS PREV(price, NULL::int8) IS NOT NULL ); -ERROR: PREV/NEXT offset must not be null +ERROR: row pattern navigation offset must not be null -- 2-arg PREV/NEXT: NULL offset (untyped) SELECT company, tdate, price, first_value(price) OVER w FROM stock @@ -1462,7 +1467,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS PREV(price, NULL) IS NOT NULL ); -ERROR: PREV/NEXT offset must not be null +ERROR: row pattern navigation offset must not be null -- 2-arg PREV/NEXT: host variable negative and NULL PREPARE test_prev_offset(int8) AS SELECT company, tdate, price, first_value(price) OVER w @@ -1474,9 +1479,9 @@ WINDOW w AS ( DEFINE A AS price > PREV(price, $1) ); EXECUTE test_prev_offset(-1); -ERROR: PREV/NEXT offset must not be negative +ERROR: row pattern navigation offset must not be negative EXECUTE test_prev_offset(NULL); -ERROR: PREV/NEXT offset must not be null +ERROR: row pattern navigation offset must not be null DEALLOCATE test_prev_offset; -- 2-arg PREV/NEXT: host variable with expression (0 + $1) PREPARE test_prev_offset(int8) AS @@ -1489,9 +1494,9 @@ WINDOW w AS ( DEFINE A AS price > PREV(price, 0 + $1) ); EXECUTE test_prev_offset(-1); -ERROR: PREV/NEXT offset must not be negative +ERROR: row pattern navigation offset must not be negative EXECUTE test_prev_offset(NULL); -ERROR: PREV/NEXT offset must not be null +ERROR: row pattern navigation offset must not be null DEALLOCATE test_prev_offset; -- 2-arg PREV/NEXT: host variable with positive value -- Exercises RPR_NAV_OFFSET_NEEDS_EVAL -> eval_nav_max_offset() path @@ -1630,6 +1635,604 @@ WINDOW w AS ( company2 | 07-10-2023 | 1300 | | | 0 (20 rows) +-- +-- FIRST/LAST navigation +-- +-- Test data for FIRST/LAST: values cycle back so FIRST(val) = LAST(val) +-- at specific positions. +CREATE TEMP TABLE rpr_nav (id int, val int); +INSERT INTO rpr_nav VALUES (1,10),(2,20),(3,30),(4,10),(5,50),(6,10); +-- FIRST(val) = constant: B matches when match_start has val=10 +-- match_start=1(10): A=id1, B=id2, FIRST(val)=10 → match {1,2} +-- match_start=3(30): A=id3, B=id4, FIRST(val)=30≠10 → no match +-- match_start=4(10): A=id4, B=id5, FIRST(val)=10 → match {4,5} +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B) + DEFINE A AS TRUE, B AS FIRST(val) = 10 +); + id | val | mf | ml +----+-----+----+---- + 1 | 10 | 1 | 2 + 2 | 20 | | + 3 | 30 | | + 4 | 10 | 4 | 5 + 5 | 50 | | + 6 | 10 | | +(6 rows) + +-- LAST(val): always equals current row's val (offset 0 default) +-- Equivalent to: B AS val > 15 +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B) + DEFINE A AS TRUE, B AS LAST(val) > 15 +); + id | val | mf | ml +----+-----+----+---- + 1 | 10 | 1 | 2 + 2 | 20 | | + 3 | 30 | | + 4 | 10 | 4 | 5 + 5 | 50 | | + 6 | 10 | | +(6 rows) + +-- Reluctant A+? with FIRST(val) = LAST(val): find shortest match where +-- first and last rows have the same val. +-- match_start=1(10): reluctant tries B early: +-- id2(20≠10), id3(30≠10), id4(10=10) → match {1,2,3,4} +-- match_start=5(50): id6(10≠50) → no match +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+? B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + id | val | mf | ml +----+-----+----+---- + 1 | 10 | 1 | 4 + 2 | 20 | | + 3 | 30 | | + 4 | 10 | | + 5 | 50 | | + 6 | 10 | | +(6 rows) + +-- Greedy A+ with FIRST(val) = LAST(val): find longest match where +-- first and last rows have the same val. +-- match_start=1(10): greedy A eats all, B tries last: +-- id6(10=10) → match {1,2,3,4,5,6} +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + id | val | mf | ml +----+-----+----+---- + 1 | 10 | 1 | 6 + 2 | 20 | | + 3 | 30 | | + 4 | 10 | | + 5 | 50 | | + 6 | 10 | | +(6 rows) + +-- SKIP TO NEXT ROW with FIRST(val) = LAST(val): overlapping match attempts. +-- With ONE ROW PER MATCH, each row shows only its first match result. +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A+? B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + id | val | mf | ml +----+-----+----+---- + 1 | 10 | 1 | 4 + 2 | 20 | | + 3 | 30 | | + 4 | 10 | 4 | 6 + 5 | 50 | | + 6 | 10 | | +(6 rows) + +-- FIRST/LAST 2-arg offset form +-- +-- FIRST(val, 0) = FIRST(val): match_start row +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val, 0) = 10 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 6 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- FIRST(val, 1): match_start + 1 row (second row of match) +-- match_start=1(10): FIRST(val,1)=20, B needs val=20 → id2(20) match, id3(30) no +-- match_start=3(30): FIRST(val,1)=10, B needs val=10 → id4(10) match +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS val = FIRST(val, 1) +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 2 + 2 | 20 | | 0 + 3 | 30 | 3 | 2 + 4 | 10 | | 0 + 5 | 50 | 5 | 2 + 6 | 10 | | 0 +(6 rows) + +-- FIRST(val, 99): offset beyond match range → NULL, no match +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val, 99) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- LAST(val, 0) = LAST(val): current row +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 0) > 15 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 3 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | 4 | 2 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- LAST(val, 1): one row back from current (previous match row) +-- At B evaluation on id2: LAST(val,1) = val at id1 = 10 +-- B matches when previous row val < 30 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 1) < 30 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 3 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | 4 | 2 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- LAST(val, 99): offset before match_start → NULL +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 99) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- Error: NULL offset +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(val, NULL::int8) IS NULL +); +ERROR: row pattern navigation offset must not be null +-- Error: negative offset +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(val, -1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +-- FIRST/LAST outside DEFINE clause (error cases) +SELECT first(val) FROM rpr_nav; +ERROR: first can only be used in a DEFINE clause +LINE 1: SELECT first(val) FROM rpr_nav; + ^ +SELECT last(val) FROM rpr_nav; +ERROR: last can only be used in a DEFINE clause +LINE 1: SELECT last(val) FROM rpr_nav; + ^ +SELECT first(val, 1) FROM rpr_nav; +ERROR: first can only be used in a DEFINE clause +LINE 1: SELECT first(val, 1) FROM rpr_nav; + ^ +-- Functional notation: should access column, not RPR navigation +CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text); +INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow'); +SELECT prev(f), next(f), first(f), last(f) FROM rpr_names f; + prev | next | first | last +------+------+-------+------ + 1 | 2 | Joe | Blow +(1 row) + +DROP TABLE rpr_names; +-- Compound navigation: PREV(FIRST(val), M) +-- rpr_nav: (1,10),(2,20),(3,30),(4,10),(5,50),(6,10) +-- PREV(FIRST(val), 1): target = match_start + 0 - 1 = match_start - 1 +-- At match_start=1: target=0 → out of range → NULL +-- At match_start=3: target=2(val=20) → 20 > 0 → true +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 1) > 0 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | | 0 + 2 | 20 | 2 | 5 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- NEXT(FIRST(val, 1), 1): target = match_start + 1 + 1 = match_start + 2 +-- At match_start=1, B on id2: target=1+1+1=3(val=30), 30>0 → true +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val, 1), 1) > 0 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 6 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- PREV(LAST(val), 2): target = currentpos - 0 - 2 = currentpos - 2 +-- Same backward reach as PREV(val, 2) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val), 2) IS NOT NULL +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | | 0 + 2 | 20 | 2 | 5 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- NEXT(LAST(val, 1), 2): target = currentpos - 1 + 2 = currentpos + 1 +-- Looks 1 row ahead: same as NEXT(val, 1) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 1), 2) IS NOT NULL +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 5 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- Compound: outer offset beyond partition (PREV far back) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 99) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- Compound: outer offset beyond partition (NEXT far forward) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 99) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- Compound: inner offset beyond match range (FIRST offset too large) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 99), 1) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- Compound: inner offset beyond match range (LAST offset too large) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 99), 1) IS NOT NULL +); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +-- Compound: NULL outer offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(val), NULL::int8) IS NULL +); +ERROR: row pattern navigation offset must not be null +-- Compound: negative outer offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(val), -1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +-- Compound: default offsets on both sides +-- PREV(FIRST(val)): inner=0 (match_start), outer=1 → target = match_start - 1 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val)) IS NOT NULL +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | | 0 + 2 | 20 | 2 | 5 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- NEXT(LAST(val)): inner=0 (currentpos), outer=1 → target = currentpos + 1 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val)) IS NOT NULL +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 5 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +-- Compound: inner NULL offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(val, NULL::int8), 1) IS NULL +); +ERROR: row pattern navigation offset must not be null +-- Compound: inner negative offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(val, -1), 1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +-- Compound + host variable offsets +PREPARE test_compound_offset(int8, int8) AS +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, $1), $2) IS NOT NULL +); +EXECUTE test_compound_offset(0, 1); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | | 0 + 2 | 20 | 2 | 5 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +EXECUTE test_compound_offset(1, 1); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | 1 | 6 + 2 | 20 | | 0 + 3 | 30 | | 0 + 4 | 10 | | 0 + 5 | 50 | | 0 + 6 | 10 | | 0 +(6 rows) + +DEALLOCATE test_compound_offset; +-- Compound + SKIP TO NEXT ROW: overlapping matches with PREV(FIRST()) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 1) > 0 +); + id | val | mf | cnt +----+-----+----+----- + 1 | 10 | | 0 + 2 | 20 | 2 | 5 + 3 | 30 | 3 | 4 + 4 | 10 | 4 | 3 + 5 | 50 | 5 | 2 + 6 | 10 | | 0 +(6 rows) + +-- Compound + multiple partitions +CREATE TEMP TABLE rpr_nav_part (gid int, id int, val int); +INSERT INTO rpr_nav_part VALUES + (1,1,10),(1,2,20),(1,3,30), + (2,1,40),(2,2,50),(2,3,60); +SELECT gid, id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav_part WINDOW w AS ( + PARTITION BY gid ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 1) > 0 +); + gid | id | val | mf | cnt +-----+----+-----+----+----- + 1 | 1 | 10 | 1 | 3 + 1 | 2 | 20 | | 0 + 1 | 3 | 30 | | 0 + 2 | 1 | 40 | 1 | 3 + 2 | 2 | 50 | | 0 + 2 | 3 | 60 | | 0 +(6 rows) + +DROP TABLE rpr_nav_part; +-- Reverse nesting: FIRST wrapping PREV is prohibited +SELECT id, val FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B) + DEFINE A AS TRUE, B AS FIRST(PREV(val)) > 0 +); +ERROR: FIRST and LAST cannot contain PREV or NEXT +LINE 5: DEFINE A AS TRUE, B AS FIRST(PREV(val)) > 0 + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +-- Reverse nesting: LAST wrapping NEXT is prohibited +SELECT id, val FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B) + DEFINE A AS TRUE, B AS LAST(NEXT(val)) > 0 +); +ERROR: FIRST and LAST cannot contain PREV or NEXT +LINE 5: DEFINE A AS TRUE, B AS LAST(NEXT(val)) > 0 + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +DROP TABLE rpr_nav; -- -- SKIP TO / Backtracking / Frame boundary -- @@ -2247,6 +2850,27 @@ FROM result WHERE match_len > 0; 1 | 99999 (1 row) +RESET jit_above_cost; +RESET jit; +-- JIT compound navigation test +SET jit = on; +SET jit_above_cost = 0; +SELECT count(*) AS matched_rows +FROM ( + SELECT v, count(*) OVER w AS match_len + FROM generate_series(1, 1000) AS t(v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(v), 1) > 0 + ) +) sub WHERE match_len > 0; + matched_rows +-------------- + 1 +(1 row) + RESET jit_above_cost; RESET jit; -- diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 1e450a07ced..0845316965e 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -1629,7 +1629,7 @@ LINE 6: PATTERN (A{,2147483647}) -- Expected: ERROR: quantifier bound must be between 1 and 2147483646 DROP TABLE rpr_bounds; -- ============================================================ --- Navigation Functions Tests (PREV / NEXT) +-- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) -- ============================================================ CREATE TABLE rpr_nav (id INT, val INT); INSERT INTO rpr_nav VALUES @@ -1730,6 +1730,81 @@ ERROR: next can only be used in a DEFINE clause LINE 1: SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt ^ -- Expected: ERROR: next can only be used in a DEFINE clause +-- FIRST function - reference match_start row +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > FIRST(val) +) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 5 + 2 | 20 | 0 + 3 | 15 | 0 + 4 | 25 | 0 + 5 | 30 | 0 +(5 rows) + +-- LAST function without offset - equivalent to current row's value +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS LAST(val) > PREV(val) +) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 3 + 4 | 25 | 0 + 5 | 30 | 0 +(5 rows) + +-- FIRST and LAST combined +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > FIRST(val) AND LAST(val) > PREV(val) +) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 3 + 4 | 25 | 0 + 5 | 30 | 0 +(5 rows) + +-- FIRST function cannot be used other than in DEFINE +SELECT FIRST(id), id, val FROM rpr_nav; +ERROR: first can only be used in a DEFINE clause +LINE 1: SELECT FIRST(id), id, val FROM rpr_nav; + ^ +-- Expected: ERROR: first can only be used in a DEFINE clause +-- LAST function cannot be used other than in DEFINE +SELECT LAST(id), id, val FROM rpr_nav; +ERROR: last can only be used in a DEFINE clause +LINE 1: SELECT LAST(id), id, val FROM rpr_nav; + ^ +-- Expected: ERROR: last can only be used in a DEFINE clause DROP TABLE rpr_nav; -- ============================================================ -- SKIP TO / INITIAL Tests @@ -2227,6 +2302,150 @@ SELECT pg_get_viewdef('rpr_serial_v8'::regclass); d AS (val > 30) ); (1 row) +-- Navigation function serialization: PREV with offset +CREATE VIEW rpr_serial_nav1 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS val > PREV(val, 2)); +SELECT pg_get_viewdef('rpr_serial_nav1'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (val > PREV(val, (2)::bigint)) ); +(1 row) + +-- Navigation function serialization: FIRST and LAST +CREATE VIEW rpr_serial_nav2 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val) < LAST(val, 1)); +SELECT pg_get_viewdef('rpr_serial_nav2'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (FIRST(val) < LAST(val, (1)::bigint)) ); +(1 row) + +-- Navigation function serialization: compound PREV(FIRST()) +CREATE VIEW rpr_serial_nav3 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 1), 2) > 0); +SELECT pg_get_viewdef('rpr_serial_nav3'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (PREV(FIRST(val, (1)::bigint), (2)::bigint) > 0) ); +(1 row) + +-- Navigation function serialization: compound NEXT(LAST()) +CREATE VIEW rpr_serial_nav4 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val), 2) IS NOT NULL); +SELECT pg_get_viewdef('rpr_serial_nav4'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (NEXT(LAST(val), (2)::bigint) IS NOT NULL) ); +(1 row) + +-- Navigation function serialization: compound PREV(LAST()) +CREATE VIEW rpr_serial_nav5 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val, 1), 2) > 0); +SELECT pg_get_viewdef('rpr_serial_nav5'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (PREV(LAST(val, (1)::bigint), (2)::bigint) > 0) ); +(1 row) + +-- Navigation function serialization: compound NEXT(FIRST()) +CREATE VIEW rpr_serial_nav6 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 3) > 0); +SELECT pg_get_viewdef('rpr_serial_nav6'::regclass); + pg_get_viewdef +------------------------------------------------------------------------------ + SELECT id, + + val, + + count(*) OVER w AS count + + FROM rpr_serial + + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a b+) + + DEFINE + + a AS true, + + b AS (NEXT(FIRST(val), (3)::bigint) > 0) ); +(1 row) + -- Reluctant {1}? quantifier deparse through ruleutils CREATE VIEW rpr_quant_reluctant_v AS SELECT id, val, count(*) OVER w @@ -3021,6 +3240,42 @@ ORDER BY id; (3 rows) DROP TABLE rpr_null; +-- Compound navigation: inner nav must be direct arg (not nested in expression) +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v + FIRST(v)) > 0 +); +ERROR: row pattern navigation operation must be a direct argument of the outer navigation +LINE 6: DEFINE A AS PREV(v + FIRST(v)) > 0 + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +-- FIRST/LAST wrapping FIRST/LAST: prohibited +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(FIRST(v)) > 0 +); +ERROR: FIRST and LAST cannot contain FIRST or LAST +LINE 6: DEFINE A AS FIRST(FIRST(v)) > 0 + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +-- Triple nesting: prohibited (3-level deep navigation) +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(PREV(v))) > 0 +); +ERROR: row pattern navigation cannot be nested more than two levels deep +LINE 6: DEFINE A AS PREV(FIRST(PREV(v))) > 0 + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. -- ============================================================ -- Window Deduplication Tests -- ============================================================ diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 77ab25a2289..dc3522f930f 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -36,7 +36,7 @@ -- Window Function Combinations -- DEFINE Expression Variations -- Large Scale Statistics Verification --- Nav Mark Lookback (tuplestore trim) +-- Nav Mark Lookback/Lookahead (tuplestore trim) -- ============================================================ -- Filter function to normalize platform-dependent memory values (not NFA statistics). -- NFA statistics should not change between platforms; if they do, it could @@ -1131,6 +1131,127 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=42.00 loops=1) (10 rows) +-- No absorption when DEFINE uses FIRST (match_start-dependent) +-- Same pattern as rpr_ev_ctx_absorb_unbounded but with FIRST in DEFINE. +-- Compare: absorbed count should be 0 here vs >0 above. +CREATE VIEW rpr_ev_ctx_no_absorb_first AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND v > FIRST(v) +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_no_absorb_first'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------- + PATTERN (a+ b) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND v > FIRST(v) +);'); + rpr_explain_filter +---------------------------------------------------------------------- + WindowAgg (actual rows=50.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ b + Nav Mark Lookback: 0 + Nav Mark Lookahead: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 9 peak, 151 total, 0 merged + NFA Contexts: 5 peak, 51 total, 0 pruned + NFA: 10 matched (len 5/5/5.0), 0 mismatched + NFA: 0 absorbed, 40 skipped (len 1/4/2.5) + -> Function Scan on generate_series s (actual rows=50.00 loops=1) +(11 rows) + +-- Absorption preserved when DEFINE uses only LAST without offset +-- LAST(v) is match_start-independent (always currentpos), so absorption +-- remains active. Compare: absorbed count should be >0, like the +-- PREV-only case above. +CREATE VIEW rpr_ev_ctx_absorb_last AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS LAST(v) % 5 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_last'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------- + PATTERN (a+ b) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS LAST(v) % 5 = 0 +);'); + rpr_explain_filter +---------------------------------------------------------------------- + WindowAgg (actual rows=50.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" b + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 3 peak, 91 total, 0 merged + NFA Contexts: 2 peak, 51 total, 0 pruned + NFA: 10 matched (len 5/5/5.0), 0 mismatched + NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) + -> Function Scan on generate_series s (actual rows=50.00 loops=1) +(10 rows) + +-- No absorption with compound PREV(FIRST()) (match_start-dependent) +CREATE VIEW rpr_ev_ctx_no_absorb_compound AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND PREV(FIRST(v), 1) IS NOT NULL +); +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND PREV(FIRST(v), 1) IS NOT NULL +);'); + rpr_explain_filter +---------------------------------------------------------------------- + WindowAgg (actual rows=50.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ b + Nav Mark Lookback: 0 + Nav Mark Lookahead: -1 + Storage: Memory Maximum Storage: NkB + NFA States: 9 peak, 151 total, 0 merged + NFA Contexts: 5 peak, 51 total, 0 pruned + NFA: 10 matched (len 4/5/4.9), 1 mismatched (len 5/5/5.0) + NFA: 0 absorbed, 39 skipped (len 1/4/2.5) + -> Function Scan on generate_series s (actual rows=50.00 loops=1) +(11 rows) + -- ============================================================ -- Match Length Statistics Tests -- ============================================================ @@ -4343,9 +4464,10 @@ SELECT * FROM ( (9 rows) -- ============================================================ --- Nav Mark Lookback Tests --- Verifies planner-computed navigation offset for tuplestore trim. --- Lookback: how far back from currentpos (PREV/LAST). +-- Nav Mark Lookback/Lookahead Tests +-- Verifies planner-computed navigation offsets for tuplestore trim. +-- Lookback: how far back from currentpos (PREV, LAST, compound PREV_LAST/NEXT_LAST). +-- Lookahead: how far forward from match_start (FIRST, compound PREV_FIRST/NEXT_FIRST). -- ============================================================ -- Prepare statement for host variable offset test below PREPARE rpr_nav_offset_prep(int8) AS @@ -4466,3 +4588,298 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); RESET plan_cache_mode; DEALLOCATE rpr_nav_offset_prep; +-- FIRST(v): retain all (references match_start row) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + Nav Mark Lookahead: 0 + -> Function Scan on generate_series s +(6 rows) + +-- LAST(v, 1): backward reach 1, same as PREV(v, 1) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v, 1) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 1 + -> Function Scan on generate_series s +(5 rows) + +-- LAST(v) without offset + PREV(v): no match_start dependency, offset 1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v) > PREV(v) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 1 + -> Function Scan on generate_series s +(5 rows) + +-- Compound PREV(FIRST(val, 1), 2): lookback from match_start, firstOffset = 1-2 = -1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, 1), 2) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + Nav Mark Lookahead: -1 + -> Function Scan on generate_series s +(6 rows) + +-- Compound NEXT(FIRST(val), 3): firstOffset = 0+3 = 3 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v), 3) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + Nav Mark Lookahead: 3 + -> Function Scan on generate_series s +(6 rows) + +-- Compound PREV(LAST(val), 2): lookback = 0+2 = 2 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v), 2) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 2 + -> Function Scan on generate_series s +(5 rows) + +-- Compound NEXT(LAST(val, 1), 3): lookback = max(1-3, 0) = 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 1), 3) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + -> Function Scan on generate_series s +(5 rows) + +-- Compound PREV(LAST(val, N), M): constant near-overflow (N+M just fits int64) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 4611686018427387903), 4611686018427387903) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 9223372036854775806 + -> Function Scan on generate_series s +(5 rows) + +-- Compound PREV(LAST(val, N), M): constant overflow -> retain all +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: retain all + -> Function Scan on generate_series s +(5 rows) + +-- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> no trim impact +-- N + M overflows int64, but target is forward from match_start so it never +-- constrains trim. Lookahead remains at default (0). +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + Nav Mark Lookahead: 0 + -> Function Scan on generate_series s +(6 rows) + +-- Compound PREV(LAST(val, $1), $2): parameter lookback overflow -> retain all +-- EXPLAIN shows "runtime" (plan-level); EXPLAIN ANALYZE shows "retain all" +-- (executor-resolved). +PREPARE test_overflow_lookback(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE test_overflow_lookback(4611686018427387904, 4611686018427387904); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: runtime + -> Function Scan on generate_series s +(5 rows) + +EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF) + EXECUTE test_overflow_lookback(4611686018427387904, 4611686018427387904); + QUERY PLAN +---------------------------------------------------------------------- + WindowAgg (actual rows=10.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: retain all + Storage: Memory Maximum Storage: 17kB + NFA States: 1 peak, 11 total, 0 merged + NFA Contexts: 2 peak, 11 total, 10 pruned + NFA: 0 matched, 0 mismatched + -> Function Scan on generate_series s (actual rows=10.00 loops=1) +(9 rows) + +RESET plan_cache_mode; +DEALLOCATE test_overflow_lookback; +-- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> no trim impact +PREPARE test_overflow_lookahead(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF) + EXECUTE test_overflow_lookahead(4611686018427387904, 4611686018427387904); + QUERY PLAN +---------------------------------------------------------------------- + WindowAgg (actual rows=10.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 0 + Nav Mark Lookahead: 0 + Storage: Memory Maximum Storage: 17kB + NFA States: 1 peak, 11 total, 0 merged + NFA Contexts: 2 peak, 11 total, 10 pruned + NFA: 0 matched, 0 mismatched + -> Function Scan on generate_series s (actual rows=10.00 loops=1) +(10 rows) + +RESET plan_cache_mode; +DEALLOCATE test_overflow_lookahead; +-- PREV(v) + PREV(v, $1): NEEDS_EVAL path must account for implicit lookback=1 +-- Previously, eval_nav_max_offset_walker skipped PREV(v) when offset_arg was +-- NULL, causing maxOffset=0 when $1=0, which would trim the row needed by +-- PREV(v). Verify this executes without "cannot fetch row before mark" error. +PREPARE test_prev_implicit_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v) IS NOT NULL AND PREV(v, $1) IS NOT NULL +); +EXECUTE test_prev_implicit_offset(0); + count +------- + 0 + 9 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 +(10 rows) + +DEALLOCATE test_prev_implicit_offset; +-- Runtime error: negative offset at execution time +PREPARE test_runtime_neg_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, $1) IS NOT NULL +); +EXECUTE test_runtime_neg_offset(-1); +ERROR: row pattern navigation offset must not be negative +DEALLOCATE test_runtime_neg_offset; +-- Runtime error: null offset at execution time +PREPARE test_runtime_null_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, $1) IS NOT NULL +); +EXECUTE test_runtime_null_offset(NULL); +ERROR: row pattern navigation offset must not be null +DEALLOCATE test_runtime_null_offset; diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index 47f33904690..a05b429ce74 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -776,6 +776,363 @@ WINDOW w AS ( DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1) ); +-- +-- FIRST/LAST navigation +-- + +-- Test data for FIRST/LAST: values cycle back so FIRST(val) = LAST(val) +-- at specific positions. +CREATE TEMP TABLE rpr_nav (id int, val int); +INSERT INTO rpr_nav VALUES (1,10),(2,20),(3,30),(4,10),(5,50),(6,10); + +-- FIRST(val) = constant: B matches when match_start has val=10 +-- match_start=1(10): A=id1, B=id2, FIRST(val)=10 → match {1,2} +-- match_start=3(30): A=id3, B=id4, FIRST(val)=30≠10 → no match +-- match_start=4(10): A=id4, B=id5, FIRST(val)=10 → match {4,5} +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B) + DEFINE A AS TRUE, B AS FIRST(val) = 10 +); + +-- LAST(val): always equals current row's val (offset 0 default) +-- Equivalent to: B AS val > 15 +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B) + DEFINE A AS TRUE, B AS LAST(val) > 15 +); + +-- Reluctant A+? with FIRST(val) = LAST(val): find shortest match where +-- first and last rows have the same val. +-- match_start=1(10): reluctant tries B early: +-- id2(20≠10), id3(30≠10), id4(10=10) → match {1,2,3,4} +-- match_start=5(50): id6(10≠50) → no match +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+? B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + +-- Greedy A+ with FIRST(val) = LAST(val): find longest match where +-- first and last rows have the same val. +-- match_start=1(10): greedy A eats all, B tries last: +-- id6(10=10) → match {1,2,3,4,5,6} +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + +-- SKIP TO NEXT ROW with FIRST(val) = LAST(val): overlapping match attempts. +-- With ONE ROW PER MATCH, each row shows only its first match result. +SELECT id, val, first_value(id) OVER w AS mf, last_value(id) OVER w AS ml +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A+? B) + DEFINE A AS TRUE, B AS FIRST(val) = LAST(val) +); + +-- FIRST/LAST 2-arg offset form +-- +-- FIRST(val, 0) = FIRST(val): match_start row +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val, 0) = 10 +); + +-- FIRST(val, 1): match_start + 1 row (second row of match) +-- match_start=1(10): FIRST(val,1)=20, B needs val=20 → id2(20) match, id3(30) no +-- match_start=3(30): FIRST(val,1)=10, B needs val=10 → id4(10) match +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS val = FIRST(val, 1) +); + +-- FIRST(val, 99): offset beyond match range → NULL, no match +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val, 99) IS NOT NULL +); + +-- LAST(val, 0) = LAST(val): current row +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 0) > 15 +); + +-- LAST(val, 1): one row back from current (previous match row) +-- At B evaluation on id2: LAST(val,1) = val at id1 = 10 +-- B matches when previous row val < 30 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 1) < 30 +); + +-- LAST(val, 99): offset before match_start → NULL +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS LAST(val, 99) IS NOT NULL +); + +-- Error: NULL offset +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(val, NULL::int8) IS NULL +); + +-- Error: negative offset +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(val, -1) IS NULL +); + +-- FIRST/LAST outside DEFINE clause (error cases) +SELECT first(val) FROM rpr_nav; +SELECT last(val) FROM rpr_nav; +SELECT first(val, 1) FROM rpr_nav; + +-- Functional notation: should access column, not RPR navigation +CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text); +INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow'); +SELECT prev(f), next(f), first(f), last(f) FROM rpr_names f; +DROP TABLE rpr_names; + +-- Compound navigation: PREV(FIRST(val), M) +-- rpr_nav: (1,10),(2,20),(3,30),(4,10),(5,50),(6,10) +-- PREV(FIRST(val), 1): target = match_start + 0 - 1 = match_start - 1 +-- At match_start=1: target=0 → out of range → NULL +-- At match_start=3: target=2(val=20) → 20 > 0 → true +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 1) > 0 +); + +-- NEXT(FIRST(val, 1), 1): target = match_start + 1 + 1 = match_start + 2 +-- At match_start=1, B on id2: target=1+1+1=3(val=30), 30>0 → true +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val, 1), 1) > 0 +); + +-- PREV(LAST(val), 2): target = currentpos - 0 - 2 = currentpos - 2 +-- Same backward reach as PREV(val, 2) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val), 2) IS NOT NULL +); + +-- NEXT(LAST(val, 1), 2): target = currentpos - 1 + 2 = currentpos + 1 +-- Looks 1 row ahead: same as NEXT(val, 1) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 1), 2) IS NOT NULL +); + +-- Compound: outer offset beyond partition (PREV far back) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 99) IS NOT NULL +); + +-- Compound: outer offset beyond partition (NEXT far forward) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 99) IS NOT NULL +); + +-- Compound: inner offset beyond match range (FIRST offset too large) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 99), 1) IS NOT NULL +); + +-- Compound: inner offset beyond match range (LAST offset too large) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 99), 1) IS NOT NULL +); + +-- Compound: NULL outer offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(val), NULL::int8) IS NULL +); + +-- Compound: negative outer offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(val), -1) IS NULL +); + +-- Compound: default offsets on both sides +-- PREV(FIRST(val)): inner=0 (match_start), outer=1 → target = match_start - 1 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val)) IS NOT NULL +); + +-- NEXT(LAST(val)): inner=0 (currentpos), outer=1 → target = currentpos + 1 +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val)) IS NOT NULL +); + +-- Compound: inner NULL offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(val, NULL::int8), 1) IS NULL +); + +-- Compound: inner negative offset (runtime error) +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(val, -1), 1) IS NULL +); + +-- Compound + host variable offsets +PREPARE test_compound_offset(int8, int8) AS +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, $1), $2) IS NOT NULL +); +EXECUTE test_compound_offset(0, 1); +EXECUTE test_compound_offset(1, 1); +DEALLOCATE test_compound_offset; + +-- Compound + SKIP TO NEXT ROW: overlapping matches with PREV(FIRST()) +SELECT id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val), 1) > 0 +); + +-- Compound + multiple partitions +CREATE TEMP TABLE rpr_nav_part (gid int, id int, val int); +INSERT INTO rpr_nav_part VALUES + (1,1,10),(1,2,20),(1,3,30), + (2,1,40),(2,2,50),(2,3,60); +SELECT gid, id, val, first_value(id) OVER w AS mf, count(*) OVER w AS cnt +FROM rpr_nav_part WINDOW w AS ( + PARTITION BY gid ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 1) > 0 +); +DROP TABLE rpr_nav_part; + +-- Reverse nesting: FIRST wrapping PREV is prohibited +SELECT id, val FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B) + DEFINE A AS TRUE, B AS FIRST(PREV(val)) > 0 +); + +-- Reverse nesting: LAST wrapping NEXT is prohibited +SELECT id, val FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B) + DEFINE A AS TRUE, B AS LAST(NEXT(val)) > 0 +); + +DROP TABLE rpr_nav; + -- -- SKIP TO / Backtracking / Frame boundary -- @@ -1129,6 +1486,23 @@ FROM result WHERE match_len > 0; RESET jit_above_cost; RESET jit; +-- JIT compound navigation test +SET jit = on; +SET jit_above_cost = 0; +SELECT count(*) AS matched_rows +FROM ( + SELECT v, count(*) OVER w AS match_len + FROM generate_series(1, 1000) AS t(v) + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(v), 1) > 0 + ) +) sub WHERE match_len > 0; +RESET jit_above_cost; +RESET jit; + -- -- IGNORE NULLS -- diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 3accecb73ba..f9d5aa89d7a 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1202,7 +1202,7 @@ WINDOW w AS ( DROP TABLE rpr_bounds; -- ============================================================ --- Navigation Functions Tests (PREV / NEXT) +-- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) -- ============================================================ @@ -1278,6 +1278,53 @@ WINDOW w AS ( ORDER BY id; -- Expected: ERROR: next can only be used in a DEFINE clause +-- FIRST function - reference match_start row +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > FIRST(val) +) +ORDER BY id; + +-- LAST function without offset - equivalent to current row's value +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS LAST(val) > PREV(val) +) +ORDER BY id; + +-- FIRST and LAST combined +SELECT id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > FIRST(val) AND LAST(val) > PREV(val) +) +ORDER BY id; + +-- FIRST function cannot be used other than in DEFINE +SELECT FIRST(id), id, val FROM rpr_nav; +-- Expected: ERROR: first can only be used in a DEFINE clause + +-- LAST function cannot be used other than in DEFINE +SELECT LAST(id), id, val FROM rpr_nav; +-- Expected: ERROR: last can only be used in a DEFINE clause + DROP TABLE rpr_nav; -- ============================================================ @@ -1548,6 +1595,66 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v8 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v8'::regclass); +-- Navigation function serialization: PREV with offset +CREATE VIEW rpr_serial_nav1 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS val > PREV(val, 2)); +SELECT pg_get_viewdef('rpr_serial_nav1'::regclass); + +-- Navigation function serialization: FIRST and LAST +CREATE VIEW rpr_serial_nav2 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS FIRST(val) < LAST(val, 1)); +SELECT pg_get_viewdef('rpr_serial_nav2'::regclass); + +-- Navigation function serialization: compound PREV(FIRST()) +CREATE VIEW rpr_serial_nav3 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 1), 2) > 0); +SELECT pg_get_viewdef('rpr_serial_nav3'::regclass); + +-- Navigation function serialization: compound NEXT(LAST()) +CREATE VIEW rpr_serial_nav4 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val), 2) IS NOT NULL); +SELECT pg_get_viewdef('rpr_serial_nav4'::regclass); + +-- Navigation function serialization: compound PREV(LAST()) +CREATE VIEW rpr_serial_nav5 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val, 1), 2) > 0); +SELECT pg_get_viewdef('rpr_serial_nav5'::regclass); + +-- Navigation function serialization: compound NEXT(FIRST()) +CREATE VIEW rpr_serial_nav6 AS +SELECT id, val, count(*) OVER w +FROM rpr_serial +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val), 3) > 0); +SELECT pg_get_viewdef('rpr_serial_nav6'::regclass); + -- Reluctant {1}? quantifier deparse through ruleutils CREATE VIEW rpr_quant_reluctant_v AS SELECT id, val, count(*) OVER w @@ -2121,6 +2228,33 @@ ORDER BY id; DROP TABLE rpr_null; +-- Compound navigation: inner nav must be direct arg (not nested in expression) +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v + FIRST(v)) > 0 +); + +-- FIRST/LAST wrapping FIRST/LAST: prohibited +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(FIRST(v)) > 0 +); + +-- Triple nesting: prohibited (3-level deep navigation) +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(PREV(v))) > 0 +); + -- ============================================================ -- Window Deduplication Tests -- ============================================================ diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index 5082cc2b5de..a3789e92631 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -36,7 +36,7 @@ -- Window Function Combinations -- DEFINE Expression Variations -- Large Scale Statistics Verification --- Nav Mark Lookback (tuplestore trim) +-- Nav Mark Lookback/Lookahead (tuplestore trim) -- ============================================================ -- Filter function to normalize platform-dependent memory values (not NFA statistics). @@ -705,6 +705,76 @@ WINDOW w AS ( C AS v % 21 IN (3, 5, 8, 10, 13, 15, 18, 20) );'); +-- No absorption when DEFINE uses FIRST (match_start-dependent) +-- Same pattern as rpr_ev_ctx_absorb_unbounded but with FIRST in DEFINE. +-- Compare: absorbed count should be 0 here vs >0 above. +CREATE VIEW rpr_ev_ctx_no_absorb_first AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND v > FIRST(v) +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_no_absorb_first'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND v > FIRST(v) +);'); + +-- Absorption preserved when DEFINE uses only LAST without offset +-- LAST(v) is match_start-independent (always currentpos), so absorption +-- remains active. Compare: absorbed count should be >0, like the +-- PREV-only case above. +CREATE VIEW rpr_ev_ctx_absorb_last AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS LAST(v) % 5 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_ctx_absorb_last'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS LAST(v) % 5 = 0 +);'); + +-- No absorption with compound PREV(FIRST()) (match_start-dependent) +CREATE VIEW rpr_ev_ctx_no_absorb_compound AS +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND PREV(FIRST(v), 1) IS NOT NULL +); +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 50) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A+ B) + DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 AND PREV(FIRST(v), 1) IS NOT NULL +);'); + -- ============================================================ -- Match Length Statistics Tests -- ============================================================ @@ -2478,9 +2548,10 @@ SELECT * FROM ( ) t WHERE cnt > 0; -- ============================================================ --- Nav Mark Lookback Tests --- Verifies planner-computed navigation offset for tuplestore trim. --- Lookback: how far back from currentpos (PREV/LAST). +-- Nav Mark Lookback/Lookahead Tests +-- Verifies planner-computed navigation offsets for tuplestore trim. +-- Lookback: how far back from currentpos (PREV, LAST, compound PREV_LAST/NEXT_LAST). +-- Lookahead: how far forward from match_start (FIRST, compound PREV_FIRST/NEXT_FIRST). -- ============================================================ -- Prepare statement for host variable offset test below @@ -2547,3 +2618,166 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); RESET plan_cache_mode; DEALLOCATE rpr_nav_offset_prep; +-- FIRST(v): retain all (references match_start row) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v) +); + +-- LAST(v, 1): backward reach 1, same as PREV(v, 1) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v, 1) > 0 +); + +-- LAST(v) without offset + PREV(v): no match_start dependency, offset 1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v) > PREV(v) +); + +-- Compound PREV(FIRST(val, 1), 2): lookback from match_start, firstOffset = 1-2 = -1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, 1), 2) > 0 +); + +-- Compound NEXT(FIRST(val), 3): firstOffset = 0+3 = 3 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v), 3) > 0 +); + +-- Compound PREV(LAST(val), 2): lookback = 0+2 = 2 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v), 2) > 0 +); + +-- Compound NEXT(LAST(val, 1), 3): lookback = max(1-3, 0) = 0 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 1), 3) > 0 +); + +-- Compound PREV(LAST(val, N), M): constant near-overflow (N+M just fits int64) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 4611686018427387903), 4611686018427387903) IS NOT NULL +); + +-- Compound PREV(LAST(val, N), M): constant overflow -> retain all +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + +-- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> no trim impact +-- N + M overflows int64, but target is forward from match_start so it never +-- constrains trim. Lookahead remains at default (0). +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + +-- Compound PREV(LAST(val, $1), $2): parameter lookback overflow -> retain all +-- EXPLAIN shows "runtime" (plan-level); EXPLAIN ANALYZE shows "retain all" +-- (executor-resolved). +PREPARE test_overflow_lookback(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE test_overflow_lookback(4611686018427387904, 4611686018427387904); +EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF) + EXECUTE test_overflow_lookback(4611686018427387904, 4611686018427387904); +RESET plan_cache_mode; +DEALLOCATE test_overflow_lookback; + +-- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> no trim impact +PREPARE test_overflow_lookahead(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF) + EXECUTE test_overflow_lookahead(4611686018427387904, 4611686018427387904); +RESET plan_cache_mode; +DEALLOCATE test_overflow_lookahead; + +-- PREV(v) + PREV(v, $1): NEEDS_EVAL path must account for implicit lookback=1 +-- Previously, eval_nav_max_offset_walker skipped PREV(v) when offset_arg was +-- NULL, causing maxOffset=0 when $1=0, which would trim the row needed by +-- PREV(v). Verify this executes without "cannot fetch row before mark" error. +PREPARE test_prev_implicit_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v) IS NOT NULL AND PREV(v, $1) IS NOT NULL +); +EXECUTE test_prev_implicit_offset(0); +DEALLOCATE test_prev_implicit_offset; + +-- Runtime error: negative offset at execution time +PREPARE test_runtime_neg_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, $1) IS NOT NULL +); +EXECUTE test_runtime_neg_offset(-1); +DEALLOCATE test_runtime_neg_offset; + +-- Runtime error: null offset at execution time +PREPARE test_runtime_null_offset(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, $1) IS NOT NULL +); +EXECUTE test_runtime_null_offset(NULL); +DEALLOCATE test_runtime_null_offset; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 16de1421302..a3b1a855cdc 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -747,6 +747,8 @@ ErrorData ErrorSaveContext EstimateDSMForeignScan_function EstimationInfo +EvalNavFirstContext +EvalNavMaxContext EventTriggerCacheEntry EventTriggerCacheItem EventTriggerCacheStateType @@ -1801,6 +1803,8 @@ NamedLWLockTrancheRequest NamedTuplestoreScan NamedTuplestoreScanState NamespaceInfo +NavCheckResult +NavOffsetContext NestLoop NestLoopParam NestLoopState @@ -2479,6 +2483,7 @@ QueuePosition QuitSignalReason RPRNavExpr RPRNavKind +RPRNavOffsetKind RBTNode RBTOrderControl RBTree -- 2.50.1 (Apple Git-155)