From 5c2d5dac16b300742a9a6961cdb0d3b6e92e8e9c Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 28 Sep 2026 15:53:21 +0900 Subject: [PATCH 02/10] Tighten parse analysis of DEFINE clauses and row pattern syntax This commit brings the names allowed in a DEFINE clause in line with the standard, narrows where those rules apply, and tidies the frame and alternation reports. 1. Names in a DEFINE condition ISO/IEC 19075-5 6.5 limits the range variables in scope in a DEFINE clause to the row pattern variables, and so reserves the qualifier slot of a column reference for a pattern variable. transformColumnRef() now enforces that for every name the ref hooks leave to the query parser. - A qualified name ending in "*" is rejected by its form, before the qualifier is looked up: "whole-row reference is not allowed in DEFINE clause". A lone name that fails to resolve as a column but resolves as a range variable, a whole-row reference without the star, gets the same error. Before, a bare relation name yielded a whole-row Var, and "t.*" and "s.t.*" were reported under other messages. - A pattern variable qualifier such as A.price is still reported as not supported ahead of resolution, but only for a two-part name. A three-part name whose first part spells a pattern variable is now reported as a qualified name (42601) rather than as a pattern variable qualifier (0A000). - Every other two-part name is rejected, whatever the qualifier names. A name that resolves through p_post_columnref_hook, such as a SQL function parameter or a PL/pgSQL variable qualified by routine name or block label, used to be accepted; it now fails with "qualified expression ... is not allowed in DEFINE clause", with a hint to drop the qualifier or write "(x).field". A name a pre-columnref hook answers first is untouched, so PL/pgSQL functions using "#variable_conflict use_variable" are unaffected. - The range variable qualifier check moves after resolution, so a misspelled column in "t.colx" keeps the "Perhaps you meant" hint. - The outer-reference check also asks at which level a two-part qualifier resolved, which catches an outer range variable used as a function-call qualifier (it comes back as a FuncExpr, not a Var). Row constructors got around all of these checks: transformExpressionList() expanded "t.*" through ExpandColumnRefStar(), which binds by RTE, so ROW(t.*) was accepted. ExpandColumnRefStar() now declines inside a DEFINE condition once both ref hooks have passed on the name, and says so through a new output argument (NIL is also a valid expansion of a zero-column relation). The caller then hands the reference to transformExpr(), where the rules above apply. A name a ref hook owns, such as a PL/pgSQL record, keeps expanding as it does outside DEFINE, and "(x).*" is unaffected. 2. Where the restrictions apply They were keyed on p_expr_kind being EXPR_KIND_RPR_DEFINE, but p_expr_kind names only the innermost clause and is replaced inside an aggregate's FILTER and ORDER BY (including WITHIN GROUP). There an outer column reference, a pattern variable qualifier, a whole-row reference or a sublink got through. An outer reference made the aggregate belong to the outer query, which turned the condition into a constant, and the deparsed view failed to re-parse. Add ParseState.p_rpr_define, set while transformDefineClause() transforms the DEFINE expressions and not inherited by a sub-select's parse state, and test it in the column reference rules, the star expansion, the navigation function name rule in ParseFuncOrColumn() and the sublink check in transformSubLink(). The aggregate, window function and set-returning function rules stay keyed on the expression kind. 3. Window frame Replace the separate GROUPS, RANGE, start-bound, CURRENT ROW end and EXCLUDE checks in transformRPR(), each of which named the one option it caught, with rpr_frame_is_supported() and a single report: 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 offset FOLLOWING". The old wording also described a window with no frame clause as using "FRAME option RANGE", which the query never wrote. EXCLUDE is not part of the frame shape, so it is now reported on its own, after the shape, as "cannot use EXCLUDE with row pattern recognition", without the former DETAIL and HINT. A frame ending at CURRENT ROW with an EXCLUDE clause, which used to be reported for the EXCLUDE, now gets the shape report. Each report keeps its error position and the set of accepted frames does not change; a zero offset is still rejected at execution by calculate_frame_offsets(). Drop the Assert on windef, which is dereferenced on the next line. 4. Other diagnostics and the lexers - A navigation offset that contains a column reference is now reported with SQLSTATE 42601 instead of 0A000, matching the guard for an offset containing a navigation operation; the message is unchanged. - A glued quantifier-plus-alternation token such as "*|" followed by an operator, as in PATTERN (A *| ?), now reports "alternation operator "|" requires a pattern on both sides" at the second token instead of 'invalid token "?" after "*|" quantifier'. - psqlscan.l and pgc.l now make '|' a self character as scan.l does, both in the self class and in the operator rule's list of single characters returned as self, as scan.l's header asks. ecpg hands its grammar the same '|' token the backend grammar expects. 5. Refactoring, no behavior change row_pattern_alt and row_pattern_seq in gram.y use castNode() in place of an IsA() test that could never fail. transformDefineClause() no longer calls markTargetListOrigins(), whose result nothing reads for a DEFINE clause, or assign_expr_collations(), since assign_query_collations() reaches the DEFINE clause later anyway. A comment in transformSubLink() notes that rejecting the SubLink does not keep a DEFINE subquery unanalyzed. 6. Documentation and tests advanced.sgml lists whole-row references among what a DEFINE expression may not contain, and the SELECT reference page describes the qualifier rule, the "(p).field" spelling and the use_variable exception. Regression tests cover the new rejections, the nested FILTER and ORDER BY cases, ref-hook star expansion and the new frame, EXCLUDE and alternation reports. The rpr_integration test that relied on a bare relation name in DEFINE now expects the new error, and a new case reaches a whole-row Var through subquery pull-up instead. Author: Henson Choi Author: jian he --- doc/src/sgml/advanced.sgml | 4 +- doc/src/sgml/ref/select.sgml | 37 + src/backend/parser/gram.y | 43 +- src/backend/parser/parse_expr.c | 185 +++- src/backend/parser/parse_func.c | 2 +- src/backend/parser/parse_rpr.c | 152 ++-- src/backend/parser/parse_target.c | 83 +- src/fe_utils/psqlscan.l | 2 +- src/include/parser/parse_node.h | 13 + src/interfaces/ecpg/preproc/pgc.l | 4 +- src/test/regress/expected/rpr.out | 793 +++++++++++++++++- src/test/regress/expected/rpr_base.out | 135 ++- src/test/regress/expected/rpr_integration.out | 52 +- src/test/regress/sql/rpr.sql | 599 ++++++++++++- src/test/regress/sql/rpr_base.sql | 63 +- src/test/regress/sql/rpr_integration.sql | 33 +- 16 files changed, 1964 insertions(+), 236 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index b0f929266ff..8c0e2f384a6 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -565,8 +565,8 @@ WHERE pos < 3; it must return TRUE, FALSE or NULL. The expression may comprise column references and non-volatile functions. Window functions, aggregate functions, - set-returning functions and subqueries are not allowed. An example - of DEFINE is as follows. + set-returning functions, whole-row references and subqueries are not + allowed. An example of DEFINE is as follows. DEFINE diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 791e604b640..2ef862e54a1 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1185,6 +1185,43 @@ DEFINE definition_variable_name AS to WITH RECURSIVE. + + A column reference in the DEFINE clause must be + written without a qualifier. The SQL standard reserves the qualifier + slot for a pattern variable, so a table name or alias is rejected + there, and a pattern variable qualifier is rejected as not supported. + The column name therefore has to resolve on its own across the + whole FROM clause, and an ambiguous name is + rejected. Where the name is not unique, rename the column in + the FROM clause, for example with a column alias on + a subquery. + + + + The same applies to names that are not columns. A parameter or + variable of the routine whose body contains the query is readable from + a DEFINE condition, but only unqualified: the + PostgreSQL spellings that qualify one with + the routine name or a block label occupy the reserved slot and are + rejected there. To read a field of a composite parameter or record + variable, parenthesize it, as in (p).amount; that is + field selection on a value rather than a qualified name, so the slot + stays free. + + + + A PL/pgSQL function written with + #variable_conflict use_variable is an exception, as + it is everywhere else: that setting makes + PL/pgSQL resolve any name one of its + variables owns before the query does, so inside such a function + qualified spellings such as fn.threshold keep + working, and a variable whose name matches a pattern variable takes + A.price as well. This is the same shadowing the + setting applies to table columns; see + . + + The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a1346dcd959..cf30f443aad 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -17104,23 +17104,22 @@ row_pattern_alt: } | row_pattern_alt '|' row_pattern_seq { - RPRPatternNode *n; - RPRPatternNode *rhs = splitRPRTrailingAlt((RPRPatternNode *) $3, + RPRPatternNode *lhs = castNode(RPRPatternNode, $1); + RPRPatternNode *rhs = splitRPRTrailingAlt(castNode(RPRPatternNode, $3), yyscanner); /* If left side is already ALT, append to it */ - if (IsA($1, RPRPatternNode) && - ((RPRPatternNode *) $1)->nodeType == RPR_PATTERN_ALT) + if (lhs->nodeType == RPR_PATTERN_ALT) { - n = (RPRPatternNode *) $1; - n->children = lappend(n->children, rhs); - $$ = (Node *) n; + lhs->children = lappend(lhs->children, rhs); + $$ = (Node *) lhs; } else { - n = makeNode(RPRPatternNode); + RPRPatternNode *n = makeNode(RPRPatternNode); + n->nodeType = RPR_PATTERN_ALT; - n->children = list_make2($1, rhs); + n->children = list_make2(lhs, rhs); n->min = 1; n->max = 1; n->reluctant = false; @@ -17134,25 +17133,24 @@ row_pattern_seq: row_pattern_term { $$ = $1; } | row_pattern_seq row_pattern_term { - RPRPatternNode *n; + RPRPatternNode *seq = castNode(RPRPatternNode, $1); /* * If left side is already SEQ, append to it. A glued * quantifier's trailing_alt stays on the child term; * row_pattern_alt splits on it once the seq is complete. */ - if (IsA($1, RPRPatternNode) && - ((RPRPatternNode *) $1)->nodeType == RPR_PATTERN_SEQ) + if (seq->nodeType == RPR_PATTERN_SEQ) { - n = (RPRPatternNode *) $1; - n->children = lappend(n->children, $2); - $$ = (Node *) n; + seq->children = lappend(seq->children, $2); + $$ = (Node *) seq; } else { - n = makeNode(RPRPatternNode); + RPRPatternNode *n = makeNode(RPRPatternNode); + n->nodeType = RPR_PATTERN_SEQ; - n->children = list_make2($1, $2); + n->children = list_make2(seq, $2); n->min = 1; n->max = 1; n->reluctant = false; @@ -17326,6 +17324,17 @@ row_pattern_quantifier_opt: errmsg("invalid quantifier \"%s\"", rpr_invalid_quantifier_token($1)), errhint("Valid quantifiers are: *, +, ?, {n}, {n,}, {,m}, {n,m}, each optionally followed by \"?\" for the reluctant version."), parser_errposition(@1)); + /* + * A first token ending in "|" carries the alternation + * operator, so what comes after it has to be a pattern. + * An Op never is one, and naming the pair would describe + * a quantifier the token only half spells. + */ + if (strchr($1, '|') != NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("alternation operator \"|\" requires a pattern on both sides"), + parser_errposition(@2)); if (strcmp($1, "?") != 0) ereport(ERROR, errcode(ERRCODE_SYNTAX_ERROR), diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 4e23726e995..7dd53a1f455 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -614,17 +614,27 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) } /*---------- - * Qualified references in DEFINE need a tri-classification: + * A pattern variable qualifier (e.g. UP.price) is valid per ISO/IEC + * 19075-5 6.15 / 4.16 but not yet implemented, and has to be recognized + * here: a pattern variable names no range table entry, so leaving it to + * normal resolution would report a missing FROM-clause entry instead. * - * pattern variable qualifier (e.g. UP.price): valid per - * ISO/IEC 19075-5 6.15 / 4.16 but not yet implemented -- - * raise FEATURE_NOT_SUPPORTED. + * Like every other rule below, this one only reaches names the ref hooks + * left for the query parser to resolve. A PL that answers a name first + * keeps it: under "#variable_conflict use_variable" PL/pgSQL claims any + * name one of its variables owns, so a PL/pgSQL variable sharing a name + * with a pattern variable takes A.price, exactly as it takes a name a + * table column would otherwise own. That is what asking for + * use_variable means, and the DEFINE rules do not override it. * - * FROM-clause range variable qualifier: prohibited by - * ISO/IEC 19075-5 6.5 -- raise SYNTAX_ERROR. + * Only a two-part name has that shape. A longer name whose first part + * happens to spell a pattern variable is schema- or catalog-qualified, + * and goes the way of the other qualified forms. * - * any other qualifier (typo, undefined name): fall through and let - * normal column resolution produce a sensible error. + * The other qualified forms DEFINE disallows are diagnosed after the + * reference resolves, below. Classifying them here on the qualifier + * alone would report a misspelled column as a problem with the qualifier + * and lose the "Perhaps you meant" hint normal resolution offers. * * The quoted text reflects only the ColumnRef portion; a trailing field * selection on a composite type (e.g. ".amount" in "(A.items).amount") @@ -633,35 +643,35 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) * traversal. *---------- */ - if (pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE && + if (pstate->p_rpr_define && list_length(cref->fields) != 1) { - char *qualifier = strVal(linitial(cref->fields)); - bool is_pattern_var = false; - - foreach_node(String, pv, pstate->p_rpr_pattern_vars) + if (list_length(cref->fields) == 2) { - if (strcmp(strVal(pv), qualifier) == 0) + char *qualifier = strVal(linitial(cref->fields)); + + foreach_node(String, pv, pstate->p_rpr_pattern_vars) { - is_pattern_var = true; - break; + if (strcmp(strVal(pv), qualifier) == 0) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("pattern variable qualified expression \"%s\" is not supported in DEFINE clause", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location)); } } - if (is_pattern_var) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("pattern variable qualified expression \"%s\" is not supported in DEFINE clause", - NameListToString(cref->fields)), - parser_errposition(pstate, cref->location)); - else if (refnameNamespaceItem(pstate, NULL, qualifier, - cref->location, NULL) != NULL) + /* + * A whole-row reference is barred by its form alone: no qualifier + * makes one legal here, so resolving it first would only choose which + * rejection it gets. + */ + if (IsA(llast(cref->fields), A_Star)) ereport(ERROR, errcode(ERRCODE_SYNTAX_ERROR), - errmsg("range variable qualified expression \"%s\" is not allowed in DEFINE clause", - NameListToString(cref->fields)), + errmsg("whole-row reference is not allowed in DEFINE clause"), + errhint("A DEFINE condition may reference individual columns only."), parser_errposition(pstate, cref->location)); - /* else: unknown qualifier -- fall through to normal resolution */ } /*---------- @@ -715,8 +725,22 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) cref->location, &levels_up); if (nsitem) + { + /* + * A lone name that resolves as a range variable is a + * whole-row reference without the star, which the + * rule above has no A_Star to match on. + */ + if (pstate->p_rpr_define) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("whole-row reference is not allowed in DEFINE clause"), + errhint("A DEFINE condition may reference individual columns only."), + parser_errposition(pstate, cref->location)); + node = transformWholeRowRef(pstate, nsitem, levels_up, cref->location); + } } break; } @@ -911,6 +935,23 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) errorMissingColumn(pstate, relname, colname, cref->location); break; case CRERR_NO_RTE: + + /* + * ISO/IEC 19075-5 6.5 reserves the qualifier slot in a DEFINE + * clause for a row pattern variable, so a qualifier naming + * nothing is rejected for occupying it, the same as one that + * names something. Reporting a missing FROM-clause entry + * would point at a repair that does not exist: adding the + * relation only moves the reference to the range variable + * rejection below. + */ + if (pstate->p_rpr_define) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("qualified expression \"%s\" is not allowed in DEFINE clause", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location)); + errorMissingRTE(pstate, makeRangeVar(nspname, relname, cref->location)); break; @@ -933,20 +974,78 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) /* * Restrict column references in a row pattern DEFINE clause. node is now - * a successfully resolved reference, so reject the two forms RPR does not - * allow: a correlated reference to an outer query's column, and a - * schema/catalog-qualified reference (three or more name parts). Simple - * two-part qualifiers (pattern or range variable) are handled earlier, - * before resolution. + * a successfully resolved reference, so the qualified forms RPR does not + * allow can be rejected without mistaking a name that does not resolve at + * all for one of them: a correlated reference to an outer query's column, + * a range variable qualifier, and a schema/catalog-qualified reference. + * + * The error class follows the division the neighbouring restrictions use: + * ERRCODE_FEATURE_NOT_SUPPORTED for what the standard allows and this + * implementation does not, ERRCODE_SYNTAX_ERROR for every other rejected + * spelling, whether the standard forbids it or never gave it at all. */ - if (pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE) + if (pstate->p_rpr_define) { - if (IsA(node, Var) && ((Var *) node)->varlevelsup > 0) + ParseNamespaceItem *qual_nsitem = NULL; + int qual_levels_up = 0; + + if (list_length(cref->fields) == 2) + qual_nsitem = refnameNamespaceItem(pstate, NULL, + strVal(linitial(cref->fields)), + cref->location, + &qual_levels_up); + + /* + * Ask which level the qualifier resolved at, not merely whether it + * resolved. A two-part name whose second part is not a column is + * retried as a function call on the whole row, and that yields a + * FuncExpr rather than a Var, so the outer reference its argument + * carries is invisible to the varlevelsup test. + * + * The level counts levels searched, not levels found, so it means + * nothing unless the search succeeded. + */ + if ((IsA(node, Var) && ((Var *) node)->varlevelsup > 0) || + (qual_nsitem != NULL && qual_levels_up > 0)) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot use outer query column in DEFINE clause"), parser_errposition(pstate, cref->location)); + if (qual_nsitem != NULL) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("range variable qualified expression \"%s\" is not allowed in DEFINE clause", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location)); + + /* + * ISO/IEC 19075-5 6.5 reserves the qualifier slot for a row pattern + * variable, so a name is rejected for occupying it whatever the + * qualifier turns out to name. What is left here resolved through + * p_post_columnref_hook, which reads a two-part name as a routine's + * parameter or variable, or as a field of a composite one, and the + * hook is public enough that an extension may add readings of its + * own; the message names none of them. Selecting a field from an + * unqualified value, written "(x).f", occupies no qualifier slot and + * remains the way to reach a composite. + * + * The pre hook's readings never arrive here, so this rule is not the + * last word on a qualified name: in a PL/pgSQL function written with + * "#variable_conflict use_variable", plpgsql_pre_column_ref() answers + * fn.var and rec.field itself and returns before any of this runs, + * leaving those spellings usable there. The pragma redirects name + * resolution wholesale -- it takes names a table column would + * otherwise own too -- and DEFINE does not carve itself out of it. + */ + if (list_length(cref->fields) == 2) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("qualified expression \"%s\" is not allowed in DEFINE clause", + NameListToString(cref->fields)), + errhint("Write the name without its qualifier, or write \"(x).field\" to select a field of a composite value."), + parser_errposition(pstate, cref->location)); + if (list_length(cref->fields) >= 3) ereport(ERROR, errcode(ERRCODE_SYNTAX_ERROR), @@ -1866,9 +1965,13 @@ transformSubLink(ParseState *pstate, SubLink *sublink) * Check to see if the sublink is in an invalid place within the query. We * allow sublinks everywhere in SELECT/INSERT/UPDATE/DELETE/MERGE, but * generally not in utility statements. + * + * A row pattern DEFINE condition rejects a sublink wherever in the + * condition it stands, so its scope is asked about ahead of p_expr_kind, + * which by here may name a clause nested in the condition instead. */ err = NULL; - switch (pstate->p_expr_kind) + switch (pstate->p_rpr_define ? EXPR_KIND_RPR_DEFINE : pstate->p_expr_kind) { case EXPR_KIND_NONE: Assert(false); /* can't happen */ @@ -1965,8 +2068,16 @@ transformSubLink(ParseState *pstate, SubLink *sublink) * are doable with the existing infrastructure -- they are * left as future work, not blocked on any other feature. * Until then this blanket rejection is intentional - * over-rejection, not a standard fit; it subsumes both (a) - * and (b) by making the subquery itself unreachable. + * over-rejection, not a standard fit. + * + * It rejects the SubLink, which is not the same as keeping + * the subquery unanalyzed: a construct that analyzes its + * query before building the SubLink, as + * transformJsonArrayQueryConstructor() does, has already + * resolved names and opened relations inside it by the time + * we get here, and reports its own errors first. Whoever + * implements (a) and (b) must not read this rejection as + * proof that nothing inside a DEFINE subquery runs. *---------- */ case EXPR_KIND_RPR_DEFINE: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 772650d229a..5295271fbce 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -231,7 +231,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, * ordinary function of one of these names. */ if (!is_column && !proc_call && - pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE && + pstate->p_rpr_define && list_length(funcname) == 1) { const char *name = strVal(linitial(funcname)); diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index 6292cd0547f..ae03cb30edb 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -29,10 +29,8 @@ #include "optimizer/optimizer.h" #include "optimizer/rpr.h" #include "parser/parse_coerce.h" -#include "parser/parse_collate.h" #include "parser/parse_expr.h" #include "parser/parse_rpr.h" -#include "parser/parse_target.h" /* DEFINE clause walker context -- see define_walker for usage. */ typedef enum @@ -58,6 +56,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 @@ -76,102 +75,44 @@ void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist) { - /* 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; - /* 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) + if (!rpr_frame_is_supported(wc->frameOptions)) { - 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)); + /* + * The frame type keyword beats the start of the window definition, + * which is all a defaulted frame leaves to point at. + */ + int location = windef->frameLocation >= 0 ? + windef->frameLocation : windef->location; 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)); + errmsg("unsupported frame for row pattern recognition"), + /*- 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 options are not permitted */ - if ((wc->frameOptions & FRAMEOPTION_EXCLUSION) != 0) + /* + * 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) { - const char *excludeType = "EXCLUDE"; - - /* 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)); + int location = windef->excludeLocation >= 0 ? + windef->excludeLocation : windef->location; 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("cannot use EXCLUDE with row pattern recognition"), + 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 +123,31 @@ 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? + * + * 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) +{ + 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; + + return true; +} + /* * validateRPRPatternVarCount * Validate that PATTERN variable count fits the varId range. @@ -290,6 +256,15 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, &patternVarNames); pstate->p_rpr_pattern_vars = patternVarNames; + /* + * Open the DEFINE scope. The restrictions a DEFINE condition is under + * hold for the whole condition, so they are keyed on this rather than on + * p_expr_kind, which names only the innermost clause and is replaced by + * anything nested in the condition that has a kind of its own. It is + * closed below, once every DEFINE expression has been transformed. + */ + pstate->p_rpr_define = true; + /* * Reject any DEFINE variable whose name does not appear in PATTERN. This * cross-check only needs to run once, so it lives here in the caller @@ -396,6 +371,7 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, } list_free(vars); } + pstate->p_rpr_define = false; pstate->p_rpr_pattern_vars = NIL; /* @@ -414,12 +390,6 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, (void) define_walker((Node *) te->expr, &ctx); } - /* mark column origins */ - markTargetListOrigins(pstate, defineClause); - - /* mark all nodes in the DEFINE clause tree with collation information */ - assign_expr_collations(pstate, (Node *) defineClause); - return defineClause; } @@ -607,7 +577,7 @@ define_walker(Node *node, void *context) (void) define_walker((Node *) nav->offset_arg, ctx); if (ctx->has_column_ref) ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errcode(ERRCODE_SYNTAX_ERROR), errmsg("row pattern navigation offset must be a run-time constant"), parser_errposition(ctx->pstate, exprLocation((Node *) nav->offset_arg))); } @@ -617,7 +587,7 @@ define_walker(Node *node, void *context) (void) define_walker((Node *) nav->compound_offset_arg, ctx); if (ctx->has_column_ref) ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errcode(ERRCODE_SYNTAX_ERROR), errmsg("row pattern navigation offset must be a run-time constant"), parser_errposition(ctx->pstate, exprLocation((Node *) nav->compound_offset_arg))); } diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 728936b502e..30217f9c9fb 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -45,7 +45,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate, CoercionContext ccontext, int location); static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry); + bool make_target_entry, bool *expanded); static List *ExpandAllTables(ParseState *pstate, int location); static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, bool make_target_entry, ParseExprKind exprKind); @@ -147,11 +147,15 @@ transformTargetList(ParseState *pstate, List *targetlist, if (IsA(llast(cref->fields), A_Star)) { + bool expanded; + List *items; + /* It is something.*, expand into multiple items */ - p_target = list_concat(p_target, - ExpandColumnRefStar(pstate, - cref, - true)); + items = ExpandColumnRefStar(pstate, cref, true, + &expanded); + /* only a DEFINE condition declines, and this is not one */ + Assert(expanded); + p_target = list_concat(p_target, items); continue; } } @@ -237,11 +241,22 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(cref->fields), A_Star)) { - /* It is something.*, expand into multiple items */ - result = list_concat(result, - ExpandColumnRefStar(pstate, cref, - false)); - continue; + bool expanded; + List *items; + + /* + * It is something.*, expand into multiple items -- unless + * ExpandColumnRefStar() declines, which it does for a + * reference a row pattern DEFINE condition may not expand. + * Fall through then and let transformExpr() have the + * reference, which is where that is diagnosed. + */ + items = ExpandColumnRefStar(pstate, cref, false, &expanded); + if (expanded) + { + result = list_concat(result, items); + continue; + } } } else if (IsA(e, A_Indirection)) @@ -250,7 +265,17 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(ind->indirection), A_Star)) { - /* It is something.*, expand into multiple items */ + /* + * It is something.*, expand into multiple items. + * + * No DEFINE test is needed here, unlike the ColumnRef arm + * above. ExpandIndirectionStar() transforms the + * parenthesized argument under the same expression kind, so a + * range variable still reaches transformWholeRowRef() and is + * rejected; what survives is field selection on a value, + * "(x).*", which occupies no qualifier slot and is allowed in + * DEFINE for the same reason "(x).f" is. + */ result = list_concat(result, ExpandIndirectionStar(pstate, ind, false, exprKind)); @@ -1119,14 +1144,22 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos) * expressions). * * The referenced columns are marked as requiring SELECT access. + * + * *expanded is set false, and NIL returned, if the reference is one this + * refuses to expand; the caller is then to leave it to transformExpr(). A + * row pattern DEFINE condition is the only thing that brings that about, and + * the DEFINE branch below says why. It is a separate flag because NIL is + * also what expanding a relation with no columns of its own returns. */ static List * ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry) + bool make_target_entry, bool *expanded) { List *fields = cref->fields; int numnames = list_length(fields); + *expanded = true; + if (numnames == 1) { /* @@ -1246,6 +1279,32 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, } } + /* + * Both hooks have had their shot, so what is left is a reference to a + * FROM-clause relation, or a name that resolves to nothing at all. A + * row pattern DEFINE condition may have neither, and expanding one + * here binds it by RTE rather than by name, past the checks in + * transformColumnRef() and transformWholeRowRef(). Decline, so that + * the caller hands the whole reference to transformExpr() and it is + * diagnosed there, where every other DEFINE spelling is: a relation + * as a whole-row reference, a pattern variable as the qualifier it + * reserves, an unresolved name as the qualified name it is. + * + * A name a hook owns has returned above, which is the point of + * deciding here rather than in the caller. Withholding the expansion + * would not reject such a name -- none of those checks has anything + * to say about one the query parser never resolves -- it would leave + * transformColumnRef() to read "rec.*" as the single whole value + * "rec", which is a different condition rather than a refused one, + * and differs silently wherever a row constructor is not counting its + * entries. + */ + if (pstate->p_rpr_define) + { + *expanded = false; + return NIL; + } + /* * Throw error if no translation found. */ diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l index 76819112ba1..134143637db 100644 --- a/src/fe_utils/psqlscan.l +++ b/src/fe_utils/psqlscan.l @@ -308,7 +308,7 @@ not_equals "!=" * If you change either set, adjust the character lists appearing in the * rule for "operator"! */ -self [,()\[\].;\:\+\-\*\/\%\^\<\>\=] +self [,()\[\].;\:\|\+\-\*\/\%\^\<\>\=] op_chars [\~\!\@\#\^\&\|\`\?\+\-\*\/\%\<\>\=] operator {op_chars}+ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 2d6e1eaf404..d898d117e95 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -163,6 +163,18 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param, * p_expr_kind: kind of expression we're currently parsing, as per enum above; * EXPR_KIND_NONE when not in an expression. * + * p_rpr_define: true while we are anywhere inside a row pattern DEFINE + * expression of this query level. p_expr_kind names the innermost clause, so + * it stops saying EXPR_KIND_RPR_DEFINE as soon as a construct nested in the + * condition sets a kind of its own -- FILTER and an aggregate's ORDER BY both + * do. The DEFINE restrictions apply to the whole condition, so they test this + * instead. It is not inherited by a sub-select's ParseState, which is right: + * the restrictions stop at the query boundary. + * + * p_rpr_pattern_vars: names of the row pattern variables of the PATTERN that + * the DEFINE expression being parsed belongs to; NIL when p_rpr_define is + * false. + * * p_next_resno: next TargetEntry.resno to assign, starting from 1. * * p_multiassign_exprs: partially-processed MultiAssignRef source expressions. @@ -208,6 +220,7 @@ struct ParseState ParseNamespaceItem *p_grouping_nsitem; /* NSItem for grouping, or NULL */ List *p_windowdefs; /* raw representations of window clauses */ ParseExprKind p_expr_kind; /* what kind of expression we're parsing */ + bool p_rpr_define; /* inside a row pattern DEFINE expression? */ List *p_rpr_pattern_vars; /* Row pattern variable names */ int p_next_resno; /* next targetlist resno to assign */ List *p_multiassign_exprs; /* junk tlist entries for multiassign */ diff --git a/src/interfaces/ecpg/preproc/pgc.l b/src/interfaces/ecpg/preproc/pgc.l index 96eb076ea1f..9dad34e39bb 100644 --- a/src/interfaces/ecpg/preproc/pgc.l +++ b/src/interfaces/ecpg/preproc/pgc.l @@ -346,7 +346,7 @@ not_equals "!=" * If you change either set, adjust the character lists appearing in the * rule for "operator"! */ -self [,()\[\].;\:\+\-\*\/\%\^\<\>\=] +self [,()\[\].;\:\|\+\-\*\/\%\^\<\>\=] op_chars [\~\!\@\#\^\&\|\`\?\+\-\*\/\%\<\>\=] operator {op_chars}+ @@ -947,7 +947,7 @@ cppline {space}*#([^i][A-Za-z]*|{if}|{ifdef}|{ifndef}|{import})((\/\*[^*/]*\*+ * that the "self" rule would have. */ if (nchars == 1 && - strchr(",()[].;:+-*/%^<>=", yytext[0])) + strchr(",()[].;:|+-*/%^<>=", yytext[0])) return yytext[0]; /* diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index c4958c1b8d8..7b7524b42ef 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1327,6 +1327,419 @@ LATERAL ( ERROR: cannot use outer query column in DEFINE clause LINE 8: DEFINE A AS PREV(o.threshold, 1) > 0 ^ +-- An outer range variable is subject to the same two rules as a local one: a +-- whole-row reference is rejected as one, and a name that does not resolve +-- keeps its own diagnosis rather than being reported as a qualifier problem. +SELECT * FROM (VALUES (95)) AS o(threshold), +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (o.*) IS NOT NULL + ) +) s; +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 9: DEFINE A AS (o.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +SELECT * FROM (VALUES (95)) AS o(threshold), +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS o.threshhold > 0 + ) +) s; +ERROR: column o.threshhold does not exist +LINE 9: DEFINE A AS o.threshhold > 0 + ^ +HINT: Perhaps you meant to reference the column "o.threshold". +-- A two-part name is not always a range variable qualifier: a SQL function's +-- parameter and a PL/pgSQL variable both resolve through +-- p_post_columnref_hook. The qualifier slot is reserved all the same, so +-- these are rejected for the spelling, not for what they name. +CREATE FUNCTION rpr_sqlfn(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_sqlfn.threshold) +$$; +ERROR: qualified expression "rpr_sqlfn.threshold" is not allowed in DEFINE clause +LINE 9: DEFINE A AS price > rpr_sqlfn.threshold) + ^ +HINT: Write the name without its qualifier, or write "(x).field" to select a field of a composite value. +CREATE FUNCTION rpr_plfn(threshold int) RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + n bigint; +BEGIN + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_plfn.threshold) + ) s; + RETURN n; +END +$$; +SELECT rpr_plfn(0); +ERROR: qualified expression "rpr_plfn.threshold" is not allowed in DEFINE clause +LINE 8: DEFINE A AS price > rpr_plfn.threshold) + ^ +HINT: Write the name without its qualifier, or write "(x).field" to select a field of a composite value. +QUERY: SELECT count(*) FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_plfn.threshold) + ) s +CONTEXT: PL/pgSQL function rpr_plfn(integer) line 5 at SQL statement +DROP FUNCTION rpr_plfn(int); +-- Unqualified, the same parameter is readable. +CREATE FUNCTION rpr_sqlfn(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > threshold) +$$; +SELECT count(*) FROM rpr_sqlfn(0); + count +------- + 20 +(1 row) + +DROP FUNCTION rpr_sqlfn(int); +-- The qualifier slot is decided on the qualifier alone, before resolution, so +-- a pattern variable takes the slot even from the routine that contains the +-- query. Naming a pattern variable after the function makes rpr_pv.threshold +-- the pattern variable's, and the reservation is reported; that the function +-- has a parameter of that name, and the query has no such column, does not +-- enter into it. +CREATE FUNCTION rpr_pv(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (rpr_pv) + DEFINE rpr_pv AS price > rpr_pv.threshold) +$$; +ERROR: pattern variable qualified expression "rpr_pv.threshold" is not supported in DEFINE clause +LINE 9: DEFINE rpr_pv AS price > rpr_pv.threshold) + ^ +-- The collision is in the qualifier, not in the DEFINE variable being +-- defined: any pattern variable of that name reserves it. +CREATE FUNCTION rpr_pv(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (rpr_pv A) + DEFINE A AS price > rpr_pv.threshold) +$$; +ERROR: pattern variable qualified expression "rpr_pv.threshold" is not supported in DEFINE clause +LINE 9: DEFINE A AS price > rpr_pv.threshold) + ^ +-- A field of a composite parameter has no unqualified spelling, so it is +-- reached by parenthesizing the value: "(p).lo" selects a field rather than +-- qualifying a name, and occupies no qualifier slot. +CREATE TYPE rpr_pair AS (lo int, hi int); +CREATE FUNCTION rpr_compfn(p rpr_pair) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > p.lo) +$$; +ERROR: qualified expression "p.lo" is not allowed in DEFINE clause +LINE 9: DEFINE A AS price > p.lo) + ^ +HINT: Write the name without its qualifier, or write "(x).field" to select a field of a composite value. +CREATE FUNCTION rpr_compfn(p rpr_pair) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > (p).lo) +$$; +SELECT count(*) FROM rpr_compfn(ROW(0, 0)::rpr_pair); + count +------- + 20 +(1 row) + +DROP FUNCTION rpr_compfn(rpr_pair); +DROP TYPE rpr_pair; +-- The DEFINE rules apply to the names the ref hooks leave to the query +-- parser. Under use_variable resolution PL/pgSQL answers first and keeps +-- any name one of its variables owns, so the qualified spelling rejected +-- above is resolved by PL/pgSQL here and never reaches the rule. +CREATE FUNCTION rpr_plfn_var(threshold int) RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + n bigint; +BEGIN + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_plfn_var.threshold) + ) s; + RETURN n; +END +$$; +SELECT rpr_plfn_var(0); + rpr_plfn_var +-------------- + 20 +(1 row) + +DROP FUNCTION rpr_plfn_var(int); +-- The same applies to a pattern variable's name. Under the default +-- resolution PL/pgSQL declines the name, so the reservation is reached and +-- the collision is reported rather than resolved. +CREATE FUNCTION rpr_conflictfn_err() RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + a stock%ROWTYPE; + n bigint; +BEGIN + a.price := 95; + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > a.price) + ) s; + RETURN n; +END +$$; +SELECT rpr_conflictfn_err(); +ERROR: pattern variable qualified expression "a.price" is not supported in DEFINE clause +LINE 8: DEFINE A AS price > a.price) + ^ +QUERY: SELECT count(*) FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > a.price) + ) s +CONTEXT: PL/pgSQL function rpr_conflictfn_err() line 7 at SQL statement +DROP FUNCTION rpr_conflictfn_err(); +CREATE FUNCTION rpr_conflictfn() RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + a stock%ROWTYPE; + n bigint; +BEGIN + a.price := 95; + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > a.price) + ) s; + RETURN n; +END +$$; +SELECT rpr_conflictfn(); + rpr_conflictfn +---------------- + 20 +(1 row) + +DROP FUNCTION rpr_conflictfn(); +-- A star on a name a ref hook owns is not a reference to a FROM-clause +-- relation, so it expands in a DEFINE condition as it does anywhere else. +-- Withholding the expansion would not reject such a name; it would read +-- "rec.*" as the single whole value "rec", which is a different condition, +-- and one that differs silently wherever a row constructor is not counting +-- its entries. Each function below matches every row when the record holds +-- (1,2) and none when it does not, so a reading that is off returns the +-- wrong count rather than an error. +CREATE TYPE rpr_pair AS (a int, b int); +CREATE TEMP TABLE rpr_rec (id int); +INSERT INTO rpr_rec VALUES (1), (2), (3); +-- under the default resolution the post hook answers the name: +CREATE FUNCTION rpr_recstar(x int, y int) RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + rec rpr_pair; + n bigint; +BEGIN + rec := ROW(x, y); + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rec.*)::text = '(1,2)') + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_recstar(1, 2); + rpr_recstar +------------- + 3 +(1 row) + +SELECT rpr_recstar(1, 3); + rpr_recstar +------------- + 0 +(1 row) + +DROP FUNCTION rpr_recstar(int, int); +-- under use_variable the pre hook answers it instead: +CREATE FUNCTION rpr_recstar_var(x int, y int) RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + rec rpr_pair; + n bigint; +BEGIN + rec := ROW(x, y); + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rec.*)::text = '(1,2)') + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_recstar_var(1, 2); + rpr_recstar_var +----------------- + 3 +(1 row) + +SELECT rpr_recstar_var(1, 3); + rpr_recstar_var +----------------- + 0 +(1 row) + +DROP FUNCTION rpr_recstar_var(int, int); +-- the same spelling outside a DEFINE condition, which is what the two above +-- have to agree with: +CREATE FUNCTION rpr_recstar_plain(x int, y int) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + rec rpr_pair; +BEGIN + rec := ROW(x, y); + RETURN (SELECT ROW(rec.*)::text FROM rpr_rec LIMIT 1); +END +$$; +SELECT rpr_recstar_plain(1, 2); + rpr_recstar_plain +------------------- + (1,2) +(1 row) + +DROP FUNCTION rpr_recstar_plain(int, int); +-- A FROM-clause relation is not such a name, inside such a function or out. +CREATE FUNCTION rpr_relstar() RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + rec rpr_pair := ROW(1, 2); + n bigint; +BEGIN + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rpr_rec.*)::text IS NOT NULL) + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_relstar(); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 6: DEFINE A AS ROW(rpr_rec.*)::text IS NOT NULL) + ^ +HINT: A DEFINE condition may reference individual columns only. +QUERY: SELECT count(*) OVER w FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rpr_rec.*)::text IS NOT NULL) + LIMIT 1 +CONTEXT: PL/pgSQL function rpr_relstar() line 7 at SQL statement +DROP FUNCTION rpr_relstar(); +DROP TABLE rpr_rec; +DROP TYPE rpr_pair; +-- An outer range variable used as a function-call qualifier reaches DEFINE as +-- a FuncExpr rather than a Var, so the level the qualifier resolved at, not +-- the shape of the resulting node, is what identifies the outer reference. +CREATE TABLE rpr_outer (threshold int); +INSERT INTO rpr_outer VALUES (95); +CREATE FUNCTION rpr_rowfn(rpr_outer) RETURNS int LANGUAGE sql AS 'SELECT 1'; +SELECT * FROM rpr_outer AS o, +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS o.rpr_rowfn > 0 + ) +) s; +ERROR: cannot use outer query column in DEFINE clause +LINE 9: DEFINE A AS o.rpr_rowfn > 0 + ^ +DROP FUNCTION rpr_rowfn(rpr_outer); +DROP TABLE rpr_outer; -- DEFINE rejects a schema-qualified column reference (three or more name -- parts) once it resolves; the qualified form itself is not allowed. (stock -- is a temp table, so it is qualified with pg_temp here.) @@ -1351,12 +1764,13 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS (pg_temp.stock.*) IS NOT NULL ); -ERROR: qualified expression "pg_temp.stock.*" is not allowed in DEFINE clause +ERROR: whole-row reference is not allowed in DEFINE clause LINE 7: DEFINE A AS (pg_temp.stock.*) IS NOT NULL ^ --- A two-part table-qualified whole-row reference is rejected as well, through --- a separate range-variable check (a bare relation name is instead accepted --- as a whole-row Var). +HINT: A DEFINE condition may reference individual columns only. +-- A two-part table-qualified whole-row reference is rejected as well, and by +-- the whole-row check rather than by a qualifier rule: the error names the +-- whole-row reference, not the qualifier. -- 2-part (table.*): SELECT price FROM stock WINDOW w AS ( @@ -1366,9 +1780,378 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS (stock.*) IS NOT NULL ); -ERROR: range variable qualified expression "stock.*" is not allowed in DEFINE clause +ERROR: whole-row reference is not allowed in DEFINE clause LINE 7: DEFINE A AS (stock.*) IS NOT NULL ^ +HINT: A DEFINE condition may reference individual columns only. +-- The form decides before the qualifier is looked up, so a misspelled table +-- name is reported as the whole-row reference it is written as, not as a +-- missing FROM-clause entry: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stok.*) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS (stok.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- and the same through a row constructor: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(stok.*) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS ROW(stok.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- A row constructor reaches the same references through +-- transformExpressionList(), whose star expansion binds them by RTE into +-- individual column Vars, past every check. DEFINE skips it. +-- ROW(schema.table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(pg_temp.stock.*) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS ROW(pg_temp.stock.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- ROW(table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(stock.*) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS ROW(stock.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- the ROW keyword is optional, so the bare constructor needs the same +-- treatment: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stock.*, 1) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS (stock.*, 1) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- redundant parentheses are not a way around it: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW((stock.*)) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: DEFINE A AS ROW((stock.*)) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +-- a pattern variable qualifier is a separate class of rejection: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(A.*) IS NOT NULL +); +ERROR: pattern variable qualified expression "a.*" is not supported in DEFINE clause +LINE 7: DEFINE A AS ROW(A.*) IS NOT NULL + ^ +-- The plain two-part form is the one the standard writes its DEFINE examples +-- with, and it is decided on the qualifier alone, before resolution. +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS A.price > 100 +); +ERROR: pattern variable qualified expression "a.price" is not supported in DEFINE clause +LINE 7: DEFINE A AS A.price > 100 + ^ +-- Deciding on the qualifier alone means a pattern variable takes a name a +-- range variable would otherwise answer to: the rejection names the pattern +-- variable, not the alias, even though "a" is a live alias here. +SELECT price FROM stock AS a +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS a.price > 100 +); +ERROR: pattern variable qualified expression "a.price" is not supported in DEFINE clause +LINE 7: DEFINE A AS a.price > 100 + ^ +-- Each rejection above classifies the reference only after it resolves, so a +-- misspelled column keeps the diagnosis and the suggestion it gets anywhere +-- else. Firing on the qualifier alone would report a range variable problem +-- before the rest of the name was looked at. +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS stock.pric > 0 +); +ERROR: column stock.pric does not exist +LINE 7: DEFINE A AS stock.pric > 0 + ^ +HINT: Perhaps you meant to reference the column "stock.price". +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS pg_temp.stock.pric > 0 +); +ERROR: column stock.pric does not exist +LINE 7: DEFINE A AS pg_temp.stock.pric > 0 + ^ +HINT: Perhaps you meant to reference the column "stock.price". +-- the same typo outside a DEFINE clause, for comparison: +SELECT price FROM stock WHERE stock.pric > 0; +ERROR: column stock.pric does not exist +LINE 1: SELECT price FROM stock WHERE stock.pric > 0; + ^ +HINT: Perhaps you meant to reference the column "stock.price". +-- Retrying an unresolved column as a function call on the whole row builds a +-- whole-row reference the query does not contain. That must not be reported +-- as one, and must not let the reference through either: rpr_tag(rpr_stock) +-- below resolves, so the retry succeeds and the result is rejected by the +-- qualifier rules rather than by the whole-row check. +CREATE FUNCTION rpr_tag(rpr_stock) RETURNS int + LANGUAGE sql IMMUTABLE AS $$SELECT 1$$; +SELECT price FROM rpr_stock +WINDOW w AS ( + PARTITION BY part_id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS rpr_stock.rpr_tag > 0 +); +ERROR: range variable qualified expression "rpr_stock.rpr_tag" is not allowed in DEFINE clause +LINE 7: DEFINE A AS rpr_stock.rpr_tag > 0 + ^ +SELECT price FROM rpr_stock +WINDOW w AS ( + PARTITION BY part_id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS public.rpr_stock.rpr_tag > 0 +); +ERROR: qualified expression "public.rpr_stock.rpr_tag" is not allowed in DEFINE clause +LINE 7: DEFINE A AS public.rpr_stock.rpr_tag > 0 + ^ +DROP FUNCTION rpr_tag(rpr_stock); +-- A JOIN USING alias has no whole-row Var of its own, so the same retry +-- expands it to a row constructor instead. The retry carries no star, so +-- DEFINE lets it through to that arm. +CREATE TEMP TABLE rpr_j_l (x int, y int); +CREATE TEMP TABLE rpr_j_r (x int, z int); +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS j.yy > 0 +); +ERROR: column j.yy does not exist +LINE 6: DEFINE A AS j.yy > 0 + ^ +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS j.y > 0 +); +ERROR: range variable qualified expression "j.y" is not allowed in DEFINE clause +LINE 6: DEFINE A AS j.y > 0 + ^ +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS (j.*) IS NOT NULL +); +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 6: DEFINE A AS (j.*) IS NOT NULL + ^ +HINT: A DEFINE condition may reference individual columns only. +DROP TABLE rpr_j_l, rpr_j_r; +-- A row constructor over plain columns is unaffected. +SELECT company, tdate, count(*) OVER w AS cnt +FROM stock +WHERE company = 'company2' AND tdate <= '2023-07-03' +WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A+) + DEFINE A AS ROW(price, price) IS NOT NULL +); + company | tdate | cnt +----------+------------+----- + company2 | 07-01-2023 | 3 + company2 | 07-02-2023 | 0 + company2 | 07-03-2023 | 0 +(3 rows) + +-- A restriction on a DEFINE condition covers the whole condition, including a +-- clause nested inside it that carries an expression kind of its own. FILTER +-- and an aggregate's ORDER BY are the two such clauses a condition can reach, +-- and each case below is preceded by the same reference written directly in +-- the condition, which is the rejection the nested one has to keep. +CREATE TEMP TABLE rpr_nest_i (i int, v int); +CREATE TEMP TABLE rpr_nest_o (v int); +-- an outer query column: +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS o.v > 0) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +ERROR: cannot use outer query column in DEFINE clause +LINE 6: DEFINE A AS o.v > 0) + ^ +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE o.v > 0) > 0) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +ERROR: cannot use outer query column in DEFINE clause +LINE 6: ... DEFINE A AS count(*) FILTER (WHERE o.v > 0) >... + ^ +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > count(1 ORDER BY o.v)) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +ERROR: cannot use outer query column in DEFINE clause +LINE 6: DEFINE A AS v > count(1 ORDER BY o.v)) + ^ +-- a pattern variable qualifier, where an outer alias answers to the same +-- name, so letting it through would silently read the outer column instead: +SELECT count(*) OVER w FROM rpr_nest_i A +WINDOW w AS ( + ORDER BY A.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS A.v > 0 +); +ERROR: pattern variable qualified expression "a.v" is not supported in DEFINE clause +LINE 6: DEFINE A AS A.v > 0 + ^ +SELECT A.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS percentile_disc(0.5) + WITHIN GROUP (ORDER BY A.v) > 0) + LIMIT 1) +FROM rpr_nest_i A GROUP BY A.v; +ERROR: pattern variable qualified expression "a.v" is not supported in DEFINE clause +LINE 7: ... WITHIN GROUP (ORDER BY A.v) > 0) + ^ +-- a whole-row reference, which a row constructor would expand by RTE: +SELECT (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(o.*)::text IS NOT NULL) + LIMIT 1) +FROM rpr_nest_o o; +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 6: DEFINE A AS ROW(o.*)::text IS NOT NULL) + ^ +HINT: A DEFINE condition may reference individual columns only. +SELECT (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS percentile_disc(0.5) + WITHIN GROUP (ORDER BY ROW(o.*)::text) IS NOT NULL) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 7: ... WITHIN GROUP (ORDER BY ROW(o.*)::text... + ^ +HINT: A DEFINE condition may reference individual columns only. +-- a subquery: +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > (SELECT 1) +); +ERROR: cannot use subquery in DEFINE expression +LINE 6: DEFINE A AS v > (SELECT 1) + ^ +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE (SELECT 1) = 1) > 0 +); +ERROR: cannot use subquery in DEFINE expression +LINE 6: DEFINE A AS count(*) FILTER (WHERE (SELECT 1) = 1) > 0 + ^ +-- The nesting itself is not what is rejected: with nothing forbidden inside +-- it, the aggregate carrying the FILTER is what the condition trips over. +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE v > 0) > 0 +); +ERROR: aggregate functions are not allowed in DEFINE +LINE 6: DEFINE A AS count(*) FILTER (WHERE v > 0) > 0 + ^ +DROP TABLE rpr_nest_i, rpr_nest_o; -- -- 2-arg PREV/NEXT: functional tests -- diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 3f38ae9cae6..f2c3cfbbfa6 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 offset FOLLOWING". -- EXCLUDE options -- EXCLUDE not permitted SELECT COUNT(*) OVER w @@ -509,11 +508,9 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE CURRENT ROW ^ -DETAIL: Frame definition includes EXCLUDE CURRENT ROW. -HINT: Remove the EXCLUDE clause from the window definition. -- EXCLUDE GROUP not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -524,11 +521,9 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE GROUP ^ -DETAIL: Frame definition includes EXCLUDE GROUP. -HINT: Remove the EXCLUDE clause from the window definition. -- EXCLUDE TIES not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -539,11 +534,24 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); -ERROR: cannot use EXCLUDE options with row pattern recognition +ERROR: cannot use EXCLUDE with row pattern recognition LINE 6: EXCLUDE TIES ^ -DETAIL: Frame definition includes EXCLUDE TIES. -HINT: Remove the EXCLUDE clause from the window definition. +-- 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 @@ -553,10 +561,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 offset FOLLOWING". -- GROUPS frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -566,10 +574,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 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. +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 offset FOLLOWING". -- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame @@ -579,11 +601,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 offset FOLLOWING". -- ERROR: frame must start at current row with RPR SELECT COUNT(*) OVER w FROM rpr_frame @@ -593,11 +614,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 offset FOLLOWING". -- ERROR: end before start: CURRENT ROW AND 1 PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame @@ -634,10 +654,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 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 @@ -756,10 +776,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 offset FOLLOWING". -- GROUPS frame with RPR (not permitted) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -771,10 +791,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 offset FOLLOWING". DROP TABLE rpr_frame; -- ============================================================ -- PARTITION BY + FRAME Tests @@ -818,10 +838,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 offset FOLLOWING". DROP TABLE rpr_partition; -- ============================================================ -- PATTERN Syntax Tests @@ -1632,8 +1652,10 @@ ERROR: invalid token "?" after "*?" quantifier LINE 6: PATTERN (A *? ?) ^ HINT: Valid quantifiers are: *, +, ?, {n}, {n,}, {,m}, {n,m}, each optionally followed by "?" for the reluctant version. --- The first token is quoted as typed: stripping its "|" would name "*", and --- "A* ?" is accepted, so the error would describe a pair the grammar takes +-- A first token ending in "|" is a quantifier plus the alternation operator, +-- so what follows it has to be a pattern and an Op never is one. The report +-- names the alternation rather than the pair: "A* ?" is accepted, so naming +-- that pair would describe something the grammar takes. SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1642,10 +1664,22 @@ WINDOW w AS ( PATTERN (A *| ?) DEFINE A AS val > 0 ); -ERROR: invalid token "?" after "*|" quantifier +ERROR: alternation operator "|" requires a pattern on both sides LINE 6: PATTERN (A *| ?) ^ -HINT: Valid quantifiers are: *, +, ?, {n}, {n,}, {,m}, {n,m}, each optionally followed by "?" for the reluctant version. +-- the same for a glued two-character quantifier, where naming the pair would +-- have to invent "*?" out of "*?|" +SELECT COUNT(*) OVER w +FROM rpr_reluctant +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A *?| ??) + DEFINE A AS val > 0 +); +ERROR: alternation operator "|" requires a pattern on both sides +LINE 6: PATTERN (A *?| ??) + ^ -- A first token that is no quantifier at all is itself the offending one, so it -- is reported the same way as when it stands alone SELECT COUNT(*) OVER w @@ -4671,9 +4705,22 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS nosuch.val > 0 ); -ERROR: missing FROM-clause entry for table "nosuch" +ERROR: qualified expression "nosuch.val" is not allowed in DEFINE clause LINE 7: DEFINE A AS nosuch.val > 0 ^ +-- A three-part name is schema-qualified even when its first part spells a +-- pattern variable, and is rejected as any other qualified name +SELECT COUNT(*) OVER w +FROM rpr_err +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (public+) + DEFINE public AS public.rpr_err.val > 0 +); +ERROR: qualified expression "public.rpr_err.val" is not allowed in DEFINE clause +LINE 7: DEFINE public AS public.rpr_err.val > 0 + ^ -- Unqualified composite field access in DEFINE works: no qualifier means no -- pattern/range-var navigation, so the pre-check skips and normal resolution -- handles "(items).amount" via A_Indirection on the current row. @@ -4721,6 +4768,24 @@ WINDOW w AS ( ERROR: range variable qualified expression "rpr_composite.items" is not allowed in DEFINE clause LINE 7: DEFINE A AS (rpr_composite.items).amount > 10 ^ +-- A trailing star on a composite column is a different thing from a trailing +-- star on a relation: it names no relation, so the row constructor keeps +-- expanding it and the DEFINE restrictions do not apply. +SELECT COUNT(*) OVER w +FROM rpr_composite +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW((items).*) IS NOT NULL +); + count +------- + 3 + 0 + 0 +(3 rows) + DROP TABLE rpr_composite; DROP TYPE rpr_item; -- ERROR: undefined column in DEFINE diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 2d5edebb128..00ed80bce3d 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -16,7 +16,7 @@ -- A2. Run condition pushdown bypass -- A3. Window dedup prevention (RPR vs non-RPR) -- A4. Window dedup prevention (same PATTERN, different DEFINE) --- A5. Unused window removal prevention +-- A5. Unused output removal around an RPR window -- A6. Inverse transition bypass -- A7. Cost estimation RPR awareness -- A8. Subquery flattening prevention @@ -546,13 +546,7 @@ SELECT count(*) FROM ( 10 (1 row) --- The same guard must also cover a whole-row Var. Writing the bare --- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose --- attribute number is 0. remove_unused_subquery_outputs() matches the --- guard on attribute number, so the whole-row Var is retained as a single --- entry while the unused scalar "val" output is still replaced with NULL; --- DEFINE evaluates against the intact row, and the match is unchanged. -EXPLAIN (VERBOSE, COSTS OFF) +-- Whole-row Var in DEFINE is not allowed SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id @@ -560,27 +554,49 @@ SELECT sum(c) FROM ( PATTERN (A B+) DEFINE B AS rpr_integ IS NOT NULL) ) t; - QUERY PLAN ------------------------------------------------------------------------------------------------ +ERROR: whole-row reference is not allowed in DEFINE clause +LINE 6: DEFINE B AS rpr_integ IS NOT NULL) + ^ +HINT: A DEFINE condition may reference individual columns only. +-- It still reaches a DEFINE clause without being written there: pulling up a +-- subquery substitutes that subquery's output expressions into defineClause, +-- and one of them can be a whole-row Var (attribute number 0). The parser's +-- junk targetlist entry carries it into the WindowAgg's input like any other +-- DEFINE column, so the pattern match sees the full row regardless of what +-- the subquery projects. The unused scalar output "val" is therefore free to +-- be replaced with NULL (nothing reads it), while c is kept because sum(c) +-- reads it; the match result is unchanged. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c + FROM (SELECT r, r.id AS id, r.val AS val FROM rpr_integ r) s + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS r IS NOT NULL) +) t; + QUERY PLAN +--------------------------------------------------------------------------------------- Aggregate Output: sum((count(*) OVER w)) -> WindowAgg - Output: NULL::integer, count(*) OVER w, rpr_integ.id, rpr_integ.* - Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Output: NULL::integer, count(*) OVER w, r.id, r.* + Window: w AS (ORDER BY r.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ -> Sort - Output: rpr_integ.id, rpr_integ.* - Sort Key: rpr_integ.id - -> Seq Scan on public.rpr_integ - Output: rpr_integ.id, rpr_integ.* + Output: r.id, r.* + Sort Key: r.id + -> Seq Scan on public.rpr_integ r + Output: r.id, r.* (11 rows) SELECT sum(c) FROM ( - SELECT val, count(*) OVER w AS c FROM rpr_integ + SELECT val, count(*) OVER w AS c + FROM (SELECT r, r.id AS id, r.val AS val FROM rpr_integ r) s WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) - DEFINE B AS rpr_integ IS NOT NULL) + DEFINE B AS r IS NOT NULL) ) t; sum ----- diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index 6bb4adfe320..271ae074340 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -701,6 +701,326 @@ LATERAL ( ) ) s; +-- An outer range variable is subject to the same two rules as a local one: a +-- whole-row reference is rejected as one, and a name that does not resolve +-- keeps its own diagnosis rather than being reported as a qualifier problem. +SELECT * FROM (VALUES (95)) AS o(threshold), +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (o.*) IS NOT NULL + ) +) s; +SELECT * FROM (VALUES (95)) AS o(threshold), +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS o.threshhold > 0 + ) +) s; + +-- A two-part name is not always a range variable qualifier: a SQL function's +-- parameter and a PL/pgSQL variable both resolve through +-- p_post_columnref_hook. The qualifier slot is reserved all the same, so +-- these are rejected for the spelling, not for what they name. +CREATE FUNCTION rpr_sqlfn(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_sqlfn.threshold) +$$; + +CREATE FUNCTION rpr_plfn(threshold int) RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + n bigint; +BEGIN + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_plfn.threshold) + ) s; + RETURN n; +END +$$; +SELECT rpr_plfn(0); +DROP FUNCTION rpr_plfn(int); + +-- Unqualified, the same parameter is readable. +CREATE FUNCTION rpr_sqlfn(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > threshold) +$$; +SELECT count(*) FROM rpr_sqlfn(0); +DROP FUNCTION rpr_sqlfn(int); + +-- The qualifier slot is decided on the qualifier alone, before resolution, so +-- a pattern variable takes the slot even from the routine that contains the +-- query. Naming a pattern variable after the function makes rpr_pv.threshold +-- the pattern variable's, and the reservation is reported; that the function +-- has a parameter of that name, and the query has no such column, does not +-- enter into it. +CREATE FUNCTION rpr_pv(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (rpr_pv) + DEFINE rpr_pv AS price > rpr_pv.threshold) +$$; + +-- The collision is in the qualifier, not in the DEFINE variable being +-- defined: any pattern variable of that name reserves it. +CREATE FUNCTION rpr_pv(threshold int) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (rpr_pv A) + DEFINE A AS price > rpr_pv.threshold) +$$; + +-- A field of a composite parameter has no unqualified spelling, so it is +-- reached by parenthesizing the value: "(p).lo" selects a field rather than +-- qualifying a name, and occupies no qualifier slot. +CREATE TYPE rpr_pair AS (lo int, hi int); +CREATE FUNCTION rpr_compfn(p rpr_pair) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > p.lo) +$$; +CREATE FUNCTION rpr_compfn(p rpr_pair) RETURNS SETOF int +LANGUAGE sql AS $$ + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > (p).lo) +$$; +SELECT count(*) FROM rpr_compfn(ROW(0, 0)::rpr_pair); +DROP FUNCTION rpr_compfn(rpr_pair); +DROP TYPE rpr_pair; + +-- The DEFINE rules apply to the names the ref hooks leave to the query +-- parser. Under use_variable resolution PL/pgSQL answers first and keeps +-- any name one of its variables owns, so the qualified spelling rejected +-- above is resolved by PL/pgSQL here and never reaches the rule. +CREATE FUNCTION rpr_plfn_var(threshold int) RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + n bigint; +BEGIN + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > rpr_plfn_var.threshold) + ) s; + RETURN n; +END +$$; +SELECT rpr_plfn_var(0); +DROP FUNCTION rpr_plfn_var(int); + +-- The same applies to a pattern variable's name. Under the default +-- resolution PL/pgSQL declines the name, so the reservation is reached and +-- the collision is reported rather than resolved. +CREATE FUNCTION rpr_conflictfn_err() RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + a stock%ROWTYPE; + n bigint; +BEGIN + a.price := 95; + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > a.price) + ) s; + RETURN n; +END +$$; +SELECT rpr_conflictfn_err(); +DROP FUNCTION rpr_conflictfn_err(); + +CREATE FUNCTION rpr_conflictfn() RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + a stock%ROWTYPE; + n bigint; +BEGIN + a.price := 95; + SELECT count(*) INTO n FROM ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS price > a.price) + ) s; + RETURN n; +END +$$; +SELECT rpr_conflictfn(); +DROP FUNCTION rpr_conflictfn(); + +-- A star on a name a ref hook owns is not a reference to a FROM-clause +-- relation, so it expands in a DEFINE condition as it does anywhere else. +-- Withholding the expansion would not reject such a name; it would read +-- "rec.*" as the single whole value "rec", which is a different condition, +-- and one that differs silently wherever a row constructor is not counting +-- its entries. Each function below matches every row when the record holds +-- (1,2) and none when it does not, so a reading that is off returns the +-- wrong count rather than an error. +CREATE TYPE rpr_pair AS (a int, b int); +CREATE TEMP TABLE rpr_rec (id int); +INSERT INTO rpr_rec VALUES (1), (2), (3); + +-- under the default resolution the post hook answers the name: +CREATE FUNCTION rpr_recstar(x int, y int) RETURNS bigint +LANGUAGE plpgsql AS $$ +DECLARE + rec rpr_pair; + n bigint; +BEGIN + rec := ROW(x, y); + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rec.*)::text = '(1,2)') + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_recstar(1, 2); +SELECT rpr_recstar(1, 3); +DROP FUNCTION rpr_recstar(int, int); + +-- under use_variable the pre hook answers it instead: +CREATE FUNCTION rpr_recstar_var(x int, y int) RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + rec rpr_pair; + n bigint; +BEGIN + rec := ROW(x, y); + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rec.*)::text = '(1,2)') + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_recstar_var(1, 2); +SELECT rpr_recstar_var(1, 3); +DROP FUNCTION rpr_recstar_var(int, int); + +-- the same spelling outside a DEFINE condition, which is what the two above +-- have to agree with: +CREATE FUNCTION rpr_recstar_plain(x int, y int) RETURNS text +LANGUAGE plpgsql AS $$ +DECLARE + rec rpr_pair; +BEGIN + rec := ROW(x, y); + RETURN (SELECT ROW(rec.*)::text FROM rpr_rec LIMIT 1); +END +$$; +SELECT rpr_recstar_plain(1, 2); +DROP FUNCTION rpr_recstar_plain(int, int); + +-- A FROM-clause relation is not such a name, inside such a function or out. +CREATE FUNCTION rpr_relstar() RETURNS bigint +LANGUAGE plpgsql AS $$ +#variable_conflict use_variable +DECLARE + rec rpr_pair := ROW(1, 2); + n bigint; +BEGIN + SELECT count(*) OVER w INTO n FROM rpr_rec + WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(rpr_rec.*)::text IS NOT NULL) + LIMIT 1; + RETURN n; +END +$$; +SELECT rpr_relstar(); +DROP FUNCTION rpr_relstar(); +DROP TABLE rpr_rec; +DROP TYPE rpr_pair; + +-- An outer range variable used as a function-call qualifier reaches DEFINE as +-- a FuncExpr rather than a Var, so the level the qualifier resolved at, not +-- the shape of the resulting node, is what identifies the outer reference. +CREATE TABLE rpr_outer (threshold int); +INSERT INTO rpr_outer VALUES (95); +CREATE FUNCTION rpr_rowfn(rpr_outer) RETURNS int LANGUAGE sql AS 'SELECT 1'; +SELECT * FROM rpr_outer AS o, +LATERAL ( + SELECT price FROM stock + WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS o.rpr_rowfn > 0 + ) +) s; +DROP FUNCTION rpr_rowfn(rpr_outer); +DROP TABLE rpr_outer; + -- DEFINE rejects a schema-qualified column reference (three or more name -- parts) once it resolves; the qualified form itself is not allowed. (stock -- is a temp table, so it is qualified with pg_temp here.) @@ -722,9 +1042,9 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS (pg_temp.stock.*) IS NOT NULL ); --- A two-part table-qualified whole-row reference is rejected as well, through --- a separate range-variable check (a bare relation name is instead accepted --- as a whole-row Var). +-- A two-part table-qualified whole-row reference is rejected as well, and by +-- the whole-row check rather than by a qualifier rule: the error names the +-- whole-row reference, not the qualifier. -- 2-part (table.*): SELECT price FROM stock WINDOW w AS ( @@ -734,6 +1054,279 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS (stock.*) IS NOT NULL ); +-- The form decides before the qualifier is looked up, so a misspelled table +-- name is reported as the whole-row reference it is written as, not as a +-- missing FROM-clause entry: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stok.*) IS NOT NULL +); +-- and the same through a row constructor: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(stok.*) IS NOT NULL +); + +-- A row constructor reaches the same references through +-- transformExpressionList(), whose star expansion binds them by RTE into +-- individual column Vars, past every check. DEFINE skips it. +-- ROW(schema.table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(pg_temp.stock.*) IS NOT NULL +); +-- ROW(table.*): +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(stock.*) IS NOT NULL +); +-- the ROW keyword is optional, so the bare constructor needs the same +-- treatment: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS (stock.*, 1) IS NOT NULL +); +-- redundant parentheses are not a way around it: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW((stock.*)) IS NOT NULL +); +-- a pattern variable qualifier is a separate class of rejection: +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS ROW(A.*) IS NOT NULL +); +-- The plain two-part form is the one the standard writes its DEFINE examples +-- with, and it is decided on the qualifier alone, before resolution. +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS A.price > 100 +); +-- Deciding on the qualifier alone means a pattern variable takes a name a +-- range variable would otherwise answer to: the rejection names the pattern +-- variable, not the alias, even though "a" is a live alias here. +SELECT price FROM stock AS a +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS a.price > 100 +); +-- Each rejection above classifies the reference only after it resolves, so a +-- misspelled column keeps the diagnosis and the suggestion it gets anywhere +-- else. Firing on the qualifier alone would report a range variable problem +-- before the rest of the name was looked at. +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS stock.pric > 0 +); +SELECT price FROM stock +WINDOW w AS ( + PARTITION BY company + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS pg_temp.stock.pric > 0 +); +-- the same typo outside a DEFINE clause, for comparison: +SELECT price FROM stock WHERE stock.pric > 0; + +-- Retrying an unresolved column as a function call on the whole row builds a +-- whole-row reference the query does not contain. That must not be reported +-- as one, and must not let the reference through either: rpr_tag(rpr_stock) +-- below resolves, so the retry succeeds and the result is rejected by the +-- qualifier rules rather than by the whole-row check. +CREATE FUNCTION rpr_tag(rpr_stock) RETURNS int + LANGUAGE sql IMMUTABLE AS $$SELECT 1$$; +SELECT price FROM rpr_stock +WINDOW w AS ( + PARTITION BY part_id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS rpr_stock.rpr_tag > 0 +); +SELECT price FROM rpr_stock +WINDOW w AS ( + PARTITION BY part_id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A) + DEFINE A AS public.rpr_stock.rpr_tag > 0 +); +DROP FUNCTION rpr_tag(rpr_stock); + +-- A JOIN USING alias has no whole-row Var of its own, so the same retry +-- expands it to a row constructor instead. The retry carries no star, so +-- DEFINE lets it through to that arm. +CREATE TEMP TABLE rpr_j_l (x int, y int); +CREATE TEMP TABLE rpr_j_r (x int, z int); +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS j.yy > 0 +); +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS j.y > 0 +); +SELECT count(*) OVER w FROM (rpr_j_l JOIN rpr_j_r USING (x)) j +WINDOW w AS ( + ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS (j.*) IS NOT NULL +); +DROP TABLE rpr_j_l, rpr_j_r; + +-- A row constructor over plain columns is unaffected. +SELECT company, tdate, count(*) OVER w AS cnt +FROM stock +WHERE company = 'company2' AND tdate <= '2023-07-03' +WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL + PATTERN (A+) + DEFINE A AS ROW(price, price) IS NOT NULL +); + +-- A restriction on a DEFINE condition covers the whole condition, including a +-- clause nested inside it that carries an expression kind of its own. FILTER +-- and an aggregate's ORDER BY are the two such clauses a condition can reach, +-- and each case below is preceded by the same reference written directly in +-- the condition, which is the rejection the nested one has to keep. +CREATE TEMP TABLE rpr_nest_i (i int, v int); +CREATE TEMP TABLE rpr_nest_o (v int); +-- an outer query column: +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS o.v > 0) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE o.v > 0) > 0) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +SELECT o.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > count(1 ORDER BY o.v)) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +-- a pattern variable qualifier, where an outer alias answers to the same +-- name, so letting it through would silently read the outer column instead: +SELECT count(*) OVER w FROM rpr_nest_i A +WINDOW w AS ( + ORDER BY A.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS A.v > 0 +); +SELECT A.v, (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS percentile_disc(0.5) + WITHIN GROUP (ORDER BY A.v) > 0) + LIMIT 1) +FROM rpr_nest_i A GROUP BY A.v; +-- a whole-row reference, which a row constructor would expand by RTE: +SELECT (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW(o.*)::text IS NOT NULL) + LIMIT 1) +FROM rpr_nest_o o; +SELECT (SELECT count(*) OVER w FROM rpr_nest_i inn + WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS percentile_disc(0.5) + WITHIN GROUP (ORDER BY ROW(o.*)::text) IS NOT NULL) + LIMIT 1) +FROM rpr_nest_o o GROUP BY o.v; +-- a subquery: +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > (SELECT 1) +); +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE (SELECT 1) = 1) > 0 +); +-- The nesting itself is not what is rejected: with nothing forbidden inside +-- it, the aggregate carrying the FILTER is what the condition trips over. +SELECT count(*) OVER w FROM rpr_nest_i inn +WINDOW w AS ( + ORDER BY inn.i + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS count(*) FILTER (WHERE v > 0) > 0 +); +DROP TABLE rpr_nest_i, rpr_nest_o; -- -- 2-arg PREV/NEXT: functional tests diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index e887537a4e8..b3a698889a3 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 @@ -459,6 +471,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 @@ -1171,8 +1194,10 @@ WINDOW w AS ( DEFINE A AS val > 0 ); --- The first token is quoted as typed: stripping its "|" would name "*", and --- "A* ?" is accepted, so the error would describe a pair the grammar takes +-- A first token ending in "|" is a quantifier plus the alternation operator, +-- so what follows it has to be a pattern and an Op never is one. The report +-- names the alternation rather than the pair: "A* ?" is accepted, so naming +-- that pair would describe something the grammar takes. SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1182,6 +1207,17 @@ WINDOW w AS ( DEFINE A AS val > 0 ); +-- the same for a glued two-character quantifier, where naming the pair would +-- have to invent "*?" out of "*?|" +SELECT COUNT(*) OVER w +FROM rpr_reluctant +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A *?| ??) + DEFINE A AS val > 0 +); + -- A first token that is no quantifier at all is itself the offending one, so it -- is reported the same way as when it stands alone SELECT COUNT(*) OVER w @@ -2995,6 +3031,17 @@ WINDOW w AS ( DEFINE A AS nosuch.val > 0 ); +-- A three-part name is schema-qualified even when its first part spells a +-- pattern variable, and is rejected as any other qualified name +SELECT COUNT(*) OVER w +FROM rpr_err +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (public+) + DEFINE public AS public.rpr_err.val > 0 +); + -- Unqualified composite field access in DEFINE works: no qualifier means no -- pattern/range-var navigation, so the pre-check skips and normal resolution -- handles "(items).amount" via A_Indirection on the current row. @@ -3030,6 +3077,18 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS (rpr_composite.items).amount > 10 ); + +-- A trailing star on a composite column is a different thing from a trailing +-- star on a relation: it names no relation, so the row constructor keeps +-- expanding it and the DEFINE restrictions do not apply. +SELECT COUNT(*) OVER w +FROM rpr_composite +WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS ROW((items).*) IS NOT NULL +); DROP TABLE rpr_composite; DROP TYPE rpr_item; diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 4d60545c6cb..4f96186ddd7 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -16,7 +16,7 @@ -- A2. Run condition pushdown bypass -- A3. Window dedup prevention (RPR vs non-RPR) -- A4. Window dedup prevention (same PATTERN, different DEFINE) --- A5. Unused window removal prevention +-- A5. Unused output removal around an RPR window -- A6. Inverse transition bypass -- A7. Cost estimation RPR awareness -- A8. Subquery flattening prevention @@ -364,13 +364,7 @@ SELECT count(*) FROM ( DEFINE B AS val > PREV(val)) ) t; --- The same guard must also cover a whole-row Var. Writing the bare --- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose --- attribute number is 0. remove_unused_subquery_outputs() matches the --- guard on attribute number, so the whole-row Var is retained as a single --- entry while the unused scalar "val" output is still replaced with NULL; --- DEFINE evaluates against the intact row, and the match is unchanged. -EXPLAIN (VERBOSE, COSTS OFF) +-- Whole-row Var in DEFINE is not allowed SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id @@ -379,12 +373,31 @@ SELECT sum(c) FROM ( DEFINE B AS rpr_integ IS NOT NULL) ) t; +-- It still reaches a DEFINE clause without being written there: pulling up a +-- subquery substitutes that subquery's output expressions into defineClause, +-- and one of them can be a whole-row Var (attribute number 0). The parser's +-- junk targetlist entry carries it into the WindowAgg's input like any other +-- DEFINE column, so the pattern match sees the full row regardless of what +-- the subquery projects. The unused scalar output "val" is therefore free to +-- be replaced with NULL (nothing reads it), while c is kept because sum(c) +-- reads it; the match result is unchanged. +EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(c) FROM ( - SELECT val, count(*) OVER w AS c FROM rpr_integ + SELECT val, count(*) OVER w AS c + FROM (SELECT r, r.id AS id, r.val AS val FROM rpr_integ r) s WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) - DEFINE B AS rpr_integ IS NOT NULL) + DEFINE B AS r IS NOT NULL) +) t; + +SELECT sum(c) FROM ( + SELECT val, count(*) OVER w AS c + FROM (SELECT r, r.id AS id, r.val AS val FROM rpr_integ r) s + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS r IS NOT NULL) ) t; -- ============================================================ -- 2.54.0 (Apple Git-157)