From f22f8e90163e89bbb235a363db3cfcac5b95f522 Mon Sep 17 00:00:00 2001 From: Henson Choi 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 @@ prev - prev ( value anyelement ) + prev ( value anyelement [, offset bigint ] ) anyelement - 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 offset + rows before the current row within the window frame; + returns NULL if the target row is outside the window frame. + offset defaults to 1 if omitted. + offset must be a non-negative integer; + an offset of 0 refers to the current row itself. + If offset is NULL, NULL is returned. @@ -318,12 +323,17 @@ next - next ( value anyelement ) + next ( value anyelement [, offset bigint ] ) anyelement - 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 offset + rows after the current row within the window frame; + returns NULL if the target row is outside the window frame. + offset defaults to 1 if omitted. + offset must be a non-negative integer; + an offset of 0 refers to the current row itself. + If offset is NULL, NULL is returned. 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)