From 160163510afef899824cd4632a70f25203f6142d Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 3 Sep 2026 12:54:00 +0900 Subject: [PATCH] Report an EXCLUDE clause on its own, not as a frame shape The unsupported-frame report treated EXCLUDE as one of the things a frame could be. Its DETAIL listed the two frames row pattern recognition accepts and then added "with no EXCLUDE clause", which is not another item in that list: EXCLUDE modifies a frame, it is not a frame the query could have written instead. Give it a report of its own. rpr_frame_is_supported() drops its FRAMEOPTION_EXCLUSION test, so the predicate answers only the question its name asks, and transformRPR() rejects EXCLUDE after the shape has been settled: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES ERROR: cannot use EXCLUDE with row pattern recognition The order matters for a frame that breaks both. Such a query is told about the shape first, since that is what has to be rewritten either way, and the EXCLUDE clause may well not survive the rewrite. Both reports take the position of what the query actually names -- the frame type keyword for one, the EXCLUDE keyword for the other -- and fall back to the start of the window definition, which is all a defaulted frame leaves to point at. The remaining message carries its two frames as parameters, so a translator is not asked to reproduce SQL syntax, and names the end bound "offset FOLLOWING" rather than "at least 1 FOLLOWING": the parser accepts any offset expression there, and it is calculate_frame_offsets() that rejects a non-positive value, at execution. The assertion on windef goes as well; transformRPR() dereferences windef on the next line. The set of accepted frames does not change, and neither does the error class of either report. rpr_base pins that order with a frame that breaks both rules. --- src/backend/parser/parse_rpr.c | 59 +++++++++++++------------- src/test/regress/expected/rpr_base.out | 44 ++++++++++++------- src/test/regress/sql/rpr_base.sql | 12 ++++++ 3 files changed, 70 insertions(+), 45 deletions(-) diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index 02be3370fff..62746fc33bd 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -84,41 +84,41 @@ void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist, List *groupClause) { - /* Window definition must exist when called */ - Assert(windef != NULL); - - /* - * Row Pattern Common Syntax clause exists? - */ + /* Nothing to do unless the window carries a row pattern */ if (windef->rpCommonSyntax == NULL) return; - /* - * 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)) { - int location = windef->location; - /* - * 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. + * The frame type keyword 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; + int location = windef->frameLocation >= 0 ? + windef->frameLocation : windef->location; ereport(ERROR, errcode(ERRCODE_WINDOWING_ERROR), 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."), + /*- translator: both %s are SQL window frame specifications */ + errdetail("The frame must be \"%s\" or \"%s\".", + "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING", + "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING"), + parser_errposition(pstate, location)); + } + + /* + * EXCLUDE is not part of the frame shape, so it is reported on its own, + * and from its own location. + */ + if (wc->frameOptions & FRAMEOPTION_EXCLUSION) + { + int location = windef->excludeLocation >= 0 ? + windef->excludeLocation : windef->location; + + ereport(ERROR, + errcode(ERRCODE_WINDOWING_ERROR), + errmsg("cannot use EXCLUDE with row pattern recognition"), parser_errposition(pstate, location)); } @@ -139,9 +139,12 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, * 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. + * Only ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING and ROWS BETWEEN + * CURRENT ROW AND offset FOLLOWING are supported. EXCLUDE is rejected by + * the caller, since it is not part of the shape. + * + * The offset's value is not settled until execution; + * calculate_frame_offsets() rejects a non-positive one there. */ static bool rpr_frame_is_supported(int frameOptions) @@ -153,8 +156,6 @@ rpr_frame_is_supported(int frameOptions) if ((frameOptions & (FRAMEOPTION_END_UNBOUNDED_FOLLOWING | FRAMEOPTION_END_OFFSET_FOLLOWING)) == 0) return false; - if (frameOptions & FRAMEOPTION_EXCLUSION) - return false; return true; } diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 411dad51488..37835eb7924 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -496,7 +496,7 @@ WINDOW w AS ( ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- EXCLUDE options -- EXCLUDE not permitted SELECT COUNT(*) OVER w @@ -508,10 +508,9 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: unsupported frame for row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE CURRENT ROW ^ -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 @@ -522,10 +521,9 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: unsupported frame for row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE GROUP ^ -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 @@ -536,10 +534,24 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: unsupported frame for row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE TIES ^ -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. +-- Both rules broken at once. The frame shape is settled first, so the +-- report names the shape; the EXCLUDE clause may not survive the rewrite. +SELECT COUNT(*) OVER w +FROM rpr_frame +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + EXCLUDE TIES + PATTERN (A+) + DEFINE A AS val > 0 +); +ERROR: unsupported frame for row pattern recognition +LINE 5: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ^ +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- range frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -552,7 +564,7 @@ WINDOW w AS ( ERROR: unsupported frame for row pattern recognition LINE 5: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWIN... ^ -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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- GROUPS frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -565,7 +577,7 @@ WINDOW w AS ( ERROR: unsupported frame for row pattern recognition LINE 5: GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI... ^ -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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- 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. @@ -579,7 +591,7 @@ WINDOW w AS ( 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame @@ -592,7 +604,7 @@ WINDOW w AS ( ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN 1 PRECEDING AND UNBOUNDED 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- ERROR: frame must start at current row with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -605,7 +617,7 @@ WINDOW w AS ( ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- ERROR: end before start: CURRENT ROW AND 1 PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame @@ -645,7 +657,7 @@ ORDER BY id; ERROR: unsupported frame for row pattern recognition LINE 5: ROWS BETWEEN CURRENT ROW AND CURRENT ROW ^ -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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- 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 @@ -767,7 +779,7 @@ ORDER BY id; ERROR: unsupported frame for row pattern recognition LINE 5: RANGE BETWEEN CURRENT ROW AND 10 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". -- GROUPS frame with RPR (not permitted) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -782,7 +794,7 @@ ORDER BY id; ERROR: unsupported frame for row pattern recognition LINE 5: GROUPS BETWEEN CURRENT ROW AND 1 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". DROP TABLE rpr_frame; -- ============================================================ -- PARTITION BY + FRAME Tests @@ -829,7 +841,7 @@ ORDER BY id; ERROR: unsupported frame for row pattern recognition LINE 6: RANGE BETWEEN CURRENT ROW AND 10 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. +DETAIL: The frame must be "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" or "ROWS BETWEEN CURRENT ROW AND offset FOLLOWING". 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 41c18e96e3e..2b1e8ac7ad8 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -439,6 +439,18 @@ WINDOW w AS ( DEFINE A AS val > 0 ); +-- Both rules broken at once. The frame shape is settled first, so the +-- report names the shape; the EXCLUDE clause may not survive the rewrite. +SELECT COUNT(*) OVER w +FROM rpr_frame +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + EXCLUDE TIES + PATTERN (A+) + DEFINE A AS val > 0 +); + -- range frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame