public inbox for [email protected]
help / color / mirror / Atom feedFrom: Henson Choi <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Row pattern recognition
Date: Sat, 9 May 2026 16:43:18 +0900
Message-ID: <CAAAe_zCL9UtiYthrSaXCmhFMK6Q3YQ6BQGgae7C9en2k=S9doA@mail.gmail.com> (raw)
In-Reply-To: <CAAAe_zAf-vCJvnTuiFF_85BHozgBC5aGLoNOUSuSonoXZhkEnA@mail.gmail.com>
References: <CACJufxEWL_ZnC-bs_yrg-Ys6ZUD3Ut_p1Ebj0bAcbzj67+HDAQ@mail.gmail.com>
<[email protected]>
<CAAAe_zAhXuBTRiNVU1RrJKOiKoyeu-pgYaYb8HaqbhSsC=sGUg@mail.gmail.com>
<[email protected]>
<CAAAe_zCf2nsQF920p7jLxu37G9CQAQHwm-B39Lzw_Pk9wFP+WQ@mail.gmail.com>
<CAAAe_zAf-vCJvnTuiFF_85BHozgBC5aGLoNOUSuSonoXZhkEnA@mail.gmail.com>
Hi Tatsuo,
Eleven incremental patches on top of v47. Patches 0001-0010
are the same set I posted on 2026-05-07, with cosmetic touches
only. Patch 0011 is new and tightens the consistency of how
this code base cites the SQL standard.
So far I have been pushing ahead narrowly on the feature
implementation itself. I am aware I still have much to learn
about the PostgreSQL project's culture and conventions, and
your continued guidance would be very much appreciated.
Patch summary:
- 0001 Add DEFINE non-volatile baseline to rpr_integration B9
Adds a STABLE/IMMUTABLE baseline ahead of the volatile
case so the rejection in 0002 is visible against a working
reference.
- 0002 Unify RPR DEFINE walkers and reject volatile callees
[A]. Collapses the four DEFINE walkers (parser / planner
/ executor) into one phase-tagged traversal and, on that
base, rejects volatile / NextValueExpr at parse-analysis.
STABLE / IMMUTABLE accepted. The offset runtime-constant
check is folded in; B9 flips to error-case and its XXX is
dropped.
- 0003 Cover RPR empty-match path with EXPLAIN tests; fix
stale XXX comments
[B]. Adds rpr_explain coverage that surfaces "NFA: N
matched (len 0/0/0.0)" so the NFA-found-but-empty-frame
path is regression-visible; replaces stale rpr_nfa XXXs.
- 0004 Reclassify DEFINE qualifier check and reword
diagnostic to "expression"
[D]. Tri-classifies (pattern var / range var / fall-
through) so unknown qualifiers fall through to the
standard "column does not exist" diagnostic. Reworded to
"expression" since the quoted token may include
indirection on composite types (e.g. (A.items).amount).
- 0005 Sync stale comments on DEFINE/PATTERN handling
validateRPRPatternVarCount() rejects DEFINE names not in
PATTERN, but comments / SELECT doc described it as
"collection" / "filtered during planning". Wording
aligned; XXX note's "column references" bumped to
"expressions" to match 0004. Comment + doc only.
- 0006 Add trailing commas to RPR enum definitions
Per Jian. RPRNavKind, RPRNavOffsetKind, RPRPatternNodeType.
- 0007 Remove optional outer parentheses from ereport() calls
in RPR files
Per Jian + your gram.y patch. 19 sites in parse_rpr.c /
optimizer/plan/rpr.c plus the gram.y sites.
- 0008 Add high-water mark tracking to NFA visited bitmap
reset
[C]. Resets only the touched span (O(span_words) instead
of O(numElements/64)), at the cost of two int16 comparisons
per visit. Semantics unchanged; happy to drop if you
prefer the simpler bulk reset.
- 0009 Document DEFINE subquery rejection as intentional
over-rejection
Comment-only. Records that SQL/RPR permits a non-RPR,
non-pattern-var-correlated subquery in DEFINE (see 0011
for the normalized citation), and that our blanket
rejection is deliberate over-rejection. The case
distinction (walk subquery Query for nested RPR; match
ColumnRef qualifiers against ancestor p_rpr_pattern_vars)
is doable with existing infrastructure and left as future
work, not blocked on any other feature.
- 0010 Remove duplicate #include in nodeWindowAgg.c
"common/int.h" was included twice and the first occurrence
was misplaced between catalog/* headers. Drops the
misplaced first; the second sits at the correct
alphabetical position.
- 0011 Normalize SQL/RPR standard references across code,
comments, and tests
Doc/comment/test-header hygiene. Standardizes citations
to ISO/IEC 19075-5 (SQL Technical Report Part 5, "Row
pattern recognition in SQL"); earlier comments mixed bare
"19075-5", "9075-2", "SQL standard", and "SQL:2016 STR06"
forms. Where Chapter 4 (FROM / R010) and Chapter 6
(WINDOW / R020) describe parallel material, cites the
Chapter 6 subclause first since this implementation
targets R020. Pins "STR06" to its source (Subclause
7.2.8) in the rpr_nfa / rpr_explain commentary; replaces
ad-hoc section / arrow glyphs with ASCII. Adds a short
normative-reference paragraph to executor/README.rpr so
future patches inherit the policy. Comments / docs /
test headers only.
Still deferred:
- B7 Recursive CTE XXX: pending community input on the
ISO/IEC 19075-5 6.17.5 / 4.18.5 interpretation.
Best regards,
Henson
From aba6c56a6854e8f101846aa03e42204facc02485 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Mon, 4 May 2026 19:09:34 +0900
Subject: [PATCH 01/11] Add DEFINE non-volatile baseline to rpr_integration B9
B9 in src/test/regress/sql/rpr_integration.sql today only exercises a
volatile callee (random()) inside DEFINE. Add a baseline query in the
same section that uses STABLE (to_char) and IMMUTABLE (length) callees,
which must remain accepted when the upcoming volatile-only prohibition
lands. This guards against the prohibition being broadened by accident
(e.g. contain_volatile_functions -> contain_mutable_functions); the
volatile case alone would not catch over-rejection.
Ordered baseline-first then volatile, matching other B sections.
---
src/test/regress/expected/rpr_integration.out | 26 +++++++++++++++++++
src/test/regress/sql/rpr_integration.sql | 13 ++++++++++
2 files changed, 39 insertions(+)
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index 0cc79b75601..ef6a157f45d 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1421,6 +1421,32 @@ DROP INDEX rpr_integ_id_idx;
-- pattern matching non-deterministic. When the prohibition lands,
-- this test must be replaced with an error-case test that expects
-- random() in DEFINE to be rejected.
+-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
+-- This locks the boundary of the volatile-only prohibition.
+SELECT id, val, count(*) OVER w AS cnt
+FROM rpr_integ
+WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE B AS val > PREV(val)
+ AND length('x') = 1
+ AND to_char(date '2026-01-01', 'YYYY') = '2026')
+ORDER BY id;
+ id | val | cnt
+----+-----+-----
+ 1 | 10 | 2
+ 2 | 20 | 0
+ 3 | 15 | 2
+ 4 | 25 | 0
+ 5 | 5 | 3
+ 6 | 30 | 0
+ 7 | 35 | 0
+ 8 | 20 | 3
+ 9 | 40 | 0
+ 10 | 45 | 0
+(10 rows)
+
+-- Volatile (random) is the prohibition target; today still accepted.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index 6d47728e911..d9748979d54 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -884,6 +884,19 @@ DROP INDEX rpr_integ_id_idx;
-- this test must be replaced with an error-case test that expects
-- random() in DEFINE to be rejected.
+-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
+-- This locks the boundary of the volatile-only prohibition.
+SELECT id, val, count(*) OVER w AS cnt
+FROM rpr_integ
+WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE B AS val > PREV(val)
+ AND length('x') = 1
+ AND to_char(date '2026-01-01', 'YYYY') = '2026')
+ORDER BY id;
+
+-- Volatile (random) is the prohibition target; today still accepted.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
--
2.50.1 (Apple Git-155)
From 84cd98072184ec63bb2f79477f03bbe81c659b83 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Mon, 4 May 2026 21:37:59 +0900
Subject: [PATCH 02/11] Unify RPR DEFINE walkers and reject volatile callees
Planner/executor: shared nav_traversal_walker + visit_nav_plan /
visit_nav_exec replace four pre-existing walkers; each DEFINE
variable is walked once per phase.
Parser: single define_walker with phase tag (BODY / NAV_ARG /
NAV_OFFSET) replaces two pre-existing walkers and enforces all rules
in one traversal. Volatile and NextValueExpr are rejected (RPR's NFA
may re-evaluate predicates during backtracking, making volatile
results non-deterministic; STABLE and IMMUTABLE are accepted).
The constant-offset rule now also catches column references in the
inner offset of compound forms.
---
src/backend/commands/explain.c | 8 +-
src/backend/executor/nodeWindowAgg.c | 282 +++++-------
src/backend/optimizer/plan/createplan.c | 434 +++++++++---------
src/backend/optimizer/plan/rpr.c | 34 ++
src/backend/parser/parse_rpr.c | 393 ++++++++++------
src/include/optimizer/rpr.h | 22 +
src/test/regress/expected/rpr.out | 74 +--
src/test/regress/expected/rpr_explain.out | 11 +-
src/test/regress/expected/rpr_integration.out | 40 +-
src/test/regress/sql/rpr.sql | 35 +-
src/test/regress/sql/rpr_explain.sql | 7 +-
src/test/regress/sql/rpr_integration.sql | 23 +-
src/tools/pgindent/typedefs.list | 10 +-
13 files changed, 758 insertions(+), 615 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 99de36b57f2..1a754bcdac5 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -3312,8 +3312,12 @@ show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es)
es);
break;
case RPR_NAV_OFFSET_FIXED:
- ExplainPropertyInteger("Nav Mark Lookahead", NULL,
- firstOffset, es);
+ if (firstOffset == INT64_MAX)
+ ExplainPropertyText("Nav Mark Lookahead", "infinite",
+ es);
+ else
+ ExplainPropertyInteger("Nav Mark Lookahead", NULL,
+ firstOffset, es);
break;
default:
elog(ERROR, "unrecognized RPR nav offset kind: %d",
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 93cb9bbdd11..af2351bccb8 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -246,8 +246,7 @@ static void update_reduced_frame(WindowObject winobj, int64 pos);
static bool nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched);
/* Forward declarations - navigation offset evaluation */
-static void eval_nav_max_offset(WindowAggState *winstate, List *defineClause);
-static void eval_nav_first_offset(WindowAggState *winstate, List *defineClause);
+static void eval_define_offsets(WindowAggState *winstate, List *defineClause);
/*
* Not null info bit array consists of 2-bit items
@@ -2579,12 +2578,10 @@ ExecWindowAgg(PlanState *pstate)
{
int64 firstreach;
- if (winstate->navFirstOffset > -winstate->nfaContext->matchStartRow)
- firstreach = winstate->nfaContext->matchStartRow
- + winstate->navFirstOffset;
- else
- firstreach = 0;
- navmarkpos = Min(navmarkpos, firstreach);
+ if (!pg_add_s64_overflow(winstate->nfaContext->matchStartRow,
+ winstate->navFirstOffset,
+ &firstreach))
+ navmarkpos = Min(navmarkpos, Max(firstreach, 0));
}
if (navmarkpos > winstate->nav_winobj->markpos)
@@ -3037,17 +3034,13 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->rpSkipTo = node->rpSkipTo;
/* Set up row pattern recognition PATTERN clause (compiled NFA) */
winstate->rpPattern = node->rpPattern;
- /* Set up nav offsets for tuplestore trim */
+ /* Set up nav offsets for tuplestore trim; resolve any NEEDS_EVAL kinds */
winstate->navMaxOffsetKind = node->navMaxOffsetKind;
winstate->navMaxOffset = node->navMaxOffset;
- if (winstate->navMaxOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL)
- eval_nav_max_offset(winstate, node->defineClause);
winstate->hasFirstNav = node->hasFirstNav;
winstate->navFirstOffsetKind = node->navFirstOffsetKind;
winstate->navFirstOffset = node->navFirstOffset;
- if (winstate->hasFirstNav &&
- winstate->navFirstOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL)
- eval_nav_first_offset(winstate, node->defineClause);
+ eval_define_offsets(winstate, node->defineClause);
/* Copy match_start dependency bitmapset for per-context evaluation */
winstate->defineMatchStartDependent = bms_copy(node->defineMatchStartDependent);
@@ -3997,42 +3990,64 @@ eval_nav_offset_helper(WindowAggState *winstate, Expr *offset_expr,
typedef struct
{
WindowAggState *winstate;
- int64 maxOffset;
- bool overflow; /* true if overflow detected */
-} EvalNavMaxContext;
+ int64 maxOffset; /* max backward-reach offset across all nav
+ * exprs */
+ bool maxOverflow; /* true if backward-reach overflow detected */
+ int64 minFirstOffset; /* min forward-from-match_start offset; may be
+ * negative (PREV_FIRST: inner - outer < 0) */
+} EvalDefineOffsetsContext;
/*
- * eval_nav_max_offset_walker
- * Walk expression tree evaluating backward-reach offsets at runtime.
+ * visit_nav_exec
+ * nav_traversal_walker callback (NavVisitFn) for the executor side.
+ * At each RPRNavExpr, evaluates the nav's offset expression(s) at
+ * runtime via eval_nav_offset_helper and accumulates:
+ *
+ * - maxOffset (backward reach): PREV, LAST-with-offset, compound
+ * PREV_LAST (sets maxOverflow on int64 overflow), compound
+ * NEXT_LAST (= max(inner - outer, 0))
+ * - minFirstOffset (forward reach from match_start): FIRST,
+ * compound PREV_FIRST (= inner - outer, may be negative),
+ * compound NEXT_FIRST (= inner + outer, clamped to INT64_MAX on
+ * overflow; always >= 0 so never updates minFirstOffset in practice)
*
- * Handles simple PREV, LAST-with-offset, and compound PREV_LAST/NEXT_LAST.
+ * Counterpart of visit_nav_plan but using runtime evaluation instead of
+ * Const folding; runs only for offsets the planner marked NEEDS_EVAL.
+ * Match-start dependency is not recomputed here -- the planner's bitmapset
+ * is reused via winstate->defineMatchStartDependent.
*/
-static bool
-eval_nav_max_offset_walker(Node *node, void *ctx)
+static void
+visit_nav_exec(NavTraversal *t, RPRNavExpr *nav)
{
- EvalNavMaxContext *context = (EvalNavMaxContext *) ctx;
-
- if (node == NULL)
- return false;
+ EvalDefineOffsetsContext *context = (EvalDefineOffsetsContext *) t->data;
- /* Short-circuit if overflow already detected */
- if (context->overflow)
- return false;
+ /*
+ * Parser guarantee (mirrors visit_nav_plan): nav's direct children are
+ * never RPRNavExpr -- compound nesting is flattened in place and any
+ * other nesting is rejected. Outer-kind dispatch is sufficient.
+ */
+ Assert(nav->arg == NULL || !IsA(nav->arg, RPRNavExpr));
+ Assert(nav->offset_arg == NULL || !IsA(nav->offset_arg, RPRNavExpr));
+ Assert(nav->compound_offset_arg == NULL ||
+ !IsA(nav->compound_offset_arg, RPRNavExpr));
- if (IsA(node, RPRNavExpr))
+ /* Backward reach: PREV, LAST-with-offset */
+ if (!context->maxOverflow)
{
- RPRNavExpr *nav = (RPRNavExpr *) node;
int64 reach = 0;
+ bool gotReach = false;
if (nav->kind == RPR_NAV_PREV)
{
reach = eval_nav_offset_helper(context->winstate,
nav->offset_arg, 1);
+ gotReach = true;
}
else if (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL)
{
reach = eval_nav_offset_helper(context->winstate,
nav->offset_arg, 0);
+ gotReach = true;
}
else if (nav->kind == RPR_NAV_PREV_LAST ||
nav->kind == RPR_NAV_NEXT_LAST)
@@ -4045,168 +4060,123 @@ eval_nav_max_offset_walker(Node *node, void *ctx)
if (nav->kind == RPR_NAV_PREV_LAST)
{
if (pg_add_s64_overflow(inner, outer, &reach))
- {
- context->overflow = true;
- return false;
- }
+ context->maxOverflow = true;
+ else
+ gotReach = true;
}
else
- reach = (inner > outer) ? inner - outer : 0;
+ {
+ reach = Max(inner - outer, 0);
+ gotReach = true;
+ }
}
- context->maxOffset = Max(context->maxOffset, reach);
-
- return false; /* don't walk into children */
+ if (gotReach)
+ context->maxOffset = Max(context->maxOffset, reach);
}
- return expression_tree_walker(node, eval_nav_max_offset_walker, ctx);
-}
-
-/*
- * eval_nav_max_offset
- * Evaluate non-constant backward-reach offsets at executor init time.
- *
- * Called when the planner set navMaxOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL
- * because some offset in PREV, LAST-with-offset, or compound PREV_LAST/
- * NEXT_LAST contains a parameter or non-foldable expression.
- *
- * On overflow, sets navMaxOffsetKind to RPR_NAV_OFFSET_RETAIN_ALL so that
- * tuplestore trim is disabled for backward navigation.
- */
-static void
-eval_nav_max_offset(WindowAggState *winstate, List *defineClause)
-{
- EvalNavMaxContext ctx;
- ListCell *lc;
-
- ctx.winstate = winstate;
- ctx.maxOffset = 0;
- ctx.overflow = false;
-
- foreach(lc, defineClause)
+ /* Forward reach from match_start: FIRST, compound PREV_FIRST/NEXT_FIRST */
+ if (nav->kind == RPR_NAV_FIRST)
{
- TargetEntry *te = (TargetEntry *) lfirst(lc);
+ int64 reach;
- eval_nav_max_offset_walker((Node *) te->expr, &ctx);
- }
-
- if (ctx.overflow)
- {
- winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL;
- winstate->navMaxOffset = 0;
+ reach = eval_nav_offset_helper(context->winstate,
+ nav->offset_arg, 0);
+ context->minFirstOffset = Min(context->minFirstOffset, reach);
}
- else
+ else if (nav->kind == RPR_NAV_PREV_FIRST ||
+ nav->kind == RPR_NAV_NEXT_FIRST)
{
- winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED;
- winstate->navMaxOffset = ctx.maxOffset;
- }
-}
+ int64 inner = eval_nav_offset_helper(context->winstate,
+ nav->offset_arg, 0);
+ int64 outer = eval_nav_offset_helper(context->winstate,
+ nav->compound_offset_arg, 1);
+ int64 reach;
-typedef struct
-{
- WindowAggState *winstate;
- int64 minOffset;
- bool found;
-} EvalNavFirstContext;
-
-/*
- * eval_nav_first_offset_walker
- * Walk expression tree evaluating forward-from-match_start offsets.
- *
- * Handles simple FIRST and compound PREV_FIRST/NEXT_FIRST.
- */
-static bool
-eval_nav_first_offset_walker(Node *node, void *ctx)
-{
- EvalNavFirstContext *context = (EvalNavFirstContext *) ctx;
-
- if (node == NULL)
- return false;
-
- if (IsA(node, RPRNavExpr))
- {
- RPRNavExpr *nav = (RPRNavExpr *) node;
- int64 combined = INT64_MAX;
-
- if (nav->kind == RPR_NAV_FIRST)
+ if (nav->kind == RPR_NAV_PREV_FIRST)
{
- context->found = true;
- combined = eval_nav_offset_helper(context->winstate,
- nav->offset_arg, 0);
+ /*
+ * reach = inner - outer. Both are non-negative, so the result >=
+ * -INT64_MAX, which cannot underflow int64.
+ */
+ reach = inner - outer;
}
- else if (nav->kind == RPR_NAV_PREV_FIRST ||
- nav->kind == RPR_NAV_NEXT_FIRST)
+ else
{
- int64 inner = eval_nav_offset_helper(context->winstate,
- nav->offset_arg, 0);
- int64 outer = eval_nav_offset_helper(context->winstate,
- nav->compound_offset_arg, 1);
-
- context->found = true;
- if (nav->kind == RPR_NAV_PREV_FIRST)
- {
- /*
- * combined = inner - outer. Both are non-negative, so the
- * result >= -INT64_MAX, which cannot underflow int64.
- */
- combined = inner - outer;
- }
- else
- {
- /*
- * NEXT_FIRST: combined = inner + outer. This can overflow,
- * but the result is always >= 0, so it never updates
- * minOffset (which tracks the minimum). Clamp to INT64_MAX
- * on overflow.
- */
- if (pg_add_s64_overflow(inner, outer, &combined))
- combined = INT64_MAX;
- }
+ /*
+ * NEXT_FIRST: reach = inner + outer. This can overflow, but the
+ * result is always >= 0, so it never updates minFirstOffset
+ * (which tracks the minimum). Clamp to INT64_MAX on overflow.
+ */
+ if (pg_add_s64_overflow(inner, outer, &reach))
+ reach = INT64_MAX;
}
-
- context->minOffset = Min(context->minOffset, combined);
-
- return false;
+ context->minFirstOffset = Min(context->minFirstOffset, reach);
}
-
- return expression_tree_walker(node, eval_nav_first_offset_walker, ctx);
}
/*
- * eval_nav_first_offset
- * Evaluate non-constant forward-from-match_start offsets at executor
- * init time.
+ * eval_define_offsets
+ * Evaluate non-constant nav offsets at executor init time.
+ *
+ * Called when the planner set navMaxOffsetKind and/or navFirstOffsetKind
+ * to RPR_NAV_OFFSET_NEEDS_EVAL because some offset contains a parameter
+ * or non-foldable expression. Updates only the fields whose kind was
+ * NEEDS_EVAL; FIXED kinds are left unchanged.
*
- * Called when the planner set navFirstOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL
- * because some offset in FIRST or compound PREV_FIRST/NEXT_FIRST contains
- * a parameter or non-foldable expression.
+ * On backward-reach overflow, sets navMaxOffsetKind to
+ * RPR_NAV_OFFSET_RETAIN_ALL so that tuplestore trim is disabled for
+ * backward navigation.
*/
static void
-eval_nav_first_offset(WindowAggState *winstate, List *defineClause)
+eval_define_offsets(WindowAggState *winstate, List *defineClause)
{
- EvalNavFirstContext ctx;
+ EvalDefineOffsetsContext ctx;
+ NavTraversal trav;
ListCell *lc;
+ bool needsMax = (winstate->navMaxOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL);
+ bool needsFirst = (winstate->hasFirstNav &&
+ winstate->navFirstOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL);
+
+ if (!needsMax && !needsFirst)
+ return;
ctx.winstate = winstate;
- ctx.minOffset = INT64_MAX;
- ctx.found = false;
+ ctx.maxOffset = 0;
+ ctx.maxOverflow = false;
+ ctx.minFirstOffset = INT64_MAX;
+
+ trav.visit = visit_nav_exec;
+ trav.data = &ctx;
foreach(lc, defineClause)
{
TargetEntry *te = (TargetEntry *) lfirst(lc);
- eval_nav_first_offset_walker((Node *) te->expr, &ctx);
+ nav_traversal_walker((Node *) te->expr, &trav);
}
- if (ctx.found && ctx.minOffset < INT64_MAX)
+ if (needsMax)
{
- winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED;
- winstate->navFirstOffset = ctx.minOffset;
+ if (ctx.maxOverflow)
+ {
+ winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL;
+ winstate->navMaxOffset = 0;
+ }
+ else
+ {
+ winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED;
+ winstate->navMaxOffset = ctx.maxOffset;
+ }
}
- else
+
+ if (needsFirst)
{
winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED;
- winstate->navFirstOffset = 0;
+ if (ctx.minFirstOffset < INT64_MAX)
+ winstate->navFirstOffset = ctx.minFirstOffset;
+ else
+ winstate->navFirstOffset = INT64_MAX;
}
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 52205cc7159..c8ecaeea7cf 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -294,6 +294,9 @@ static WindowAgg *make_windowagg(List *tlist, WindowClause *wc,
RPRPattern *compiledPattern,
List *defineClause,
Bitmapset *defineMatchStartDependent,
+ RPRNavOffsetKind navMaxOffsetKind, int64 navMaxOffset,
+ bool hasFirstNav,
+ RPRNavOffsetKind navFirstOffsetKind, int64 navFirstOffset,
List *qual, bool topWindow,
Plan *lefttree);
static Group *make_group(List *tlist, List *qual, int numGroupCols,
@@ -2464,13 +2467,20 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
}
/*
- * NavOffsetContext - context for compute_nav_offsets walker.
+ * DefineMetadataContext - context for compute_define_metadata walker.
*
- * Collects both backward reach (PREV, LAST-with-offset, compound
- * PREV_LAST/NEXT_LAST) and forward-from-match-start reach (FIRST,
- * compound PREV_FIRST/NEXT_FIRST) in a single tree walk.
+ * Collects three pieces of metadata from the DEFINE clause in a single
+ * tree walk per variable:
+ * - backward reach (PREV, LAST-with-offset, compound PREV_LAST/NEXT_LAST)
+ * - forward-from-match-start reach (FIRST, compound PREV_FIRST/NEXT_FIRST)
+ * - per-variable match_start dependency (variables containing FIRST,
+ * LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/PREV_LAST/
+ * NEXT_LAST-with-offset require per-context re-evaluation)
+ *
+ * The driver sets curVarIdx to the index of the variable being walked
+ * before each invocation; the walker uses it to populate matchStartDependent.
*/
-typedef struct NavOffsetContext
+typedef struct DefineMetadataContext
{
int64 maxOffset; /* max PREV/LAST backward offset (>= 0) */
bool maxNeedsEval; /* non-constant PREV/LAST offset found */
@@ -2479,7 +2489,10 @@ typedef struct NavOffsetContext
* PREV_FIRST) */
bool hasFirst; /* any FIRST node found */
bool firstNeedsEval; /* non-constant FIRST offset found */
-} NavOffsetContext;
+ int curVarIdx; /* DEFINE variable currently being walked */
+ Bitmapset *matchStartDependent; /* variables that depend on
+ * match_start */
+} DefineMetadataContext;
/*
* Helper: extract constant offset from an expression, handling NULL/negative.
@@ -2514,175 +2527,207 @@ extract_const_offset(Expr *expr, int64 defaultOffset, int64 *result)
}
/*
- * nav_offset_walker
- * Expression tree walker for compute_nav_offsets.
+ * visit_nav_plan
+ * nav_traversal_walker callback (NavVisitFn) for the planner side.
+ * At each RPRNavExpr in a DEFINE expression, computes:
+ *
+ * 1. backward reach (maxOffset) for tuplestore trim:
+ * - PREV(v, N), LAST(v, N) -> N (default 1)
+ * - compound PREV_LAST(v, N, M) -> N + M (overflow -> maxOverflow)
+ * - compound NEXT_LAST(v, N, M) -> max(N - M, 0)
*
- * For each RPRNavExpr found, extract its constant offset(s) and update the
- * NavOffsetContext with the maximum backward reach (maxOffset) and minimum
- * forward reach (firstOffset). Handles simple navigation (PREV, NEXT,
- * FIRST, LAST) and compound forms (PREV_FIRST, NEXT_FIRST, PREV_LAST,
- * NEXT_LAST) by combining inner and outer offsets.
+ * 2. forward reach (firstOffset) for tuplestore trim:
+ * - FIRST(v, N) -> N (default 0)
+ * - compound PREV_FIRST(v, N, M) -> N - M (may be negative)
+ * - compound NEXT_FIRST(v, N, M) -> N + M
*
- * Non-constant offsets set maxNeedsEval or firstNeedsEval. Overflow sets
- * maxOverflow or firstOverflow for RETAIN_ALL fallback.
+ * 3. per-variable match_start dependency for absorption suppression:
+ * outer nav kinds that reach match_start (FIRST, LAST-with-offset,
+ * PREV_FIRST, NEXT_FIRST, PREV_LAST/NEXT_LAST-with-offset) add
+ * curVarIdx to matchStartDependent.
+ *
+ * Constant offsets are extracted via extract_const_offset; non-constant
+ * offsets set maxNeedsEval / firstNeedsEval so the executor can resolve
+ * them at init time (see visit_nav_exec). Classification uses only the
+ * outer nav kind: parser nesting restrictions prevent FIRST/LAST inside
+ * a PREV/NEXT value subexpression.
*/
-static bool
-nav_offset_walker(Node *node, void *ctx)
+static void
+visit_nav_plan(NavTraversal *t, RPRNavExpr *nav)
{
- NavOffsetContext *context = (NavOffsetContext *) ctx;
+ DefineMetadataContext *context = (DefineMetadataContext *) t->data;
- if (node == NULL)
- return false;
+ /*
+ * Parser guarantee: by the time the planner sees a DEFINE expression,
+ * compound nesting has been flattened into a single RPRNavExpr and any
+ * other RPRNavExpr nesting has been rejected. So nav's direct child
+ * fields are not themselves RPRNavExpr nodes, and outer-kind dispatch
+ * below is sufficient.
+ */
+ Assert(nav->arg == NULL || !IsA(nav->arg, RPRNavExpr));
+ Assert(nav->offset_arg == NULL || !IsA(nav->offset_arg, RPRNavExpr));
+ Assert(nav->compound_offset_arg == NULL ||
+ !IsA(nav->compound_offset_arg, RPRNavExpr));
- if (IsA(node, RPRNavExpr))
+ /*
+ * Simple PREV(v, N) and LAST(v, N): backward reach from currentpos. LAST
+ * without offset = currentpos, no backward reach. NEXT: forward only,
+ * irrelevant for trim.
+ */
+ if (nav->kind == RPR_NAV_PREV ||
+ (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL))
{
- RPRNavExpr *nav = (RPRNavExpr *) node;
-
- /*
- * Simple PREV(v, N) and LAST(v, N): backward reach from currentpos.
- * LAST without offset = currentpos, no backward reach. NEXT: forward
- * only, irrelevant for trim.
- */
- if (nav->kind == RPR_NAV_PREV ||
- (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL))
+ if (!context->maxNeedsEval)
{
- if (!context->maxNeedsEval)
- {
- int64 offset;
+ int64 offset;
- if (extract_const_offset(nav->offset_arg, 1, &offset))
- context->maxOffset = Max(context->maxOffset, offset);
- else
- context->maxNeedsEval = true;
- }
+ if (extract_const_offset(nav->offset_arg, 1, &offset))
+ context->maxOffset = Max(context->maxOffset, offset);
+ else
+ context->maxNeedsEval = true;
}
+ }
- /*
- * Simple FIRST(v, N): forward reach from match_start. Smaller N means
- * older rows needed.
- */
- if (nav->kind == RPR_NAV_FIRST)
- {
- context->hasFirst = true;
+ /*
+ * Simple FIRST(v, N): forward reach from match_start. Smaller N means
+ * older rows needed.
+ */
+ if (nav->kind == RPR_NAV_FIRST)
+ {
+ context->hasFirst = true;
- if (!context->firstNeedsEval)
- {
- int64 offset;
+ if (!context->firstNeedsEval)
+ {
+ int64 offset;
- if (extract_const_offset(nav->offset_arg, 0, &offset))
- context->firstOffset = Min(context->firstOffset, offset);
- else
- context->firstNeedsEval = true;
- }
+ if (extract_const_offset(nav->offset_arg, 0, &offset))
+ context->firstOffset = Min(context->firstOffset, offset);
+ else
+ context->firstNeedsEval = true;
}
+ }
- /*
- * Compound PREV_LAST / NEXT_LAST: base = currentpos. PREV_LAST(v, N,
- * M): target = currentpos - N - M -> lookback = N + M NEXT_LAST(v, N,
- * M): target = currentpos - N + M -> lookback = max(N - M, 0)
- */
- if (nav->kind == RPR_NAV_PREV_LAST ||
- nav->kind == RPR_NAV_NEXT_LAST)
+ /*
+ * Compound PREV_LAST / NEXT_LAST: base = currentpos. PREV_LAST(v, N, M):
+ * target = currentpos - N - M -> lookback = N + M NEXT_LAST(v, N, M):
+ * target = currentpos - N + M -> lookback = max(N - M, 0)
+ */
+ if (nav->kind == RPR_NAV_PREV_LAST ||
+ nav->kind == RPR_NAV_NEXT_LAST)
+ {
+ if (!context->maxNeedsEval)
{
- if (!context->maxNeedsEval)
- {
- int64 inner,
- outer,
- combined;
+ int64 inner;
+ int64 outer;
+ int64 reach;
- if (extract_const_offset(nav->offset_arg, 0, &inner) &&
- extract_const_offset(nav->compound_offset_arg, 1, &outer))
+ if (extract_const_offset(nav->offset_arg, 0, &inner) &&
+ extract_const_offset(nav->compound_offset_arg, 1, &outer))
+ {
+ if (nav->kind == RPR_NAV_PREV_LAST)
{
- if (nav->kind == RPR_NAV_PREV_LAST)
- {
- if (pg_add_s64_overflow(inner, outer, &combined))
- {
- context->maxOverflow = true;
- return false;
- }
- }
+ if (pg_add_s64_overflow(inner, outer, &reach))
+ context->maxOverflow = true;
else
- combined = (inner > outer) ? inner - outer : 0;
-
- context->maxOffset = Max(context->maxOffset, combined);
+ context->maxOffset = Max(context->maxOffset, reach);
}
else
- context->maxNeedsEval = true;
+ {
+ reach = Max(inner - outer, 0);
+ context->maxOffset = Max(context->maxOffset, reach);
+ }
}
+ else
+ context->maxNeedsEval = true;
}
+ }
- /*
- * Compound PREV_FIRST / NEXT_FIRST: base = match_start. PREV_FIRST(v,
- * N, M): target = match_start + N - M NEXT_FIRST(v, N, M): target =
- * match_start + N + M The combined offset (N+/-M) from match_start
- * can be negative, meaning rows before match_start are needed.
- */
- if (nav->kind == RPR_NAV_PREV_FIRST ||
- nav->kind == RPR_NAV_NEXT_FIRST)
+ /*
+ * Compound PREV_FIRST / NEXT_FIRST: base = match_start. PREV_FIRST(v, N,
+ * M): target = match_start + N - M NEXT_FIRST(v, N, M): target =
+ * match_start + N + M The combined offset (N+/-M) from match_start can be
+ * negative, meaning rows before match_start are needed.
+ */
+ if (nav->kind == RPR_NAV_PREV_FIRST ||
+ nav->kind == RPR_NAV_NEXT_FIRST)
+ {
+ context->hasFirst = true;
+
+ if (!context->firstNeedsEval)
{
- context->hasFirst = true;
+ int64 inner;
+ int64 outer;
+ int64 reach;
- if (!context->firstNeedsEval)
+ if (extract_const_offset(nav->offset_arg, 0, &inner) &&
+ extract_const_offset(nav->compound_offset_arg, 1, &outer))
{
- int64 inner,
- outer,
- combined;
-
- if (extract_const_offset(nav->offset_arg, 0, &inner) &&
- extract_const_offset(nav->compound_offset_arg, 1, &outer))
+ if (nav->kind == RPR_NAV_PREV_FIRST)
{
- if (nav->kind == RPR_NAV_PREV_FIRST)
- {
- /*
- * combined = inner - outer. Both are non-negative,
- * so the result >= -INT64_MAX, which cannot underflow
- * int64. No overflow check needed.
- */
- combined = inner - outer;
- }
- else
- {
- /*
- * NEXT_FIRST: combined = inner + outer. This can
- * overflow, but the result is always >= 0, so it
- * never updates firstOffset (which tracks the
- * minimum). Clamp to INT64_MAX on overflow.
- */
- if (pg_add_s64_overflow(inner, outer, &combined))
- combined = INT64_MAX;
- }
-
- context->firstOffset = Min(context->firstOffset, combined);
+ /*
+ * reach = inner - outer. Both are non-negative, so the
+ * result >= -INT64_MAX, which cannot underflow int64. No
+ * overflow check needed.
+ */
+ reach = inner - outer;
}
else
- context->firstNeedsEval = true;
+ {
+ /*
+ * NEXT_FIRST: reach = inner + outer. This can overflow,
+ * but the result is always >= 0, so it never updates
+ * firstOffset (which tracks the minimum). Clamp to
+ * INT64_MAX on overflow.
+ */
+ if (pg_add_s64_overflow(inner, outer, &reach))
+ reach = INT64_MAX;
+ }
+
+ context->firstOffset = Min(context->firstOffset, reach);
}
+ else
+ context->firstNeedsEval = true;
}
-
- /* Don't walk into RPRNavExpr children */
- return false;
}
- return expression_tree_walker(node, nav_offset_walker, ctx);
+ /* Match-start dependency: classify the outer nav kind. */
+ if (nav->kind == RPR_NAV_FIRST ||
+ (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL) ||
+ nav->kind == RPR_NAV_PREV_FIRST ||
+ nav->kind == RPR_NAV_NEXT_FIRST ||
+ ((nav->kind == RPR_NAV_PREV_LAST ||
+ nav->kind == RPR_NAV_NEXT_LAST) &&
+ nav->offset_arg != NULL))
+ context->matchStartDependent =
+ bms_add_member(context->matchStartDependent,
+ context->curVarIdx);
}
/*
- * compute_nav_offsets
- * Compute navigation offsets for tuplestore trim in a single pass.
+ * compute_define_metadata
+ * Compute navigation offsets and match_start dependency for the
+ * DEFINE clause in a single pass per variable.
*
- * Walks all DEFINE clause expressions once, computing:
+ * Walks each DEFINE variable expression once, computing:
* - maxOffset: max backward reach from PREV, LAST-with-offset,
* compound PREV_LAST/NEXT_LAST
* - hasFirst/firstOffset: min forward-from-match-start reach from
* FIRST, compound PREV_FIRST/NEXT_FIRST
+ * - matchStartDependent: bitmapset of variable indices whose
+ * expressions contain navigation that depends on match_start
+ * (FIRST, LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/
+ * PREV_LAST/NEXT_LAST-with-offset). Such variables require
+ * per-context re-evaluation during NFA processing.
*/
static void
-compute_nav_offsets(List *defineClause,
- RPRNavOffsetKind *maxKind, int64 *maxResult,
- bool *hasFirst,
- RPRNavOffsetKind *firstKind, int64 *firstResult)
+compute_define_metadata(List *defineClause,
+ RPRNavOffsetKind *maxKind, int64 *maxResult,
+ bool *hasFirst,
+ RPRNavOffsetKind *firstKind, int64 *firstResult,
+ Bitmapset **matchStartDependent)
{
- NavOffsetContext ctx;
+ DefineMetadataContext ctx;
+ NavTraversal trav;
ListCell *lc;
ctx.maxOffset = 0;
@@ -2691,14 +2736,22 @@ compute_nav_offsets(List *defineClause,
ctx.firstOffset = INT64_MAX; /* sentinel: no FIRST found yet */
ctx.hasFirst = false;
ctx.firstNeedsEval = false;
+ ctx.curVarIdx = 0;
+ ctx.matchStartDependent = NULL;
+
+ trav.visit = visit_nav_plan;
+ trav.data = &ctx;
foreach(lc, defineClause)
{
TargetEntry *te = (TargetEntry *) lfirst(lc);
- nav_offset_walker((Node *) te->expr, &ctx);
+ nav_traversal_walker((Node *) te->expr, &trav);
+ ctx.curVarIdx++;
}
+ *matchStartDependent = ctx.matchStartDependent;
+
/* Max backward offset */
if (ctx.maxOverflow)
{
@@ -2725,15 +2778,11 @@ compute_nav_offsets(List *defineClause,
*firstKind = RPR_NAV_OFFSET_NEEDS_EVAL;
*firstResult = 0;
}
- else if (ctx.firstOffset == INT64_MAX)
- {
- *firstKind = RPR_NAV_OFFSET_FIXED;
- *firstResult = 0; /* only implicit FIRST(v) */
- }
else
{
*firstKind = RPR_NAV_OFFSET_FIXED;
- *firstResult = ctx.firstOffset; /* may be negative */
+ *firstResult = ctx.firstOffset; /* may be negative; INT64_MAX if
+ * overflowed */
}
}
else
@@ -2743,83 +2792,6 @@ compute_nav_offsets(List *defineClause,
}
}
-/*
- * has_match_start_dependency
- * Check if an expression tree contains navigation that depends on
- * match_start: FIRST, LAST-with-offset, or compound PREV_FIRST/
- * NEXT_FIRST/PREV_LAST/NEXT_LAST with offset. Such expressions
- * require per-context re-evaluation during NFA processing.
- *
- * LAST without offset always resolves to currentpos and is
- * match_start-independent.
- */
-static bool
-has_match_start_dependency(Node *node, void *context)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, RPRNavExpr))
- {
- RPRNavExpr *nav = (RPRNavExpr *) node;
-
- if (nav->kind == RPR_NAV_FIRST)
- return true;
- if (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL)
- return true;
-
- /* Compound kinds with FIRST base depend on match_start */
- if (nav->kind == RPR_NAV_PREV_FIRST ||
- nav->kind == RPR_NAV_NEXT_FIRST)
- return true;
-
- /*
- * PREV_LAST/NEXT_LAST: inner is LAST, which uses currentpos.
- * match_start-dependent only if inner has offset (clamped to
- * match_start).
- */
- if ((nav->kind == RPR_NAV_PREV_LAST ||
- nav->kind == RPR_NAV_NEXT_LAST) &&
- nav->offset_arg != NULL)
- return true;
-
- /* Check children (arg may contain further nav expressions) */
- return has_match_start_dependency((Node *) nav->arg, context);
- }
-
- return expression_tree_walker(node, has_match_start_dependency, NULL);
-}
-
-/*
- * compute_match_start_dependent
- * Build a Bitmapset of DEFINE variable indices whose expressions
- * depend on match_start (contain FIRST, LAST-with-offset, or
- * compound PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST with offset).
- *
- * Variables in this set require per-context re-evaluation during NFA
- * processing, because different contexts may have different match_start
- * values.
- */
-static Bitmapset *
-compute_match_start_dependent(List *defineClause)
-{
- Bitmapset *result = NULL;
- ListCell *lc;
- int varIdx = 0;
-
- foreach(lc, defineClause)
- {
- TargetEntry *te = (TargetEntry *) lfirst(lc);
-
- if (has_match_start_dependency((Node *) te->expr, NULL))
- result = bms_add_member(result, varIdx);
-
- varIdx++;
- }
-
- return result;
-}
-
/*
* create_windowagg_plan
*
@@ -2848,6 +2820,11 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
List *filteredDefineClause = NIL;
RPRPattern *compiledPattern = NULL;
Bitmapset *matchStartDependent = NULL;
+ RPRNavOffsetKind navMaxOffsetKind = RPR_NAV_OFFSET_FIXED;
+ int64 navMaxOffset = 0;
+ bool hasFirstNav = false;
+ RPRNavOffsetKind navFirstOffsetKind = RPR_NAV_OFFSET_FIXED;
+ int64 navFirstOffset = 0;
/*
@@ -2910,8 +2887,16 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
buildDefineVariableList(wc->defineClause, &defineVariableList);
filteredDefineClause = wc->defineClause;
- /* Identify match_start-dependent DEFINE variables */
- matchStartDependent = compute_match_start_dependent(wc->defineClause);
+ /*
+ * Walk DEFINE once: collect nav offsets (for tuplestore trim) and the
+ * bitmapset of match_start-dependent variables (for absorption
+ * suppression in buildRPRPattern).
+ */
+ compute_define_metadata(wc->defineClause,
+ &navMaxOffsetKind, &navMaxOffset,
+ &hasFirstNav,
+ &navFirstOffsetKind, &navFirstOffset,
+ &matchStartDependent);
/* Compile and optimize RPR patterns */
compiledPattern = buildRPRPattern(wc->rpPattern,
@@ -2937,6 +2922,9 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
compiledPattern,
filteredDefineClause,
matchStartDependent,
+ navMaxOffsetKind, navMaxOffset,
+ hasFirstNav,
+ navFirstOffsetKind, navFirstOffset,
best_path->qual,
best_path->topwindow,
subplan);
@@ -7011,6 +6999,9 @@ make_windowagg(List *tlist, WindowClause *wc,
RPRPattern *compiledPattern,
List *defineClause,
Bitmapset *defineMatchStartDependent,
+ RPRNavOffsetKind navMaxOffsetKind, int64 navMaxOffset,
+ bool hasFirstNav,
+ RPRNavOffsetKind navFirstOffsetKind, int64 navFirstOffset,
List *qual, bool topWindow, Plan *lefttree)
{
WindowAgg *node = makeNode(WindowAgg);
@@ -7048,11 +7039,12 @@ make_windowagg(List *tlist, WindowClause *wc,
/* Store pre-computed match_start dependency bitmapset */
node->defineMatchStartDependent = defineMatchStartDependent;
- /* Compute nav offsets for tuplestore trim optimization */
- compute_nav_offsets(defineClause,
- &node->navMaxOffsetKind, &node->navMaxOffset,
- &node->hasFirstNav,
- &node->navFirstOffsetKind, &node->navFirstOffset);
+ /* Store pre-computed nav offsets for tuplestore trim optimization */
+ node->navMaxOffsetKind = navMaxOffsetKind;
+ node->navMaxOffset = navMaxOffset;
+ node->hasFirstNav = hasFirstNav;
+ node->navFirstOffsetKind = navFirstOffsetKind;
+ node->navFirstOffset = navFirstOffset;
plan->targetlist = tlist;
plan->lefttree = lefttree;
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index 2543170c374..a817eb4a63f 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -41,6 +41,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
#include "optimizer/rpr.h"
/* Forward declarations - pattern comparison */
@@ -1991,3 +1992,36 @@ buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList,
return result;
}
+
+/*
+ * nav_traversal_walker
+ * Shared expression-tree walker that locates RPRNavExpr nodes in a
+ * DEFINE expression and dispatches each one to a caller-supplied
+ * visitor. Used by:
+ * - planner (visit_nav_plan in createplan.c) to collect tuplestore
+ * trim offsets and per-variable match_start dependency
+ * - executor (visit_nav_exec in nodeWindowAgg.c) to evaluate
+ * non-constant nav offsets at WindowAggState init time
+ *
+ * The driver wraps a mode-specific context in a NavTraversal and passes
+ * it as ctx; the visitor casts t->data to its own context type. Children
+ * of an RPRNavExpr are not walked: the parser's nesting restrictions
+ * ensure offsets and dependencies are fully captured by the outer nav
+ * kind, so the visitor only needs to inspect the RPRNavExpr itself.
+ */
+bool
+nav_traversal_walker(Node *node, void *ctx)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, RPRNavExpr))
+ {
+ NavTraversal *t = (NavTraversal *) ctx;
+
+ t->visit(t, (RPRNavExpr *) node);
+ return false;
+ }
+
+ return expression_tree_walker(node, nav_traversal_walker, ctx);
+}
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index f56b7db5bc8..87411abcbe2 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -25,6 +25,7 @@
#include "postgres.h"
+#include "catalog/pg_proc.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -35,14 +36,33 @@
#include "parser/parse_expr.h"
#include "parser/parse_rpr.h"
#include "parser/parse_target.h"
+#include "utils/lsyscache.h"
+
+/* DEFINE clause walker context -- see define_walker for usage. */
+typedef enum
+{
+ DEFINE_PHASE_BODY, /* top-level DEFINE expression */
+ DEFINE_PHASE_NAV_ARG, /* inside an outer nav's arg subtree */
+ DEFINE_PHASE_NAV_OFFSET, /* inside an outer nav's offset_arg /
+ * compound_offset_arg */
+} DefinePhase;
+
+typedef struct
+{
+ ParseState *pstate;
+ DefinePhase phase;
+ int nav_count; /* RPRNavExpr nodes seen in current nav.arg */
+ bool has_column_ref; /* Var seen in current nav scope */
+ RPRNavKind inner_kind; /* kind of first nested nav in current arg */
+} DefineWalkCtx;
/* Forward declarations */
static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
List *rpDefs, List **varNames);
static List *transformDefineClause(ParseState *pstate, WindowClause *wc,
WindowDef *windef, List **targetlist);
-static void check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate);
-static bool check_rpr_nav_nesting_walker(Node *node, void *context);
+static bool define_walker(Node *node, void *context);
+static bool nav_volatile_func_checker(Oid funcid, void *context);
/*
* transformRPR
@@ -412,9 +432,22 @@ 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 */
+ /*
+ * Validate DEFINE expressions: nested PREV/NEXT, column references,
+ * compound flatten, volatile callees -- all in a single walk per
+ * variable.
+ */
foreach_ptr(TargetEntry, te, defineClause)
- (void) check_rpr_nav_nesting_walker((Node *) te->expr, pstate);
+ {
+ DefineWalkCtx ctx;
+
+ ctx.pstate = pstate;
+ ctx.phase = DEFINE_PHASE_BODY;
+ ctx.nav_count = 0;
+ ctx.has_column_ref = false;
+ ctx.inner_kind = 0;
+ (void) define_walker((Node *) te->expr, &ctx);
+ }
/* mark column origins */
markTargetListOrigins(pstate, defineClause);
@@ -426,169 +459,239 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
}
/*
- * check_rpr_nav_expr
- * Validate a single RPRNavExpr node by walking its arg and offset_arg
- * subtrees in a single pass each. Check for illegal nesting, missing
- * column references, and non-constant offset expressions.
+ * Single-pass DEFINE clause validator.
*
- * Nesting rules (SQL standard 5.6.4):
- * - PREV/NEXT wrapping FIRST/LAST: allowed (compound navigation)
- * - FIRST/LAST wrapping PREV/NEXT: prohibited
- * - Same-category nesting (PREV inside PREV, FIRST inside FIRST, etc.):
- * prohibited
+ * One walker function (define_walker) visits every node in a DEFINE
+ * expression exactly once and enforces every rule:
+ * - Volatile callees and NextValueExpr are rejected at parse time
+ * (RPR's NFA may evaluate the same row's predicate multiple times
+ * during backtracking, so a volatile result would make matching
+ * non-deterministic).
+ * - For each outer RPRNavExpr (per SQL 5.6.4 nesting rules):
+ * * arg must contain at least one column reference
+ * * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind
+ * * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...)
+ * * offset_arg / compound_offset_arg must not contain column refs
+ *
+ * The walker uses a phase tag to know which subtree it is in: DEFINE
+ * body (top-level), inside a nav.arg, or inside a nav.offset_arg /
+ * compound_offset_arg. When entering an outer nav (PHASE_BODY), it
+ * walks nav.arg in PHASE_NAV_ARG to collect nesting/column-ref state,
+ * applies compound flatten or raises a nesting error, then walks the
+ * (post-flatten) offset(s) in PHASE_NAV_OFFSET to enforce the
+ * constant-offset rule. No subtree is walked twice.
*/
-typedef struct
+
+/*
+ * nav_volatile_func_checker
+ * check_functions_in_node callback: true if funcid is VOLATILE.
+ */
+static bool
+nav_volatile_func_checker(Oid funcid, void *context)
{
- int nav_count; /* number of RPRNavExpr nodes found */
- bool has_column_ref; /* Var found */
- RPRNavKind inner_kind; /* kind of first (outermost) nested RPRNavExpr */
-} NavCheckResult;
+ return (func_volatile(funcid) == PROVOLATILE_VOLATILE);
+}
+/*
+ * define_walker
+ * Single-pass DEFINE clause validator. At each node, enforces:
+ *
+ * [1] no volatile callees (and no NextValueExpr) -- anywhere in
+ * the tree, regardless of phase
+ * [2] for each outer RPRNavExpr (PHASE_BODY -> PHASE_NAV_ARG):
+ * - nav.arg must contain at least one column reference
+ * - PREV/NEXT wrapping FIRST/LAST is flattened in place
+ * to a compound kind (PREV_FIRST, PREV_LAST, NEXT_FIRST,
+ * NEXT_LAST)
+ * - any other nesting is rejected (FIRST(PREV()),
+ * PREV(PREV()), FIRST(FIRST()), three-or-more deep)
+ * [3] for each nav offset (PHASE_NAV_OFFSET):
+ * - must be a run-time constant (no column references)
+ *
+ * Var sightings feed the column-ref rule for the enclosing nav scope;
+ * RPRNavExpr sightings inside PHASE_NAV_ARG feed the nesting decision.
+ * See the comment block above DefinePhase for the overall design and
+ * how each subtree is walked exactly once.
+ */
static bool
-nav_check_walker(Node *node, void *context)
+define_walker(Node *node, void *context)
{
- NavCheckResult *result = (NavCheckResult *) context;
+ DefineWalkCtx *ctx = (DefineWalkCtx *) context;
if (node == NULL)
return false;
- if (IsA(node, RPRNavExpr))
- {
- if (result->nav_count == 0)
- result->inner_kind = ((RPRNavExpr *) node)->kind;
- result->nav_count++;
- }
- if (IsA(node, Var))
- result->has_column_ref = true;
-
- return expression_tree_walker(node, nav_check_walker, context);
-}
-static void
-check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate)
-{
- NavCheckResult result;
- bool outer_is_physical = (nav->kind == RPR_NAV_PREV ||
- nav->kind == RPR_NAV_NEXT);
+ /*
+ * Reject volatile callees and sequence operations anywhere in the DEFINE
+ * clause: they are non-deterministic across the multiple predicate
+ * evaluations that NFA backtracking and PREV/NEXT navigation may trigger
+ * for a single row.
+ */
+ if (check_functions_in_node(node, nav_volatile_func_checker, NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("volatile functions are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node))));
+ if (IsA(node, NextValueExpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("sequence operations are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node))));
- /* Check arg subtree: nesting + column reference in one walk */
- memset(&result, 0, sizeof(result));
- (void) nav_check_walker((Node *) nav->arg, &result);
+ /* Var sighting feeds the column-ref rule for the enclosing nav scope. */
+ if (IsA(node, Var) &&
+ (ctx->phase == DEFINE_PHASE_NAV_ARG ||
+ ctx->phase == DEFINE_PHASE_NAV_OFFSET))
+ ctx->has_column_ref = true;
- if (result.nav_count > 0)
+ if (IsA(node, RPRNavExpr))
{
- bool inner_is_physical = (result.inner_kind == RPR_NAV_PREV ||
- result.inner_kind == RPR_NAV_NEXT);
+ RPRNavExpr *nav = (RPRNavExpr *) node;
- if (outer_is_physical && !inner_is_physical)
+ if (ctx->phase == DEFINE_PHASE_NAV_ARG)
{
/*
- * PREV/NEXT wrapping FIRST/LAST: compound navigation per SQL
- * standard 5.6.4. Flatten the nested RPRNavExpr into a single
- * compound node. The inner RPRNavExpr must be the direct arg of
- * the outer; expressions like PREV(val + FIRST(v)) are not valid
- * compound navigation.
+ * Nested nav inside an outer nav.arg: record for the outer's
+ * compound / nesting decision, then keep recursing so deeper Vars
+ * and volatile callees are still observed.
*/
- RPRNavExpr *inner;
-
- /* Reject triple-or-deeper nesting (e.g. PREV(FIRST(PREV(x)))) */
- if (result.nav_count > 1)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot nest row pattern navigation more than two levels deep"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(pstate, nav->location)));
-
- if (!IsA(nav->arg, RPRNavExpr))
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(pstate, nav->location)));
- inner = (RPRNavExpr *) nav->arg;
-
- /* Determine compound kind */
- if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_FIRST)
- nav->kind = RPR_NAV_PREV_FIRST;
- else if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_LAST)
- nav->kind = RPR_NAV_PREV_LAST;
- else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_FIRST)
- nav->kind = RPR_NAV_NEXT_FIRST;
- else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_LAST)
- nav->kind = RPR_NAV_NEXT_LAST;
-
- /* Move outer offset to compound_offset_arg */
- nav->compound_offset_arg = nav->offset_arg;
-
- /* Move inner offset and arg up */
- nav->offset_arg = inner->offset_arg;
- nav->arg = inner->arg;
-
- /* No further nesting check needed - already validated */
- return;
- }
- else if (!outer_is_physical && inner_is_physical)
- {
- /* FIRST/LAST wrapping PREV/NEXT: prohibited by standard */
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(pstate, nav->location)));
+ if (ctx->nav_count == 0)
+ ctx->inner_kind = nav->kind;
+ ctx->nav_count++;
+ return expression_tree_walker(node, define_walker, ctx);
}
- else if (outer_is_physical && inner_is_physical)
+
+ if (ctx->phase == DEFINE_PHASE_NAV_OFFSET)
{
- /* PREV/NEXT wrapping PREV/NEXT: prohibited */
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("PREV and NEXT cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(pstate, nav->location)));
+ /*
+ * Navs inside offset_arg are unusual but not directly banned; the
+ * constant-offset rule will catch any Var or volatile they
+ * contain.
+ */
+ return expression_tree_walker(node, define_walker, ctx);
}
- else
+
+ /*
+ * PHASE_BODY: this is an outer nav at top level. Walk arg first to
+ * collect nesting / column-ref state, then validate and (for compound
+ * forms) flatten, then walk offset(s).
+ */
{
- /* FIRST/LAST wrapping FIRST/LAST: prohibited */
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain FIRST or LAST"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(pstate, nav->location)));
- }
- }
- if (!result.has_column_ref)
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("argument of row pattern navigation operation must include at least one column reference"),
- parser_errposition(pstate, nav->location)));
+ DefineWalkCtx saved = *ctx;
+ bool outer_phys = (nav->kind == RPR_NAV_PREV ||
+ nav->kind == RPR_NAV_NEXT);
+ bool flattened = false;
+
+ ctx->phase = DEFINE_PHASE_NAV_ARG;
+ ctx->nav_count = 0;
+ ctx->has_column_ref = false;
+ ctx->inner_kind = 0;
+ (void) define_walker((Node *) nav->arg, ctx);
+
+ if (ctx->nav_count > 0)
+ {
+ bool inner_phys = (ctx->inner_kind == RPR_NAV_PREV ||
+ ctx->inner_kind == RPR_NAV_NEXT);
- /* Check offset_arg: column ref + volatile in one walk */
- if (nav->offset_arg != NULL)
- {
- memset(&result, 0, sizeof(result));
- (void) nav_check_walker((Node *) nav->offset_arg, &result);
-
- if (result.has_column_ref ||
- contain_volatile_functions((Node *) nav->offset_arg))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row pattern navigation offset must be a run-time constant"),
- parser_errposition(pstate, nav->location)));
- }
-}
+ if (outer_phys && !inner_phys)
+ {
+ RPRNavExpr *inner;
-/*
- * check_rpr_nav_nesting_walker
- * Walk the DEFINE clause expression tree and validate each RPRNavExpr.
- */
-static bool
-check_rpr_nav_nesting_walker(Node *node, void *context)
-{
- if (node == NULL)
- return false;
- if (IsA(node, RPRNavExpr))
- {
- check_rpr_nav_expr((RPRNavExpr *) node, (ParseState *) context);
- /* don't recurse into arg; nesting already checked above */
- return false;
+ /* Reject triple-or-deeper nesting */
+ if (ctx->nav_count > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot nest row pattern navigation more than two levels deep"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location)));
+
+ if (!IsA(nav->arg, RPRNavExpr))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location)));
+
+ inner = (RPRNavExpr *) nav->arg;
+
+ if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_FIRST)
+ nav->kind = RPR_NAV_PREV_FIRST;
+ else if (nav->kind == RPR_NAV_PREV && inner->kind == RPR_NAV_LAST)
+ nav->kind = RPR_NAV_PREV_LAST;
+ else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_FIRST)
+ nav->kind = RPR_NAV_NEXT_FIRST;
+ else if (nav->kind == RPR_NAV_NEXT && inner->kind == RPR_NAV_LAST)
+ nav->kind = RPR_NAV_NEXT_LAST;
+
+ nav->compound_offset_arg = nav->offset_arg;
+ nav->offset_arg = inner->offset_arg;
+ nav->arg = inner->arg;
+ flattened = true;
+ }
+ else if (!outer_phys && inner_phys)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location)));
+ else if (outer_phys && inner_phys)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("PREV and NEXT cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location)));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain FIRST or LAST"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location)));
+ }
+ else if (!ctx->has_column_ref)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition(ctx->pstate, nav->location)));
+ }
+
+ /*
+ * Walk offset arg(s) in PHASE_NAV_OFFSET to enforce the
+ * constant-offset rule. For compound forms, both the inner
+ * (post-flatten nav->offset_arg) and outer (compound_offset_arg)
+ * offsets must be constants; the inner's column-ref status was
+ * not separately tracked during the PHASE_NAV_ARG walk (which
+ * only checks that nav.arg as a whole has at least one Var), so
+ * it is re-walked here to catch column references the inner
+ * offset would have leaked.
+ */
+ ctx->phase = DEFINE_PHASE_NAV_OFFSET;
+
+ if (nav->offset_arg != NULL)
+ {
+ ctx->has_column_ref = false;
+ (void) define_walker((Node *) nav->offset_arg, ctx);
+ if (ctx->has_column_ref)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg))));
+ }
+ if (flattened && nav->compound_offset_arg != NULL)
+ {
+ ctx->has_column_ref = false;
+ (void) define_walker((Node *) nav->compound_offset_arg, ctx);
+ if (ctx->has_column_ref)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg))));
+ }
+
+ *ctx = saved;
+ return false;
+ }
}
- return expression_tree_walker(node, check_rpr_nav_nesting_walker, context);
+
+ return expression_tree_walker(node, define_walker, ctx);
}
diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h
index 0a14cfad79b..63c4b09daff 100644
--- a/src/include/optimizer/rpr.h
+++ b/src/include/optimizer/rpr.h
@@ -62,4 +62,26 @@ extern RPRPattern *buildRPRPattern(RPRPatternNode *pattern, List *defineVariable
RPSkipTo rpSkipTo, int frameOptions,
bool hasMatchStartDependent);
+/*
+ * Shared traversal walker for DEFINE clause RPRNavExpr collection.
+ *
+ * Both planner (nav-offset / match_start dependency analysis) and executor
+ * (runtime offset evaluation) need to walk DEFINE expressions and dispatch
+ * per RPRNavExpr. They differ only in what they do at each nav node, so
+ * the traversal frame is shared (nav_traversal_walker, defined in rpr.c)
+ * and the per-nav action is supplied as a callback. The driver allocates
+ * a mode-specific context, points NavTraversal.data at it, and casts
+ * inside its visitor.
+ */
+struct NavTraversal;
+typedef void (*NavVisitFn) (struct NavTraversal *t, RPRNavExpr *nav);
+
+typedef struct NavTraversal
+{
+ NavVisitFn visit;
+ void *data; /* mode-specific context */
+} NavTraversal;
+
+extern bool nav_traversal_walker(Node *node, void *ctx);
+
#endif /* OPTIMIZER_RPR_H */
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index 85384f6b096..8793dda3cc3 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1144,7 +1144,31 @@ WINDOW w AS (
);
ERROR: row pattern navigation offset must be a run-time constant
LINE 7: DEFINE A AS PREV(price, price) > 0
- ^
+ ^
+-- Non-constant offset: column reference in compound inner 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(LAST(price, price), 2) > 0
+);
+ERROR: row pattern navigation offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(LAST(price, price), 2) > 0
+ ^
+-- Non-constant offset: column reference in compound outer 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(LAST(price, 1), price) > 0
+);
+ERROR: row pattern navigation offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(LAST(price, 1), price) > 0
+ ^
-- Non-constant offset: volatile function as offset
SELECT price FROM stock
WINDOW w AS (
@@ -1154,9 +1178,9 @@ WINDOW w AS (
PATTERN (A)
DEFINE A AS PREV(price, random()::int) > 0
);
-ERROR: row pattern navigation offset must be a run-time constant
+ERROR: volatile functions are not allowed in DEFINE clause
LINE 7: DEFINE A AS PREV(price, random()::int) > 0
- ^
+ ^
-- Non-constant offset: subquery as offset
SELECT price FROM stock
WINDOW w AS (
@@ -1181,7 +1205,7 @@ WINDOW w AS (
ERROR: cannot use subquery in DEFINE expression
LINE 7: DEFINE A AS PREV(price + (SELECT 1)) > 0
^
--- First arg: volatile function is allowed (evaluated on target row)
+-- Volatile function inside nav.arg is rejected at parse time
SELECT company, tdate, price,
first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
FROM stock
@@ -1191,30 +1215,24 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS PREV(price + random() * 0) >= 0
);
- company | tdate | price | first_value | last_value | count
-----------+------------+-------+-------------+------------+-------
- company1 | 07-01-2023 | 100 | | | 0
- company1 | 07-02-2023 | 200 | 200 | 130 | 9
- 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 | | | 0
- company2 | 07-02-2023 | 2000 | 2000 | 1300 | 9
- 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)
-
+ERROR: volatile functions are not allowed in DEFINE clause
+LINE 8: DEFINE A AS PREV(price + random() * 0) >= 0
+ ^
+-- nextval is volatile (per pg_proc), so it is rejected via the FuncExpr
+-- path with the "volatile functions" message
+CREATE SEQUENCE rpr_seq;
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > nextval('rpr_seq')
+);
+ERROR: volatile functions are not allowed in DEFINE clause
+LINE 7: DEFINE A AS price > nextval('rpr_seq')
+ ^
+DROP SEQUENCE rpr_seq;
--
-- 2-arg PREV/NEXT: functional tests
--
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index dc3522f930f..0a049d1beba 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -4744,9 +4744,8 @@ WINDOW w AS (
-> Function Scan on generate_series s
(5 rows)
--- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> no trim impact
--- N + M overflows int64, but target is forward from match_start so it never
--- constrains trim. Lookahead remains at default (0).
+-- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> infinite
+-- N + M overflows int64; forward reach is unbounded, displayed as infinite.
EXPLAIN (COSTS OFF) SELECT count(*) OVER w
FROM generate_series(1,10) s(v)
WINDOW w AS (
@@ -4760,7 +4759,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a+
Nav Mark Lookback: 0
- Nav Mark Lookahead: 0
+ Nav Mark Lookahead: infinite
-> Function Scan on generate_series s
(6 rows)
@@ -4803,7 +4802,7 @@ EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
RESET plan_cache_mode;
DEALLOCATE test_overflow_lookback;
--- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> no trim impact
+-- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> infinite
PREPARE test_overflow_lookahead(int8, int8) AS
SELECT count(*) OVER w
FROM generate_series(1,10) s(v)
@@ -4821,7 +4820,7 @@ EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a+
Nav Mark Lookback: 0
- Nav Mark Lookahead: 0
+ Nav Mark Lookahead: infinite
Storage: Memory Maximum Storage: 17kB
NFA States: 1 peak, 11 total, 0 merged
NFA Contexts: 2 peak, 11 total, 10 pruned
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index ef6a157f45d..905bd3538de 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1406,23 +1406,12 @@ DROP INDEX rpr_integ_id_idx;
-- ============================================================
-- B9. RPR + Volatile function in DEFINE
-- ============================================================
--- Records the current behaviour: DEFINE today accepts volatile
--- functions such as random() and the query runs to completion.
--- To keep the expected output deterministic the predicate uses
--- "random() >= 0.0", which is structurally equivalent to TRUE and
--- therefore does not perturb the match result. The interesting
--- property is that volatile invocation does not crash or short-
--- circuit pattern matching.
---
--- XXX: volatile functions in DEFINE are slated to be rejected at
--- parse time. Under RPR's NFA engine the same row's DEFINE
--- predicate may be evaluated multiple times (backtracking,
--- PREV/NEXT navigation), so a truly volatile result would make
--- pattern matching non-deterministic. When the prohibition lands,
--- this test must be replaced with an error-case test that expects
--- random() in DEFINE to be rejected.
+-- Volatile functions in DEFINE are rejected at parse time. Under
+-- RPR's NFA engine the same row's DEFINE predicate may be evaluated
+-- multiple times (backtracking, PREV/NEXT navigation), so a volatile
+-- result would make pattern matching non-deterministic. STABLE and
+-- IMMUTABLE callees are accepted.
-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
--- This locks the boundary of the volatile-only prohibition.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
@@ -1446,7 +1435,7 @@ ORDER BY id;
10 | 45 | 0
(10 rows)
--- Volatile (random) is the prohibition target; today still accepted.
+-- Volatile (random) is rejected.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
@@ -1454,20 +1443,9 @@ WINDOW w AS (ORDER BY id
PATTERN (A B+)
DEFINE B AS val > PREV(val) AND random() >= 0.0)
ORDER BY id;
- id | val | cnt
-----+-----+-----
- 1 | 10 | 2
- 2 | 20 | 0
- 3 | 15 | 2
- 4 | 25 | 0
- 5 | 5 | 3
- 6 | 30 | 0
- 7 | 35 | 0
- 8 | 20 | 3
- 9 | 40 | 0
- 10 | 45 | 0
-(10 rows)
-
+ERROR: volatile functions are not allowed in DEFINE clause
+LINE 6: DEFINE B AS val > PREV(val) AND random() >= 0.0)
+ ^
-- ============================================================
-- B10. RPR + Correlated subquery in WHERE
-- ============================================================
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index 5563e062cde..e4790f75b0a 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -541,6 +541,26 @@ WINDOW w AS (
DEFINE A AS PREV(price, price) > 0
);
+-- Non-constant offset: column reference in compound inner 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(LAST(price, price), 2) > 0
+);
+
+-- Non-constant offset: column reference in compound outer 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(LAST(price, 1), price) > 0
+);
+
-- Non-constant offset: volatile function as offset
SELECT price FROM stock
WINDOW w AS (
@@ -571,7 +591,7 @@ WINDOW w AS (
DEFINE A AS PREV(price + (SELECT 1)) > 0
);
--- First arg: volatile function is allowed (evaluated on target row)
+-- Volatile function inside nav.arg is rejected at parse time
SELECT company, tdate, price,
first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
FROM stock
@@ -582,6 +602,19 @@ WINDOW w AS (
DEFINE A AS PREV(price + random() * 0) >= 0
);
+-- nextval is volatile (per pg_proc), so it is rejected via the FuncExpr
+-- path with the "volatile functions" message
+CREATE SEQUENCE rpr_seq;
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > nextval('rpr_seq')
+);
+DROP SEQUENCE rpr_seq;
+
--
-- 2-arg PREV/NEXT: functional tests
--
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index a3789e92631..e123be60aea 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -2699,9 +2699,8 @@ WINDOW w AS (
DEFINE A AS PREV(LAST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL
);
--- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> no trim impact
--- N + M overflows int64, but target is forward from match_start so it never
--- constrains trim. Lookahead remains at default (0).
+-- Compound NEXT(FIRST(val, N), M): constant lookahead overflow -> infinite
+-- N + M overflows int64; forward reach is unbounded, displayed as infinite.
EXPLAIN (COSTS OFF) SELECT count(*) OVER w
FROM generate_series(1,10) s(v)
WINDOW w AS (
@@ -2728,7 +2727,7 @@ EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
RESET plan_cache_mode;
DEALLOCATE test_overflow_lookback;
--- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> no trim impact
+-- Compound NEXT(FIRST(val, $1), $2): parameter lookahead overflow -> infinite
PREPARE test_overflow_lookahead(int8, int8) AS
SELECT count(*) OVER w
FROM generate_series(1,10) s(v)
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index d9748979d54..29b2db2f7bb 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -868,24 +868,13 @@ DROP INDEX rpr_integ_id_idx;
-- ============================================================
-- B9. RPR + Volatile function in DEFINE
-- ============================================================
--- Records the current behaviour: DEFINE today accepts volatile
--- functions such as random() and the query runs to completion.
--- To keep the expected output deterministic the predicate uses
--- "random() >= 0.0", which is structurally equivalent to TRUE and
--- therefore does not perturb the match result. The interesting
--- property is that volatile invocation does not crash or short-
--- circuit pattern matching.
---
--- XXX: volatile functions in DEFINE are slated to be rejected at
--- parse time. Under RPR's NFA engine the same row's DEFINE
--- predicate may be evaluated multiple times (backtracking,
--- PREV/NEXT navigation), so a truly volatile result would make
--- pattern matching non-deterministic. When the prohibition lands,
--- this test must be replaced with an error-case test that expects
--- random() in DEFINE to be rejected.
+-- Volatile functions in DEFINE are rejected at parse time. Under
+-- RPR's NFA engine the same row's DEFINE predicate may be evaluated
+-- multiple times (backtracking, PREV/NEXT navigation), so a volatile
+-- result would make pattern matching non-deterministic. STABLE and
+-- IMMUTABLE callees are accepted.
-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
--- This locks the boundary of the volatile-only prohibition.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
@@ -896,7 +885,7 @@ WINDOW w AS (ORDER BY id
AND to_char(date '2026-01-01', 'YYYY') = '2026')
ORDER BY id;
--- Volatile (random) is the prohibition target; today still accepted.
+-- Volatile (random) is rejected.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8e889ab5e0f..d23b392800e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -663,7 +663,10 @@ DecodingWorkerShared
DefElem
DefElemAction
DefaultACLInfo
+DefineMetadataContext
+DefinePhase
DefineStmt
+DefineWalkCtx
DefnDumperPtr
DeleteStmt
DependenciesParseState
@@ -762,8 +765,7 @@ ErrorData
ErrorSaveContext
EstimateDSMForeignScan_function
EstimationInfo
-EvalNavFirstContext
-EvalNavMaxContext
+EvalDefineOffsetsContext
EventTriggerCacheEntry
EventTriggerCacheItem
EventTriggerCacheStateType
@@ -1824,8 +1826,8 @@ NamedLWLockTrancheRequest
NamedTuplestoreScan
NamedTuplestoreScanState
NamespaceInfo
-NavCheckResult
-NavOffsetContext
+NavTraversal
+NavVisitFn
NestLoop
NestLoopParam
NestLoopState
--
2.50.1 (Apple Git-155)
From 56ebb1ad70a152cb22d4adb60069b4625659421e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 14:29:14 +0900
Subject: [PATCH 03/11] Cover RPR empty-match path with EXPLAIN tests; fix
stale XXX comments
The test_728_* cases in rpr_nfa.sql claimed (via XXX) that the visited
bitmap blocks empty iterations. In fact the NFA finds the empty
matches; window aggregates just return 0 / NULL over the length-0
frame, indistinguishable from "no match" without MATCH_NUMBER().
Replace the XXX comments with the actual behavior, and add paired
EXPLAIN ANALYZE tests in rpr_explain.sql that lock in
"NFA: 3 matched (len 0/0/0.0)" as observable regression coverage.
---
src/test/regress/expected/rpr_explain.out | 200 ++++++++++++++++++++++
src/test/regress/expected/rpr_nfa.out | 29 ++--
src/test/regress/sql/rpr_explain.sql | 116 +++++++++++++
src/test/regress/sql/rpr_nfa.sql | 29 ++--
4 files changed, 340 insertions(+), 34 deletions(-)
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index 0a049d1beba..c4516d3c756 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -2102,6 +2102,206 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=0.00 loops=1)
(5 rows)
+-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql.
+-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT
+-- result alone cannot distinguish "no match" from "empty match"; the
+-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable
+-- proof that the empty matches were found.
+-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min0 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){0,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){0,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 6 peak, 20 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min1 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){1,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){1,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 5 peak, 16 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min2 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 5 peak, 16 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty
+CREATE VIEW rpr_ev_edge_empty_match_mixed AS
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=4.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 8 peak, 26 total, 5 merged
+ NFA Contexts: 4 peak, 5 total, 1 pruned
+ NFA: 3 matched (len 0/2/1.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=4.00 loops=1)
+(9 rows)
+
+-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_multi AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+---------------------------
+ PATTERN ((a? b?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a? b?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 6 peak, 20 total, 0 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
-- Single row
CREATE VIEW rpr_ev_edge_single_row AS
SELECT count(*) OVER w
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index a19b26c3b94..4cff7cfbbd7 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -4395,9 +4395,9 @@ WINDOW w AS (
(3 rows)
-- (A?){0,3}: min=0, nullable inner.
--- A never matches. A? matches empty, min=0 satisfied immediately.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches but A? matches empty, satisfying min=0 immediately.
+-- NFA reports 3 length-0 matches (one per row); first_value / last_value
+-- are NULL because the window frame for an empty match has no rows.
WITH test_728_min0 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4425,9 +4425,8 @@ WINDOW w AS (
(3 rows)
-- (A?){1,3}: min=1, nullable inner.
--- A never matches. Need 1 empty iteration to satisfy min=1.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches; one empty iteration satisfies min=1.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min1 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4454,11 +4453,9 @@ WINDOW w AS (
3 | {B} | |
(3 rows)
--- (A?){2,3}: min=2, nullable inner.
--- A never matches. Need 2 empty iterations to satisfy min=2.
--- Per standard: STR06=(STRE STRE) is valid for min=2.
--- Expected: empty match for every row
--- XXX: visited bitmap blocks second empty iteration -> match failure
+-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- is valid: two empty iterations satisfy min=2.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4486,9 +4483,8 @@ WINDOW w AS (
(3 rows)
-- (A?){2,3} mixed: some rows match A, some don't
--- Rows 1-2: A matches, greedy takes 2 -> min satisfied
--- Row 3: A doesn't match, needs 2 empty iterations for min=2
--- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3})
+-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match)
+-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match)
-- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4
WITH test_728_min2_mixed AS (
SELECT * FROM (VALUES
@@ -4559,9 +4555,8 @@ WINDOW w AS (
6 | {B} | 6 | 6
(6 rows)
--- (A? B?){2,3}: pure empty body (nothing matches)
--- XXX: All NULL: same issue as test_728_min2 (empty match at context
--- start yields UNMATCHED via startPos-1 initial advance)
+-- (A? B?){2,3}: pure empty body (nothing matches A or B).
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
(1, ARRAY['C']),
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index e123be60aea..d339a80a673 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -1178,6 +1178,122 @@ WINDOW w AS (
DEFINE A AS v = 1, B AS v = 2
);');
+-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql.
+-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT
+-- result alone cannot distinguish "no match" from "empty match"; the
+-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable
+-- proof that the empty matches were found.
+
+-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min0 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min1 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min2 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty
+CREATE VIEW rpr_ev_edge_empty_match_mixed AS
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);');
+
+-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_multi AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);');
+
-- Single row
CREATE VIEW rpr_ev_edge_single_row AS
SELECT count(*) OVER w
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 1d27e0dc09e..29ec4a9dacb 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -3234,9 +3234,9 @@ WINDOW w AS (
);
-- (A?){0,3}: min=0, nullable inner.
--- A never matches. A? matches empty, min=0 satisfied immediately.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches but A? matches empty, satisfying min=0 immediately.
+-- NFA reports 3 length-0 matches (one per row); first_value / last_value
+-- are NULL because the window frame for an empty match has no rows.
WITH test_728_min0 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3258,9 +3258,8 @@ WINDOW w AS (
);
-- (A?){1,3}: min=1, nullable inner.
--- A never matches. Need 1 empty iteration to satisfy min=1.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches; one empty iteration satisfies min=1.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min1 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3281,11 +3280,9 @@ WINDOW w AS (
A AS 'A' = ANY(flags)
);
--- (A?){2,3}: min=2, nullable inner.
--- A never matches. Need 2 empty iterations to satisfy min=2.
--- Per standard: STR06=(STRE STRE) is valid for min=2.
--- Expected: empty match for every row
--- XXX: visited bitmap blocks second empty iteration -> match failure
+-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- is valid: two empty iterations satisfy min=2.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3307,9 +3304,8 @@ WINDOW w AS (
);
-- (A?){2,3} mixed: some rows match A, some don't
--- Rows 1-2: A matches, greedy takes 2 -> min satisfied
--- Row 3: A doesn't match, needs 2 empty iterations for min=2
--- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3})
+-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match)
+-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match)
-- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4
WITH test_728_min2_mixed AS (
SELECT * FROM (VALUES
@@ -3364,9 +3360,8 @@ WINDOW w AS (
B AS 'B' = ANY(flags)
);
--- (A? B?){2,3}: pure empty body (nothing matches)
--- XXX: All NULL: same issue as test_728_min2 (empty match at context
--- start yields UNMATCHED via startPos-1 initial advance)
+-- (A? B?){2,3}: pure empty body (nothing matches A or B).
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
(1, ARRAY['C']),
--
2.50.1 (Apple Git-155)
From af9f50ed1522bda716d10edf932bc5aeb4b0cb37 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 11:36:53 +0900
Subject: [PATCH 04/11] Reclassify DEFINE qualifier check and reword diagnostic
to "expression"
Split the pre-check into three branches: pattern variable ->
FEATURE_NOT_SUPPORTED, range variable (via refnameNamespaceItem) ->
SYNTAX_ERROR, anything else -> fall through to normal resolution.
Reword "qualified column reference" to "qualified expression" since
the quoted token may include an indirection target on composite
types (e.g. (A.items).amount).
---
src/backend/parser/parse_expr.c | 31 +++++++---
src/test/regress/expected/rpr_base.out | 80 +++++++++++++++++++++++---
src/test/regress/sql/rpr_base.sql | 61 ++++++++++++++++++--
3 files changed, 153 insertions(+), 19 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f145342e1fb..69148328719 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -628,11 +628,24 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
if (node != NULL)
return node;
- /*
- * Qualified column references in DEFINE are not supported. This covers
- * both FROM-clause range variables (prohibited by §6.5) and pattern
- * variable qualified names (e.g. UP.price), which are valid per §4.16
- * but not yet implemented.
+ /*----------
+ * Qualified references in DEFINE need a tri-classification:
+ *
+ * pattern variable qualifier (e.g. UP.price): valid per 19075-5 4.16
+ * but not yet implemented -- raise FEATURE_NOT_SUPPORTED.
+ *
+ * FROM-clause range variable qualifier: prohibited by 19075-5 6.5
+ * -- raise SYNTAX_ERROR.
+ *
+ * any other qualifier (typo, undefined name): fall through and let
+ * normal column resolution produce a sensible error.
+ *
+ * The quoted text reflects only the ColumnRef portion; a trailing field
+ * selection on a composite type (e.g. ".amount" in "(A.items).amount")
+ * lives in the surrounding A_Indirection node and is not included here.
+ * That can be revisited when MEASURES support adds indirection-aware
+ * traversal.
+ *----------
*/
if (pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE &&
list_length(cref->fields) != 1)
@@ -653,15 +666,17 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
if (is_pattern_var)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("pattern variable qualified column reference \"%s\" is not supported in DEFINE clause",
+ errmsg("pattern variable qualified expression \"%s\" is not supported in DEFINE clause",
NameListToString(cref->fields)),
parser_errposition(pstate, cref->location)));
- else
+ else if (refnameNamespaceItem(pstate, NULL, qualifier,
+ cref->location, NULL) != NULL)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("range variable qualified column reference \"%s\" is not allowed in DEFINE clause",
+ errmsg("range variable qualified expression \"%s\" is not allowed in DEFINE clause",
NameListToString(cref->fields)),
parser_errposition(pstate, cref->location)));
+ /* else: unknown qualifier -- fall through to normal resolution */
}
/*----------
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index a63211ff364..86abb96c177 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -3074,10 +3074,10 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS A.val > 0
);
-ERROR: pattern variable qualified column reference "a.val" is not supported in DEFINE clause
+ERROR: pattern variable qualified expression "a.val" is not supported in DEFINE clause
LINE 7: DEFINE A AS A.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "a.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "a.val" is not supported
-- PATTERN-only variable qualified name: not supported even without DEFINE entry
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3087,10 +3087,10 @@ WINDOW w AS (
PATTERN (A+ B+)
DEFINE A AS B.val > 0
);
-ERROR: pattern variable qualified column reference "b.val" is not supported in DEFINE clause
+ERROR: pattern variable qualified expression "b.val" is not supported in DEFINE clause
LINE 7: DEFINE A AS B.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- DEFINE-only variable qualified name: still a pattern variable, not a range variable
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3103,7 +3103,7 @@ WINDOW w AS (
ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3113,10 +3113,76 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS rpr_err.val > 0
);
-ERROR: range variable qualified column reference "rpr_err.val" is not allowed in DEFINE clause
+ERROR: range variable qualified expression "rpr_err.val" is not allowed in DEFINE clause
LINE 7: DEFINE A AS rpr_err.val > 0
^
--- Expected: ERROR: range variable qualified column reference "rpr_err.val" is not allowed
+-- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed
+-- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check
+-- must fall through so that normal column resolution produces a sensible error.
+SELECT COUNT(*) OVER w
+FROM rpr_err
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS nosuch.val > 0
+);
+ERROR: missing FROM-clause entry for table "nosuch"
+LINE 7: DEFINE A AS nosuch.val > 0
+ ^
+-- Expected: ERROR: missing FROM-clause entry for table "nosuch"
+-- Unqualified composite field access in DEFINE works: no qualifier means no
+-- pattern/range-var navigation, so the pre-check skips and normal resolution
+-- handles "(items).amount" via A_Indirection on the current row.
+CREATE TYPE rpr_item AS (name TEXT, amount INT);
+CREATE TEMP TABLE rpr_composite (id int, items rpr_item);
+INSERT INTO rpr_composite VALUES (1, ROW('a',5)), (2, ROW('b',15)), (3, ROW('c',25));
+SELECT id, (items).amount, COUNT(*) OVER w AS cnt
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A+)
+ DEFINE A AS (items).amount > 10
+);
+ id | amount | cnt
+----+--------+-----
+ 1 | 5 | 0
+ 2 | 15 | 2
+ 3 | 25 | 1
+(3 rows)
+
+-- Expected: rows where (items).amount > 10 form matches; counts reflect frame size
+-- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or
+-- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in
+-- the surrounding A_Indirection node and is not visible to the pre-check.
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (A.items).amount > 10
+);
+ERROR: pattern variable qualified expression "a.items" is not supported in DEFINE clause
+LINE 7: DEFINE A AS (A.items).amount > 10
+ ^
+-- Expected: ERROR: pattern variable qualified expression "a.items" is not supported
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (rpr_composite.items).amount > 10
+);
+ERROR: range variable qualified expression "rpr_composite.items" is not allowed in DEFINE clause
+LINE 7: DEFINE A AS (rpr_composite.items).amount > 10
+ ^
+-- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed
+DROP TABLE rpr_composite;
+DROP TYPE rpr_item;
-- Semantic errors
-- Undefined column in DEFINE
SELECT COUNT(*) OVER w
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index 86ed06fec68..e8c72706720 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -2092,7 +2092,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS A.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "a.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "a.val" is not supported
-- PATTERN-only variable qualified name: not supported even without DEFINE entry
SELECT COUNT(*) OVER w
@@ -2103,7 +2103,7 @@ WINDOW w AS (
PATTERN (A+ B+)
DEFINE A AS B.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- DEFINE-only variable qualified name: still a pattern variable, not a range variable
SELECT COUNT(*) OVER w
@@ -2114,7 +2114,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0, B AS B.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
SELECT COUNT(*) OVER w
@@ -2125,7 +2125,60 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS rpr_err.val > 0
);
--- Expected: ERROR: range variable qualified column reference "rpr_err.val" is not allowed
+-- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed
+
+-- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check
+-- must fall through so that normal column resolution produces a sensible error.
+SELECT COUNT(*) OVER w
+FROM rpr_err
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS nosuch.val > 0
+);
+-- Expected: ERROR: missing FROM-clause entry for table "nosuch"
+
+-- Unqualified composite field access in DEFINE works: no qualifier means no
+-- pattern/range-var navigation, so the pre-check skips and normal resolution
+-- handles "(items).amount" via A_Indirection on the current row.
+CREATE TYPE rpr_item AS (name TEXT, amount INT);
+CREATE TEMP TABLE rpr_composite (id int, items rpr_item);
+INSERT INTO rpr_composite VALUES (1, ROW('a',5)), (2, ROW('b',15)), (3, ROW('c',25));
+SELECT id, (items).amount, COUNT(*) OVER w AS cnt
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A+)
+ DEFINE A AS (items).amount > 10
+);
+-- Expected: rows where (items).amount > 10 form matches; counts reflect frame size
+
+-- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or
+-- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in
+-- the surrounding A_Indirection node and is not visible to the pre-check.
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (A.items).amount > 10
+);
+-- Expected: ERROR: pattern variable qualified expression "a.items" is not supported
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (rpr_composite.items).amount > 10
+);
+-- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed
+DROP TABLE rpr_composite;
+DROP TYPE rpr_item;
-- Semantic errors
--
2.50.1 (Apple Git-155)
From c3ccc037cc128f5a640a7357669db43f1dcdf6f9 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 11:44:45 +0900
Subject: [PATCH 05/11] Sync stale comments on DEFINE/PATTERN handling
validateRPRPatternVarCount() validates DEFINE names against the
PATTERN list and rejects any not used in PATTERN; the surrounding
comments said it "collected" them. Align the function header,
inline block comment, and the call-site comment. Also fix the
matching SELECT doc ("filtered during planning" -> "rejected with
an error") and the XXX note's wording.
---
doc/src/sgml/ref/select.sgml | 4 ++--
src/backend/parser/parse_rpr.c | 28 ++++++++++++++--------------
2 files changed, 16 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 5272d6c0bfa..e4708331439 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1192,8 +1192,8 @@ DEFINE <replaceable class="parameter">definition_variable_name</replaceable> AS
</synopsis>
Conversely, variables defined in the <literal>DEFINE</literal> clause
- but not used in the <literal>PATTERN</literal> clause are filtered out
- during query planning.
+ but not used in the <literal>PATTERN</literal> clause are rejected
+ with an error.
</para>
<para>
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 87411abcbe2..2c6fccebd47 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -183,11 +183,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* Recursively traverses the pattern tree, collecting unique variable names.
* Throws an error if the number of unique variables exceeds RPR_VARID_MAX.
*
- * If rpDefs is non-NULL, DEFINE variable names are also collected into
- * varNames so that transformColumnRef can distinguish pattern variable
- * qualifiers from FROM-clause range variables.
- *
- * varNames is both input and output: existing names are preserved, new ones added.
+ * If rpDefs is non-NULL, each DEFINE variable name is also validated against
+ * varNames; any DEFINE name not present in PATTERN is rejected with an error.
+ * varNames itself is not extended by this step -- it carries only PATTERN
+ * variable names, which is what transformColumnRef checks via
+ * p_rpr_pattern_vars to identify pattern variable qualifiers.
*/
static void
validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
@@ -244,10 +244,10 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
/*
- * After the top-level call, also collect DEFINE variable names that are
- * not already in the list. This is only done once at the outermost
- * recursion level, detected by rpDefs being non-NULL (recursive calls
- * pass NULL).
+ * After the top-level call, validate that every DEFINE variable name is
+ * present in the PATTERN variable list; reject names not used in PATTERN.
+ * This is only done once at the outermost recursion level, detected by
+ * rpDefs being non-NULL (recursive calls pass NULL).
*/
if (rpDefs)
{
@@ -293,9 +293,9 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
* Note: Variables not in DEFINE are evaluated as TRUE by the executor.
* Variables in DEFINE but not in PATTERN are rejected as an error.
*
- * XXX Pattern variable qualified column references in DEFINE (e.g.
- * "A.price") are not yet supported. Currently rejected by
- * transformColumnRef in parse_expr.c via the p_rpr_pattern_vars check.
+ * XXX Pattern variable qualified expressions in DEFINE (e.g. "A.price")
+ * are not yet supported. Currently rejected by transformColumnRef in
+ * parse_expr.c via the p_rpr_pattern_vars check.
*/
static List *
transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
@@ -317,8 +317,8 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
Assert(windef->rpCommonSyntax->rpDefs != NULL);
/*
- * Validate PATTERN variable count and collect all RPR variable names
- * (PATTERN + DEFINE) for use in transformColumnRef.
+ * Validate PATTERN variable count, reject DEFINE variables not used in
+ * PATTERN, and collect PATTERN variable names for transformColumnRef.
*/
validateRPRPatternVarCount(pstate, windef->rpCommonSyntax->rpPattern,
windef->rpCommonSyntax->rpDefs,
--
2.50.1 (Apple Git-155)
From 1aba5a8b605023ea2993faf98869b7be2860fc53 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 17:21:56 +0900
Subject: [PATCH 06/11] Add trailing commas to RPR enum definitions
RPRNavKind, RPRNavOffsetKind, RPRPatternNodeType lacked a trailing
comma on the last enumerator, contrary to project coding style.
---
src/include/nodes/parsenodes.h | 4 ++--
src/include/nodes/primnodes.h | 2 +-
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 455db2cec61..adefb1d5bad 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -604,7 +604,7 @@ typedef enum RPRNavOffsetKind
RPR_NAV_OFFSET_FIXED, /* resolved constant; use the offset value */
RPR_NAV_OFFSET_NEEDS_EVAL, /* non-constant offset; evaluate at executor
* init */
- RPR_NAV_OFFSET_RETAIN_ALL /* cannot determine; retain all rows (no trim) */
+ RPR_NAV_OFFSET_RETAIN_ALL, /* cannot determine; retain all rows (no trim) */
} RPRNavOffsetKind;
/*
@@ -615,7 +615,7 @@ typedef enum RPRPatternNodeType
RPR_PATTERN_VAR, /* variable reference */
RPR_PATTERN_SEQ, /* sequence (concatenation) */
RPR_PATTERN_ALT, /* alternation (|) */
- RPR_PATTERN_GROUP /* group (parentheses) */
+ RPR_PATTERN_GROUP, /* group (parentheses) */
} RPRPatternNodeType;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 656c552b0a8..d7138f5141b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -684,7 +684,7 @@ typedef enum RPRNavKind
RPR_NAV_PREV_FIRST,
RPR_NAV_PREV_LAST,
RPR_NAV_NEXT_FIRST,
- RPR_NAV_NEXT_LAST
+ RPR_NAV_NEXT_LAST,
} RPRNavKind;
typedef struct RPRNavExpr
--
2.50.1 (Apple Git-155)
From 37cb8a6e9d36ac98b7cd10bb81a9a4f70720d016 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 17:27:51 +0900
Subject: [PATCH 07/11] Remove optional outer parentheses from ereport() calls
in RPR files
Per project coding style, the outer parentheses wrapping errcode/
errmsg/etc. arguments in ereport() are optional and should be omitted.
Applies to parse_rpr.c, optimizer/plan/rpr.c, and gram.y.
---
src/backend/optimizer/plan/rpr.c | 16 ++--
src/backend/parser/gram.y | 72 ++++++++--------
src/backend/parser/parse_rpr.c | 140 +++++++++++++++----------------
3 files changed, 114 insertions(+), 114 deletions(-)
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index a817eb4a63f..ed8b6c3414c 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1027,10 +1027,10 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
/* Check recursion depth limit before overflow occurs */
if (depth >= RPR_DEPTH_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("pattern nesting too deep"),
- errdetail("Pattern nesting depth %d exceeds maximum %d.",
- depth, RPR_DEPTH_MAX - 1)));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("pattern nesting too deep"),
+ errdetail("Pattern nesting depth %d exceeds maximum %d.",
+ depth, RPR_DEPTH_MAX - 1));
/* Track maximum depth */
if (depth > *maxDepth)
@@ -1120,10 +1120,10 @@ scanRPRPattern(RPRPatternNode *node, char **varNames, int *numVars,
if (*numElements > RPR_ELEMIDX_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("pattern too complex"),
- errdetail("Pattern has %d elements, maximum is %d.",
- *numElements, RPR_ELEMIDX_MAX)));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("pattern too complex"),
+ errdetail("Pattern has %d elements, maximum is %d.",
+ *numElements, RPR_ELEMIDX_MAX));
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f3cedfbbb18..aa587e6aced 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17610,10 +17610,10 @@ opt_row_pattern_initial_or_seek:
| SEEK
{
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("SEEK is not supported"),
- errhint("Use INITIAL instead."),
- parser_errposition(@1)));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("SEEK is not supported"),
+ errhint("Use INITIAL instead."),
+ parser_errposition(@1));
}
| /*EMPTY*/ { $$ = true; }
;
@@ -17740,40 +17740,40 @@ row_pattern_quantifier_opt:
$$ = (Node *) makeRPRQuantifier(0, 1, @1 + 1, @1, yyscanner);
else
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("unsupported quantifier \"%s\"", $1),
- errhint("Valid quantifiers are: *, +, ?, *?, +?, ??, {n}, {n,}, {,m}, {n,m} and their reluctant versions."),
- parser_errposition(@1)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unsupported quantifier \"%s\"", $1),
+ errhint("Valid quantifiers are: *, +, ?, *?, +?, ??, {n}, {n,}, {,m}, {n,m} and their reluctant versions."),
+ parser_errposition(@1));
}
/* RELUCTANT quantifiers (when lexer separates tokens) */
| '*' Op
{
if (strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after \"*\" quantifier"),
- errhint("Did you mean \"*?\" for reluctant quantifier?"),
- parser_errposition(@2)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after \"*\" quantifier"),
+ errhint("Did you mean \"*?\" for reluctant quantifier?"),
+ parser_errposition(@2));
$$ = (Node *) makeRPRQuantifier(0, INT_MAX, @2, @1, yyscanner);
}
| '+' Op
{
if (strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after \"+\" quantifier"),
- errhint("Did you mean \"+?\" for reluctant quantifier?"),
- parser_errposition(@2)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after \"+\" quantifier"),
+ errhint("Did you mean \"+?\" for reluctant quantifier?"),
+ parser_errposition(@2));
$$ = (Node *) makeRPRQuantifier(1, INT_MAX, @2, @1, yyscanner);
}
| Op Op
{
if (strcmp($1, "?") != 0 || strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid quantifier combination"),
- errhint("Did you mean \"??\" for reluctant quantifier?"),
- parser_errposition(@1)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid quantifier combination"),
+ errhint("Did you mean \"??\" for reluctant quantifier?"),
+ parser_errposition(@1));
$$ = (Node *) makeRPRQuantifier(0, 1, @2, @1, yyscanner);
}
/* {n}, {n,}, {,m}, {n,m} quantifiers */
@@ -17823,10 +17823,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($4, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n} to make it reluctant."),
- parser_errposition(@4)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n} to make it reluctant."),
+ parser_errposition(@4));
if ($2 <= 0 || $2 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17838,10 +17838,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($5, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
- parser_errposition(@5)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
+ parser_errposition(@5));
if ($2 < 0 || $2 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17853,10 +17853,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($5, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
- parser_errposition(@5)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
+ parser_errposition(@5));
if ($3 <= 0 || $3 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17868,10 +17868,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($6, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,m} to make it reluctant."),
- parser_errposition(@6)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,m} to make it reluctant."),
+ parser_errposition(@6));
if ($2 < 0 || $4 <= 0 || $2 >= INT_MAX || $4 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 2c6fccebd47..bba887f17ce 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -95,20 +95,20 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
/* Frame type must be "ROW" */
if (wc->frameOptions & FRAMEOPTION_GROUPS)
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use FRAME option GROUPS with row pattern recognition"),
- errhint("Use ROWS instead."),
- parser_errposition(pstate,
- windef->frameLocation >= 0 ?
- windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use FRAME option GROUPS with row pattern recognition"),
+ errhint("Use ROWS instead."),
+ parser_errposition(pstate,
+ windef->frameLocation >= 0 ?
+ windef->frameLocation : windef->location));
if (wc->frameOptions & FRAMEOPTION_RANGE)
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use FRAME option RANGE with row pattern recognition"),
- errhint("Use ROWS instead."),
- parser_errposition(pstate,
- windef->frameLocation >= 0 ?
- windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use FRAME option RANGE with row pattern recognition"),
+ errhint("Use ROWS instead."),
+ parser_errposition(pstate,
+ windef->frameLocation >= 0 ?
+ windef->frameLocation : windef->location));
/* Frame must start at current row */
if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0)
@@ -130,11 +130,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING));
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("FRAME must start at CURRENT ROW when using row pattern recognition"),
- errdetail("Current frame starts with %s.", startBound),
- errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
- parser_errposition(pstate, windef->frameLocation >= 0 ? windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("FRAME must start at CURRENT ROW when using row pattern recognition"),
+ errdetail("Current frame starts with %s.", startBound),
+ errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
+ parser_errposition(pstate, windef->frameLocation >= 0 ? windef->frameLocation : windef->location));
}
/* EXCLUDE options are not permitted */
@@ -156,11 +156,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES));
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use EXCLUDE options with row pattern recognition"),
- errdetail("Frame definition includes %s.", excludeType),
- errhint("Remove the EXCLUDE clause from the window definition."),
- parser_errposition(pstate, windef->excludeLocation >= 0 ? windef->excludeLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use EXCLUDE options with row pattern recognition"),
+ errdetail("Frame definition includes %s.", excludeType),
+ errhint("Remove the EXCLUDE clause from the window definition."),
+ parser_errposition(pstate, windef->excludeLocation >= 0 ? windef->excludeLocation : windef->location));
}
/* Transform AFTER MATCH SKIP TO clause */
@@ -220,11 +220,11 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
/* Check against RPR_VARID_MAX before adding */
if (list_length(*varNames) >= RPR_VARID_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("too many pattern variables"),
- errdetail("Maximum is %d.", RPR_VARID_MAX),
- parser_errposition(pstate,
- exprLocation((Node *) node))));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many pattern variables"),
+ errdetail("Maximum is %d.", RPR_VARID_MAX),
+ parser_errposition(pstate,
+ exprLocation((Node *) node)));
*varNames = lappend(*varNames, makeString(pstrdup(node->varName)));
}
@@ -267,10 +267,10 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
if (!found)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("DEFINE variable \"%s\" is not used in PATTERN",
- rt->name),
- parser_errposition(pstate, rt->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" is not used in PATTERN",
+ rt->name),
+ parser_errposition(pstate, rt->location));
}
}
}
@@ -347,10 +347,10 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
if (!strcmp(n, name))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("DEFINE variable \"%s\" appears more than once",
- name),
- parser_errposition(pstate, exprLocation((Node *) r))));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" appears more than once",
+ name),
+ parser_errposition(pstate, exprLocation((Node *) r)));
}
restargets = lappend(restargets, restarget);
@@ -529,14 +529,14 @@ define_walker(Node *node, void *context)
*/
if (check_functions_in_node(node, nav_volatile_func_checker, NULL))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("volatile functions are not allowed in DEFINE clause"),
- parser_errposition(ctx->pstate, exprLocation(node))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("volatile functions are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node)));
if (IsA(node, NextValueExpr))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("sequence operations are not allowed in DEFINE clause"),
- parser_errposition(ctx->pstate, exprLocation(node))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("sequence operations are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node)));
/* Var sighting feeds the column-ref rule for the enclosing nav scope. */
if (IsA(node, Var) &&
@@ -600,17 +600,17 @@ define_walker(Node *node, void *context)
/* Reject triple-or-deeper nesting */
if (ctx->nav_count > 1)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot nest row pattern navigation more than two levels deep"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot nest row pattern navigation more than two levels deep"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
if (!IsA(nav->arg, RPRNavExpr))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
inner = (RPRNavExpr *) nav->arg;
@@ -630,29 +630,29 @@ define_walker(Node *node, void *context)
}
else if (!outer_phys && inner_phys)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
else if (outer_phys && inner_phys)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("PREV and NEXT cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("PREV and NEXT cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
else
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain FIRST or LAST"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain FIRST or LAST"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
}
else if (!ctx->has_column_ref)
{
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("argument of row pattern navigation operation must include at least one column reference"),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition(ctx->pstate, nav->location));
}
/*
@@ -673,9 +673,9 @@ define_walker(Node *node, void *context)
(void) define_walker((Node *) nav->offset_arg, ctx);
if (ctx->has_column_ref)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row pattern navigation offset must be a run-time constant"),
- parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg)));
}
if (flattened && nav->compound_offset_arg != NULL)
{
@@ -683,9 +683,9 @@ define_walker(Node *node, void *context)
(void) define_walker((Node *) nav->compound_offset_arg, ctx);
if (ctx->has_column_ref)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row pattern navigation offset must be a run-time constant"),
- parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg)));
}
*ctx = saved;
--
2.50.1 (Apple Git-155)
From 8f87a3952268ff3b4cb7fd8a09aff1df013e1d18 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Mon, 4 May 2026 22:31:06 +0900
Subject: [PATCH 08/11] Add high-water mark tracking to NFA visited bitmap
reset
Track the min/max bitmapword index touched in nfa_mark_visited so the
per-advance reset memsets only the touched range, not the full
nfaVisitedNWords array.
Each visited mark performs two int16 comparisons. For single-word
bitmaps this overhead is added with no reset saving; for larger NFAs
the reset walks only the slice the DFS actually touched, which is
where the win comes from. Applied unconditionally for simplicity.
Semantics unchanged.
---
src/backend/executor/execRPR.c | 41 +++++++++++++++++++++++-----
src/backend/executor/nodeWindowAgg.c | 3 ++
src/include/nodes/execnodes.h | 4 +++
3 files changed, 41 insertions(+), 7 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 242ae9c6dcf..1e6196d6960 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -38,6 +38,21 @@
#define WORDNUM(x) ((x) / BITS_PER_BITMAPWORD)
#define BITNUM(x) ((x) % BITS_PER_BITMAPWORD)
+/*
+ * Set the visited bit for elemIdx and update the high-water marks
+ * (nfaVisitedMin/MaxWord) so that the next reset only has to clear
+ * the touched range instead of the full nfaVisitedNWords array.
+ */
+static inline void
+nfa_mark_visited(WindowAggState *winstate, int16 elemIdx)
+{
+ int16 w = WORDNUM(elemIdx);
+
+ winstate->nfaVisitedElems[w] |= ((bitmapword) 1 << BITNUM(elemIdx));
+ winstate->nfaVisitedMinWord = Min(winstate->nfaVisitedMinWord, w);
+ winstate->nfaVisitedMaxWord = Max(winstate->nfaVisitedMaxWord, w);
+}
+
/* Forward declarations - NFA state management */
static RPRNFAState *nfa_state_alloc(WindowAggState *winstate);
static void nfa_state_free(WindowAggState *winstate, RPRNFAState *state);
@@ -320,8 +335,7 @@ nfa_add_state_unique(WindowAggState *winstate, RPRNFAContext *ctx, RPRNFAState *
RPRNFAState *tail = NULL;
/* Mark VAR in visited before duplicate check to prevent DFS loops */
- winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] |=
- ((bitmapword) 1 << BITNUM(state->elemIdx));
+ nfa_mark_visited(winstate, state->elemIdx);
/* Check for duplicate and find tail */
for (s = ctx->states; s != NULL; s = s->next)
@@ -1341,8 +1355,7 @@ nfa_advance_state(WindowAggState *winstate, RPRNFAContext *ctx,
* the same VAR in a new iteration.
*/
if (!RPRElemIsVar(elem))
- winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] |=
- ((bitmapword) 1 << BITNUM(state->elemIdx));
+ nfa_mark_visited(winstate, state->elemIdx);
switch (elem->varId)
{
@@ -1394,9 +1407,23 @@ nfa_advance(WindowAggState *winstate, RPRNFAContext *ctx, int64 currentPos)
CHECK_FOR_INTERRUPTS();
savedMatchedState = ctx->matchedState;
- /* Clear visited bitmap before each state's DFS expansion */
- memset(winstate->nfaVisitedElems, 0,
- sizeof(bitmapword) * winstate->nfaVisitedNWords);
+ /*
+ * Clear visited bitmap before each state's DFS expansion. Only the
+ * range touched since the previous reset (tracked via the high-water
+ * marks updated in nfa_mark_visited) needs to be cleared; for small
+ * NFAs this is the whole array, but for large NFAs whose DFS only
+ * reaches a few elements per advance it avoids walking the full
+ * bitmap.
+ */
+ if (winstate->nfaVisitedMaxWord >= winstate->nfaVisitedMinWord)
+ {
+ memset(&winstate->nfaVisitedElems[winstate->nfaVisitedMinWord], 0,
+ sizeof(bitmapword) *
+ (winstate->nfaVisitedMaxWord -
+ winstate->nfaVisitedMinWord + 1));
+ winstate->nfaVisitedMinWord = INT16_MAX;
+ winstate->nfaVisitedMaxWord = -1;
+ }
state = states;
states = states->next;
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index af2351bccb8..d82ad8d3897 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3054,6 +3054,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
(node->rpPattern->numElements - 1) / BITS_PER_BITMAPWORD + 1;
winstate->nfaVisitedElems = palloc0(sizeof(bitmapword) *
winstate->nfaVisitedNWords);
+ /* High-water mark sentinels: no bits set yet. */
+ winstate->nfaVisitedMinWord = INT16_MAX;
+ winstate->nfaVisitedMaxWord = -1;
}
/* Set up row pattern recognition DEFINE clause */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 0cb01baa949..1fba14b892e 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2673,6 +2673,10 @@ typedef struct WindowAggState
* detection */
int nfaVisitedNWords; /* number of bitmapwords in
* nfaVisitedElems */
+ int16 nfaVisitedMinWord; /* lowest bitmapword index touched since
+ * last reset (INT16_MAX = none) */
+ int16 nfaVisitedMaxWord; /* highest bitmapword index touched since
+ * last reset (-1 = none) */
int64 nfaLastProcessedRow; /* last row processed by NFA (-1 =
* none) */
--
2.50.1 (Apple Git-155)
From 26ff8122ea01c9208b8c7c48bca330192c18d08d Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 21:00:48 +0900
Subject: [PATCH 09/11] Document DEFINE subquery rejection as intentional
over-rejection
The SubLink switch in transformSubLink() rejects every subquery in
EXPR_KIND_RPR_DEFINE with no inline rationale. Add an XXX block
recording that SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 /
R020) actually permits a nested subquery in DEFINE provided it
does not itself perform row pattern recognition and does not
reference an outer pattern variable, and that the blanket
rejection here subsumes both restrictions by making the subquery
itself unreachable.
Implementing the case distinction would mean walking the analyzed
subquery Query tree for nested RPR and walking it for ColumnRef
qualifiers matching any ancestor's p_rpr_pattern_vars; both are
doable with the existing infrastructure and are left as future
work, not blocked on any other feature. Comment-only change.
---
src/backend/parser/parse_expr.c | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 69148328719..58ebd7d24b8 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1944,6 +1944,28 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_FOR_PORTION:
err = _("cannot use subquery in FOR PORTION OF expression");
break;
+
+ /*----------
+ * XXX SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 / R020)
+ * permits a subquery nested in a DEFINE expression provided
+ * that:
+ * (a) the subquery does not itself perform row pattern
+ * recognition, and
+ * (b) the subquery does not reference a row pattern variable
+ * of the outer query.
+ *
+ * We reject all subqueries here for now. Implementing the
+ * case distinction would mean walking the analyzed subquery
+ * Query tree for nested RPR window clauses to enforce (a),
+ * and walking it for ColumnRef qualifiers matching any
+ * ancestor's p_rpr_pattern_vars to enforce (b). Both checks
+ * are doable with the existing infrastructure -- they are
+ * left as future work, not blocked on any other feature.
+ * Until then this blanket rejection is intentional
+ * over-rejection, not a standard fit; it subsumes both (a)
+ * and (b) by making the subquery itself unreachable.
+ *----------
+ */
case EXPR_KIND_RPR_DEFINE:
err = _("cannot use subquery in DEFINE expression");
break;
--
2.50.1 (Apple Git-155)
From 0c65fed46bafe16ff228fe460d15f2fbdca45e3f Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 21:37:51 +0900
Subject: [PATCH 10/11] Remove duplicate #include in nodeWindowAgg.c
#include "common/int.h" was included twice; the first occurrence
was also misplaced between two catalog/* headers, breaking the
alphabetical grouping of system header includes. Drop the
misplaced first occurrence; the second sits in the correct
alphabetical position between catalog/ and executor/ headers.
---
src/backend/executor/nodeWindowAgg.c | 1 -
1 file changed, 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d82ad8d3897..2f87449a0e0 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -36,7 +36,6 @@
#include "access/htup_details.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
-#include "common/int.h"
#include "catalog/pg_proc.h"
#include "common/int.h"
#include "executor/executor.h"
--
2.50.1 (Apple Git-155)
From bd0f11a6a80004168ff0aef512da58d0f3fe137a Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sat, 9 May 2026 13:47:49 +0900
Subject: [PATCH 11/11] Normalize SQL/RPR standard references
Make every reference cite ISO/IEC 19075-5 explicitly across RPR code
and regress tests. Prefix bare "19075-5" / "SQL standard" forms,
pin STR06 to its source (7.2.8), and where a clause is mirrored in
both Chapter 4 (FROM) and Chapter 6 (WINDOW), cite the Chapter 6
subclause first because this implementation targets Feature R020.
Document the citation policy in README.rpr.
---
src/backend/executor/README.rpr | 8 +++++++-
src/backend/executor/execRPR.c | 5 +++--
src/backend/optimizer/plan/rpr.c | 8 ++++----
src/backend/parser/parse_expr.c | 11 ++++++-----
src/backend/parser/parse_rpr.c | 2 +-
src/test/regress/expected/rpr_base.out | 6 +++---
src/test/regress/expected/rpr_explain.out | 2 +-
src/test/regress/expected/rpr_integration.out | 4 ++--
src/test/regress/expected/rpr_nfa.out | 4 ++--
src/test/regress/sql/rpr_base.sql | 6 +++---
src/test/regress/sql/rpr_explain.sql | 2 +-
src/test/regress/sql/rpr_integration.sql | 4 ++--
src/test/regress/sql/rpr_nfa.sql | 4 ++--
13 files changed, 37 insertions(+), 29 deletions(-)
diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr
index 52bcd77390c..e64efe0c7fc 100644
--- a/src/backend/executor/README.rpr
+++ b/src/backend/executor/README.rpr
@@ -38,6 +38,12 @@ What is a Flat-Array Stream NFA?
Chapter I Row Pattern Recognition Overview
============================================================================
+Normative reference: ISO/IEC 19075-5 (SQL Technical Report, Part 5: Row
+pattern recognition in SQL). Subclause numbers cited throughout this code
+base refer to that document. Where Chapters 4 (FROM clause) and 6 (WINDOW
+clause) describe parallel material, this implementation cites the Chapter 6
+subclause first because it targets Feature R020.
+
Row Pattern Recognition (hereafter RPR) is a feature introduced in SQL:2016
that matches regex-based patterns against ordered row sets.
@@ -1033,7 +1039,7 @@ match:
X-3. INITIAL vs SEEK
- Standard definition (section 6.12):
+ Standard definition (ISO/IEC 19075-5 6.12):
INITIAL: "is used to look for a match whose first row is R."
SEEK: "is used to permit a search for the first match anywhere
from R through the end of the full window frame."
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 1e6196d6960..e1caa7bb528 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -736,8 +736,9 @@ nfa_absorb_contexts(WindowAggState *winstate)
* once per row by evaluating all DEFINE expressions. NULL means no DEFINE
* clauses exist (only possible during early development/testing).
*
- * Per SQL:2016 R020, pattern variables not listed in DEFINE are implicitly
- * TRUE -- they match every row. This is checked via varId >= list_length.
+ * Per ISO/IEC 19075-5 Feature R020, pattern variables not listed in DEFINE
+ * are implicitly TRUE -- they match every row. This is checked via
+ * varId >= list_length.
*/
static bool
nfa_eval_var_match(WindowAggState *winstate, RPRPatternElement *elem,
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index ed8b6c3414c..c65681463b3 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1050,10 +1050,10 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
}
/*
- * Variable not in DEFINE clause - this is valid per SQL standard.
- * Such variables are implicitly TRUE. Add to varNames so they get
- * a varId >= defineVariableList length, which executor treats as
- * TRUE.
+ * Variable not in DEFINE clause - this is valid per ISO/IEC
+ * 19075-5 Feature R020. Such variables are implicitly TRUE. Add
+ * to varNames so they get a varId >= defineVariableList length,
+ * which executor treats as TRUE.
*/
Assert(*numVars < RPR_VARID_MAX);
varNames[(*numVars)++] = node->varName;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 58ebd7d24b8..228d3b063db 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -631,11 +631,12 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
/*----------
* Qualified references in DEFINE need a tri-classification:
*
- * pattern variable qualifier (e.g. UP.price): valid per 19075-5 4.16
- * but not yet implemented -- raise FEATURE_NOT_SUPPORTED.
+ * pattern variable qualifier (e.g. UP.price): valid per
+ * ISO/IEC 19075-5 6.15 / 4.16 but not yet implemented --
+ * raise FEATURE_NOT_SUPPORTED.
*
- * FROM-clause range variable qualifier: prohibited by 19075-5 6.5
- * -- raise SYNTAX_ERROR.
+ * FROM-clause range variable qualifier: prohibited by
+ * ISO/IEC 19075-5 6.5 -- raise SYNTAX_ERROR.
*
* any other qualifier (typo, undefined name): fall through and let
* normal column resolution produce a sensible error.
@@ -1946,7 +1947,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
break;
/*----------
- * XXX SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 / R020)
+ * XXX SQL/RPR (ISO/IEC 19075-5 6.17.4 / 4.18.4; R020 / R010)
* permits a subquery nested in a DEFINE expression provided
* that:
* (a) the subquery does not itself perform row pattern
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index bba887f17ce..d2ed6c14811 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -467,7 +467,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* (RPR's NFA may evaluate the same row's predicate multiple times
* during backtracking, so a volatile result would make matching
* non-deterministic).
- * - For each outer RPRNavExpr (per SQL 5.6.4 nesting rules):
+ * - For each outer RPRNavExpr (per ISO/IEC 19075-5 5.6.4 nesting rules):
* * arg must contain at least one column reference
* * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind
* * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...)
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 86abb96c177..cfd2645bbed 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -3065,7 +3065,7 @@ LINE 6: DEFINE A AS val > 0
^
-- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -3104,7 +3104,7 @@ ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
^
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index c4516d3c756..77079d5e8c9 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -2185,7 +2185,7 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=3.00 loops=1)
(9 rows)
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index 905bd3538de..7cbeed3347e 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1278,8 +1278,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 /
+-- 4.18.5 prohibition is not something I can judge. If this case
-- is not prohibited, the open question is whether a query that
-- does trigger the prohibition can be constructed at all.
-- Whether to prohibit this case is left to the community.
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index 4cff7cfbbd7..fe5bb324df0 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -4083,7 +4083,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
-- 7.2.2 Alternation: first alternative is preferred
@@ -4453,7 +4453,7 @@ WINDOW w AS (
3 | {B} | |
(3 rows)
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index e8c72706720..fd289d7cf67 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -2083,7 +2083,7 @@ WINDOW w AS (
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -2116,7 +2116,7 @@ WINDOW w AS (
);
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index d339a80a673..a527615849a 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -1228,7 +1228,7 @@ WINDOW w AS (
DEFINE A AS FALSE
);');
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index 29b2db2f7bb..f4267c74645 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -792,8 +792,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 /
+-- 4.18.5 prohibition is not something I can judge. If this case
-- is not prohibited, the open question is whether a query that
-- does trigger the prohibition can be constructed at all.
-- Whether to prohibit this case is left to the community.
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 29ec4a9dacb..7a5b5c41b24 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -2976,7 +2976,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
@@ -3280,7 +3280,7 @@ WINDOW w AS (
A AS 'A' = ANY(flags)
);
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] nocfbot-0001-DEFINE-non-volatile-baseline.txt (3.2K, 3-nocfbot-0001-DEFINE-non-volatile-baseline.txt)
download | inline diff:
From aba6c56a6854e8f101846aa03e42204facc02485 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Mon, 4 May 2026 19:09:34 +0900
Subject: [PATCH 01/11] Add DEFINE non-volatile baseline to rpr_integration B9
B9 in src/test/regress/sql/rpr_integration.sql today only exercises a
volatile callee (random()) inside DEFINE. Add a baseline query in the
same section that uses STABLE (to_char) and IMMUTABLE (length) callees,
which must remain accepted when the upcoming volatile-only prohibition
lands. This guards against the prohibition being broadened by accident
(e.g. contain_volatile_functions -> contain_mutable_functions); the
volatile case alone would not catch over-rejection.
Ordered baseline-first then volatile, matching other B sections.
---
src/test/regress/expected/rpr_integration.out | 26 +++++++++++++++++++
src/test/regress/sql/rpr_integration.sql | 13 ++++++++++
2 files changed, 39 insertions(+)
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index 0cc79b75601..ef6a157f45d 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1421,6 +1421,32 @@ DROP INDEX rpr_integ_id_idx;
-- pattern matching non-deterministic. When the prohibition lands,
-- this test must be replaced with an error-case test that expects
-- random() in DEFINE to be rejected.
+-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
+-- This locks the boundary of the volatile-only prohibition.
+SELECT id, val, count(*) OVER w AS cnt
+FROM rpr_integ
+WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE B AS val > PREV(val)
+ AND length('x') = 1
+ AND to_char(date '2026-01-01', 'YYYY') = '2026')
+ORDER BY id;
+ id | val | cnt
+----+-----+-----
+ 1 | 10 | 2
+ 2 | 20 | 0
+ 3 | 15 | 2
+ 4 | 25 | 0
+ 5 | 5 | 3
+ 6 | 30 | 0
+ 7 | 35 | 0
+ 8 | 20 | 3
+ 9 | 40 | 0
+ 10 | 45 | 0
+(10 rows)
+
+-- Volatile (random) is the prohibition target; today still accepted.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index 6d47728e911..d9748979d54 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -884,6 +884,19 @@ DROP INDEX rpr_integ_id_idx;
-- this test must be replaced with an error-case test that expects
-- random() in DEFINE to be rejected.
+-- Baseline: STABLE (to_char) and IMMUTABLE (length) callees are accepted.
+-- This locks the boundary of the volatile-only prohibition.
+SELECT id, val, count(*) OVER w AS cnt
+FROM rpr_integ
+WINDOW w AS (ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE B AS val > PREV(val)
+ AND length('x') = 1
+ AND to_char(date '2026-01-01', 'YYYY') = '2026')
+ORDER BY id;
+
+-- Volatile (random) is the prohibition target; today still accepted.
SELECT id, val, count(*) OVER w AS cnt
FROM rpr_integ
WINDOW w AS (ORDER BY id
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0002-Unify-DEFINE-walkers-reject-volatile.txt (69.1K, 4-nocfbot-0002-Unify-DEFINE-walkers-reject-volatile.txt)
download
[text/plain] nocfbot-0003-Empty-match-EXPLAIN-coverage.txt (19.0K, 5-nocfbot-0003-Empty-match-EXPLAIN-coverage.txt)
download | inline diff:
From 56ebb1ad70a152cb22d4adb60069b4625659421e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 14:29:14 +0900
Subject: [PATCH 03/11] Cover RPR empty-match path with EXPLAIN tests; fix
stale XXX comments
The test_728_* cases in rpr_nfa.sql claimed (via XXX) that the visited
bitmap blocks empty iterations. In fact the NFA finds the empty
matches; window aggregates just return 0 / NULL over the length-0
frame, indistinguishable from "no match" without MATCH_NUMBER().
Replace the XXX comments with the actual behavior, and add paired
EXPLAIN ANALYZE tests in rpr_explain.sql that lock in
"NFA: 3 matched (len 0/0/0.0)" as observable regression coverage.
---
src/test/regress/expected/rpr_explain.out | 200 ++++++++++++++++++++++
src/test/regress/expected/rpr_nfa.out | 29 ++--
src/test/regress/sql/rpr_explain.sql | 116 +++++++++++++
src/test/regress/sql/rpr_nfa.sql | 29 ++--
4 files changed, 340 insertions(+), 34 deletions(-)
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index 0a049d1beba..c4516d3c756 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -2102,6 +2102,206 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=0.00 loops=1)
(5 rows)
+-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql.
+-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT
+-- result alone cannot distinguish "no match" from "empty match"; the
+-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable
+-- proof that the empty matches were found.
+-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min0 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){0,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){0,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 6 peak, 20 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min1 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){1,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){1,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 5 peak, 16 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min2 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 5 peak, 16 total, 4 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
+-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty
+CREATE VIEW rpr_ev_edge_empty_match_mixed AS
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+------------------------
+ PATTERN ((a?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=4.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 8 peak, 26 total, 5 merged
+ NFA Contexts: 4 peak, 5 total, 1 pruned
+ NFA: 3 matched (len 0/2/1.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=4.00 loops=1)
+(9 rows)
+
+-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_multi AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN';
+ line
+---------------------------
+ PATTERN ((a? b?){2,3})
+(1 row)
+
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);');
+ rpr_explain_filter
+---------------------------------------------------------------------
+ WindowAgg (actual rows=3.00 loops=1)
+ Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a? b?){2,3}
+ Nav Mark Lookback: 0
+ Storage: Memory Maximum Storage: NkB
+ NFA States: 6 peak, 20 total, 0 merged
+ NFA Contexts: 2 peak, 4 total, 0 pruned
+ NFA: 3 matched (len 0/0/0.0), 0 mismatched
+ -> Function Scan on generate_series s (actual rows=3.00 loops=1)
+(9 rows)
+
-- Single row
CREATE VIEW rpr_ev_edge_single_row AS
SELECT count(*) OVER w
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index a19b26c3b94..4cff7cfbbd7 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -4395,9 +4395,9 @@ WINDOW w AS (
(3 rows)
-- (A?){0,3}: min=0, nullable inner.
--- A never matches. A? matches empty, min=0 satisfied immediately.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches but A? matches empty, satisfying min=0 immediately.
+-- NFA reports 3 length-0 matches (one per row); first_value / last_value
+-- are NULL because the window frame for an empty match has no rows.
WITH test_728_min0 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4425,9 +4425,8 @@ WINDOW w AS (
(3 rows)
-- (A?){1,3}: min=1, nullable inner.
--- A never matches. Need 1 empty iteration to satisfy min=1.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches; one empty iteration satisfies min=1.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min1 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4454,11 +4453,9 @@ WINDOW w AS (
3 | {B} | |
(3 rows)
--- (A?){2,3}: min=2, nullable inner.
--- A never matches. Need 2 empty iterations to satisfy min=2.
--- Per standard: STR06=(STRE STRE) is valid for min=2.
--- Expected: empty match for every row
--- XXX: visited bitmap blocks second empty iteration -> match failure
+-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- is valid: two empty iterations satisfy min=2.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -4486,9 +4483,8 @@ WINDOW w AS (
(3 rows)
-- (A?){2,3} mixed: some rows match A, some don't
--- Rows 1-2: A matches, greedy takes 2 -> min satisfied
--- Row 3: A doesn't match, needs 2 empty iterations for min=2
--- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3})
+-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match)
+-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match)
-- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4
WITH test_728_min2_mixed AS (
SELECT * FROM (VALUES
@@ -4559,9 +4555,8 @@ WINDOW w AS (
6 | {B} | 6 | 6
(6 rows)
--- (A? B?){2,3}: pure empty body (nothing matches)
--- XXX: All NULL: same issue as test_728_min2 (empty match at context
--- start yields UNMATCHED via startPos-1 initial advance)
+-- (A? B?){2,3}: pure empty body (nothing matches A or B).
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
(1, ARRAY['C']),
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index e123be60aea..d339a80a673 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -1178,6 +1178,122 @@ WINDOW w AS (
DEFINE A AS v = 1, B AS v = 2
);');
+-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql.
+-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT
+-- result alone cannot distinguish "no match" from "empty match"; the
+-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable
+-- proof that the empty matches were found.
+
+-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min0 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){0,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min1 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){1,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_min2 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS FALSE
+);');
+
+-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty
+CREATE VIEW rpr_ev_edge_empty_match_mixed AS
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 4) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A?){2,3})
+ DEFINE A AS v <= 2
+);');
+
+-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches
+CREATE VIEW rpr_ev_edge_empty_match_multi AS
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT rpr_explain_filter('
+EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
+SELECT count(*) OVER w
+FROM generate_series(1, 3) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN ((A? B?){2,3})
+ DEFINE A AS FALSE, B AS FALSE
+);');
+
-- Single row
CREATE VIEW rpr_ev_edge_single_row AS
SELECT count(*) OVER w
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 1d27e0dc09e..29ec4a9dacb 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -3234,9 +3234,9 @@ WINDOW w AS (
);
-- (A?){0,3}: min=0, nullable inner.
--- A never matches. A? matches empty, min=0 satisfied immediately.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches but A? matches empty, satisfying min=0 immediately.
+-- NFA reports 3 length-0 matches (one per row); first_value / last_value
+-- are NULL because the window frame for an empty match has no rows.
WITH test_728_min0 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3258,9 +3258,8 @@ WINDOW w AS (
);
-- (A?){1,3}: min=1, nullable inner.
--- A never matches. Need 1 empty iteration to satisfy min=1.
--- Per standard: empty match expected for every row.
--- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3})
+-- A never matches; one empty iteration satisfies min=1.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min1 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3281,11 +3280,9 @@ WINDOW w AS (
A AS 'A' = ANY(flags)
);
--- (A?){2,3}: min=2, nullable inner.
--- A never matches. Need 2 empty iterations to satisfy min=2.
--- Per standard: STR06=(STRE STRE) is valid for min=2.
--- Expected: empty match for every row
--- XXX: visited bitmap blocks second empty iteration -> match failure
+-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- is valid: two empty iterations satisfy min=2.
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3307,9 +3304,8 @@ WINDOW w AS (
);
-- (A?){2,3} mixed: some rows match A, some don't
--- Rows 1-2: A matches, greedy takes 2 -> min satisfied
--- Row 3: A doesn't match, needs 2 empty iterations for min=2
--- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3})
+-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match)
+-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match)
-- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4
WITH test_728_min2_mixed AS (
SELECT * FROM (VALUES
@@ -3364,9 +3360,8 @@ WINDOW w AS (
B AS 'B' = ANY(flags)
);
--- (A? B?){2,3}: pure empty body (nothing matches)
--- XXX: All NULL: same issue as test_728_min2 (empty match at context
--- start yields UNMATCHED via startPos-1 initial advance)
+-- (A? B?){2,3}: pure empty body (nothing matches A or B).
+-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
(1, ARRAY['C']),
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0004-Reclassify-DEFINE-qualifier-check.txt (11.9K, 6-nocfbot-0004-Reclassify-DEFINE-qualifier-check.txt)
download | inline diff:
From af9f50ed1522bda716d10edf932bc5aeb4b0cb37 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 11:36:53 +0900
Subject: [PATCH 04/11] Reclassify DEFINE qualifier check and reword diagnostic
to "expression"
Split the pre-check into three branches: pattern variable ->
FEATURE_NOT_SUPPORTED, range variable (via refnameNamespaceItem) ->
SYNTAX_ERROR, anything else -> fall through to normal resolution.
Reword "qualified column reference" to "qualified expression" since
the quoted token may include an indirection target on composite
types (e.g. (A.items).amount).
---
src/backend/parser/parse_expr.c | 31 +++++++---
src/test/regress/expected/rpr_base.out | 80 +++++++++++++++++++++++---
src/test/regress/sql/rpr_base.sql | 61 ++++++++++++++++++--
3 files changed, 153 insertions(+), 19 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index f145342e1fb..69148328719 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -628,11 +628,24 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
if (node != NULL)
return node;
- /*
- * Qualified column references in DEFINE are not supported. This covers
- * both FROM-clause range variables (prohibited by §6.5) and pattern
- * variable qualified names (e.g. UP.price), which are valid per §4.16
- * but not yet implemented.
+ /*----------
+ * Qualified references in DEFINE need a tri-classification:
+ *
+ * pattern variable qualifier (e.g. UP.price): valid per 19075-5 4.16
+ * but not yet implemented -- raise FEATURE_NOT_SUPPORTED.
+ *
+ * FROM-clause range variable qualifier: prohibited by 19075-5 6.5
+ * -- raise SYNTAX_ERROR.
+ *
+ * any other qualifier (typo, undefined name): fall through and let
+ * normal column resolution produce a sensible error.
+ *
+ * The quoted text reflects only the ColumnRef portion; a trailing field
+ * selection on a composite type (e.g. ".amount" in "(A.items).amount")
+ * lives in the surrounding A_Indirection node and is not included here.
+ * That can be revisited when MEASURES support adds indirection-aware
+ * traversal.
+ *----------
*/
if (pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE &&
list_length(cref->fields) != 1)
@@ -653,15 +666,17 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
if (is_pattern_var)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("pattern variable qualified column reference \"%s\" is not supported in DEFINE clause",
+ errmsg("pattern variable qualified expression \"%s\" is not supported in DEFINE clause",
NameListToString(cref->fields)),
parser_errposition(pstate, cref->location)));
- else
+ else if (refnameNamespaceItem(pstate, NULL, qualifier,
+ cref->location, NULL) != NULL)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("range variable qualified column reference \"%s\" is not allowed in DEFINE clause",
+ errmsg("range variable qualified expression \"%s\" is not allowed in DEFINE clause",
NameListToString(cref->fields)),
parser_errposition(pstate, cref->location)));
+ /* else: unknown qualifier -- fall through to normal resolution */
}
/*----------
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index a63211ff364..86abb96c177 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -3074,10 +3074,10 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS A.val > 0
);
-ERROR: pattern variable qualified column reference "a.val" is not supported in DEFINE clause
+ERROR: pattern variable qualified expression "a.val" is not supported in DEFINE clause
LINE 7: DEFINE A AS A.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "a.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "a.val" is not supported
-- PATTERN-only variable qualified name: not supported even without DEFINE entry
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3087,10 +3087,10 @@ WINDOW w AS (
PATTERN (A+ B+)
DEFINE A AS B.val > 0
);
-ERROR: pattern variable qualified column reference "b.val" is not supported in DEFINE clause
+ERROR: pattern variable qualified expression "b.val" is not supported in DEFINE clause
LINE 7: DEFINE A AS B.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- DEFINE-only variable qualified name: still a pattern variable, not a range variable
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3103,7 +3103,7 @@ WINDOW w AS (
ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
^
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3113,10 +3113,76 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS rpr_err.val > 0
);
-ERROR: range variable qualified column reference "rpr_err.val" is not allowed in DEFINE clause
+ERROR: range variable qualified expression "rpr_err.val" is not allowed in DEFINE clause
LINE 7: DEFINE A AS rpr_err.val > 0
^
--- Expected: ERROR: range variable qualified column reference "rpr_err.val" is not allowed
+-- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed
+-- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check
+-- must fall through so that normal column resolution produces a sensible error.
+SELECT COUNT(*) OVER w
+FROM rpr_err
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS nosuch.val > 0
+);
+ERROR: missing FROM-clause entry for table "nosuch"
+LINE 7: DEFINE A AS nosuch.val > 0
+ ^
+-- Expected: ERROR: missing FROM-clause entry for table "nosuch"
+-- Unqualified composite field access in DEFINE works: no qualifier means no
+-- pattern/range-var navigation, so the pre-check skips and normal resolution
+-- handles "(items).amount" via A_Indirection on the current row.
+CREATE TYPE rpr_item AS (name TEXT, amount INT);
+CREATE TEMP TABLE rpr_composite (id int, items rpr_item);
+INSERT INTO rpr_composite VALUES (1, ROW('a',5)), (2, ROW('b',15)), (3, ROW('c',25));
+SELECT id, (items).amount, COUNT(*) OVER w AS cnt
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A+)
+ DEFINE A AS (items).amount > 10
+);
+ id | amount | cnt
+----+--------+-----
+ 1 | 5 | 0
+ 2 | 15 | 2
+ 3 | 25 | 1
+(3 rows)
+
+-- Expected: rows where (items).amount > 10 form matches; counts reflect frame size
+-- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or
+-- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in
+-- the surrounding A_Indirection node and is not visible to the pre-check.
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (A.items).amount > 10
+);
+ERROR: pattern variable qualified expression "a.items" is not supported in DEFINE clause
+LINE 7: DEFINE A AS (A.items).amount > 10
+ ^
+-- Expected: ERROR: pattern variable qualified expression "a.items" is not supported
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (rpr_composite.items).amount > 10
+);
+ERROR: range variable qualified expression "rpr_composite.items" is not allowed in DEFINE clause
+LINE 7: DEFINE A AS (rpr_composite.items).amount > 10
+ ^
+-- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed
+DROP TABLE rpr_composite;
+DROP TYPE rpr_item;
-- Semantic errors
-- Undefined column in DEFINE
SELECT COUNT(*) OVER w
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index 86ed06fec68..e8c72706720 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -2092,7 +2092,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS A.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "a.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "a.val" is not supported
-- PATTERN-only variable qualified name: not supported even without DEFINE entry
SELECT COUNT(*) OVER w
@@ -2103,7 +2103,7 @@ WINDOW w AS (
PATTERN (A+ B+)
DEFINE A AS B.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- DEFINE-only variable qualified name: still a pattern variable, not a range variable
SELECT COUNT(*) OVER w
@@ -2114,7 +2114,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0, B AS B.val > 0
);
--- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
+-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
SELECT COUNT(*) OVER w
@@ -2125,7 +2125,60 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS rpr_err.val > 0
);
--- Expected: ERROR: range variable qualified column reference "rpr_err.val" is not allowed
+-- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed
+
+-- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check
+-- must fall through so that normal column resolution produces a sensible error.
+SELECT COUNT(*) OVER w
+FROM rpr_err
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS nosuch.val > 0
+);
+-- Expected: ERROR: missing FROM-clause entry for table "nosuch"
+
+-- Unqualified composite field access in DEFINE works: no qualifier means no
+-- pattern/range-var navigation, so the pre-check skips and normal resolution
+-- handles "(items).amount" via A_Indirection on the current row.
+CREATE TYPE rpr_item AS (name TEXT, amount INT);
+CREATE TEMP TABLE rpr_composite (id int, items rpr_item);
+INSERT INTO rpr_composite VALUES (1, ROW('a',5)), (2, ROW('b',15)), (3, ROW('c',25));
+SELECT id, (items).amount, COUNT(*) OVER w AS cnt
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A+)
+ DEFINE A AS (items).amount > 10
+);
+-- Expected: rows where (items).amount > 10 form matches; counts reflect frame size
+
+-- Composite type field selection (qualified forms): the ColumnRef portion ("A.items" or
+-- "rpr_composite.items") is what gets quoted; the trailing ".amount" lives in
+-- the surrounding A_Indirection node and is not visible to the pre-check.
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (A.items).amount > 10
+);
+-- Expected: ERROR: pattern variable qualified expression "a.items" is not supported
+SELECT COUNT(*) OVER w
+FROM rpr_composite
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS (rpr_composite.items).amount > 10
+);
+-- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed
+DROP TABLE rpr_composite;
+DROP TYPE rpr_item;
-- Semantic errors
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0005-Sync-DEFINE-PATTERN-comments.txt (4.4K, 7-nocfbot-0005-Sync-DEFINE-PATTERN-comments.txt)
download | inline diff:
From c3ccc037cc128f5a640a7357669db43f1dcdf6f9 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 11:44:45 +0900
Subject: [PATCH 05/11] Sync stale comments on DEFINE/PATTERN handling
validateRPRPatternVarCount() validates DEFINE names against the
PATTERN list and rejects any not used in PATTERN; the surrounding
comments said it "collected" them. Align the function header,
inline block comment, and the call-site comment. Also fix the
matching SELECT doc ("filtered during planning" -> "rejected with
an error") and the XXX note's wording.
---
doc/src/sgml/ref/select.sgml | 4 ++--
src/backend/parser/parse_rpr.c | 28 ++++++++++++++--------------
2 files changed, 16 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 5272d6c0bfa..e4708331439 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1192,8 +1192,8 @@ DEFINE <replaceable class="parameter">definition_variable_name</replaceable> AS
</synopsis>
Conversely, variables defined in the <literal>DEFINE</literal> clause
- but not used in the <literal>PATTERN</literal> clause are filtered out
- during query planning.
+ but not used in the <literal>PATTERN</literal> clause are rejected
+ with an error.
</para>
<para>
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 87411abcbe2..2c6fccebd47 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -183,11 +183,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* Recursively traverses the pattern tree, collecting unique variable names.
* Throws an error if the number of unique variables exceeds RPR_VARID_MAX.
*
- * If rpDefs is non-NULL, DEFINE variable names are also collected into
- * varNames so that transformColumnRef can distinguish pattern variable
- * qualifiers from FROM-clause range variables.
- *
- * varNames is both input and output: existing names are preserved, new ones added.
+ * If rpDefs is non-NULL, each DEFINE variable name is also validated against
+ * varNames; any DEFINE name not present in PATTERN is rejected with an error.
+ * varNames itself is not extended by this step -- it carries only PATTERN
+ * variable names, which is what transformColumnRef checks via
+ * p_rpr_pattern_vars to identify pattern variable qualifiers.
*/
static void
validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
@@ -244,10 +244,10 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
/*
- * After the top-level call, also collect DEFINE variable names that are
- * not already in the list. This is only done once at the outermost
- * recursion level, detected by rpDefs being non-NULL (recursive calls
- * pass NULL).
+ * After the top-level call, validate that every DEFINE variable name is
+ * present in the PATTERN variable list; reject names not used in PATTERN.
+ * This is only done once at the outermost recursion level, detected by
+ * rpDefs being non-NULL (recursive calls pass NULL).
*/
if (rpDefs)
{
@@ -293,9 +293,9 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
* Note: Variables not in DEFINE are evaluated as TRUE by the executor.
* Variables in DEFINE but not in PATTERN are rejected as an error.
*
- * XXX Pattern variable qualified column references in DEFINE (e.g.
- * "A.price") are not yet supported. Currently rejected by
- * transformColumnRef in parse_expr.c via the p_rpr_pattern_vars check.
+ * XXX Pattern variable qualified expressions in DEFINE (e.g. "A.price")
+ * are not yet supported. Currently rejected by transformColumnRef in
+ * parse_expr.c via the p_rpr_pattern_vars check.
*/
static List *
transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
@@ -317,8 +317,8 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
Assert(windef->rpCommonSyntax->rpDefs != NULL);
/*
- * Validate PATTERN variable count and collect all RPR variable names
- * (PATTERN + DEFINE) for use in transformColumnRef.
+ * Validate PATTERN variable count, reject DEFINE variables not used in
+ * PATTERN, and collect PATTERN variable names for transformColumnRef.
*/
validateRPRPatternVarCount(pstate, windef->rpCommonSyntax->rpPattern,
windef->rpCommonSyntax->rpDefs,
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0006-Trailing-commas-RPR-enums.txt (1.8K, 8-nocfbot-0006-Trailing-commas-RPR-enums.txt)
download | inline diff:
From 1aba5a8b605023ea2993faf98869b7be2860fc53 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 17:21:56 +0900
Subject: [PATCH 06/11] Add trailing commas to RPR enum definitions
RPRNavKind, RPRNavOffsetKind, RPRPatternNodeType lacked a trailing
comma on the last enumerator, contrary to project coding style.
---
src/include/nodes/parsenodes.h | 4 ++--
src/include/nodes/primnodes.h | 2 +-
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 455db2cec61..adefb1d5bad 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -604,7 +604,7 @@ typedef enum RPRNavOffsetKind
RPR_NAV_OFFSET_FIXED, /* resolved constant; use the offset value */
RPR_NAV_OFFSET_NEEDS_EVAL, /* non-constant offset; evaluate at executor
* init */
- RPR_NAV_OFFSET_RETAIN_ALL /* cannot determine; retain all rows (no trim) */
+ RPR_NAV_OFFSET_RETAIN_ALL, /* cannot determine; retain all rows (no trim) */
} RPRNavOffsetKind;
/*
@@ -615,7 +615,7 @@ typedef enum RPRPatternNodeType
RPR_PATTERN_VAR, /* variable reference */
RPR_PATTERN_SEQ, /* sequence (concatenation) */
RPR_PATTERN_ALT, /* alternation (|) */
- RPR_PATTERN_GROUP /* group (parentheses) */
+ RPR_PATTERN_GROUP, /* group (parentheses) */
} RPRPatternNodeType;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 656c552b0a8..d7138f5141b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -684,7 +684,7 @@ typedef enum RPRNavKind
RPR_NAV_PREV_FIRST,
RPR_NAV_PREV_LAST,
RPR_NAV_NEXT_FIRST,
- RPR_NAV_NEXT_LAST
+ RPR_NAV_NEXT_LAST,
} RPRNavKind;
typedef struct RPRNavExpr
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0007-ereport-outer-parens.txt (18.8K, 9-nocfbot-0007-ereport-outer-parens.txt)
download | inline diff:
From 37cb8a6e9d36ac98b7cd10bb81a9a4f70720d016 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 5 May 2026 17:27:51 +0900
Subject: [PATCH 07/11] Remove optional outer parentheses from ereport() calls
in RPR files
Per project coding style, the outer parentheses wrapping errcode/
errmsg/etc. arguments in ereport() are optional and should be omitted.
Applies to parse_rpr.c, optimizer/plan/rpr.c, and gram.y.
---
src/backend/optimizer/plan/rpr.c | 16 ++--
src/backend/parser/gram.y | 72 ++++++++--------
src/backend/parser/parse_rpr.c | 140 +++++++++++++++----------------
3 files changed, 114 insertions(+), 114 deletions(-)
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index a817eb4a63f..ed8b6c3414c 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1027,10 +1027,10 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
/* Check recursion depth limit before overflow occurs */
if (depth >= RPR_DEPTH_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("pattern nesting too deep"),
- errdetail("Pattern nesting depth %d exceeds maximum %d.",
- depth, RPR_DEPTH_MAX - 1)));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("pattern nesting too deep"),
+ errdetail("Pattern nesting depth %d exceeds maximum %d.",
+ depth, RPR_DEPTH_MAX - 1));
/* Track maximum depth */
if (depth > *maxDepth)
@@ -1120,10 +1120,10 @@ scanRPRPattern(RPRPatternNode *node, char **varNames, int *numVars,
if (*numElements > RPR_ELEMIDX_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("pattern too complex"),
- errdetail("Pattern has %d elements, maximum is %d.",
- *numElements, RPR_ELEMIDX_MAX)));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("pattern too complex"),
+ errdetail("Pattern has %d elements, maximum is %d.",
+ *numElements, RPR_ELEMIDX_MAX));
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f3cedfbbb18..aa587e6aced 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17610,10 +17610,10 @@ opt_row_pattern_initial_or_seek:
| SEEK
{
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("SEEK is not supported"),
- errhint("Use INITIAL instead."),
- parser_errposition(@1)));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("SEEK is not supported"),
+ errhint("Use INITIAL instead."),
+ parser_errposition(@1));
}
| /*EMPTY*/ { $$ = true; }
;
@@ -17740,40 +17740,40 @@ row_pattern_quantifier_opt:
$$ = (Node *) makeRPRQuantifier(0, 1, @1 + 1, @1, yyscanner);
else
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("unsupported quantifier \"%s\"", $1),
- errhint("Valid quantifiers are: *, +, ?, *?, +?, ??, {n}, {n,}, {,m}, {n,m} and their reluctant versions."),
- parser_errposition(@1)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unsupported quantifier \"%s\"", $1),
+ errhint("Valid quantifiers are: *, +, ?, *?, +?, ??, {n}, {n,}, {,m}, {n,m} and their reluctant versions."),
+ parser_errposition(@1));
}
/* RELUCTANT quantifiers (when lexer separates tokens) */
| '*' Op
{
if (strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after \"*\" quantifier"),
- errhint("Did you mean \"*?\" for reluctant quantifier?"),
- parser_errposition(@2)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after \"*\" quantifier"),
+ errhint("Did you mean \"*?\" for reluctant quantifier?"),
+ parser_errposition(@2));
$$ = (Node *) makeRPRQuantifier(0, INT_MAX, @2, @1, yyscanner);
}
| '+' Op
{
if (strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after \"+\" quantifier"),
- errhint("Did you mean \"+?\" for reluctant quantifier?"),
- parser_errposition(@2)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after \"+\" quantifier"),
+ errhint("Did you mean \"+?\" for reluctant quantifier?"),
+ parser_errposition(@2));
$$ = (Node *) makeRPRQuantifier(1, INT_MAX, @2, @1, yyscanner);
}
| Op Op
{
if (strcmp($1, "?") != 0 || strcmp($2, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid quantifier combination"),
- errhint("Did you mean \"??\" for reluctant quantifier?"),
- parser_errposition(@1)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid quantifier combination"),
+ errhint("Did you mean \"??\" for reluctant quantifier?"),
+ parser_errposition(@1));
$$ = (Node *) makeRPRQuantifier(0, 1, @2, @1, yyscanner);
}
/* {n}, {n,}, {,m}, {n,m} quantifiers */
@@ -17823,10 +17823,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($4, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n} to make it reluctant."),
- parser_errposition(@4)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n} to make it reluctant."),
+ parser_errposition(@4));
if ($2 <= 0 || $2 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17838,10 +17838,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($5, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
- parser_errposition(@5)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
+ parser_errposition(@5));
if ($2 < 0 || $2 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17853,10 +17853,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($5, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
- parser_errposition(@5)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,} or {,m} to make it reluctant."),
+ parser_errposition(@5));
if ($3 <= 0 || $3 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
@@ -17868,10 +17868,10 @@ row_pattern_quantifier_opt:
{
if (strcmp($6, "?") != 0)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("invalid token after range quantifier"),
- errhint("Only \"?\" is allowed after {n,m} to make it reluctant."),
- parser_errposition(@6)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid token after range quantifier"),
+ errhint("Only \"?\" is allowed after {n,m} to make it reluctant."),
+ parser_errposition(@6));
if ($2 < 0 || $4 <= 0 || $2 >= INT_MAX || $4 >= INT_MAX)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 2c6fccebd47..bba887f17ce 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -95,20 +95,20 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
/* Frame type must be "ROW" */
if (wc->frameOptions & FRAMEOPTION_GROUPS)
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use FRAME option GROUPS with row pattern recognition"),
- errhint("Use ROWS instead."),
- parser_errposition(pstate,
- windef->frameLocation >= 0 ?
- windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use FRAME option GROUPS with row pattern recognition"),
+ errhint("Use ROWS instead."),
+ parser_errposition(pstate,
+ windef->frameLocation >= 0 ?
+ windef->frameLocation : windef->location));
if (wc->frameOptions & FRAMEOPTION_RANGE)
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use FRAME option RANGE with row pattern recognition"),
- errhint("Use ROWS instead."),
- parser_errposition(pstate,
- windef->frameLocation >= 0 ?
- windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use FRAME option RANGE with row pattern recognition"),
+ errhint("Use ROWS instead."),
+ parser_errposition(pstate,
+ windef->frameLocation >= 0 ?
+ windef->frameLocation : windef->location));
/* Frame must start at current row */
if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0)
@@ -130,11 +130,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING));
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("FRAME must start at CURRENT ROW when using row pattern recognition"),
- errdetail("Current frame starts with %s.", startBound),
- errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
- parser_errposition(pstate, windef->frameLocation >= 0 ? windef->frameLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("FRAME must start at CURRENT ROW when using row pattern recognition"),
+ errdetail("Current frame starts with %s.", startBound),
+ errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
+ parser_errposition(pstate, windef->frameLocation >= 0 ? windef->frameLocation : windef->location));
}
/* EXCLUDE options are not permitted */
@@ -156,11 +156,11 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES));
ereport(ERROR,
- (errcode(ERRCODE_WINDOWING_ERROR),
- errmsg("cannot use EXCLUDE options with row pattern recognition"),
- errdetail("Frame definition includes %s.", excludeType),
- errhint("Remove the EXCLUDE clause from the window definition."),
- parser_errposition(pstate, windef->excludeLocation >= 0 ? windef->excludeLocation : windef->location)));
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use EXCLUDE options with row pattern recognition"),
+ errdetail("Frame definition includes %s.", excludeType),
+ errhint("Remove the EXCLUDE clause from the window definition."),
+ parser_errposition(pstate, windef->excludeLocation >= 0 ? windef->excludeLocation : windef->location));
}
/* Transform AFTER MATCH SKIP TO clause */
@@ -220,11 +220,11 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
/* Check against RPR_VARID_MAX before adding */
if (list_length(*varNames) >= RPR_VARID_MAX)
ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("too many pattern variables"),
- errdetail("Maximum is %d.", RPR_VARID_MAX),
- parser_errposition(pstate,
- exprLocation((Node *) node))));
+ errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many pattern variables"),
+ errdetail("Maximum is %d.", RPR_VARID_MAX),
+ parser_errposition(pstate,
+ exprLocation((Node *) node)));
*varNames = lappend(*varNames, makeString(pstrdup(node->varName)));
}
@@ -267,10 +267,10 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
if (!found)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("DEFINE variable \"%s\" is not used in PATTERN",
- rt->name),
- parser_errposition(pstate, rt->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" is not used in PATTERN",
+ rt->name),
+ parser_errposition(pstate, rt->location));
}
}
}
@@ -347,10 +347,10 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
if (!strcmp(n, name))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("DEFINE variable \"%s\" appears more than once",
- name),
- parser_errposition(pstate, exprLocation((Node *) r))));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" appears more than once",
+ name),
+ parser_errposition(pstate, exprLocation((Node *) r)));
}
restargets = lappend(restargets, restarget);
@@ -529,14 +529,14 @@ define_walker(Node *node, void *context)
*/
if (check_functions_in_node(node, nav_volatile_func_checker, NULL))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("volatile functions are not allowed in DEFINE clause"),
- parser_errposition(ctx->pstate, exprLocation(node))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("volatile functions are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node)));
if (IsA(node, NextValueExpr))
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("sequence operations are not allowed in DEFINE clause"),
- parser_errposition(ctx->pstate, exprLocation(node))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("sequence operations are not allowed in DEFINE clause"),
+ parser_errposition(ctx->pstate, exprLocation(node)));
/* Var sighting feeds the column-ref rule for the enclosing nav scope. */
if (IsA(node, Var) &&
@@ -600,17 +600,17 @@ define_walker(Node *node, void *context)
/* Reject triple-or-deeper nesting */
if (ctx->nav_count > 1)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("cannot nest row pattern navigation more than two levels deep"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot nest row pattern navigation more than two levels deep"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
if (!IsA(nav->arg, RPRNavExpr))
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("row pattern navigation operation must be a direct argument of the outer navigation"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
inner = (RPRNavExpr *) nav->arg;
@@ -630,29 +630,29 @@ define_walker(Node *node, void *context)
}
else if (!outer_phys && inner_phys)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
else if (outer_phys && inner_phys)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("PREV and NEXT cannot contain PREV or NEXT"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("PREV and NEXT cannot contain PREV or NEXT"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
else
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FIRST and LAST cannot contain FIRST or LAST"),
- errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("FIRST and LAST cannot contain FIRST or LAST"),
+ errhint("Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed."),
+ parser_errposition(ctx->pstate, nav->location));
}
else if (!ctx->has_column_ref)
{
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("argument of row pattern navigation operation must include at least one column reference"),
- parser_errposition(ctx->pstate, nav->location)));
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition(ctx->pstate, nav->location));
}
/*
@@ -673,9 +673,9 @@ define_walker(Node *node, void *context)
(void) define_walker((Node *) nav->offset_arg, ctx);
if (ctx->has_column_ref)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row pattern navigation offset must be a run-time constant"),
- parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg)));
}
if (flattened && nav->compound_offset_arg != NULL)
{
@@ -683,9 +683,9 @@ define_walker(Node *node, void *context)
(void) define_walker((Node *) nav->compound_offset_arg, ctx);
if (ctx->has_column_ref)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("row pattern navigation offset must be a run-time constant"),
- parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg))));
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("row pattern navigation offset must be a run-time constant"),
+ parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg)));
}
*ctx = saved;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0008-NFA-visited-high-water-mark.txt (5.0K, 10-nocfbot-0008-NFA-visited-high-water-mark.txt)
download | inline diff:
From 8f87a3952268ff3b4cb7fd8a09aff1df013e1d18 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Mon, 4 May 2026 22:31:06 +0900
Subject: [PATCH 08/11] Add high-water mark tracking to NFA visited bitmap
reset
Track the min/max bitmapword index touched in nfa_mark_visited so the
per-advance reset memsets only the touched range, not the full
nfaVisitedNWords array.
Each visited mark performs two int16 comparisons. For single-word
bitmaps this overhead is added with no reset saving; for larger NFAs
the reset walks only the slice the DFS actually touched, which is
where the win comes from. Applied unconditionally for simplicity.
Semantics unchanged.
---
src/backend/executor/execRPR.c | 41 +++++++++++++++++++++++-----
src/backend/executor/nodeWindowAgg.c | 3 ++
src/include/nodes/execnodes.h | 4 +++
3 files changed, 41 insertions(+), 7 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 242ae9c6dcf..1e6196d6960 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -38,6 +38,21 @@
#define WORDNUM(x) ((x) / BITS_PER_BITMAPWORD)
#define BITNUM(x) ((x) % BITS_PER_BITMAPWORD)
+/*
+ * Set the visited bit for elemIdx and update the high-water marks
+ * (nfaVisitedMin/MaxWord) so that the next reset only has to clear
+ * the touched range instead of the full nfaVisitedNWords array.
+ */
+static inline void
+nfa_mark_visited(WindowAggState *winstate, int16 elemIdx)
+{
+ int16 w = WORDNUM(elemIdx);
+
+ winstate->nfaVisitedElems[w] |= ((bitmapword) 1 << BITNUM(elemIdx));
+ winstate->nfaVisitedMinWord = Min(winstate->nfaVisitedMinWord, w);
+ winstate->nfaVisitedMaxWord = Max(winstate->nfaVisitedMaxWord, w);
+}
+
/* Forward declarations - NFA state management */
static RPRNFAState *nfa_state_alloc(WindowAggState *winstate);
static void nfa_state_free(WindowAggState *winstate, RPRNFAState *state);
@@ -320,8 +335,7 @@ nfa_add_state_unique(WindowAggState *winstate, RPRNFAContext *ctx, RPRNFAState *
RPRNFAState *tail = NULL;
/* Mark VAR in visited before duplicate check to prevent DFS loops */
- winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] |=
- ((bitmapword) 1 << BITNUM(state->elemIdx));
+ nfa_mark_visited(winstate, state->elemIdx);
/* Check for duplicate and find tail */
for (s = ctx->states; s != NULL; s = s->next)
@@ -1341,8 +1355,7 @@ nfa_advance_state(WindowAggState *winstate, RPRNFAContext *ctx,
* the same VAR in a new iteration.
*/
if (!RPRElemIsVar(elem))
- winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] |=
- ((bitmapword) 1 << BITNUM(state->elemIdx));
+ nfa_mark_visited(winstate, state->elemIdx);
switch (elem->varId)
{
@@ -1394,9 +1407,23 @@ nfa_advance(WindowAggState *winstate, RPRNFAContext *ctx, int64 currentPos)
CHECK_FOR_INTERRUPTS();
savedMatchedState = ctx->matchedState;
- /* Clear visited bitmap before each state's DFS expansion */
- memset(winstate->nfaVisitedElems, 0,
- sizeof(bitmapword) * winstate->nfaVisitedNWords);
+ /*
+ * Clear visited bitmap before each state's DFS expansion. Only the
+ * range touched since the previous reset (tracked via the high-water
+ * marks updated in nfa_mark_visited) needs to be cleared; for small
+ * NFAs this is the whole array, but for large NFAs whose DFS only
+ * reaches a few elements per advance it avoids walking the full
+ * bitmap.
+ */
+ if (winstate->nfaVisitedMaxWord >= winstate->nfaVisitedMinWord)
+ {
+ memset(&winstate->nfaVisitedElems[winstate->nfaVisitedMinWord], 0,
+ sizeof(bitmapword) *
+ (winstate->nfaVisitedMaxWord -
+ winstate->nfaVisitedMinWord + 1));
+ winstate->nfaVisitedMinWord = INT16_MAX;
+ winstate->nfaVisitedMaxWord = -1;
+ }
state = states;
states = states->next;
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index af2351bccb8..d82ad8d3897 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3054,6 +3054,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
(node->rpPattern->numElements - 1) / BITS_PER_BITMAPWORD + 1;
winstate->nfaVisitedElems = palloc0(sizeof(bitmapword) *
winstate->nfaVisitedNWords);
+ /* High-water mark sentinels: no bits set yet. */
+ winstate->nfaVisitedMinWord = INT16_MAX;
+ winstate->nfaVisitedMaxWord = -1;
}
/* Set up row pattern recognition DEFINE clause */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 0cb01baa949..1fba14b892e 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2673,6 +2673,10 @@ typedef struct WindowAggState
* detection */
int nfaVisitedNWords; /* number of bitmapwords in
* nfaVisitedElems */
+ int16 nfaVisitedMinWord; /* lowest bitmapword index touched since
+ * last reset (INT16_MAX = none) */
+ int16 nfaVisitedMaxWord; /* highest bitmapword index touched since
+ * last reset (-1 = none) */
int64 nfaLastProcessedRow; /* last row processed by NFA (-1 =
* none) */
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0009-Document-DEFINE-subquery-rejection.txt (2.6K, 11-nocfbot-0009-Document-DEFINE-subquery-rejection.txt)
download | inline diff:
From 26ff8122ea01c9208b8c7c48bca330192c18d08d Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 21:00:48 +0900
Subject: [PATCH 09/11] Document DEFINE subquery rejection as intentional
over-rejection
The SubLink switch in transformSubLink() rejects every subquery in
EXPR_KIND_RPR_DEFINE with no inline rationale. Add an XXX block
recording that SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 /
R020) actually permits a nested subquery in DEFINE provided it
does not itself perform row pattern recognition and does not
reference an outer pattern variable, and that the blanket
rejection here subsumes both restrictions by making the subquery
itself unreachable.
Implementing the case distinction would mean walking the analyzed
subquery Query tree for nested RPR and walking it for ColumnRef
qualifiers matching any ancestor's p_rpr_pattern_vars; both are
doable with the existing infrastructure and are left as future
work, not blocked on any other feature. Comment-only change.
---
src/backend/parser/parse_expr.c | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 69148328719..58ebd7d24b8 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1944,6 +1944,28 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_FOR_PORTION:
err = _("cannot use subquery in FOR PORTION OF expression");
break;
+
+ /*----------
+ * XXX SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 / R020)
+ * permits a subquery nested in a DEFINE expression provided
+ * that:
+ * (a) the subquery does not itself perform row pattern
+ * recognition, and
+ * (b) the subquery does not reference a row pattern variable
+ * of the outer query.
+ *
+ * We reject all subqueries here for now. Implementing the
+ * case distinction would mean walking the analyzed subquery
+ * Query tree for nested RPR window clauses to enforce (a),
+ * and walking it for ColumnRef qualifiers matching any
+ * ancestor's p_rpr_pattern_vars to enforce (b). Both checks
+ * are doable with the existing infrastructure -- they are
+ * left as future work, not blocked on any other feature.
+ * Until then this blanket rejection is intentional
+ * over-rejection, not a standard fit; it subsumes both (a)
+ * and (b) by making the subquery itself unreachable.
+ *----------
+ */
case EXPR_KIND_RPR_DEFINE:
err = _("cannot use subquery in DEFINE expression");
break;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0010-Remove-duplicate-include-nodeWindowAgg.txt (1.1K, 12-nocfbot-0010-Remove-duplicate-include-nodeWindowAgg.txt)
download | inline diff:
From 0c65fed46bafe16ff228fe460d15f2fbdca45e3f Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 7 May 2026 21:37:51 +0900
Subject: [PATCH 10/11] Remove duplicate #include in nodeWindowAgg.c
#include "common/int.h" was included twice; the first occurrence
was also misplaced between two catalog/* headers, breaking the
alphabetical grouping of system header includes. Drop the
misplaced first occurrence; the second sits in the correct
alphabetical position between catalog/ and executor/ headers.
---
src/backend/executor/nodeWindowAgg.c | 1 -
1 file changed, 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d82ad8d3897..2f87449a0e0 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -36,7 +36,6 @@
#include "access/htup_details.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_aggregate.h"
-#include "common/int.h"
#include "catalog/pg_proc.h"
#include "common/int.h"
#include "executor/executor.h"
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0011-Normalize-RPR-standard-refs.txt (13.6K, 13-nocfbot-0011-Normalize-RPR-standard-refs.txt)
download | inline diff:
From bd0f11a6a80004168ff0aef512da58d0f3fe137a Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sat, 9 May 2026 13:47:49 +0900
Subject: [PATCH 11/11] Normalize SQL/RPR standard references
Make every reference cite ISO/IEC 19075-5 explicitly across RPR code
and regress tests. Prefix bare "19075-5" / "SQL standard" forms,
pin STR06 to its source (7.2.8), and where a clause is mirrored in
both Chapter 4 (FROM) and Chapter 6 (WINDOW), cite the Chapter 6
subclause first because this implementation targets Feature R020.
Document the citation policy in README.rpr.
---
src/backend/executor/README.rpr | 8 +++++++-
src/backend/executor/execRPR.c | 5 +++--
src/backend/optimizer/plan/rpr.c | 8 ++++----
src/backend/parser/parse_expr.c | 11 ++++++-----
src/backend/parser/parse_rpr.c | 2 +-
src/test/regress/expected/rpr_base.out | 6 +++---
src/test/regress/expected/rpr_explain.out | 2 +-
src/test/regress/expected/rpr_integration.out | 4 ++--
src/test/regress/expected/rpr_nfa.out | 4 ++--
src/test/regress/sql/rpr_base.sql | 6 +++---
src/test/regress/sql/rpr_explain.sql | 2 +-
src/test/regress/sql/rpr_integration.sql | 4 ++--
src/test/regress/sql/rpr_nfa.sql | 4 ++--
13 files changed, 37 insertions(+), 29 deletions(-)
diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr
index 52bcd77390c..e64efe0c7fc 100644
--- a/src/backend/executor/README.rpr
+++ b/src/backend/executor/README.rpr
@@ -38,6 +38,12 @@ What is a Flat-Array Stream NFA?
Chapter I Row Pattern Recognition Overview
============================================================================
+Normative reference: ISO/IEC 19075-5 (SQL Technical Report, Part 5: Row
+pattern recognition in SQL). Subclause numbers cited throughout this code
+base refer to that document. Where Chapters 4 (FROM clause) and 6 (WINDOW
+clause) describe parallel material, this implementation cites the Chapter 6
+subclause first because it targets Feature R020.
+
Row Pattern Recognition (hereafter RPR) is a feature introduced in SQL:2016
that matches regex-based patterns against ordered row sets.
@@ -1033,7 +1039,7 @@ match:
X-3. INITIAL vs SEEK
- Standard definition (section 6.12):
+ Standard definition (ISO/IEC 19075-5 6.12):
INITIAL: "is used to look for a match whose first row is R."
SEEK: "is used to permit a search for the first match anywhere
from R through the end of the full window frame."
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 1e6196d6960..e1caa7bb528 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -736,8 +736,9 @@ nfa_absorb_contexts(WindowAggState *winstate)
* once per row by evaluating all DEFINE expressions. NULL means no DEFINE
* clauses exist (only possible during early development/testing).
*
- * Per SQL:2016 R020, pattern variables not listed in DEFINE are implicitly
- * TRUE -- they match every row. This is checked via varId >= list_length.
+ * Per ISO/IEC 19075-5 Feature R020, pattern variables not listed in DEFINE
+ * are implicitly TRUE -- they match every row. This is checked via
+ * varId >= list_length.
*/
static bool
nfa_eval_var_match(WindowAggState *winstate, RPRPatternElement *elem,
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index ed8b6c3414c..c65681463b3 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1050,10 +1050,10 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
}
/*
- * Variable not in DEFINE clause - this is valid per SQL standard.
- * Such variables are implicitly TRUE. Add to varNames so they get
- * a varId >= defineVariableList length, which executor treats as
- * TRUE.
+ * Variable not in DEFINE clause - this is valid per ISO/IEC
+ * 19075-5 Feature R020. Such variables are implicitly TRUE. Add
+ * to varNames so they get a varId >= defineVariableList length,
+ * which executor treats as TRUE.
*/
Assert(*numVars < RPR_VARID_MAX);
varNames[(*numVars)++] = node->varName;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 58ebd7d24b8..228d3b063db 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -631,11 +631,12 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
/*----------
* Qualified references in DEFINE need a tri-classification:
*
- * pattern variable qualifier (e.g. UP.price): valid per 19075-5 4.16
- * but not yet implemented -- raise FEATURE_NOT_SUPPORTED.
+ * pattern variable qualifier (e.g. UP.price): valid per
+ * ISO/IEC 19075-5 6.15 / 4.16 but not yet implemented --
+ * raise FEATURE_NOT_SUPPORTED.
*
- * FROM-clause range variable qualifier: prohibited by 19075-5 6.5
- * -- raise SYNTAX_ERROR.
+ * FROM-clause range variable qualifier: prohibited by
+ * ISO/IEC 19075-5 6.5 -- raise SYNTAX_ERROR.
*
* any other qualifier (typo, undefined name): fall through and let
* normal column resolution produce a sensible error.
@@ -1946,7 +1947,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
break;
/*----------
- * XXX SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 / R020)
+ * XXX SQL/RPR (ISO/IEC 19075-5 6.17.4 / 4.18.4; R020 / R010)
* permits a subquery nested in a DEFINE expression provided
* that:
* (a) the subquery does not itself perform row pattern
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index bba887f17ce..d2ed6c14811 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -467,7 +467,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* (RPR's NFA may evaluate the same row's predicate multiple times
* during backtracking, so a volatile result would make matching
* non-deterministic).
- * - For each outer RPRNavExpr (per SQL 5.6.4 nesting rules):
+ * - For each outer RPRNavExpr (per ISO/IEC 19075-5 5.6.4 nesting rules):
* * arg must contain at least one column reference
* * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind
* * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...)
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 86abb96c177..cfd2645bbed 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -3065,7 +3065,7 @@ LINE 6: DEFINE A AS val > 0
^
-- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -3104,7 +3104,7 @@ ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
^
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index c4516d3c756..77079d5e8c9 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -2185,7 +2185,7 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=3.00 loops=1)
(9 rows)
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index 905bd3538de..7cbeed3347e 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1278,8 +1278,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 /
+-- 4.18.5 prohibition is not something I can judge. If this case
-- is not prohibited, the open question is whether a query that
-- does trigger the prohibition can be constructed at all.
-- Whether to prohibit this case is left to the community.
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index 4cff7cfbbd7..fe5bb324df0 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -4083,7 +4083,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
-- 7.2.2 Alternation: first alternative is preferred
@@ -4453,7 +4453,7 @@ WINDOW w AS (
3 | {B} | |
(3 rows)
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index e8c72706720..fd289d7cf67 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -2083,7 +2083,7 @@ WINDOW w AS (
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -2116,7 +2116,7 @@ WINDOW w AS (
);
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index d339a80a673..a527615849a 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -1228,7 +1228,7 @@ WINDOW w AS (
DEFINE A AS FALSE
);');
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index 29b2db2f7bb..f4267c74645 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -792,8 +792,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 /
+-- 4.18.5 prohibition is not something I can judge. If this case
-- is not prohibited, the open question is whether a query that
-- does trigger the prohibition can be constructed at all.
-- Whether to prohibit this case is left to the community.
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 29ec4a9dacb..7a5b5c41b24 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -2976,7 +2976,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
@@ -3280,7 +3280,7 @@ WINDOW w AS (
A AS 'A' = ANY(flags)
);
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
--
2.50.1 (Apple Git-155)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Row pattern recognition
In-Reply-To: <CAAAe_zCL9UtiYthrSaXCmhFMK6Q3YQ6BQGgae7C9en2k=S9doA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox