From 24bb56f1ce7ba6421b3213633ce59960e385884f Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sat, 29 Aug 2026 17:11:35 +0900 Subject: [PATCH] Report the frame row pattern recognition needs, not the broken option Five guards stood over the window frame: GROUPS, RANGE, a start other than CURRENT ROW, CURRENT ROW as the frame end, and EXCLUDE. Each named the option it caught, so a frame that broke more than one rule surrendered them one at a time, and a frame the query never wrote was described as though it had. A window with no frame clause carries the standard default, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That frame is real and it is wrong, but the query does not contain the word RANGE, and it breaks three of the rules at once: -- no frame clause ERROR: cannot use FRAME option RANGE with row pattern recognition -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ERROR: FRAME must start at CURRENT ROW when using row pattern recognition -- ROWS BETWEEN CURRENT ROW AND CURRENT ROW ERROR: cannot use CURRENT ROW as frame end with row pattern recognition -- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING (accepted) Row pattern recognition matches over one frame shape, so decide the shape in one predicate and state what the frame has to be. That reads the same whether the frame was written or inherited: ERROR: unsupported frame for row pattern recognition DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. The error position still prefers whatever the query does name: EXCLUDE carries a location of its own and the frame type keyword carries another, while a defaulted frame leaves only the start of the window definition. The set of accepted frames does not change. A frame end of 0 FOLLOWING is still rejected by calculate_frame_offsets() at run time, the offset being an expression whose value is not known here. rpr_base gains the case the old wording could not state: a window with no frame clause at all. --- src/backend/parser/parse_rpr.c | 127 +++++++++---------------- src/test/regress/expected/rpr_base.out | 68 +++++++------ src/test/regress/sql/rpr_base.sql | 11 +++ 3 files changed, 96 insertions(+), 110 deletions(-) diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index 6292cd0547f..c777c2c8800 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -58,6 +58,7 @@ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node, static List *transformDefineClause(ParseState *pstate, WindowDef *windef, List **targetlist); static bool define_walker(Node *node, void *context); +static bool rpr_frame_is_supported(int frameOptions); /* * transformRPR @@ -85,93 +86,35 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, if (windef->rpCommonSyntax == NULL) return; - /* Check Frame options */ - - /* 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)); - 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)); - - /* Frame must start at current row */ - if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0) - { - const char *frameType = "ROWS"; - const char *startBound = "unknown"; - - /* Determine current start bound */ - if (wc->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) - startBound = "UNBOUNDED PRECEDING"; - else if (wc->frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) - startBound = "offset PRECEDING"; - else if (wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING) - startBound = "offset FOLLOWING"; - - /* At least one valid frame start option should be set */ - Assert((wc->frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) || - (wc->frameOptions & FRAMEOPTION_START_OFFSET_PRECEDING) || - (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)); - } - - /* EXCLUDE options are not permitted */ - if ((wc->frameOptions & FRAMEOPTION_EXCLUSION) != 0) + /* + * Row pattern recognition matches over one frame shape: ROWS, starting at + * CURRENT ROW, ending at UNBOUNDED FOLLOWING or a positive offset + * FOLLOWING, with no EXCLUDE. A window that carries no frame clause of + * its own still has a frame, so the report states what the frame has to + * be rather than naming an option the query may never have written. + */ + if (!rpr_frame_is_supported(wc->frameOptions)) { - const char *excludeType = "EXCLUDE"; + int location = windef->location; - /* Determine which EXCLUDE option was used */ - if (wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) - excludeType = "EXCLUDE CURRENT ROW"; - else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP) - excludeType = "EXCLUDE GROUP"; - else if (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES) - excludeType = "EXCLUDE TIES"; - - /* At least one valid exclude option should be set */ - Assert((wc->frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) || - (wc->frameOptions & FRAMEOPTION_EXCLUDE_GROUP) || - (wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES)); + /* + * EXCLUDE has a location of its own and the frame type keyword has + * another; either beats the start of the window definition, which is + * all a defaulted frame leaves to point at. + */ + if ((wc->frameOptions & FRAMEOPTION_EXCLUSION) && + windef->excludeLocation >= 0) + location = windef->excludeLocation; + else if (windef->frameLocation >= 0) + location = windef->frameLocation; 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)); + errmsg("unsupported frame for row pattern recognition"), + errdetail("The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause."), + parser_errposition(pstate, 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)); - /* Assign AFTER MATCH SKIP TO flag */ wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo; @@ -182,6 +125,30 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, wc->rpPattern = windef->rpCommonSyntax->rpPattern; } +/* + * rpr_frame_is_supported + * Is this the frame shape row pattern recognition matches over? + * + * ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or the same with a + * positive offset FOLLOWING as the end. The offset's value is not settled + * until execution; calculate_frame_offsets() rejects a non-positive one. + */ +static bool +rpr_frame_is_supported(int frameOptions) +{ + if ((frameOptions & FRAMEOPTION_ROWS) == 0) + return false; + if ((frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0) + return false; + if ((frameOptions & (FRAMEOPTION_END_UNBOUNDED_FOLLOWING | + FRAMEOPTION_END_OFFSET_FOLLOWING)) == 0) + return false; + if (frameOptions & FRAMEOPTION_EXCLUSION) + return false; + + return true; +} + /* * validateRPRPatternVarCount * Validate that PATTERN variable count fits the varId range. diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 7688d6284b9..765291582c7 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -493,11 +493,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: FRAME must start at CURRENT ROW when using row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ^ -DETAIL: Current frame starts with UNBOUNDED PRECEDING. -HINT: Use: ROWS BETWEEN CURRENT ROW AND ... +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- EXCLUDE options -- EXCLUDE not permitted SELECT COUNT(*) OVER w @@ -509,11 +508,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 6: EXCLUDE CURRENT ROW ^ -DETAIL: Frame definition includes EXCLUDE CURRENT ROW. -HINT: Remove the EXCLUDE clause from the window definition. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- EXCLUDE GROUP not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -524,11 +522,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 6: EXCLUDE GROUP ^ -DETAIL: Frame definition includes EXCLUDE GROUP. -HINT: Remove the EXCLUDE clause from the window definition. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- EXCLUDE TIES not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -539,11 +536,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 6: EXCLUDE TIES ^ -DETAIL: Frame definition includes EXCLUDE TIES. -HINT: Remove the EXCLUDE clause from the window definition. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- range frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -553,10 +549,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use FRAME option RANGE with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWIN... ^ -HINT: Use ROWS instead. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- GROUPS frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -566,10 +562,24 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use FRAME option GROUPS with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI... ^ -HINT: Use ROWS instead. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. +-- omitting the frame clause leaves the standard default, RANGE BETWEEN +-- UNBOUNDED PRECEDING AND CURRENT ROW, which breaks three of the rules at +-- once. One report, stating what the frame has to be. +SELECT COUNT(*) OVER w +FROM rpr_frame +WINDOW w AS ( + ORDER BY id + PATTERN (A+) + DEFINE A AS val > 0 +); +ERROR: unsupported frame for row pattern recognition +LINE 3: WINDOW w AS ( + ^ +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame @@ -579,11 +589,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: FRAME must start at CURRENT ROW when using row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ^ -DETAIL: Current frame starts with offset PRECEDING. -HINT: Use: ROWS BETWEEN CURRENT ROW AND ... +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- ERROR: frame must start at current row with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -593,11 +602,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: FRAME must start at CURRENT ROW when using row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ^ -DETAIL: Current frame starts with offset FOLLOWING. -HINT: Use: ROWS BETWEEN CURRENT ROW AND ... +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- ERROR: end before start: CURRENT ROW AND 1 PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame @@ -634,10 +642,10 @@ WINDOW w AS ( DEFINE A AS val > 0 ) ORDER BY id; -ERROR: cannot use CURRENT ROW as frame end with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN CURRENT ROW AND CURRENT ROW ^ -HINT: Use UNBOUNDED FOLLOWING or a positive offset FOLLOWING. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- 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 @@ -756,10 +764,10 @@ WINDOW w AS ( DEFINE A AS val >= 0, B AS val >= 0 ) ORDER BY id; -ERROR: cannot use FRAME option RANGE with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING ^ -HINT: Use ROWS instead. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. -- GROUPS frame with RPR (not permitted) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -771,10 +779,10 @@ WINDOW w AS ( DEFINE A AS val >= 0, B AS val >= 0 ) ORDER BY id; -ERROR: cannot use FRAME option GROUPS with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 5: GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ^ -HINT: Use ROWS instead. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. DROP TABLE rpr_frame; -- ============================================================ -- PARTITION BY + FRAME Tests @@ -818,10 +826,10 @@ WINDOW w AS ( DEFINE A AS val >= 10, B AS val >= 20 ) ORDER BY id; -ERROR: cannot use FRAME option RANGE with row pattern recognition +ERROR: unsupported frame for row pattern recognition LINE 6: RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING ^ -HINT: Use ROWS instead. +DETAIL: The frame must be ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, or ROWS BETWEEN CURRENT ROW AND at least 1 FOLLOWING, with no EXCLUDE clause. DROP TABLE rpr_partition; -- ============================================================ -- PATTERN Syntax Tests diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 1059dd21262..bccee6974bf 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -459,6 +459,17 @@ WINDOW w AS ( DEFINE A AS val > 0 ); +-- omitting the frame clause leaves the standard default, RANGE BETWEEN +-- UNBOUNDED PRECEDING AND CURRENT ROW, which breaks three of the rules at +-- once. One report, stating what the frame has to be. +SELECT COUNT(*) OVER w +FROM rpr_frame +WINDOW w AS ( + ORDER BY id + PATTERN (A+) + DEFINE A AS val > 0 +); + -- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame