From 33a8de8aeb8c022cac0204f0b1fc84bcdc63570e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sun, 5 Apr 2026 00:56:20 +0900 Subject: [PATCH] Implement FIRST/LAST 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. --- src/backend/executor/execExpr.c | 16 +- src/backend/executor/execExprInterp.c | 54 +++++-- src/backend/executor/execRPR.c | 78 ++++++++- src/backend/executor/nodeWindowAgg.c | 28 ++-- src/backend/optimizer/plan/createplan.c | 95 ++++++++++- src/backend/optimizer/plan/rpr.c | 6 +- src/backend/parser/parse_func.c | 70 ++++++-- src/backend/parser/parse_rpr.c | 56 ++++++- src/backend/utils/adt/ruleutils.c | 19 ++- src/backend/utils/adt/windowfuncs.c | 56 +++++++ src/include/catalog/pg_proc.dat | 12 ++ src/include/executor/execExpr.h | 2 +- src/include/nodes/execnodes.h | 9 +- src/include/nodes/plannodes.h | 13 +- src/include/nodes/primnodes.h | 11 +- src/include/optimizer/rpr.h | 10 +- src/test/regress/expected/rpr.out | 186 ++++++++++++++++++++-- src/test/regress/expected/rpr_base.out | 77 ++++++++- src/test/regress/expected/rpr_explain.out | 83 ++++++++++ src/test/regress/sql/rpr.sql | 100 ++++++++++++ src/test/regress/sql/rpr_base.sql | 49 +++++- src/test/regress/sql/rpr_explain.sql | 49 ++++++ 22 files changed, 990 insertions(+), 89 deletions(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index dbed4f48a0f..af2e915701e 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; diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index e41faa95be3..88c5e3b7635 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -5942,7 +5942,7 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans, } /* - * Evaluate RPR PREV/NEXT navigation: swap slot to target row. + * 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 +5963,36 @@ 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 unsigned offset. For 2-arg forms the offset expression + * has already been evaluated into offset_value. 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: 0 (standard 5.6.3) */ 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 PREV/NEXT, 0 for FIRST/LAST */ + if (op->d.rpr_nav.kind == RPR_NAV_FIRST || + op->d.rpr_nav.kind == RPR_NAV_LAST) + offset = 0; + else + offset = 1; + } /* * Calculate target position based on navigation direction. On overflow, @@ -5999,8 +6008,33 @@ 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; } + /* + * 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 +6049,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 329cbfbaaaa..10e06a30f8e 100644 --- a/src/backend/executor/execRPR.c +++ b/src/backend/executor/execRPR.c @@ -609,17 +609,17 @@ * 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 * * 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). @@ -2997,12 +2997,63 @@ ExecRPRRecordContextFailure(WindowAggState *winstate, int64 failedLen) * 2. Absorb redundant contexts - ideal timing after convergence * 3. Advance all contexts (divergence) - create new states for next row */ +/* + * 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; +} + void ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos, bool hasLimitedFrame, int64 frameOffset) { 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,7 +3080,24 @@ ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos, } } - nfa_match(winstate, ctx, varMatched); + /* + * 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); + + /* Restore shared varMatched values for dependent variables */ + nfa_reevaluate_dependent_vars(winstate, winstate->nfaContext, + currentPos); + } + else + { + nfa_match(winstate, ctx, varMatched); + } ctx->lastProcessedRow = currentPos; } diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 186029df1d9..867ff5068e3 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -938,9 +938,9 @@ eval_windowaggregates(WindowAggState *winstate) 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 RPR is used, navigation 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) { @@ -1279,7 +1279,7 @@ 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 @@ -2527,7 +2527,7 @@ 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 && @@ -2782,6 +2782,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; } /* @@ -2991,11 +2992,14 @@ 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 max backward nav offset for tuplestore trim */ winstate->navMaxOffset = node->navMaxOffset; if (winstate->navMaxOffset == RPR_NAV_OFFSET_NEEDS_EVAL) eval_nav_max_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) { @@ -4215,8 +4219,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 */ @@ -4304,8 +4314,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/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index ee2d53b5924..c4c40d24cf2 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -292,6 +292,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, @@ -2482,6 +2483,20 @@ nav_max_offset_walker(Node *node, int64 *maxOffset) { RPRNavExpr *nav = (RPRNavExpr *) node; + /* + * FIRST always references match_start, so we must retain the entire + * partition. LAST with an explicit offset can look back to + * match_start for the boundary check, so same treatment. LAST without + * offset always resolves to currentpos and never looks backward — + * no impact on trim. + */ + if (nav->kind == RPR_NAV_FIRST || + (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL)) + { + *maxOffset = RPR_NAV_OFFSET_RETAIN_ALL; + return true; /* stop walking */ + } + /* Only PREV looks backward; NEXT is irrelevant for trim */ if (nav->kind == RPR_NAV_PREV) { @@ -2538,9 +2553,10 @@ nav_max_offset_walker(Node *node, int64 *maxOffset) * compute_nav_max_offset * Compute the maximum PREV offset from DEFINE clause expressions. * - * 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. + * Returns the maximum constant offset found, RPR_NAV_OFFSET_NEEDS_EVAL if + * any PREV offset cannot be determined statically, or + * RPR_NAV_OFFSET_RETAIN_ALL if FIRST/LAST is present. NEXT offsets are + * ignored since they look forward and don't affect tuplestore trim. */ static int64 compute_nav_max_offset(List *defineClause) @@ -2559,6 +2575,65 @@ compute_nav_max_offset(List *defineClause) return maxOffset; } +/* + * has_match_start_dependency + * Check if an expression tree contains FIRST or LAST-with-offset, + * which depend on match_start and require per-context evaluation. + * + * LAST without offset always resolves to currentpos and is + * match_start-independent. + */ +static bool +has_match_start_dependency(Node *node) +{ + 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; + + /* Check children (arg may contain further nav expressions) */ + return has_match_start_dependency((Node *) nav->arg); + } + + return expression_tree_walker(node, has_match_start_dependency, NULL); +} + +/* + * compute_match_start_dependent + * Build a Bitmapset of DEFINE variable indices whose expressions + * depend on match_start (contain FIRST or LAST-with-offset). + * + * Variables in this set require per-context re-evaluation during NFA + * processing, because different contexts may have different match_start + * values. + */ +static Bitmapset * +compute_match_start_dependent(List *defineClause) +{ + Bitmapset *result = NULL; + ListCell *lc; + int varIdx = 0; + + foreach(lc, defineClause) + { + TargetEntry *te = (TargetEntry *) lfirst(lc); + + if (has_match_start_dependency((Node *) te->expr)) + result = bms_add_member(result, varIdx); + + varIdx++; + } + + return result; +} + /* * create_windowagg_plan * @@ -2586,6 +2661,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) List *defineVariableList = NIL; List *filteredDefineClause = NIL; RPRPattern *compiledPattern = NULL; + Bitmapset *matchStartDependent = NULL; /* @@ -2648,11 +2724,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 +2750,7 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) wc->rpSkipTo, compiledPattern, filteredDefineClause, + matchStartDependent, best_path->qual, best_path->topwindow, subplan); @@ -6742,6 +6823,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,7 +6858,10 @@ make_windowagg(List *tlist, WindowClause *wc, node->defineClause = defineClause; - /* Compute max PREV offset for tuplestore trim optimization */ + /* Store pre-computed match_start dependency bitmapset */ + node->defineMatchStartDependent = defineMatchStartDependent; + + /* Compute max backward nav offset for tuplestore trim optimization */ node->navMaxOffset = compute_nav_max_offset(defineClause); plan->targetlist = tlist; 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..dab02fff4f2 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,13 +429,20 @@ 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) */ bool has_column_ref; /* Var found */ + RPRNavKind inner_kind; /* kind of nested RPRNavExpr, if any */ } NavCheckResult; static bool @@ -446,7 +453,10 @@ nav_check_walker(Node *node, void *context) if (node == NULL) return false; if (IsA(node, RPRNavExpr)) + { result->has_nav = true; + result->inner_kind = ((RPRNavExpr *) node)->kind; + } if (IsA(node, Var)) result->has_column_ref = true; @@ -457,16 +467,46 @@ 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))); + { + 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: valid compound navigation per + * SQL standard 5.6.4, but not yet implemented. + */ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("compound row pattern navigation is not yet supported"), + parser_errposition(pstate, nav->location))); + } + 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"), + parser_errposition(pstate, nav->location))); + } + else + { + /* Same-category nesting: prohibited */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern navigation operations cannot be nested"), + parser_errposition(pstate, nav->location))); + } + } if (!result.has_column_ref) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -483,7 +523,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 742d889154b..e9cf49ca210 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -10109,9 +10109,24 @@ get_rule_expr(Node *node, deparse_context *context, case T_RPRNavExpr: { RPRNavExpr *nav = (RPRNavExpr *) node; + const char *funcname; - appendStringInfoString(buf, - nav->kind == RPR_NAV_PREV ? "PREV(" : "NEXT("); + switch (nav->kind) + { + case RPR_NAV_PREV: + funcname = "PREV("; + break; + case RPR_NAV_NEXT: + funcname = "NEXT("; + break; + case RPR_NAV_FIRST: + funcname = "FIRST("; + break; + case RPR_NAV_LAST: + funcname = "LAST("; + break; + } + appendStringInfoString(buf, funcname); get_rule_expr((Node *) nav->arg, context, showimplicit); if (nav->offset_arg != NULL) { 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..a34e1a8dfe6 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -699,7 +699,7 @@ typedef struct ExprEvalStep struct { WindowAggState *winstate; - RPRNavKind kind; /* PREV or NEXT */ + RPRNavKind kind; /* PREV, NEXT, FIRST, or LAST */ Datum *offset_value; /* 2-arg: runtime offset value, or * NULL */ bool *offset_isnull; /* 2-arg: runtime offset null flag */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index ff6d7d70a60..d65cee1aefd 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,14 @@ typedef struct WindowAggState TupleTableSlot *temp_slot_2; /* RPR navigation */ - int64 navMaxOffset; /* max PREV offset; see RPR_NAV_OFFSET_* */ + int64 navMaxOffset; /* max backward nav offset; see + * RPR_NAV_OFFSET_* */ 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/plannodes.h b/src/include/nodes/plannodes.h index 27a2e7b48c7..a4b1e880fe4 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -1387,11 +1387,18 @@ typedef struct WindowAgg List *defineClause; /* - * Maximum PREV offset for tuplestore mark optimization. >= 0: statically - * determined max offset (mark = currentpos - offset). + * Bitmapset of DEFINE variable indices whose expressions depend on + * match_start (contain FIRST or LAST-with-offset). Variables in this set + * require per-context re-evaluation during NFA processing. + */ + Bitmapset *defineMatchStartDependent; + + /* + * Maximum backward navigation 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). + * (e.g., FIRST/LAST navigation). */ int64 navMaxOffset; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 94723a3b909..0e40fad6291 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -651,25 +651,28 @@ 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 + * 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 which uses an implicit offset of 1 + * the 1-arg form (implicit offset: 1 for PREV/NEXT, 0 for + * FIRST/LAST) */ typedef enum RPRNavKind { RPR_NAV_PREV, RPR_NAV_NEXT, + RPR_NAV_FIRST, + RPR_NAV_LAST, } RPRNavKind; typedef struct RPRNavExpr { Expr xpr; - RPRNavKind kind; /* PREV or NEXT */ + RPRNavKind kind; /* PREV, NEXT, FIRST, or LAST */ Expr *arg; /* argument expression */ Expr *offset_arg; /* offset expression, or NULL for 1-arg form */ Oid resulttype; /* result type (same as arg's type) */ diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h index 91148f6c574..90e13ed7249 100644 --- a/src/include/optimizer/rpr.h +++ b/src/include/optimizer/rpr.h @@ -57,17 +57,19 @@ /* * navMaxOffset sentinel values. - * Non-negative values represent a statically determined maximum PREV offset. + * Non-negative values represent a statically determined maximum backward + * navigation offset (PREV). */ -#define RPR_NAV_OFFSET_NEEDS_EVAL (-1) /* has non-constant PREV offset; +#define RPR_NAV_OFFSET_NEEDS_EVAL (-1) /* has non-constant offset; * evaluate at executor init */ #define RPR_NAV_OFFSET_RETAIN_ALL (-2) /* must retain entire partition - * (e.g., future FIRST/LAST) */ + * (e.g., FIRST/LAST navigation) */ 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 de6ce4fba8a..fb623ea152b 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1040,7 +1040,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > PREV(PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: row pattern navigation operations cannot be nested LINE 7: DEFINE A AS price > PREV(PREV(price)) ^ -- Nested NEXT @@ -1052,7 +1052,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(NEXT(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: row pattern navigation operations cannot be nested LINE 7: DEFINE A AS price > NEXT(NEXT(price)) ^ -- PREV nested inside NEXT @@ -1064,7 +1064,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: row pattern navigation operations cannot be nested LINE 7: DEFINE A AS price > NEXT(PREV(price)) ^ -- PREV nested inside expression inside NEXT @@ -1076,7 +1076,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > NEXT(price * PREV(price)) ); -ERROR: PREV and NEXT cannot be nested +ERROR: row pattern navigation operations cannot be nested LINE 7: DEFINE A AS price > NEXT(price * PREV(price)) ^ -- Triple nesting: error reported at outermost PREV @@ -1088,7 +1088,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS price > PREV(PREV(PREV(price))) ); -ERROR: PREV and NEXT cannot be nested +ERROR: row pattern navigation operations cannot be nested LINE 7: DEFINE A AS price > PREV(PREV(PREV(price))) ^ -- No column reference in PREV/NEXT argument @@ -1137,7 +1137,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 +1149,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 +1442,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 +1452,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 +1462,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 +1474,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 +1489,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: two PREV with different offsets in same DEFINE clause -- B: price exceeds both 1-back and 2-back values @@ -1567,6 +1567,164 @@ 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 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: not yet supported +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 PREV(FIRST(val), 1) > 0 +); +ERROR: compound row pattern navigation is not yet supported +LINE 5: DEFINE A AS TRUE, B AS PREV(FIRST(val), 1) > 0 + ^ +-- 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 + ^ +DROP TABLE rpr_nav; -- -- SKIP TO / Backtracking / Frame boundary -- diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 7452cf1b3ab..709d8f5d8e6 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 diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 4a646d1e1d8..83a035023ba 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -1086,6 +1086,89 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=42.00 loops=1) (9 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 + 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) +(9 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 + 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) +(9 rows) + -- ============================================================ -- Match Length Statistics Tests -- ============================================================ diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index b3bbc8254c4..27929c47d7b 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -760,6 +760,106 @@ 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 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: not yet supported +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 PREV(FIRST(val), 1) > 0 +); + +-- 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 +); + +DROP TABLE rpr_nav; + -- -- SKIP TO / Backtracking / Frame boundary -- diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 8c23c7598a3..6ac248b3101 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; -- ============================================================ diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index aed2f69bc4f..9aed4f72070 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -692,6 +692,55 @@ 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 +);'); + -- ============================================================ -- Match Length Statistics Tests -- ============================================================ -- 2.50.1 (Apple Git-155)