wip-0015-Implement-FIRST-LAST-navigation-for-RPR.txt
text/plain
Filename: wip-0015-Implement-FIRST-LAST-navigation-for-RPR.txt
Type: text/plain
Part: 14
Message:
Re: Row pattern recognition
From 33a8de8aeb8c022cac0204f0b1fc84bcdc63570e Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
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)