From 58fab018ae949e0e96083921125d19e841776ef4 Mon Sep 17 00:00:00 2001 From: Henson Choi 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)