nocfbot-experimental-Implement-1-slot-PREV-NEXT-navigation.txt
text/plain
Filename: nocfbot-experimental-Implement-1-slot-PREV-NEXT-navigation.txt
Type: text/plain
Part: 15
Message:
Re: Row pattern recognition
From f22f8e90163e89bbb235a363db3cfcac5b95f522 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Fri, 20 Mar 2026 23:47:12 +0900
Subject: [PATCH] Implement 1-slot PREV/NEXT navigation for RPR
Add PREV(value [, offset]) and NEXT(value [, offset]) navigation functions
for use in the DEFINE clause of row pattern recognition.
These functions return the column value at a row offset rows before/after
the current row within the window frame, returning NULL if the target row
is outside the frame. The offset defaults to 1 if omitted; offset=0 refers
to the current row itself; NULL offset returns NULL.
Implementation adds nav_slot and nav_null_slot to WindowAggState to fetch
the target row without disturbing the main evaluation slot, along with a
dedicated nav_winobj for navigation reads. The RPRNavExpr node carries
the offset expression and direction (PREV/NEXT); it is evaluated via a
new EEOP_RPR_NAV expression step in the expression interpreter (and
corresponding LLVM JIT path).
Parser changes: PREV/NEXT are parsed as function calls and transformed into
RPRNavExpr nodes in transformDefineClause(). Validation rejects nested
PREV/NEXT calls, requires at least one column reference in the first
argument, and ensures the offset argument is a run-time constant.
Documentation updated to reflect the 2-argument form and NULL-offset
behavior.
---
doc/src/sgml/func/func-window.sgml | 22 +-
src/backend/executor/execExpr.c | 57 +++
src/backend/executor/execExprInterp.c | 105 +++++
src/backend/executor/nodeWindowAgg.c | 240 +++++------
src/backend/jit/llvm/llvmjit_expr.c | 29 ++
src/backend/jit/llvm/llvmjit_types.c | 2 +
src/backend/nodes/nodeFuncs.c | 33 ++
src/backend/parser/parse_func.c | 30 +-
src/backend/parser/parse_rpr.c | 79 ++++
src/backend/utils/adt/ruleutils.c | 16 +
src/backend/utils/adt/windowfuncs.c | 52 ++-
src/include/catalog/pg_proc.dat | 6 +
src/include/executor/execExpr.h | 19 +
src/include/executor/nodeWindowAgg.h | 3 +
src/include/nodes/execnodes.h | 10 +-
src/include/nodes/primnodes.h | 31 ++
src/test/regress/expected/rpr.out | 493 +++++++++++++++++++++-
src/test/regress/expected/rpr_explain.out | 114 ++++-
src/test/regress/sql/rpr.sql | 291 ++++++++++++-
src/test/regress/sql/rpr_explain.sql | 84 +++-
src/tools/pgindent/typedefs.list | 3 +-
21 files changed, 1524 insertions(+), 195 deletions(-)
diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml
index ae36e0f3135..cb1a852c4d2 100644
--- a/doc/src/sgml/func/func-window.sgml
+++ b/doc/src/sgml/func/func-window.sgml
@@ -304,12 +304,17 @@
<indexterm>
<primary>prev</primary>
</indexterm>
- <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> [, <parameter>offset</parameter> <type>bigint</type> ] )
<returnvalue>anyelement</returnvalue>
</para>
<para>
- Returns the column value at the previous row;
- returns NULL if there is no previous row in the window frame.
+ Returns the column value at the row <parameter>offset</parameter>
+ rows before the current row within the window frame;
+ returns NULL if the target row is outside the window frame.
+ <parameter>offset</parameter> defaults to 1 if omitted.
+ <parameter>offset</parameter> must be a non-negative integer;
+ an offset of 0 refers to the current row itself.
+ If <parameter>offset</parameter> is NULL, NULL is returned.
</para></entry>
</row>
@@ -318,12 +323,17 @@
<indexterm>
<primary>next</primary>
</indexterm>
- <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> [, <parameter>offset</parameter> <type>bigint</type> ] )
<returnvalue>anyelement</returnvalue>
</para>
<para>
- Returns the column value at the next row;
- returns NULL if there is no next row in the window frame.
+ Returns the column value at the row <parameter>offset</parameter>
+ rows after the current row within the window frame;
+ returns NULL if the target row is outside the window frame.
+ <parameter>offset</parameter> defaults to 1 if omitted.
+ <parameter>offset</parameter> must be a non-negative integer;
+ an offset of 0 refers to the current row itself.
+ If <parameter>offset</parameter> is NULL, NULL is returned.
</para></entry>
</row>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..a8136638618 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1189,6 +1189,63 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ 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.
+ */
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+ WindowAggState *winstate;
+
+ Assert(state->parent && IsA(state->parent, WindowAggState));
+ winstate = (WindowAggState *) state->parent;
+
+ /* Emit SET opcode: swap slot to target row */
+ scratch.opcode = EEOP_RPR_NAV_SET;
+ scratch.d.rpr_nav.winstate = winstate;
+ scratch.d.rpr_nav.offset = nav->kind; /* RPR_NAV_PREV=-1,
+ * RPR_NAV_NEXT=1 */
+
+ if (nav->offset_arg != NULL)
+ {
+ /*
+ * Allocate storage for the runtime offset value. The
+ * offset expression is compiled below so it runs before
+ * EEOP_RPR_NAV_SET.
+ */
+ Datum *offset_value = palloc_object(Datum);
+ bool *offset_isnull = palloc_object(bool);
+
+ /* Compile the offset expression into the temp storage */
+ ExecInitExprRec(nav->offset_arg, state,
+ offset_value, offset_isnull);
+
+ scratch.d.rpr_nav.offset_value = offset_value;
+ scratch.d.rpr_nav.offset_isnull = offset_isnull;
+ }
+ else
+ {
+ scratch.d.rpr_nav.offset_value = NULL;
+ scratch.d.rpr_nav.offset_isnull = NULL;
+ }
+
+ ExprEvalPushStep(state, &scratch);
+
+ /* Compile the argument expression normally */
+ ExecInitExprRec(nav->arg, state, resv, resnull);
+
+ /* Emit RESTORE opcode: restore original slot */
+ scratch.opcode = EEOP_RPR_NAV_RESTORE;
+ scratch.d.rpr_nav.winstate = winstate;
+ ExprEvalPushStep(state, &scratch);
+ break;
+ }
+
case T_FuncExpr:
{
FuncExpr *func = (FuncExpr *) node;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 61ff5ddc74c..789b8f0c1c7 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -56,11 +56,13 @@
*/
#include "postgres.h"
+#include "common/int.h"
#include "access/heaptoast.h"
#include "catalog/pg_type.h"
#include "commands/sequence.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
+#include "executor/nodeWindowAgg.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/miscnodes.h"
@@ -576,6 +578,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_WINDOW_FUNC,
&&CASE_EEOP_MERGE_SUPPORT_FUNC,
&&CASE_EEOP_SUBPLAN,
+ &&CASE_EEOP_RPR_NAV_SET,
+ &&CASE_EEOP_RPR_NAV_RESTORE,
&&CASE_EEOP_AGG_STRICT_DESERIALIZE,
&&CASE_EEOP_AGG_DESERIALIZE,
&&CASE_EEOP_AGG_STRICT_INPUT_CHECK_ARGS,
@@ -2003,6 +2007,24 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ /* RPR navigation: swap slot to target row */
+ EEO_CASE(EEOP_RPR_NAV_SET)
+ {
+ ExecEvalRPRNavSet(state, op, econtext);
+ outerslot = econtext->ecxt_outertuple;
+
+ EEO_NEXT();
+ }
+
+ /* RPR navigation: restore slot to original row */
+ EEO_CASE(EEOP_RPR_NAV_RESTORE)
+ {
+ ExecEvalRPRNavRestore(state, op, econtext);
+ outerslot = econtext->ecxt_outertuple;
+
+ EEO_NEXT();
+ }
+
/* evaluate a strict aggregate deserialization function */
EEO_CASE(EEOP_AGG_STRICT_DESERIALIZE)
{
@@ -5918,3 +5940,86 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans,
MemoryContextSwitchTo(oldContext);
}
+
+/*
+ * Evaluate RPR PREV/NEXT navigation: 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
+ * tuplestore, and replaces econtext->ecxt_outertuple with it.
+ *
+ * This is called both from the interpreter inline handler and from
+ * JIT-compiled expressions via build_EvalXFunc.
+ */
+void
+ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+{
+ WindowAggState *winstate = op->d.rpr_nav.winstate;
+ int64 offset;
+ int64 target_pos;
+ TupleTableSlot *target_slot;
+
+ /* Save current slot for later restore */
+ winstate->nav_saved_outertuple = econtext->ecxt_outertuple;
+
+ /*
+ * Determine the signed offset. For 2-arg PREV/NEXT the offset expression
+ * has already been evaluated into offset_value. NULL or negative offsets
+ * are errors (matching Oracle behavior).
+ */
+ if (op->d.rpr_nav.offset_value != NULL)
+ {
+ int64 raw_offset;
+
+ if (*op->d.rpr_nav.offset_isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("PREV/NEXT offset must not be null")));
+
+ raw_offset = DatumGetInt64(*op->d.rpr_nav.offset_value);
+
+ if (raw_offset < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PREV/NEXT offset must not be negative")));
+
+ /* Apply direction sign: PREV subtracts, NEXT adds */
+ offset = raw_offset * op->d.rpr_nav.offset;
+ }
+ else
+ offset = op->d.rpr_nav.offset;
+
+ /*
+ * Calculate target position. On overflow, use -1 so that
+ * ExecRPRNavGetSlot treats it as out of range.
+ */
+ if (pg_add_s64_overflow(winstate->currentpos, offset, &target_pos))
+ target_pos = -1;
+
+ /* Fetch target row slot (returns nav_null_slot if out of range) */
+ target_slot = ExecRPRNavGetSlot(winstate, target_pos);
+
+ /*
+ * Update econtext to point to the target slot. Also decompress the new
+ * slot's attributes since FETCHSOME already ran for the original slot.
+ * The caller (interpreter or JIT) is responsible for updating any local
+ * slot cache (e.g. outerslot) from econtext after we return.
+ */
+ slot_getallattrs(target_slot);
+ econtext->ecxt_outertuple = target_slot;
+}
+
+/*
+ * Evaluate RPR PREV/NEXT navigation: restore slot to original row.
+ *
+ * Restores econtext->ecxt_outertuple from the saved slot in winstate.
+ * The caller is responsible for updating any local slot cache.
+ */
+void
+ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ WindowAggState *winstate = op->d.rpr_nav.winstate;
+
+ econtext->ecxt_outertuple = winstate->nav_saved_outertuple;
+}
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index f1f9d60b39d..83377b4f18d 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -53,7 +53,6 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
-#include "utils/fmgroids.h"
#include "utils/expandeddatum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -176,14 +175,6 @@ typedef struct WindowStatePerAggData
bool restart; /* need to restart this agg in this cycle? */
} WindowStatePerAggData;
-/*
- * Structure used by check_rpr_navigation() and rpr_navigation_walker().
- */
-typedef struct NavigationInfo
-{
- bool is_prev; /* true if PREV */
- int num_vars; /* number of var nodes */
-} NavigationInfo;
static void initialize_windowaggregate(WindowAggState *winstate,
WindowStatePerFunc perfuncstate,
@@ -242,9 +233,6 @@ static uint8 get_notnull_info(WindowObject winobj,
int64 pos, int argno);
static void put_notnull_info(WindowObject winobj,
int64 pos, int argno, bool isnull);
-static void attno_map(Node *node);
-static bool attno_map_walker(Node *node, void *context);
-
static bool rpr_is_defined(WindowAggState *winstate);
static int row_is_in_reduced_frame(WindowObject winobj, int64 pos);
@@ -255,9 +243,6 @@ static void register_reduced_frame_map(WindowAggState *winstate, int64 pos,
int val);
static void update_reduced_frame(WindowObject winobj, int64 pos);
-static void check_rpr_navigation(Node *node, bool is_prev);
-static bool rpr_navigation_walker(Node *node, void *context);
-
/* Forward declarations - NFA row evaluation */
static bool nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched);
@@ -1301,6 +1286,21 @@ prepare_tuplestore(WindowAggState *winstate)
}
}
+ /* Create read/mark pointers for RPR navigation if needed */
+ if (winstate->nav_winobj)
+ {
+ /*
+ * Allocate a mark pointer pinned at position 0 so that the tuplestore
+ * never truncates rows that a PREV(expr, N) might need.
+ */
+ winstate->nav_winobj->markptr =
+ tuplestore_alloc_read_pointer(winstate->buffer, 0);
+ winstate->nav_winobj->readptr =
+ tuplestore_alloc_read_pointer(winstate->buffer,
+ EXEC_FLAG_BACKWARD);
+ winstate->nav_winobj->markpos = 0;
+ }
+
/*
* If we are in RANGE or GROUPS mode, then determining frame boundaries
* requires physical access to the frame endpoint rows, except in certain
@@ -1411,6 +1411,13 @@ begin_partition(WindowAggState *winstate)
winstate->aggregatedupto = 0;
}
+ /* reset mark and seek positions for RPR navigation */
+ if (winstate->nav_winobj)
+ {
+ winstate->nav_winobj->markpos = -1;
+ winstate->nav_winobj->seekpos = -1;
+ }
+
/* reset mark and seek positions for each real window function */
for (int i = 0; i < numfuncs; i++)
{
@@ -2756,15 +2763,18 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate, scanDesc,
&TTSOpsMinimalTuple);
- winstate->prev_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
+ if (node->rpPattern != NULL)
+ {
+ winstate->nav_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+ winstate->nav_slot_pos = -1;
- winstate->next_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
+ winstate->nav_null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+ winstate->nav_null_slot = ExecStoreAllNullTuple(winstate->nav_null_slot);
- winstate->null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
- winstate->null_slot = ExecStoreAllNullTuple(winstate->null_slot);
+ winstate->nav_saved_outertuple = NULL;
+ }
/*
* create frame head and tail slots only if needed (must create slots in
@@ -2934,6 +2944,23 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->agg_winobj = agg_winobj;
}
+ /*
+ * Set up WindowObject for RPR navigation opcodes. This is separate from
+ * agg_winobj because it needs its own read pointer to avoid interfering
+ * with aggregate processing.
+ */
+ if (node->rpPattern != NULL)
+ {
+ WindowObject nav_winobj = makeNode(WindowObjectData);
+
+ nav_winobj->winstate = winstate;
+ nav_winobj->argstates = NIL;
+ nav_winobj->localmem = NULL;
+ nav_winobj->markptr = -1;
+ nav_winobj->readptr = -1;
+ winstate->nav_winobj = nav_winobj;
+ }
+
/* Set the status to running */
winstate->status = WINDOWAGG_RUN;
@@ -2974,7 +3001,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
if (node->defineClause != NIL)
{
/*
- * Tweak arg var of PREV/NEXT so that it refers to scan/inner slot.
+ * Compile DEFINE clause expressions. PREV/NEXT navigation is handled
+ * by EEOP_RPR_NAV_SET/RESTORE opcodes emitted during ExecInitExpr, so
+ * no varno rewriting is needed here.
*/
foreach(l, node->defineClause)
{
@@ -2991,7 +3020,6 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->defineVariableList =
lappend(winstate->defineVariableList,
makeString(pstrdup(name)));
- attno_map((Node *) expr);
exps = ExecInitExpr(expr, (PlanState *) winstate);
winstate->defineClauseList =
lappend(winstate->defineClauseList, exps);
@@ -3026,107 +3054,39 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
}
/*
- * Rewrite varno of Var nodes that are the argument of PREV/NET so that they
- * see scan tuple (PREV) or inner tuple (NEXT). Also we check the arguments
- * of PREV/NEXT include at least 1 column reference. This is required by the
- * SQL standard.
+ * ExecRPRNavGetSlot
+ *
+ * Fetch tuple at given position for RPR navigation opcodes.
+ * Returns nav_slot with the tuple loaded, or nav_null_slot if out of range.
+ * Called from EEOP_RPR_NAV_SET handler in execExprInterp.c.
*/
-static void
-attno_map(Node *node)
+TupleTableSlot *
+ExecRPRNavGetSlot(WindowAggState *winstate, int64 pos)
{
- (void) expression_tree_walker(node, attno_map_walker, NULL);
-}
+ WindowObject winobj = winstate->nav_winobj;
+ TupleTableSlot *slot = winstate->nav_slot;
-static bool
-attno_map_walker(Node *node, void *context)
-{
- FuncExpr *func;
- int nargs;
- bool is_prev;
+ if (pos < 0)
+ return winstate->nav_null_slot;
- if (node == NULL)
- return false;
+ /*
+ * If nav_slot already holds this position, return it without re-fetching.
+ * This is critical when multiple PREV/NEXT calls in the same expression
+ * navigate to the same row, because re-fetching would free the slot's
+ * tuple memory and invalidate any pass-by-ref Datum pointers from earlier
+ * navigation results.
+ */
+ if (winstate->nav_slot_pos == pos)
+ return slot;
- if (IsA(node, FuncExpr))
+ if (!window_gettupleslot(winobj, pos, slot))
{
- func = (FuncExpr *) node;
-
- if (func->funcid == F_PREV || func->funcid == F_NEXT)
- {
- /*
- * The SQL standard allows to have two more arguments form of
- * PREV/NEXT. But currently we allow only 1 argument form.
- */
- nargs = list_length(func->args);
- if (list_length(func->args) != 1)
- elog(ERROR, "PREV/NEXT must have 1 argument but function %d has %d args",
- func->funcid, nargs);
-
- /*
- * Check expr of PREV/NEXT aruguments and replace varno.
- */
- is_prev = (func->funcid == F_PREV) ? true : false;
- check_rpr_navigation(node, is_prev);
- }
+ winstate->nav_slot_pos = -1;
+ return winstate->nav_null_slot;
}
- return expression_tree_walker(node, attno_map_walker, NULL);
-}
-/*
- * Rewrite varno of Var of RPR navigation operations (PREV/NEXT).
- * If is_prev is true, we take care PREV, otherwise NEXT.
- */
-static void
-check_rpr_navigation(Node *node, bool is_prev)
-{
- NavigationInfo context;
-
- context.is_prev = is_prev;
- context.num_vars = 0;
- (void) expression_tree_walker(node, rpr_navigation_walker, &context);
- if (context.num_vars < 1)
- ereport(ERROR,
- errmsg("row pattern navigation operation's argument must include at least one column reference"));
-}
-
-static bool
-rpr_navigation_walker(Node *node, void *context)
-{
- NavigationInfo *nav = (NavigationInfo *) context;
-
- if (node == NULL)
- return false;
-
- switch (nodeTag(node))
- {
- case T_Var:
- {
- Var *var = (Var *) node;
-
- nav->num_vars++;
-
- if (nav->is_prev)
- {
- /*
- * Rewrite varno from OUTER_VAR to regular var no so that
- * the var references scan tuple.
- */
- var->varno = var->varnosyn;
- }
- else
- var->varno = INNER_VAR;
- }
- break;
- case T_Const:
- case T_FuncExpr:
- case T_OpExpr:
- break;
-
- default:
- ereport(ERROR,
- errmsg("row pattern navigation operation's argument includes unsupported expression"));
- }
- return expression_tree_walker(node, rpr_navigation_walker, context);
+ winstate->nav_slot_pos = pos;
+ return slot;
}
@@ -3187,8 +3147,8 @@ ExecReScanWindowAgg(WindowAggState *node)
ExecClearTuple(node->agg_row_slot);
ExecClearTuple(node->temp_slot_1);
ExecClearTuple(node->temp_slot_2);
- ExecClearTuple(node->prev_slot);
- ExecClearTuple(node->next_slot);
+ if (node->nav_slot)
+ ExecClearTuple(node->nav_slot);
if (node->framehead_slot)
ExecClearTuple(node->framehead_slot);
if (node->frametail_slot)
@@ -4277,6 +4237,10 @@ register_result:
* Returns true if the row exists, false if out of partition.
* If row exists, fills varMatched array.
* 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.
*/
static bool
nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
@@ -4287,37 +4251,25 @@ nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
ListCell *lc;
int varIdx = 0;
TupleTableSlot *slot;
+ int64 saved_pos;
- /*
- * Set up slots for current, previous, and next rows. We don't call
- * get_slots() here to avoid recursion through row_is_in_frame ->
- * update_reduced_frame -> ExecRPRProcessRow.
- */
-
- /* Current row -> ecxt_outertuple */
+ /* Fetch current row into temp_slot_1 */
slot = winstate->temp_slot_1;
if (!window_gettupleslot(winobj, pos, slot))
return false; /* No row exists */
+
+ /* Set up 1-slot context: only ecxt_outertuple */
econtext->ecxt_outertuple = slot;
- /* Previous row -> ecxt_scantuple (for PREV) */
- if (pos > 0)
- {
- slot = winstate->prev_slot;
- if (!window_gettupleslot(winobj, pos - 1, slot))
- econtext->ecxt_scantuple = winstate->null_slot;
- else
- econtext->ecxt_scantuple = slot;
- }
- else
- econtext->ecxt_scantuple = winstate->null_slot;
+ /*
+ * Save and set currentpos so that EEOP_RPR_NAV_SET opcodes can calculate
+ * target positions (currentpos +/- offset).
+ */
+ saved_pos = winstate->currentpos;
+ winstate->currentpos = pos;
- /* Next row -> ecxt_innertuple (for NEXT) */
- slot = winstate->next_slot;
- if (!window_gettupleslot(winobj, pos + 1, slot))
- econtext->ecxt_innertuple = winstate->null_slot;
- else
- econtext->ecxt_innertuple = slot;
+ /* Invalidate nav_slot cache so PREV/NEXT re-fetch for new row */
+ winstate->nav_slot_pos = -1;
foreach(lc, winstate->defineClauseList)
{
@@ -4335,6 +4287,8 @@ nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
break;
}
+ winstate->currentpos = saved_pos;
+
return true; /* Row exists */
}
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 650f1d42a93..150a1a7226c 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2432,6 +2432,35 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_RPR_NAV_SET:
+ build_EvalXFunc(b, mod, "ExecEvalRPRNavSet",
+ v_state, op, v_econtext);
+
+ /*
+ * Reload v_outerslot from econtext since the C function
+ * swapped ecxt_outertuple to a different row.
+ */
+ v_outerslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_OUTERTUPLE,
+ "v_outerslot");
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
+ case EEOP_RPR_NAV_RESTORE:
+ build_EvalXFunc(b, mod, "ExecEvalRPRNavRestore",
+ v_state, op, v_econtext);
+
+ /* Reload v_outerslot after restore */
+ v_outerslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_OUTERTUPLE,
+ "v_outerslot");
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
case EEOP_AGG_STRICT_DESERIALIZE:
case EEOP_AGG_DESERIALIZE:
{
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index c8a1f841293..e78b31d775f 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -168,6 +168,8 @@ void *referenced_functions[] =
ExecEvalScalarArrayOp,
ExecEvalHashedScalarArrayOp,
ExecEvalSubPlan,
+ ExecEvalRPRNavSet,
+ ExecEvalRPRNavRestore,
ExecEvalSysVar,
ExecEvalWholeRowVar,
ExecEvalXmlExpr,
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index ddf6812d7b0..0799ca1108f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -69,6 +69,9 @@ exprType(const Node *expr)
case T_MergeSupportFunc:
type = ((const MergeSupportFunc *) expr)->msftype;
break;
+ case T_RPRNavExpr:
+ type = ((const RPRNavExpr *) expr)->resulttype;
+ break;
case T_SubscriptingRef:
type = ((const SubscriptingRef *) expr)->refrestype;
break;
@@ -848,6 +851,9 @@ exprCollation(const Node *expr)
case T_MergeSupportFunc:
coll = ((const MergeSupportFunc *) expr)->msfcollid;
break;
+ case T_RPRNavExpr:
+ coll = ((const RPRNavExpr *) expr)->resultcollid;
+ break;
case T_SubscriptingRef:
coll = ((const SubscriptingRef *) expr)->refcollid;
break;
@@ -1146,6 +1152,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_MergeSupportFunc:
((MergeSupportFunc *) expr)->msfcollid = collation;
break;
+ case T_RPRNavExpr:
+ ((RPRNavExpr *) expr)->resultcollid = collation;
+ break;
case T_SubscriptingRef:
((SubscriptingRef *) expr)->refcollid = collation;
break;
@@ -1418,6 +1427,9 @@ exprLocation(const Node *expr)
case T_MergeSupportFunc:
loc = ((const MergeSupportFunc *) expr)->location;
break;
+ case T_RPRNavExpr:
+ loc = ((const RPRNavExpr *) expr)->location;
+ break;
case T_SubscriptingRef:
/* just use container argument's location */
loc = exprLocation((Node *) ((const SubscriptingRef *) expr)->refexpr);
@@ -2178,6 +2190,16 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *expr = (RPRNavExpr *) node;
+
+ if (WALK(expr->arg))
+ return true;
+ if (expr->offset_arg && WALK(expr->offset_arg))
+ return true;
+ }
+ break;
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
@@ -3081,6 +3103,17 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+ RPRNavExpr *newnode;
+
+ FLATCOPY(newnode, nav, RPRNavExpr);
+ MUTATE(newnode->arg, nav->arg, Expr *);
+ MUTATE(newnode->offset_arg, nav->offset_arg, Expr *);
+ return (Node *) newnode;
+ }
+ break;
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index ba9523ae3d4..c25e2c4d3a7 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -760,7 +760,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
/* next() and prev() are only allowed in a WINDOW DEFINE clause */
if (fdresult == FUNCDETAIL_NORMAL &&
pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE &&
- (funcid == F_PREV || funcid == F_NEXT))
+ (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
+ funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("%s can only be used in a DEFINE clause",
@@ -768,7 +769,32 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* build the appropriate output structure */
- if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
+ if (fdresult == FUNCDETAIL_NORMAL &&
+ (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
+ funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_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.
+ */
+ 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);
+
+ navexpr->kind = is_next ? RPR_NAV_NEXT : RPR_NAV_PREV;
+ navexpr->arg = (Expr *) linitial(fargs);
+ navexpr->offset_arg = has_offset ? (Expr *) lsecond(fargs) : NULL;
+ navexpr->resulttype = rettype;
+ /* resultcollid will be set by parse_collate.c */
+ navexpr->location = location;
+
+ retval = (Node *) navexpr;
+ }
+ else if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
FuncExpr *funcexpr = makeNode(FuncExpr);
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 55283ab4bbe..7c6001eec9f 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -28,6 +28,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
#include "optimizer/rpr.h"
#include "parser/parse_clause.h"
#include "parser/parse_collate.h"
@@ -41,6 +42,9 @@ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
List *rpDefs, List **varNames);
static List *transformDefineClause(ParseState *pstate, WindowClause *wc,
WindowDef *windef, List **targetlist);
+static bool contains_rpr_nav_walker(Node *node, void *context);
+static bool contains_column_ref_walker(Node *node, void *context);
+static bool check_rpr_nav_nesting_walker(Node *node, void *context);
/*
* transformRPR
@@ -400,6 +404,10 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
foreach_ptr(TargetEntry, te, defineClause)
te->expr = (Expr *) coerce_to_boolean(pstate, (Node *) te->expr, "DEFINE");
+ /* check for nested PREV/NEXT and missing column references */
+ foreach_ptr(TargetEntry, te, defineClause)
+ (void) check_rpr_nav_nesting_walker((Node *) te->expr, pstate);
+
/* mark column origins */
markTargetListOrigins(pstate, defineClause);
@@ -408,3 +416,74 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
return defineClause;
}
+
+/*
+ * contains_rpr_nav_walker
+ * Return true if the expression tree contains a PREV or NEXT call.
+ */
+static bool
+contains_rpr_nav_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ return true;
+ return expression_tree_walker(node, contains_rpr_nav_walker, context);
+}
+
+/*
+ * contains_column_ref_walker
+ * Return true if the expression tree contains at least one Var node
+ * (column reference).
+ */
+static bool
+contains_column_ref_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ return true;
+ return expression_tree_walker(node, contains_column_ref_walker, context);
+}
+
+/*
+ * check_rpr_nav_nesting_walker
+ * Raise an error if PREV or NEXT appears nested inside another PREV/NEXT,
+ * if the first argument contains no column reference, or if the optional
+ * offset argument is not a run-time constant (i.e., it contains a column
+ * reference).
+ */
+static bool
+check_rpr_nav_nesting_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+
+ if (contains_rpr_nav_walker((Node *) nav->arg, NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PREV and NEXT cannot be nested"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ if (!contains_column_ref_walker((Node *) nav->arg, NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ if (nav->offset_arg != NULL &&
+ (contains_column_ref_walker((Node *) nav->offset_arg, NULL) ||
+ contain_volatile_functions((Node *) nav->offset_arg)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PREV/NEXT offset must be a run-time constant"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ /* don't recurse into arg; nesting already checked above */
+ return false;
+ }
+ return expression_tree_walker(node, check_rpr_nav_nesting_walker, context);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 928bed2e9fb..d19f3718b30 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9581,6 +9581,22 @@ get_rule_expr(Node *node, deparse_context *context,
get_func_expr((FuncExpr *) node, context, showimplicit);
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+
+ appendStringInfoString(buf,
+ nav->kind == RPR_NAV_PREV ? "PREV(" : "NEXT(");
+ get_rule_expr((Node *) nav->arg, context, showimplicit);
+ if (nav->offset_arg != NULL)
+ {
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) nav->offset_arg, context, showimplicit);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ break;
+
case T_NamedArgExpr:
{
NamedArgExpr *na = (NamedArgExpr *) node;
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 74ef109f72e..091260d2cce 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -726,22 +726,62 @@ window_nth_value(PG_FUNCTION_ARGS)
/*
* prev
- * Dummy function to invoke RPR's navigation operator "PREV".
- * This is *not* a window function.
+ * Catalog placeholder for RPR's PREV navigation operator.
+ *
+ * The parser transforms prev() calls inside DEFINE into RPRNavExpr nodes,
+ * so this function is never reached during normal RPR execution. It exists
+ * only so that the parser can resolve the function name from pg_proc.
+ * Calls outside DEFINE are rejected by parse_func.c (EXPR_KIND_RPR_DEFINE
+ * check). The error below is a defensive measure in case that check is
+ * bypassed (e.g., direct C-level function invocation).
*/
Datum
window_prev(PG_FUNCTION_ARGS)
{
- PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("prev() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
}
/*
* next
- * Dummy function to invoke RPR's navigation operation "NEXT".
- * This is *not* a window function.
+ * Catalog placeholder for RPR's NEXT navigation operator.
+ * See window_prev() for details.
*/
Datum
window_next(PG_FUNCTION_ARGS)
{
- PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("next() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
+}
+
+/*
+ * prev(value, offset)
+ * Catalog placeholder for RPR's PREV navigation operator with offset.
+ * See window_prev() for details.
+ */
+Datum
+window_prev_offset(PG_FUNCTION_ARGS)
+{
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("prev() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
+}
+
+/*
+ * next(value, offset)
+ * Catalog placeholder for RPR's NEXT navigation operator with offset.
+ * See window_prev() for details.
+ */
+Datum
+window_next_offset(PG_FUNCTION_ARGS)
+{
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("next() 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 7ddaef3d04a..8496c1babc4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10957,9 +10957,15 @@
{ oid => '8126', descr => 'previous value',
proname => 'prev', provolatile => 's', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_prev' },
+{ oid => '8128', descr => 'previous value at offset',
+ proname => 'prev', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
+ proargtypes => 'anyelement int8', prosrc => 'window_prev_offset' },
{ oid => '8127', descr => 'next value',
proname => 'next', provolatile => 's', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_next' },
+{ oid => '8129', descr => 'next value at offset',
+ proname => 'next', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
+ proargtypes => 'anyelement int8', prosrc => 'window_next_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 aa9b361fa31..6067f3b6ff8 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -274,6 +274,10 @@ typedef enum ExprEvalOp
EEOP_MERGE_SUPPORT_FUNC,
EEOP_SUBPLAN,
+ /* row pattern navigation (RPR PREV/NEXT) */
+ EEOP_RPR_NAV_SET,
+ EEOP_RPR_NAV_RESTORE,
+
/* aggregation related nodes */
EEOP_AGG_STRICT_DESERIALIZE,
EEOP_AGG_DESERIALIZE,
@@ -691,6 +695,17 @@ typedef struct ExprEvalStep
SubPlanState *sstate;
} subplan;
+ /* for EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE */
+ struct
+ {
+ WindowAggState *winstate;
+ int64 offset; /* 1-arg: static signed offset (PREV=-1,
+ * NEXT=+1) */
+ Datum *offset_value; /* 2-arg: runtime offset value, or
+ * NULL */
+ bool *offset_isnull; /* 2-arg: runtime offset null flag */
+ } rpr_nav;
+
/* for EEOP_AGG_*DESERIALIZE */
struct
{
@@ -898,6 +913,10 @@ extern void ExecEvalMergeSupportFunc(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalSubPlan(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
+extern void ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
+extern void ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
extern void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
diff --git a/src/include/executor/nodeWindowAgg.h b/src/include/executor/nodeWindowAgg.h
index ada4a1c458c..f6f6645131c 100644
--- a/src/include/executor/nodeWindowAgg.h
+++ b/src/include/executor/nodeWindowAgg.h
@@ -20,4 +20,7 @@ extern WindowAggState *ExecInitWindowAgg(WindowAgg *node, EState *estate, int ef
extern void ExecEndWindowAgg(WindowAggState *node);
extern void ExecReScanWindowAgg(WindowAggState *node);
+/* RPR navigation support for expression evaluation opcodes */
+extern TupleTableSlot *ExecRPRNavGetSlot(WindowAggState *winstate, int64 pos);
+
#endif /* NODEWINDOWAGG_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 3681d905bde..9c692301313 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2704,10 +2704,12 @@ typedef struct WindowAggState
TupleTableSlot *temp_slot_1;
TupleTableSlot *temp_slot_2;
- /* temporary slots for RPR */
- TupleTableSlot *prev_slot; /* PREV row navigation operator */
- TupleTableSlot *next_slot; /* NEXT row navigation operator */
- TupleTableSlot *null_slot; /* all NULL slot */
+ /* RPR navigation */
+ 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_saved_outertuple; /* saved slot during nav swap */
+ TupleTableSlot *nav_null_slot; /* all NULL slot */
/*
* Each byte corresponds to a row positioned at absolute its pos in
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 384df50c80a..7143570f4e0 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -648,6 +648,37 @@ typedef struct WindowFuncRunCondition
Expr *arg;
} WindowFuncRunCondition;
+/*
+ * RPRNavExpr
+ *
+ * Represents a PREV() or NEXT() 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 (-1) or RPR_NAV_NEXT (+1)
+ * 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
+ */
+typedef enum RPRNavKind
+{
+ RPR_NAV_PREV = -1,
+ RPR_NAV_NEXT = 1,
+} RPRNavKind;
+
+typedef struct RPRNavExpr
+{
+ Expr xpr;
+ RPRNavKind kind; /* PREV or NEXT */
+ Expr *arg; /* argument expression */
+ Expr *offset_arg; /* offset expression, or NULL for 1-arg form */
+ Oid resulttype; /* result type (same as arg's type) */
+ /* OID of collation of result */
+ Oid resultcollid pg_node_attr(query_jumble_ignore);
+ /* token location, or -1 if unknown */
+ ParseLoc location;
+} RPRNavExpr;
+
/*
* MergeSupportFunc
*
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index e72171050c7..79b5e63a657 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1018,6 +1018,497 @@ WINDOW w AS (
5 | 0.1 | 0
(5 rows)
+--
+-- Error cases: PREV/NEXT usage restrictions
+--
+-- PREV outside DEFINE clause
+SELECT prev(price) FROM stock;
+ERROR: prev can only be used in a DEFINE clause
+LINE 1: SELECT prev(price) FROM stock;
+ ^
+-- NEXT outside DEFINE clause
+SELECT next(price) FROM stock;
+ERROR: next can only be used in a DEFINE clause
+LINE 1: SELECT next(price) FROM stock;
+ ^
+-- Nested PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > PREV(PREV(price))
+ ^
+-- Nested NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(NEXT(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(NEXT(price))
+ ^
+-- PREV nested inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(PREV(price))
+ ^
+-- PREV nested inside expression inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(price * PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(price * PREV(price))
+ ^
+-- Triple nesting: error reported at outermost PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(PREV(price)))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > PREV(PREV(PREV(price)))
+ ^
+-- No column reference in PREV/NEXT argument
+-- PREV(1): constant only, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS PREV(1) > 0
+ ^
+-- NEXT(1 + 2): constant expression, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS NEXT(1 + 2) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS NEXT(1 + 2) > 0
+ ^
+-- 2-arg form: PREV(1, 1): constant expression as first arg
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1, 1) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS PREV(1, 1) > 0
+ ^
+-- Non-constant offset: column reference as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, price) > 0
+);
+ERROR: PREV/NEXT offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(price, price) > 0
+ ^
+-- Non-constant offset: volatile function as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, random()::int) > 0
+);
+ERROR: PREV/NEXT offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(price, random()::int) > 0
+ ^
+--
+-- 2-arg PREV/NEXT: functional tests
+--
+-- PREV(price, 2): match rows where current price > price 2 rows back
+-- stock: 100, 90, 80, 95, 110
+-- Pattern (A B+): A=any, B where price > PREV(price, 2)
+-- At pos 2 (80): A matches. pos 3 (95): 95 > PREV(95,2)=90 TRUE.
+-- pos 4 (110): 110 > PREV(110,2)=80 TRUE. Match!
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 150 | 2
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 120 | 3
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 1500 | 2
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 3
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- NEXT(price, 2): match rows where current price > price 2 rows ahead
+-- pos 0 (100): NEXT(100,2)=80, 100>80 TRUE. pos 1 (90): NEXT(90,2)=95, 90>95 FALSE. Match ends.
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > NEXT(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 200 | 1
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 150 | 2
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 2000 | 1
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | 1400 | 1500 | 2
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- Expressions inside PREV/NEXT arg: expr is evaluated on target row
+-- PREV(price - 50, 1): fetches (price - 50) from 1 row back
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price - 50, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 200 | 1
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 150 | 2
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 130 | 4
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 2000 | 1
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | 1500 | 1500 | 1
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1300 | 2
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | 1300 | 1300 | 1
+(20 rows)
+
+-- NEXT(price * 2, 1): fetches (price * 2) from 1 row ahead
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price < NEXT(price * 2, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 120 | 9
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 4
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 4
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- Large offset: PREV(val, 999) on 1000-row series matches only last row
+-- NEXT(val, 999) matches only first row
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val, 999) = 1
+)
+ORDER BY val DESC LIMIT 3;
+ val | first_value | last_value | count
+------+-------------+------------+-------
+ 1000 | 1000 | 1000 | 1
+ 999 | | | 0
+ 998 | | | 0
+(3 rows)
+
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS NEXT(val, 999) = 1000
+)
+LIMIT 3;
+ val | first_value | last_value | count
+-----+-------------+------------+-------
+ 1 | 1 | 1 | 1
+ 2 | | | 0
+ 3 | | | 0
+(3 rows)
+
+-- PREV(price, 0): offset 0 means current row, always equal to price
+-- A+ matches entire partition as one group; count = partition size
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, 0) = price
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 130 | 10
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1300 | 10
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- 2-arg PREV/NEXT outside DEFINE clause
+SELECT prev(price, 2) FROM stock;
+ERROR: prev can only be used in a DEFINE clause
+LINE 1: SELECT prev(price, 2) FROM stock;
+ ^
+SELECT next(price, 2) FROM stock;
+ERROR: next can only be used in a DEFINE clause
+LINE 1: SELECT next(price, 2) FROM stock;
+ ^
+-- 2-arg PREV/NEXT: negative offset
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, -1) IS NOT NULL
+);
+ERROR: PREV/NEXT offset must not be negative
+-- 2-arg PREV/NEXT: NULL offset (typed)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL::int8) IS NOT NULL
+);
+ERROR: PREV/NEXT offset must not be null
+-- 2-arg PREV/NEXT: NULL offset (untyped)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL) IS NOT NULL
+);
+ERROR: PREV/NEXT 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
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, $1)
+);
+EXECUTE test_prev_offset(-1);
+ERROR: PREV/NEXT offset must not be negative
+EXECUTE test_prev_offset(NULL);
+ERROR: PREV/NEXT 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
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 0 + $1)
+);
+EXECUTE test_prev_offset(-1);
+ERROR: PREV/NEXT offset must not be negative
+EXECUTE test_prev_offset(NULL);
+ERROR: PREV/NEXT 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
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 1) AND price > PREV(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 130 | 2
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1300 | 2
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- 2-arg: PREV and NEXT with explicit offsets in same DEFINE clause
+-- A: price exceeds 1-back and is below 1-ahead (ascending interior point)
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 110 | 1
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1100 | 1
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
--
-- SKIP TO / Backtracking / Frame boundary
--
@@ -1479,7 +1970,7 @@ count(*) OVER w
(14 rows)
-- ReScan test: LATERAL join forces WindowAgg rescan with RPR
--- Tests ExecReScanWindowAgg clearing prev_slot/next_slot
+-- Tests ExecReScanWindowAgg clearing nav_slot
SELECT g.x, sub.*
FROM generate_series(1, 2) g(x),
LATERAL (
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index bd345906133..0d2b7550ea8 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -3646,9 +3646,89 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=30.00 loops=1)
(8 rows)
--- Using NULL comparisons
+-- Using 1-arg PREV (implicit offset 1)
CREATE VIEW rpr_ev83 AS
SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v > PREV(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+-------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v > PREV(v)) );
+(3 rows)
+
+-- Using 1-arg NEXT (implicit offset 1)
+CREATE VIEW rpr_ev84 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v < NEXT(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+-------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v < NEXT(v)) );
+(3 rows)
+
+-- Using 2-arg PREV (explicit offset)
+CREATE VIEW rpr_ev85 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v > PREV(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+--------------------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v > PREV(v, (2)::bigint)) );
+(3 rows)
+
+-- Using 2-arg NEXT (explicit offset)
+CREATE VIEW rpr_ev86 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v < NEXT(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+--------------------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v < NEXT(v, (2)::bigint)) );
+(3 rows)
+
+-- Using NULL comparisons
+CREATE VIEW rpr_ev87 AS
+SELECT count(*) OVER w
FROM (
SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v
FROM generate_series(1, 30) v
@@ -3659,7 +3739,7 @@ WINDOW w AS (
PATTERN (A+ B)
DEFINE A AS v IS NOT NULL, B AS v IS NULL
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev87'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
-------------------
PATTERN (a+ b)
@@ -3695,7 +3775,7 @@ WINDOW w AS (
-- Large Scale Statistics Verification
-- ============================================================
-- 500 rows - verify statistics scale correctly
-CREATE VIEW rpr_ev84 AS
+CREATE VIEW rpr_ev88 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3704,7 +3784,7 @@ WINDOW w AS (
PATTERN (A+ B C)
DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev88'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
---------------------
PATTERN (a+ b c)
@@ -3734,7 +3814,7 @@ WINDOW w AS (
(9 rows)
-- High match count scenario
-CREATE VIEW rpr_ev85 AS
+CREATE VIEW rpr_ev89 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3743,7 +3823,7 @@ WINDOW w AS (
PATTERN (A B)
DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev89'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
------------------
PATTERN (a b)
@@ -3773,7 +3853,7 @@ WINDOW w AS (
(9 rows)
-- High skip count scenario
-CREATE VIEW rpr_ev86 AS
+CREATE VIEW rpr_ev90 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3787,7 +3867,7 @@ WINDOW w AS (
D AS v % 100 = 4,
E AS v % 100 = 5
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev90'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
------------------------
PATTERN (a b c d e)
@@ -3831,17 +3911,17 @@ WINDOW w AS (
-- Test with row_number() as representative case.
--
-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
-CREATE VIEW rpr_ev87 AS
+CREATE VIEW rpr_ev91 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
ORDER BY v
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev91;
QUERY PLAN
--------------------------------------------------------------
- Subquery Scan on rpr_ev87
+ Subquery Scan on rpr_ev91
-> WindowAgg
Window: w AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING)
-> Sort
@@ -3850,7 +3930,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
(6 rows)
-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-CREATE VIEW rpr_ev88 AS
+CREATE VIEW rpr_ev92 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -3861,10 +3941,10 @@ WINDOW w AS (
DEFINE
B AS v > PREV(v)
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev92;
QUERY PLAN
--------------------------------------------------------------------------------------
- Subquery Scan on rpr_ev88
+ Subquery Scan on rpr_ev92
-> WindowAgg
Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a b+
@@ -3877,7 +3957,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
-- Planner optimization: non-RPR and RPR windows that share the same base frame
-- after frame optimization are kept as separate WindowAgg nodes.
--
-CREATE VIEW rpr_ev89 AS
+CREATE VIEW rpr_ev93 AS
SELECT
row_number() OVER w_normal AS rn_normal,
row_number() OVER w_rpr AS rn_rpr
@@ -3890,10 +3970,10 @@ WINDOW
PATTERN (A+)
DEFINE A AS v > 1
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev93;
QUERY PLAN
------------------------------------------------------------------------------------------
- Subquery Scan on rpr_ev89
+ Subquery Scan on rpr_ev93
-> WindowAgg
Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a+"
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index 95794d409e1..787ebddcaec 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -440,6 +440,295 @@ WINDOW w AS (
B AS val > PREV(val) * 0.99
);
+--
+-- Error cases: PREV/NEXT usage restrictions
+--
+
+-- PREV outside DEFINE clause
+SELECT prev(price) FROM stock;
+
+-- NEXT outside DEFINE clause
+SELECT next(price) FROM stock;
+
+-- Nested PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(price))
+);
+
+-- Nested NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(NEXT(price))
+);
+
+-- PREV nested inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(PREV(price))
+);
+
+-- PREV nested inside expression inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(price * PREV(price))
+);
+
+-- Triple nesting: error reported at outermost PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(PREV(price)))
+);
+
+-- No column reference in PREV/NEXT argument
+-- PREV(1): constant only, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1) > 0
+);
+
+-- NEXT(1 + 2): constant expression, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS NEXT(1 + 2) > 0
+);
+
+-- 2-arg form: PREV(1, 1): constant expression as first arg
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1, 1) > 0
+);
+
+-- Non-constant offset: column reference as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, price) > 0
+);
+
+-- Non-constant offset: volatile function as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, random()::int) > 0
+);
+
+--
+-- 2-arg PREV/NEXT: functional tests
+--
+
+-- PREV(price, 2): match rows where current price > price 2 rows back
+-- stock: 100, 90, 80, 95, 110
+-- Pattern (A B+): A=any, B where price > PREV(price, 2)
+-- At pos 2 (80): A matches. pos 3 (95): 95 > PREV(95,2)=90 TRUE.
+-- pos 4 (110): 110 > PREV(110,2)=80 TRUE. Match!
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 2)
+);
+
+-- NEXT(price, 2): match rows where current price > price 2 rows ahead
+-- pos 0 (100): NEXT(100,2)=80, 100>80 TRUE. pos 1 (90): NEXT(90,2)=95, 90>95 FALSE. Match ends.
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > NEXT(price, 2)
+);
+
+-- Expressions inside PREV/NEXT arg: expr is evaluated on target row
+-- PREV(price - 50, 1): fetches (price - 50) from 1 row back
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price - 50, 1)
+);
+
+-- NEXT(price * 2, 1): fetches (price * 2) from 1 row ahead
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price < NEXT(price * 2, 1)
+);
+
+-- Large offset: PREV(val, 999) on 1000-row series matches only last row
+-- NEXT(val, 999) matches only first row
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val, 999) = 1
+)
+ORDER BY val DESC LIMIT 3;
+
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS NEXT(val, 999) = 1000
+)
+LIMIT 3;
+
+-- PREV(price, 0): offset 0 means current row, always equal to price
+-- A+ matches entire partition as one group; count = partition size
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, 0) = price
+);
+
+-- 2-arg PREV/NEXT outside DEFINE clause
+SELECT prev(price, 2) FROM stock;
+SELECT next(price, 2) FROM stock;
+
+-- 2-arg PREV/NEXT: negative offset
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, -1) IS NOT NULL
+);
+
+-- 2-arg PREV/NEXT: NULL offset (typed)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL::int8) IS NOT NULL
+);
+
+-- 2-arg PREV/NEXT: NULL offset (untyped)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL) IS NOT 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
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, $1)
+);
+EXECUTE test_prev_offset(-1);
+EXECUTE test_prev_offset(NULL);
+DEALLOCATE test_prev_offset;
+
+-- 2-arg PREV/NEXT: host variable with expression (0 + $1)
+PREPARE test_prev_offset(int8) AS
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 0 + $1)
+);
+EXECUTE test_prev_offset(-1);
+EXECUTE test_prev_offset(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
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 1) AND price > PREV(price, 2)
+);
+
+-- 2-arg: PREV and NEXT with explicit offsets in same DEFINE clause
+-- A: price exceeds 1-back and is below 1-ahead (ascending interior point)
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1)
+);
+
--
-- SKIP TO / Backtracking / Frame boundary
--
@@ -671,7 +960,7 @@ count(*) OVER w
);
-- ReScan test: LATERAL join forces WindowAgg rescan with RPR
--- Tests ExecReScanWindowAgg clearing prev_slot/next_slot
+-- Tests ExecReScanWindowAgg clearing nav_slot
SELECT g.x, sub.*
FROM generate_series(1, 2) g(x),
LATERAL (
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index 93e06b0cbdf..c2cbe2edd59 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -2088,9 +2088,65 @@ WINDOW w AS (
D AS v < PREV(v)
);');
--- Using NULL comparisons
+-- Using 1-arg PREV (implicit offset 1)
CREATE VIEW rpr_ev83 AS
SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v > PREV(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 1-arg NEXT (implicit offset 1)
+CREATE VIEW rpr_ev84 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v < NEXT(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 2-arg PREV (explicit offset)
+CREATE VIEW rpr_ev85 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v > PREV(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 2-arg NEXT (explicit offset)
+CREATE VIEW rpr_ev86 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) 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 TRUE,
+ B AS v < NEXT(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using NULL comparisons
+CREATE VIEW rpr_ev87 AS
+SELECT count(*) OVER w
FROM (
SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v
FROM generate_series(1, 30) v
@@ -2101,7 +2157,7 @@ WINDOW w AS (
PATTERN (A+ B)
DEFINE A AS v IS NOT NULL, B AS v IS NULL
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev87'), 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
@@ -2121,7 +2177,7 @@ WINDOW w AS (
-- ============================================================
-- 500 rows - verify statistics scale correctly
-CREATE VIEW rpr_ev84 AS
+CREATE VIEW rpr_ev88 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2130,7 +2186,7 @@ WINDOW w AS (
PATTERN (A+ B C)
DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev88'), 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
@@ -2143,7 +2199,7 @@ WINDOW w AS (
);');
-- High match count scenario
-CREATE VIEW rpr_ev85 AS
+CREATE VIEW rpr_ev89 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2152,7 +2208,7 @@ WINDOW w AS (
PATTERN (A B)
DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev89'), 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
@@ -2165,7 +2221,7 @@ WINDOW w AS (
);');
-- High skip count scenario
-CREATE VIEW rpr_ev86 AS
+CREATE VIEW rpr_ev90 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2179,7 +2235,7 @@ WINDOW w AS (
D AS v % 100 = 4,
E AS v % 100 = 5
);
-SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev90'), 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
@@ -2207,7 +2263,7 @@ WINDOW w AS (
--
-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
-CREATE VIEW rpr_ev87 AS
+CREATE VIEW rpr_ev91 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -2215,10 +2271,10 @@ WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev91;
-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-CREATE VIEW rpr_ev88 AS
+CREATE VIEW rpr_ev92 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -2230,13 +2286,13 @@ WINDOW w AS (
B AS v > PREV(v)
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev92;
--
-- Planner optimization: non-RPR and RPR windows that share the same base frame
-- after frame optimization are kept as separate WindowAgg nodes.
--
-CREATE VIEW rpr_ev89 AS
+CREATE VIEW rpr_ev93 AS
SELECT
row_number() OVER w_normal AS rn_normal,
row_number() OVER w_rpr AS rn_rpr
@@ -2250,7 +2306,7 @@ WINDOW
DEFINE A AS v > 1
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev93;
--
-- Planner optimization: find_window_run_conditions must not push down
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 36091f56418..ca9faabc7e3 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1775,7 +1775,6 @@ NamedLWLockTrancheRequest
NamedTuplestoreScan
NamedTuplestoreScanState
NamespaceInfo
-NavigationInfo
NestLoop
NestLoopParam
NestLoopState
@@ -2441,6 +2440,8 @@ QuerySource
QueueBackendStatus
QueuePosition
QuitSignalReason
+RPRNavExpr
+RPRNavKind
RBTNode
RBTOrderControl
RBTree
--
2.50.1 (Apple Git-155)