nocfbot-0025-Reject-single-row-window-frame-in-row-pattern-rec.txt
text/plain
Filename: nocfbot-0025-Reject-single-row-window-frame-in-row-pattern-rec.txt
Type: text/plain
Part: 24
Message:
Re: Row pattern recognition
From 58fab018ae949e0e96083921125d19e841776ef4 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Sat, 30 May 2026 18:57:56 +0900
Subject: [PATCH 25/26] Reject single-row window frame in row pattern
recognition
The standard allows only UNBOUNDED FOLLOWING or a positive offset
FOLLOWING as the frame end for row pattern recognition. A CURRENT ROW
end, or a zero offset, reduces the frame to the single current row, which
is not a valid search space for pattern matching.
Reject the CURRENT ROW spelling in transformRPR() at parse time, and a
zero offset in calculate_frame_offsets() at run time, since the offset
need not be a constant -- it may be a parameter, expression, or subquery.
---
src/backend/executor/README.rpr | 5 ++--
src/backend/executor/nodeWindowAgg.c | 10 +++++++
src/backend/parser/parse_rpr.c | 14 +++++++++
src/test/regress/expected/rpr_base.out | 41 ++++++++++++++++++--------
src/test/regress/sql/rpr_base.sql | 26 ++++++++++++++--
5 files changed, 80 insertions(+), 16 deletions(-)
diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr
index 1d211245a5b..467cc03ecff 100644
--- a/src/backend/executor/README.rpr
+++ b/src/backend/executor/README.rpr
@@ -1122,8 +1122,9 @@ X-3. INITIAL vs SEEK
X-4. Bounded Frame Handling
With RPR, the frame mode is always ROWS and the frame start must be
- CURRENT ROW. The frame end can be either UNBOUNDED FOLLOWING or n
- FOLLOWING.
+ CURRENT ROW. The frame end must be UNBOUNDED FOLLOWING or a positive
+ offset (n >= 1) FOLLOWING; a CURRENT ROW end or a zero offset is
+ rejected, since it would reduce the frame to the single current row.
When the frame is bounded (e.g., ROWS BETWEEN CURRENT ROW AND 5
FOLLOWING), ExecRPRProcessRow receives hasLimitedFrame=true and
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index f16d01e9743..770ea2e5e1a 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -2369,6 +2369,16 @@ calculate_frame_offsets(PlanState *pstate)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PRECEDING_OR_FOLLOWING_SIZE),
errmsg("frame ending offset must not be negative")));
+
+ /*
+ * Row pattern recognition forbids a zero-length frame end;
+ * checked here so a non-constant offset (e.g. a bind parameter)
+ * is caught, not just a literal 0.
+ */
+ if (winstate->rpPattern != NULL && offset == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("frame ending offset must be positive with row pattern recognition")));
}
}
winstate->all_first = false;
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index d2ed6c14811..fa8c375f48b 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -163,6 +163,20 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
parser_errposition(pstate, windef->excludeLocation >= 0 ? windef->excludeLocation : windef->location));
}
+ /*
+ * The standard allows only UNBOUNDED FOLLOWING or a positive offset
+ * FOLLOWING as the frame end. The equivalent 0 FOLLOWING spelling is
+ * caught at runtime in calculate_frame_offsets().
+ */
+ if (wc->frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ ereport(ERROR,
+ errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("cannot use CURRENT ROW as frame end with row pattern recognition"),
+ errhint("Use UNBOUNDED FOLLOWING or a positive offset FOLLOWING."),
+ parser_errposition(pstate,
+ windef->frameLocation >= 0 ?
+ windef->frameLocation : windef->location));
+
/* Transform AFTER MATCH SKIP TO clause */
wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo;
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index cfd2645bbed..d8f805c89aa 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -542,7 +542,8 @@ ERROR: frame end cannot be UNBOUNDED PRECEDING
LINE 5: ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING
^
-- Expected: ERROR: frame end cannot be UNBOUNDED PRECEDING
--- Single row frame: CURRENT ROW AND CURRENT ROW
+-- Single row frame: CURRENT ROW AND CURRENT ROW is rejected (the standard
+-- allows only UNBOUNDED FOLLOWING or a positive offset FOLLOWING).
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_frame
WINDOW w AS (
@@ -553,17 +554,13 @@ WINDOW w AS (
DEFINE A AS val > 0
)
ORDER BY id;
- id | val | cnt
-----+-----+-----
- 1 | 10 | 1
- 2 | 10 | 1
- 3 | 10 | 1
- 4 | 20 | 1
- 5 | 20 | 1
- 6 | 30 | 1
-(6 rows)
-
--- Zero offset: CURRENT ROW AND 0 FOLLOWING (equivalent to CURRENT ROW)
+ERROR: cannot use CURRENT ROW as frame end with row pattern recognition
+LINE 5: ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+ ^
+HINT: Use UNBOUNDED FOLLOWING or a positive offset FOLLOWING.
+-- Expected: ERROR: cannot use CURRENT ROW as frame end with row pattern recognition
+-- Zero offset: CURRENT ROW AND 0 FOLLOWING denotes the same one-row frame
+-- and is likewise rejected (caught at execution time).
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_frame
WINDOW w AS (
@@ -574,6 +571,22 @@ WINDOW w AS (
DEFINE A AS val > 0
)
ORDER BY id;
+ERROR: frame ending offset must be positive with row pattern recognition
+-- Expected: ERROR: frame ending offset must be positive with row pattern recognition
+-- A non-constant frame end offset is allowed; a zero value is still rejected,
+-- this time at execution time (a literal cannot exercise that path).
+PREPARE rpr_end_offset(int8) AS
+SELECT id, val, COUNT(*) OVER w as cnt
+FROM rpr_frame
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND $1 FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A)
+ DEFINE A AS val > 0
+)
+ORDER BY id;
+EXECUTE rpr_end_offset(2);
id | val | cnt
----+-----+-----
1 | 10 | 1
@@ -584,6 +597,10 @@ ORDER BY id;
6 | 30 | 1
(6 rows)
+EXECUTE rpr_end_offset(0);
+ERROR: frame ending offset must be positive with row pattern recognition
+-- Expected: ERROR: frame ending offset must be positive with row pattern recognition
+DEALLOCATE rpr_end_offset;
-- Large offset: CURRENT ROW AND 1000 FOLLOWING
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_frame
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index fd289d7cf67..6c2365a2d20 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -445,7 +445,8 @@ WINDOW w AS (
);
-- Expected: ERROR: frame end cannot be UNBOUNDED PRECEDING
--- Single row frame: CURRENT ROW AND CURRENT ROW
+-- Single row frame: CURRENT ROW AND CURRENT ROW is rejected (the standard
+-- allows only UNBOUNDED FOLLOWING or a positive offset FOLLOWING).
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_frame
WINDOW w AS (
@@ -456,8 +457,10 @@ WINDOW w AS (
DEFINE A AS val > 0
)
ORDER BY id;
+-- Expected: ERROR: cannot use CURRENT ROW as frame end with row pattern recognition
--- Zero offset: CURRENT ROW AND 0 FOLLOWING (equivalent to CURRENT ROW)
+-- Zero offset: CURRENT ROW AND 0 FOLLOWING denotes the same one-row frame
+-- and is likewise rejected (caught at execution time).
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_frame
WINDOW w AS (
@@ -468,6 +471,25 @@ WINDOW w AS (
DEFINE A AS val > 0
)
ORDER BY id;
+-- Expected: ERROR: frame ending offset must be positive with row pattern recognition
+
+-- A non-constant frame end offset is allowed; a zero value is still rejected,
+-- this time at execution time (a literal cannot exercise that path).
+PREPARE rpr_end_offset(int8) AS
+SELECT id, val, COUNT(*) OVER w as cnt
+FROM rpr_frame
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND $1 FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (A)
+ DEFINE A AS val > 0
+)
+ORDER BY id;
+EXECUTE rpr_end_offset(2);
+EXECUTE rpr_end_offset(0);
+-- Expected: ERROR: frame ending offset must be positive with row pattern recognition
+DEALLOCATE rpr_end_offset;
-- Large offset: CURRENT ROW AND 1000 FOLLOWING
SELECT id, val, COUNT(*) OVER w as cnt
--
2.50.1 (Apple Git-155)