From 9d0c2be032b4495183e374becaa27b9f2a51da4e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 19 Feb 2026 13:53:55 +0900 Subject: [PATCH 02/10] Restrict PREV/NEXT functions to DEFINE clause only Per the SQL standard, PREV() and NEXT() are only allowed in a DEFINE clause of row pattern recognition. Previously they could be used anywhere normal functions are allowed. Add a check in ParseFuncOrColumn to reject them outside EXPR_KIND_RPR_DEFINE context. Patch by Tatsuo Ishii. --- src/backend/parser/parse_func.c | 11 +++++++++ src/test/regress/expected/rpr_base.out | 32 ++++++++++++++++++++++++++ src/test/regress/sql/rpr_base.sql | 28 ++++++++++++++++++++++ 3 files changed, 71 insertions(+) diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 357b236a818..ba9523ae3d4 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -31,6 +31,7 @@ #include "parser/parse_target.h" #include "parser/parse_type.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/syscache.h" @@ -756,6 +757,16 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, if (retset) check_srf_call_placement(pstate, last_srf, location); + /* next() and prev() are only allowed in a WINDOW DEFINE clause */ + if (fdresult == FUNCDETAIL_NORMAL && + pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE && + (funcid == F_PREV || funcid == F_NEXT)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s can only be used in a DEFINE clause", + NameListToString(funcname)), + parser_errposition(pstate, location))); + /* build the appropriate output structure */ if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE) { diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index ec67a099ee6..1d42af15980 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -1622,6 +1622,38 @@ ORDER BY id; 5 | 30 | 0 (5 rows) +-- PREV function cannot be used other than in DEFINE +SELECT PREV(id), id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > PREV(val) +) +ORDER BY id; +ERROR: prev can only be used in a DEFINE clause +LINE 1: SELECT PREV(id), id, val, COUNT(*) OVER w as cnt + ^ +-- Expected: ERROR: prev can only be used in a DEFINE clause +-- NEXT function cannot be used other than in DEFINE +SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > PREV(val) +) +ORDER BY id; +ERROR: next can only be used in a DEFINE clause +LINE 1: SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt + ^ +-- Expected: ERROR: next can only be used in a DEFINE clause DROP TABLE rpr_nav; -- ============================================================ -- SKIP TO / INITIAL Tests diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 879f5fe48a2..b752ad12873 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1219,6 +1219,34 @@ WINDOW w AS ( ) ORDER BY id; +-- PREV function cannot be used other than in DEFINE +SELECT PREV(id), id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > PREV(val) +) +ORDER BY id; +-- Expected: ERROR: prev can only be used in a DEFINE clause + +-- NEXT function cannot be used other than in DEFINE +SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt +FROM rpr_nav +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE + A AS val > 0, + B AS val > PREV(val) +) +ORDER BY id; +-- Expected: ERROR: next can only be used in a DEFINE clause + DROP TABLE rpr_nav; -- ============================================================ -- 2.50.1 (Apple Git-155)