From 7f135d9dee30c6c4c86f470a97996880b0f6ece2 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 15 Jun 2026 15:29:59 +0900 Subject: [PATCH 03/13] Recognize row pattern navigation operations by name in DEFINE PREV, NEXT, FIRST, and LAST were placeholder functions in pg_proc that polluted the ordinary function namespace and could be silently misbound to same-named user functions. Recognize them by name inside a DEFINE clause and drop the placeholders; an unqualified call is always navigation, while a schema-qualified call still reaches an ordinary function. Also reject a navigation operation inside a navigation offset, which must be a run-time constant and previously could crash the planner. Recognition happens in two steps in ParseFuncOrColumn: note the matched name up front, skip the catalog lookup by treating it as FUNCDETAIL_NORMAL, let the common decoration and wrong-kind-of-routine checks run, and only then route to ParseRPRNavCall to build the RPRNavExpr. ParseRPRNavCall therefore does not duplicate the aggregate/window decoration checks (agg_star, DISTINCT, WITHIN GROUP, ORDER BY, FILTER, OVER); the common path performs them with identical messages. Document this in func-window.sgml. --- doc/src/sgml/func/func-window.sgml | 6 + src/backend/parser/parse_func.c | 296 ++++++++++----- src/backend/parser/parse_rpr.c | 15 +- src/backend/utils/adt/ruleutils.c | 55 ++- src/backend/utils/adt/windowfuncs.c | 118 ------ src/include/catalog/pg_proc.dat | 24 -- src/test/regress/expected/rpr.out | 32 -- src/test/regress/expected/rpr_base.out | 492 ++++++++++++++++++++++++- src/test/regress/sql/rpr.sql | 15 - src/test/regress/sql/rpr_base.sql | 251 +++++++++++++ 10 files changed, 1007 insertions(+), 297 deletions(-) diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml index ab469b56fd7..1079b6abb6e 100644 --- a/doc/src/sgml/func/func-window.sgml +++ b/doc/src/sgml/func/func-window.sgml @@ -282,6 +282,10 @@ IGNORE NULLS Row Pattern Recognition navigation functions are listed in . These functions can be used to describe the DEFINE clause of Row Pattern Recognition. + The names PREV, NEXT, + FIRST, and LAST are + recognized as navigation functions only in an unqualified call; a + schema-qualified call is resolved as an ordinary function instead. @@ -397,6 +401,8 @@ IGNORE NULLS permitted. Same-category nesting (e.g., PREV inside PREV) is also prohibited. + The offset argument must be a run-time constant: + it cannot reference columns or contain a navigation operation. diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 1f6c8fa4fb2..f3b37aa992c 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -31,7 +31,6 @@ #include "parser/parse_target.h" #include "parser/parse_type.h" #include "utils/builtins.h" -#include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/syscache.h" @@ -49,6 +48,9 @@ static void unify_hypothetical_args(ParseState *pstate, List *fargs, int numAggregatedArgs, Oid *actual_arg_types, Oid *declared_arg_types); static Oid FuncNameAsType(List *funcname); +static Node *ParseRPRNavCall(ParseState *pstate, List *funcname, + List *fargs, List *argnames, FuncCall *fn, + int location); static Node *ParseComplexProjection(ParseState *pstate, const char *funcname, Node *first_arg, int location); static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname, @@ -122,6 +124,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, int fgc_flags; char aggkind = 0; ParseCallbackState pcbstate; + bool could_be_rpr_nav = false; /* * If there's an aggregate filter, transform it using transformWhereClause @@ -218,6 +221,28 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, Assert(first_arg != NULL); } + /* + * Inside an RPR DEFINE clause, an unqualified call to one of the row + * pattern navigation names PREV/NEXT/FIRST/LAST denotes the navigation + * operation, not an ordinary function. Just note that here; the catalog + * lookup is skipped and the RPRNavExpr is built at the end, after the + * common decoration checks have run (see the could_be_rpr_nav handling + * below). A schema-qualified call is the explicit way to reach an + * ordinary function of one of these names. + */ + if (!is_column && !proc_call && + pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE && + list_length(funcname) == 1) + { + const char *name = strVal(linitial(funcname)); + + if (strcmp(name, "prev") == 0 || + strcmp(name, "next") == 0 || + strcmp(name, "first") == 0 || + strcmp(name, "last") == 0) + could_be_rpr_nav = true; + } + /* * Decide whether it's legitimate to consider the construct to be a column * projection. For that, there has to be a single argument of complex @@ -266,17 +291,32 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, * with default arguments. */ - setup_parser_errposition_callback(&pcbstate, pstate, location); + if (!could_be_rpr_nav) + { + setup_parser_errposition_callback(&pcbstate, pstate, location); + + fdresult = func_get_detail(funcname, fargs, argnames, nargs, + actual_arg_types, + !func_variadic, true, proc_call, + &fgc_flags, + &funcid, &rettype, &retset, + &nvargs, &vatype, + &declared_arg_types, &argdefaults); - fdresult = func_get_detail(funcname, fargs, argnames, nargs, - actual_arg_types, - !func_variadic, true, proc_call, - &fgc_flags, - &funcid, &rettype, &retset, - &nvargs, &vatype, - &declared_arg_types, &argdefaults); + cancel_parser_errposition_callback(&pcbstate); + } + else + { + /* + * A recognized navigation name skips catalog lookup entirely. Treat + * it as an ordinary function so the common wrong-kind-of-routine and + * decoration checks below run with the existing messages, then route + * to ParseRPRNavCall to build the RPRNavExpr. + */ + Assert(!proc_call); - cancel_parser_errposition_callback(&pcbstate); + fdresult = FUNCDETAIL_NORMAL; + } /* * Check for various wrong-kind-of-routine cases. @@ -653,6 +693,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, parser_errposition(pstate, location))); } + /* + * A recognized navigation name has now passed the common decoration and + * wrong-kind checks above; build the RPRNavExpr. No fallback to function + * resolution ever happens here. + */ + if (could_be_rpr_nav) + return ParseRPRNavCall(pstate, funcname, fargs, argnames, fn, + location); + /* * If there are default arguments, we have to include their types in * actual_arg_types for the purpose of checking generic type consistency. @@ -759,88 +808,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, if (retset) check_srf_call_placement(pstate, last_srf, location); - /* - * RPR navigation functions (PREV/NEXT/FIRST/LAST) are only meaningful - * inside a WINDOW DEFINE clause. - * - * Outside DEFINE, these polymorphic placeholders can shadow column access - * via functional notation (e.g., last(f) meaning f.last). For the 1-arg - * form, try column projection first; if that succeeds, use it instead. - * Otherwise, report a clear parser error. - */ - if (fdresult == FUNCDETAIL_NORMAL && - pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE && - (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT || - funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 || - funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT || - funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8)) - { - /* 1-arg form: try column projection before erroring out */ - if (nargs == 1 && !agg_star && !agg_distinct && over == NULL && - list_length(funcname) == 1) - { - Node *projection; - - projection = ParseComplexProjection(pstate, - strVal(linitial(funcname)), - linitial(fargs), - location); - if (projection) - return projection; - } - - /* Not a column projection -- report error */ - ereport(ERROR, - errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cannot use %s outside a DEFINE clause", - NameListToString(funcname)), - parser_errposition(pstate, location)); - } - /* build the appropriate output structure */ - if (fdresult == FUNCDETAIL_NORMAL && - pstate->p_expr_kind == EXPR_KIND_RPR_DEFINE && - (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT || - funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8 || - funcid == F_FIRST_ANYELEMENT || funcid == F_LAST_ANYELEMENT || - funcid == F_FIRST_ANYELEMENT_INT8 || funcid == F_LAST_ANYELEMENT_INT8)) - { - /* - * RPR navigation functions (PREV/NEXT/FIRST/LAST) are compiled into - * EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE opcodes instead of a normal - * function call. Represent them as RPRNavExpr nodes so that later - * stages can identify them without relying on funcid comparisons. - */ - RPRNavKind kind; - bool has_offset; - RPRNavExpr *navexpr; - - if (funcid == F_PREV_ANYELEMENT || funcid == F_PREV_ANYELEMENT_INT8) - kind = RPR_NAV_PREV; - else if (funcid == F_NEXT_ANYELEMENT || funcid == F_NEXT_ANYELEMENT_INT8) - kind = RPR_NAV_NEXT; - else if (funcid == F_FIRST_ANYELEMENT || funcid == F_FIRST_ANYELEMENT_INT8) - kind = RPR_NAV_FIRST; - else - kind = RPR_NAV_LAST; - - has_offset = (funcid == F_PREV_ANYELEMENT_INT8 || - funcid == F_NEXT_ANYELEMENT_INT8 || - funcid == F_FIRST_ANYELEMENT_INT8 || - funcid == F_LAST_ANYELEMENT_INT8); - - navexpr = makeNode(RPRNavExpr); - - navexpr->kind = kind; - navexpr->arg = (Expr *) linitial(fargs); - navexpr->offset_arg = has_offset ? (Expr *) lsecond(fargs) : NULL; - navexpr->resulttype = rettype; - /* resultcollid will be set by parse_collate.c */ - navexpr->location = location; - - retval = (Node *) navexpr; - } - else if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE) + if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE) { FuncExpr *funcexpr = makeNode(FuncExpr); @@ -2111,6 +2080,151 @@ FuncNameAsType(List *funcname) return result; } +/* + * ParseRPRNavCall + * Recognize a row pattern navigation operation in a DEFINE clause. + * + * Inside an EXPR_KIND_RPR_DEFINE clause an unqualified call to one of the + * names PREV/NEXT/FIRST/LAST denotes the corresponding row pattern navigation + * operation (ISO/IEC 19075-5 Subclause 5.6), not an ordinary function call. + * The name is matched here, before any catalog lookup, with no fallback to + * function resolution: once it matches, decoration and argument-count + * violations are dedicated errors rather than letting an ordinary function of + * the same name take over. A schema-qualified call (the caller restricts us + * to unqualified names) is the documented way to reach such a function + * instead. + * + * The caller routes here only after the name has matched one of the four + * navigation names and the common decoration/wrong-kind checks in + * ParseFuncOrColumn have run, so this always returns an RPRNavExpr. + */ +static Node * +ParseRPRNavCall(ParseState *pstate, List *funcname, List *fargs, + List *argnames, FuncCall *fn, int location) +{ + const char *name = strVal(linitial(funcname)); + RPRNavKind kind; + const char *navname; + int nargs = list_length(fargs); + Node *arg; + RPRNavExpr *navexpr; + + /* match the parser-downcased identifier; otherwise not a navigation name */ + if (strcmp(name, "prev") == 0) + { + kind = RPR_NAV_PREV; + navname = "PREV"; + } + else if (strcmp(name, "next") == 0) + { + kind = RPR_NAV_NEXT; + navname = "NEXT"; + } + else if (strcmp(name, "first") == 0) + { + kind = RPR_NAV_FIRST; + navname = "FIRST"; + } + else if (strcmp(name, "last") == 0) + { + kind = RPR_NAV_LAST; + navname = "LAST"; + } + else + { + /* the caller only routes here after matching one of the four names */ + pg_unreachable(); + return NULL; + } + + /* + * Once the name matches we never fall back to function resolution, so any + * decoration that does not make sense for a navigation operation is a + * hard error. The aggregate/window decorations (agg_star, DISTINCT, + * WITHIN GROUP, ORDER BY, FILTER, OVER, RESPECT/IGNORE NULLS) are already + * rejected by the common path in ParseFuncOrColumn, which treated the + * recognized name as an ordinary function; what remains are the + * decorations that path accepts for a plain function but a navigation + * operation must still reject. + */ + if (fn->func_variadic) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot use VARIADIC with row pattern navigation function %s", + navname), + parser_errposition(pstate, location))); + if (argnames != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("row pattern navigation operations cannot use named arguments"), + parser_errposition(pstate, location))); + /* takes a value expression and an optional offset */ + if (nargs == 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("too few arguments for row pattern navigation function %s", + navname), + errdetail("%s takes a value expression and an optional offset argument.", + navname), + parser_errposition(pstate, location))); + if (nargs > 2) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("too many arguments for row pattern navigation function %s", + navname), + errdetail("%s takes a value expression and an optional offset argument.", + navname), + parser_errposition(pstate, location))); + + /* + * Resolve a still-unknown first argument to text, the same way the + * anycompatible family does. A navigation operation is not a polymorphic + * function, so the old "could not determine polymorphic type" error does + * not apply; an unknown literal cannot contain a column reference, so the + * walker still rejects it later. + */ + arg = linitial(fargs); + if (exprType(arg) == UNKNOWNOID) + arg = coerce_to_common_type(pstate, arg, TEXTOID, navname); + + navexpr = makeNode(RPRNavExpr); + navexpr->kind = kind; + navexpr->arg = (Expr *) arg; + + /* an explicit offset is coerced to int8, which the executor reads */ + if (nargs == 2) + { + Node *offset = lsecond(fargs); + Oid offtype = exprType(offset); + + if (offtype != INT8OID) + { + Node *newoffset; + + newoffset = coerce_to_target_type(pstate, offset, offtype, + INT8OID, -1, COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, -1); + if (newoffset == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("offset argument of %s must be type %s, not type %s", + navname, "bigint", format_type_be(offtype)), + parser_errposition(pstate, exprLocation(offset)))); + offset = newoffset; + } + navexpr->offset_arg = (Expr *) offset; + } + else + navexpr->offset_arg = NULL; + + /* compound_offset_arg stays NULL; define_walker flattening fills it in */ + navexpr->resulttype = exprType(arg); + /* resultcollid will be set by parse_collate.c */ + navexpr->location = location; + + return (Node *) navexpr; +} + /* * ParseComplexProjection - * handles function calls with a single argument that is of complex type. diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index 3eaea2be750..8ed01bb8f28 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -472,6 +472,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, * * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind * * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...) * * offset_arg / compound_offset_arg must not contain column refs + * or nested navigation operations * * Volatile callees (and sequence operations) are rejected later in the * planner via validate_rpr_define_volatility(); see optimizer/plan/rpr.c. @@ -482,7 +483,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, * walks nav.arg in PHASE_NAV_ARG to collect nesting/column-ref state, * applies compound flatten or raises a nesting error, then walks the * (post-flatten) offset(s) in PHASE_NAV_OFFSET to enforce the - * constant-offset rule. No subtree is walked twice. + * constant-offset and no-nested-nav rules. No subtree is walked twice. */ /* @@ -498,6 +499,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, * PREV(PREV()), FIRST(FIRST()), three-or-more deep) * [2] for each nav offset (PHASE_NAV_OFFSET): * - must be a run-time constant (no column references) + * - must not contain a row pattern navigation operation * * Var sightings feed the column-ref rule for the enclosing nav scope; * RPRNavExpr sightings inside PHASE_NAV_ARG feed the nesting decision. @@ -538,11 +540,14 @@ define_walker(Node *node, void *context) if (ctx->phase == DEFINE_PHASE_NAV_OFFSET) { /* - * Navs inside offset_arg are unusual but not directly banned; the - * constant-offset rule will catch any Var or volatile they - * contain. + * A navigation offset must be a run-time constant, so it cannot + * contain a navigation operation. */ - return expression_tree_walker(node, define_walker, ctx); + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern navigation offset cannot contain a row pattern navigation operation"), + errdetail("A navigation offset must be a run-time constant."), + parser_errposition(ctx->pstate, nav->location)); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 4eb7e35bee4..2b7fd7367f3 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -127,6 +127,7 @@ typedef struct bool varprefix; /* true to print prefixes on Vars */ bool colNamesVisible; /* do we care about output column names? */ bool inGroupBy; /* deparsing GROUP BY clause? */ + bool inRPRDefine; /* deparsing an RPR DEFINE clause? */ bool varInOrderBy; /* deparsing simple Var in ORDER BY? */ Bitmapset *appendparents; /* if not null, map child Vars of these relids * back to the parent rel */ @@ -545,7 +546,7 @@ static char *generate_qualified_relation_name(Oid relid); static char *generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes, bool has_variadic, bool *use_variadic_p, - bool inGroupBy); + bool inGroupBy, bool inRPRDefine); static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2); static void add_cast_to(StringInfo buf, Oid typid); static char *generate_qualified_type_name(Oid typid); @@ -1131,6 +1132,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) context.indentLevel = PRETTYINDENT_STD; context.colNamesVisible = true; context.inGroupBy = false; + context.inRPRDefine = false; context.varInOrderBy = false; context.appendparents = NULL; @@ -1142,7 +1144,7 @@ pg_get_triggerdef_worker(Oid trigid, bool pretty) appendStringInfo(&buf, "EXECUTE FUNCTION %s(", generate_function_name(trigrec->tgfoid, 0, NIL, NULL, - false, NULL, false)); + false, NULL, false, false)); if (trigrec->tgnargs > 0) { @@ -3401,7 +3403,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS) appendStringInfo(&buf, " SUPPORT %s", generate_function_name(proc->prosupport, 1, NIL, argtypes, - false, NULL, false)); + false, NULL, false, false)); } if (oldlen != buf.len) @@ -4054,6 +4056,7 @@ deparse_expression_pretty(Node *expr, List *dpcontext, context.indentLevel = startIndent; context.colNamesVisible = true; context.inGroupBy = false; + context.inRPRDefine = false; context.varInOrderBy = false; context.appendparents = NULL; @@ -5849,6 +5852,7 @@ make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc, context.indentLevel = PRETTYINDENT_STD; context.colNamesVisible = true; context.inGroupBy = false; + context.inRPRDefine = false; context.varInOrderBy = false; context.appendparents = NULL; @@ -6041,6 +6045,7 @@ get_query_def(Query *query, StringInfo buf, List *parentnamespace, context.indentLevel = startIndent; context.colNamesVisible = colNamesVisible; context.inGroupBy = false; + context.inRPRDefine = false; context.varInOrderBy = false; context.appendparents = NULL; @@ -7220,15 +7225,25 @@ get_rule_define(List *defineClause, deparse_context *context) { StringInfo buf = context->buf; const char *sep; + bool save_inrprdefine = context->inRPRDefine; sep = " "; + /* + * Within the DEFINE clause an unqualified prev/next/first/last is a + * navigation operation, so a user function of one of those names must be + * schema-qualified to survive a reparse; see generate_function_name(). + */ + context->inRPRDefine = true; + foreach_node(TargetEntry, te, defineClause) { appendStringInfo(buf, "%s%s AS ", sep, quote_identifier(te->resname)); get_rule_expr((Node *) te->expr, context, false); sep = ",\n "; } + + context->inRPRDefine = save_inrprdefine; } /* @@ -7459,6 +7474,7 @@ get_window_frame_options_for_explain(int frameOptions, context.indentLevel = 0; context.colNamesVisible = true; context.inGroupBy = false; + context.inRPRDefine = false; context.varInOrderBy = false; context.appendparents = NULL; @@ -11691,7 +11707,8 @@ get_func_expr(FuncExpr *expr, deparse_context *context, argnames, argtypes, expr->funcvariadic, &use_variadic, - context->inGroupBy)); + context->inGroupBy, + context->inRPRDefine)); nargs = 0; foreach(l, expr->args) { @@ -11761,7 +11778,8 @@ get_agg_expr_helper(Aggref *aggref, deparse_context *context, funcname = generate_function_name(aggref->aggfnoid, nargs, NIL, argtypes, aggref->aggvariadic, &use_variadic, - context->inGroupBy); + context->inGroupBy, + context->inRPRDefine); /* Print the aggregate name, schema-qualified if needed */ appendStringInfo(buf, "%s(%s", funcname, @@ -11902,7 +11920,8 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context, if (!funcname) funcname = generate_function_name(wfunc->winfnoid, nargs, argnames, argtypes, false, NULL, - context->inGroupBy); + context->inGroupBy, + context->inRPRDefine); appendStringInfo(buf, "%s(", funcname); @@ -13743,7 +13762,7 @@ get_tablesample_def(TableSampleClause *tablesample, deparse_context *context) appendStringInfo(buf, " TABLESAMPLE %s (", generate_function_name(tablesample->tsmhandler, 1, NIL, argtypes, - false, NULL, false)); + false, NULL, false, false)); nargs = 0; foreach(l, tablesample->args) @@ -14157,12 +14176,14 @@ generate_qualified_relation_name(Oid relid) * * inGroupBy must be true if we're deparsing a GROUP BY clause. * + * inRPRDefine must be true if we're deparsing an RPR DEFINE clause. + * * The result includes all necessary quoting and schema-prefixing. */ static char * generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes, bool has_variadic, bool *use_variadic_p, - bool inGroupBy) + bool inGroupBy, bool inRPRDefine) { char *result; HeapTuple proctup; @@ -14196,6 +14217,24 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes, force_qualify = true; } + /* + * Inside a row pattern DEFINE clause, the parser binds an unqualified + * prev/next/first/last to a navigation operation before any catalog + * lookup, so an unqualified call to a user function of one of those names + * would change meaning across a deparse/reparse cycle. Force schema + * qualification; the qualified form is the documented escape hatch. Only + * the exact lower-case names are at risk: a mixed-case proname deparses + * quoted and cannot match the parser's downcased comparison. + */ + if (inRPRDefine) + { + if (strcmp(proname, "prev") == 0 || + strcmp(proname, "next") == 0 || + strcmp(proname, "first") == 0 || + strcmp(proname, "last") == 0) + force_qualify = true; + } + /* * Determine whether VARIADIC should be printed. We must do this first * since it affects the lookup rules in func_get_detail(). diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index d15aa0c75db..78b7f05aba2 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -724,121 +724,3 @@ window_nth_value(PG_FUNCTION_ARGS) PG_RETURN_DATUM(result); } - -/* - * prev - * Catalog placeholder for RPR's PREV navigation operator. - * - * The parser transforms prev() calls inside DEFINE into RPRNavExpr nodes, - * so this function is never reached during normal RPR execution. It exists - * only so that the parser can resolve the function name from pg_proc. - * Calls outside DEFINE are rejected by parse_func.c (EXPR_KIND_RPR_DEFINE - * check). The error below is a defensive measure in case that check is - * bypassed (e.g., direct C-level function invocation). - */ -Datum -window_prev(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use PREV() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * next - * Catalog placeholder for RPR's NEXT navigation operator. - * See window_prev() for details. - */ -Datum -window_next(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use NEXT() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * prev(value, offset) - * Catalog placeholder for RPR's PREV navigation operator with offset. - * See window_prev() for details. - */ -Datum -window_prev_offset(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use PREV() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * next(value, offset) - * Catalog placeholder for RPR's NEXT navigation operator with offset. - * See window_prev() for details. - */ -Datum -window_next_offset(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use NEXT() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * first - * Catalog placeholder for RPR's FIRST navigation operator. - * See window_prev() for details. - */ -Datum -window_first(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use FIRST() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * last - * Catalog placeholder for RPR's LAST navigation operator. - * See window_prev() for details. - */ -Datum -window_last(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use LAST() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * first(value, offset) - * Catalog placeholder for RPR's FIRST navigation operator with offset. - * See window_prev() for details. - */ -Datum -window_first_offset(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use FIRST() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} - -/* - * last(value, offset) - * Catalog placeholder for RPR's LAST navigation operator with offset. - * See window_prev() for details. - */ -Datum -window_last_offset(PG_FUNCTION_ARGS) -{ - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot use LAST() outside a DEFINE clause")); - PG_RETURN_NULL(); /* not reached */ -} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index b3aa42fc66e..be157a5fbe9 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10967,30 +10967,6 @@ { oid => '3114', descr => 'fetch the Nth row value', proname => 'nth_value', prokind => 'w', prorettype => 'anyelement', proargtypes => 'anyelement int4', prosrc => 'window_nth_value' }, -{ oid => '8126', descr => 'fetch the preceding row value', - proname => 'prev', provolatile => 's', prorettype => 'anyelement', - proargtypes => 'anyelement', prosrc => 'window_prev' }, -{ oid => '8128', descr => 'fetch the Nth preceding row value', - proname => 'prev', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', - proargtypes => 'anyelement int8', prosrc => 'window_prev_offset' }, -{ oid => '8127', descr => 'fetch the following row value', - proname => 'next', provolatile => 's', prorettype => 'anyelement', - proargtypes => 'anyelement', prosrc => 'window_next' }, -{ oid => '8129', descr => 'fetch the Nth following row value', - proname => 'next', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', - proargtypes => 'anyelement int8', prosrc => 'window_next_offset' }, -{ oid => '8130', descr => 'fetch the first row value within match', - proname => 'first', provolatile => 's', prorettype => 'anyelement', - proargtypes => 'anyelement', prosrc => 'window_first' }, -{ oid => '8132', descr => 'fetch the Nth row value within match', - proname => 'first', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', - proargtypes => 'anyelement int8', prosrc => 'window_first_offset' }, -{ oid => '8131', descr => 'fetch the last row value within match', - proname => 'last', provolatile => 's', prorettype => 'anyelement', - proargtypes => 'anyelement', prosrc => 'window_last' }, -{ oid => '8133', descr => 'fetch the Nth-from-last row value within match', - proname => 'last', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement', - proargtypes => 'anyelement int8', prosrc => 'window_last_offset' }, # functions for range types { oid => '3832', descr => 'I/O', diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index c02c9d75a9a..dc5140fecc9 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1021,16 +1021,6 @@ WINDOW w AS ( -- -- Error cases: PREV/NEXT usage restrictions -- --- PREV outside DEFINE clause -SELECT prev(price) FROM stock; -ERROR: cannot use prev outside a DEFINE clause -LINE 1: SELECT prev(price) FROM stock; - ^ --- NEXT outside DEFINE clause -SELECT next(price) FROM stock; -ERROR: cannot use next outside a DEFINE clause -LINE 1: SELECT next(price) FROM stock; - ^ -- Nested PREV SELECT price FROM stock WINDOW w AS ( @@ -1598,15 +1588,6 @@ WINDOW w AS ( company2 | 07-10-2023 | 1300 | | | 0 (20 rows) --- 2-arg PREV/NEXT outside DEFINE clause -SELECT prev(price, 2) FROM stock; -ERROR: cannot use prev outside a DEFINE clause -LINE 1: SELECT prev(price, 2) FROM stock; - ^ -SELECT next(price, 2) FROM stock; -ERROR: cannot use next outside a DEFINE clause -LINE 1: SELECT next(price, 2) FROM stock; - ^ -- 2-arg PREV/NEXT: negative offset SELECT company, tdate, price, first_value(price) OVER w FROM stock @@ -2134,19 +2115,6 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( DEFINE A AS LAST(val, -1) IS NULL ); ERROR: row pattern navigation offset must not be negative --- FIRST/LAST outside DEFINE clause (error cases) -SELECT first(val) FROM rpr_nav; -ERROR: cannot use first outside a DEFINE clause -LINE 1: SELECT first(val) FROM rpr_nav; - ^ -SELECT last(val) FROM rpr_nav; -ERROR: cannot use last outside a DEFINE clause -LINE 1: SELECT last(val) FROM rpr_nav; - ^ -SELECT first(val, 1) FROM rpr_nav; -ERROR: cannot use first outside a DEFINE clause -LINE 1: SELECT first(val, 1) FROM rpr_nav; - ^ -- Functional notation: should access column, not RPR navigation CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text); INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow'); diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 41541898f5a..cf158e1c043 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -1709,9 +1709,10 @@ WINDOW w AS ( B AS val > PREV(val) ) ORDER BY id; -ERROR: cannot use prev outside a DEFINE clause +ERROR: function prev(integer) does not exist LINE 1: SELECT PREV(id), id, val, COUNT(*) OVER w as cnt ^ +DETAIL: There is no function of that name. -- NEXT function cannot be used other than in DEFINE SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -1724,9 +1725,10 @@ WINDOW w AS ( B AS val > PREV(val) ) ORDER BY id; -ERROR: cannot use next outside a DEFINE clause +ERROR: function next(integer) does not exist LINE 1: SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt ^ +DETAIL: There is no function of that name. -- FIRST function - reference match_start row SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -1792,15 +1794,376 @@ ORDER BY id; -- FIRST function cannot be used other than in DEFINE SELECT FIRST(id), id, val FROM rpr_nav; -ERROR: cannot use first outside a DEFINE clause +ERROR: function first(integer) does not exist LINE 1: SELECT FIRST(id), id, val FROM rpr_nav; ^ +DETAIL: There is no function of that name. -- LAST function cannot be used other than in DEFINE SELECT LAST(id), id, val FROM rpr_nav; -ERROR: cannot use last outside a DEFINE clause +ERROR: function last(integer) does not exist LINE 1: SELECT LAST(id), id, val FROM rpr_nav; ^ +DETAIL: There is no function of that name. DROP TABLE rpr_nav; +-- Name-space: prev/next/first/last are navigation functions, not ordinary functions +CREATE SCHEMA rpr_navns; +SET search_path TO rpr_navns, public; +CREATE TABLE nt (g text, id int, val int); +INSERT INTO nt VALUES ('x', 1, 100), ('x', 2, 200), ('x', 3, 150), + ('x', 4, 140), ('x', 5, 150); +-- Outside DEFINE these are ordinary identifiers and resolve to nothing +SELECT prev(val) FROM nt; +ERROR: function prev(integer) does not exist +LINE 1: SELECT prev(val) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT next(val) FROM nt; +ERROR: function next(integer) does not exist +LINE 1: SELECT next(val) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT prev(val, 2) FROM nt; +ERROR: function prev(integer, integer) does not exist +LINE 1: SELECT prev(val, 2) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT next(val, 2) FROM nt; +ERROR: function next(integer, integer) does not exist +LINE 1: SELECT next(val, 2) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT first(val) FROM nt; +ERROR: function first(integer) does not exist +LINE 1: SELECT first(val) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT last(val) FROM nt; +ERROR: function last(integer) does not exist +LINE 1: SELECT last(val) FROM nt; + ^ +DETAIL: There is no function of that name. +SELECT first(val, 1) FROM nt; +ERROR: function first(integer, integer) does not exist +LINE 1: SELECT first(val, 1) FROM nt; + ^ +DETAIL: There is no function of that name. +-- A schema-qualified call is also a plain (failing) function lookup +SELECT pg_catalog.prev(val) FROM nt; +ERROR: function pg_catalog.prev(integer) does not exist +LINE 1: SELECT pg_catalog.prev(val) FROM nt; + ^ +-- Outside DEFINE, a user-defined function of that name is callable +CREATE FUNCTION next(numeric) RETURNS numeric AS 'SELECT -999::numeric' + LANGUAGE sql IMMUTABLE; +SELECT next(10); + next +------ + -999 +(1 row) + +-- Inside DEFINE, unqualified PREV is nav whether or not a user prev() exists +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; + id | val | cnt | last_id +----+-----+-----+--------- + 1 | 100 | 2 | 2 + 2 | 200 | 0 | + 3 | 150 | 0 | + 4 | 140 | 2 | 5 + 5 | 150 | 0 | +(5 rows) + +-- A qualified call invokes the function, so its volatility still matters +-- VOLATILE: unqualified is nav; qualified is rejected as a volatile function +CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' + LANGUAGE sql VOLATILE; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; + id | val | cnt | last_id +----+-----+-----+--------- + 1 | 100 | 2 | 2 + 2 | 200 | 0 | + 3 | 150 | 0 | + 4 | 140 | 2 | 5 + 5 | 150 | 0 | +(5 rows) + +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS rpr_navns.prev(val) = -999) + ORDER BY id; +ERROR: volatile functions are not allowed in DEFINE clause +LINE 6: DEFINE A AS rpr_navns.prev(val) = -999) + ^ +DROP FUNCTION prev(integer); +-- IMMUTABLE: unqualified is nav; qualified is the escape hatch and succeeds +CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' + LANGUAGE sql IMMUTABLE; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; + id | val | cnt | last_id +----+-----+-----+--------- + 1 | 100 | 2 | 2 + 2 | 200 | 0 | + 3 | 150 | 0 | + 4 | 140 | 2 | 5 + 5 | 150 | 0 | +(5 rows) + +-- (val).prev is attribute notation, so it calls the ordinary function prev(val) +-- (the IMMUTABLE user prev here), the same as the schema-qualified call below +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS (val).prev = -999) + ORDER BY id; + id | val | cnt | last_id +----+-----+-----+--------- + 1 | 100 | 5 | 5 + 2 | 200 | 0 | + 3 | 150 | 0 | + 4 | 140 | 0 | + 5 | 150 | 0 | +(5 rows) + +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS rpr_navns.prev(val) = -999) + ORDER BY id; + id | val | cnt | last_id +----+-----+-----+--------- + 1 | 100 | 5 | 5 + 2 | 200 | 0 | + 3 | 150 | 0 | + 4 | 140 | 0 | + 5 | 150 | 0 | +(5 rows) + +-- Zero or more than two arguments is an error, with no function fallback +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV() IS NULL); +ERROR: too few arguments for row pattern navigation function PREV +LINE 4: PATTERN (A+) DEFINE A AS PREV() IS NULL); + ^ +DETAIL: PREV takes a value expression and an optional offset argument. +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); +ERROR: too many arguments for row pattern navigation function PREV +LINE 4: PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); + ^ +DETAIL: PREV takes a value expression and an optional offset argument. +-- the error stands even when a user function of that exact arity exists +CREATE FUNCTION prev(integer, integer, integer) RETURNS integer + AS 'SELECT -999' LANGUAGE sql IMMUTABLE; +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); +ERROR: too many arguments for row pattern navigation function PREV +LINE 4: PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); + ^ +DETAIL: PREV takes a value expression and an optional offset argument. +DROP FUNCTION prev(integer, integer, integer); +-- Syntactic decoration is rejected +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(*) IS NULL); +ERROR: prev(*) specified, but prev is not an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(*) IS NULL); + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL); +ERROR: DISTINCT specified, but prev is not an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL); + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL); +ERROR: ORDER BY specified, but prev is not an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL)... + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) IS NULL); +ERROR: FILTER specified, but prev is not an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) I... + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER BY val) IS NULL); +ERROR: WITHIN GROUP specified, but prev is not an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER B... + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL); +ERROR: OVER specified, but prev is not a window function nor an aggregate function +LINE 4: PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL); + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NULL); +ERROR: cannot use VARIADIC with row pattern navigation function PREV +LINE 4: PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NU... + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS prev(x => val) IS NULL); +ERROR: row pattern navigation operations cannot use named arguments +LINE 4: PATTERN (A+) DEFINE A AS prev(x => val) IS NULL); + ^ +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL); +ERROR: only window functions accept RESPECT/IGNORE NULLS +LINE 4: PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL)... + ^ +-- Quoting does not escape: "prev" is nav, "PREV" is an ordinary name +SELECT id, val, count(*) OVER w AS cnt + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > "prev"(val)) + ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 100 | 2 + 2 | 200 | 0 + 3 | 150 | 0 + 4 | 140 | 2 + 5 | 150 | 0 +(5 rows) + +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL); +ERROR: function PREV(integer) does not exist +LINE 4: PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL); + ^ +DETAIL: There is no function of that name. +-- A view round-trips: bare PREV stays a navigation function, and a qualified +-- user prev() stays schema-qualified so it does not reparse as navigation +CREATE VIEW navns_nav AS + SELECT id, count(*) OVER w AS cnt FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) DEFINE START AS TRUE, UP AS val > PREV(val)); +CREATE VIEW navns_fn AS + SELECT id, count(*) OVER w AS cnt FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999); +SELECT pg_get_viewdef('navns_nav'); + pg_get_viewdef +--------------------------------------------------------------------------------------------- + SELECT id, + + count(*) OVER w AS cnt + + FROM nt + + WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (start up+) + + DEFINE + + start AS true, + + up AS (val > PREV(val)) ); +(1 row) + +SELECT pg_get_viewdef('navns_fn'); + pg_get_viewdef +--------------------------------------------------------------------------------------------- + SELECT id, + + count(*) OVER w AS cnt + + FROM nt + + WINDOW w AS (PARTITION BY g ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS (rpr_navns.prev(val) = '-999'::integer) ); +(1 row) + +DROP VIEW navns_nav, navns_fn; +-- Attribute notation is field selection only, never a function fallback +CREATE TYPE rpr_navns_pair AS (first int, last int); +CREATE TABLE ct (id int, p rpr_navns_pair); +INSERT INTO ct VALUES (1, (10, 20)), (2, (30, 40)); +SELECT (p).last FROM ct ORDER BY id; + last +------ + 20 + 40 +(2 rows) + +SELECT count(*) OVER w FROM ct + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS (p).last > 0); + count +------- + 2 + 0 +(2 rows) + +SELECT count(*) OVER w FROM ct + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS (p).prev > 0); +ERROR: column "prev" not found in data type rpr_navns_pair +LINE 4: PATTERN (A+) DEFINE A AS (p).prev > 0); + ^ +-- Navigation offset must not contain a navigation operation +SELECT id, val + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS PREV(val, FIRST(1)) > 0) + ORDER BY id; +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 6: DEFINE A AS PREV(val, FIRST(1)) > 0) + ^ +DETAIL: A navigation offset must be a run-time constant. +DROP SCHEMA rpr_navns CASCADE; +RESET search_path; -- ============================================================ -- SKIP TO / INITIAL Tests -- ============================================================ @@ -3655,6 +4018,127 @@ ERROR: cannot nest row pattern navigation more than two levels deep LINE 6: DEFINE A AS PREV(FIRST(PREV(v))) > 0 ^ HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +-- A navigation offset must be a run-time constant, not a navigation operation +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0); +ERROR: cannot nest row pattern navigation more than two levels deep +LINE 4: PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0)... + ^ +HINT: Only PREV(FIRST()), PREV(LAST()), NEXT(FIRST()), and NEXT(LAST()) compound forms are allowed. +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0); +ERROR: row pattern navigation offset cannot contain a row pattern navigation operation +LINE 4: PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0); + ^ +DETAIL: A navigation offset must be a run-time constant. +-- An unknown literal argument resolves to text; it must still reference a column +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV('foo') = 'bar'); +ERROR: argument of row pattern navigation operation must include at least one column reference +LINE 4: PATTERN (A+) DEFINE A AS PREV('foo') = 'bar'); + ^ +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV('foo')); +ERROR: argument of DEFINE must be type boolean, not type text +LINE 4: PATTERN (A+) DEFINE A AS PREV('foo')); + ^ +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL); +ERROR: argument of row pattern navigation operation must include at least one column reference +LINE 4: PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL); + ^ +PREPARE rpr_navarg AS SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV($1) IS NULL); +ERROR: argument of row pattern navigation operation must include at least one column reference +LINE 4: PATTERN (A+) DEFINE A AS PREV($1) IS NULL); + ^ +-- An int2 offset is coerced to int8 like any implicit cast (same as plain 0) +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, 0::smallint) = v); + count +------- + 5 + 0 + 0 + 0 + 0 +(5 rows) + +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, 0) = v); + count +------- + 5 + 0 + 0 + 0 + 0 +(5 rows) + -- ============================================================ -- Window Deduplication Tests -- ============================================================ diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index b15b30c85ac..e3e9de789db 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -444,12 +444,6 @@ WINDOW w AS ( -- Error cases: PREV/NEXT usage restrictions -- --- PREV outside DEFINE clause -SELECT prev(price) FROM stock; - --- NEXT outside DEFINE clause -SELECT next(price) FROM stock; - -- Nested PREV SELECT price FROM stock WINDOW w AS ( @@ -831,10 +825,6 @@ WINDOW w AS ( DEFINE A AS PREV(price, 0) = price ); --- 2-arg PREV/NEXT outside DEFINE clause -SELECT prev(price, 2) FROM stock; -SELECT next(price, 2) FROM stock; - -- 2-arg PREV/NEXT: negative offset SELECT company, tdate, price, first_value(price) OVER w FROM stock @@ -1124,11 +1114,6 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( DEFINE A AS LAST(val, -1) IS NULL ); --- FIRST/LAST outside DEFINE clause (error cases) -SELECT first(val) FROM rpr_nav; -SELECT last(val) FROM rpr_nav; -SELECT first(val, 1) FROM rpr_nav; - -- Functional notation: should access column, not RPR navigation CREATE TEMP TABLE rpr_names (prev int, next int, first text, last text); INSERT INTO rpr_names VALUES (1, 2, 'Joe', 'Blow'); diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index fcefd59de4a..e71f0dd3680 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1309,6 +1309,195 @@ SELECT LAST(id), id, val FROM rpr_nav; DROP TABLE rpr_nav; +-- Name-space: prev/next/first/last are navigation functions, not ordinary functions +CREATE SCHEMA rpr_navns; +SET search_path TO rpr_navns, public; +CREATE TABLE nt (g text, id int, val int); +INSERT INTO nt VALUES ('x', 1, 100), ('x', 2, 200), ('x', 3, 150), + ('x', 4, 140), ('x', 5, 150); + +-- Outside DEFINE these are ordinary identifiers and resolve to nothing +SELECT prev(val) FROM nt; +SELECT next(val) FROM nt; +SELECT prev(val, 2) FROM nt; +SELECT next(val, 2) FROM nt; +SELECT first(val) FROM nt; +SELECT last(val) FROM nt; +SELECT first(val, 1) FROM nt; +-- A schema-qualified call is also a plain (failing) function lookup +SELECT pg_catalog.prev(val) FROM nt; + +-- Outside DEFINE, a user-defined function of that name is callable +CREATE FUNCTION next(numeric) RETURNS numeric AS 'SELECT -999::numeric' + LANGUAGE sql IMMUTABLE; +SELECT next(10); + +-- Inside DEFINE, unqualified PREV is nav whether or not a user prev() exists +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; + +-- A qualified call invokes the function, so its volatility still matters +-- VOLATILE: unqualified is nav; qualified is rejected as a volatile function +CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' + LANGUAGE sql VOLATILE; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS rpr_navns.prev(val) = -999) + ORDER BY id; +DROP FUNCTION prev(integer); +-- IMMUTABLE: unqualified is nav; qualified is the escape hatch and succeeds +CREATE FUNCTION prev(integer) RETURNS integer AS 'SELECT -999' + LANGUAGE sql IMMUTABLE; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > PREV(val)) + ORDER BY id; +-- (val).prev is attribute notation, so it calls the ordinary function prev(val) +-- (the IMMUTABLE user prev here), the same as the schema-qualified call below +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS (val).prev = -999) + ORDER BY id; +SELECT id, val, count(*) OVER w AS cnt, last_value(id) OVER w AS last_id + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS rpr_navns.prev(val) = -999) + ORDER BY id; + +-- Zero or more than two arguments is an error, with no function fallback +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV() IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); +-- the error stands even when a user function of that exact arity exists +CREATE FUNCTION prev(integer, integer, integer) RETURNS integer + AS 'SELECT -999' LANGUAGE sql IMMUTABLE; +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val, 1, 2) IS NULL); +DROP FUNCTION prev(integer, integer, integer); + +-- Syntactic decoration is rejected +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(*) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(DISTINCT val) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val ORDER BY val) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) FILTER (WHERE true) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) WITHIN GROUP (ORDER BY val) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) OVER () IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(VARIADIC ARRAY[val]) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS prev(x => val) IS NULL); +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS PREV(val) IGNORE NULLS IS NULL); + +-- Quoting does not escape: "prev" is nav, "PREV" is an ordinary name +SELECT id, val, count(*) OVER w AS cnt + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) + DEFINE START AS TRUE, UP AS val > "prev"(val)) + ORDER BY id; +SELECT count(*) OVER w FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS "PREV"(val) IS NULL); + +-- A view round-trips: bare PREV stays a navigation function, and a qualified +-- user prev() stays schema-qualified so it does not reparse as navigation +CREATE VIEW navns_nav AS + SELECT id, count(*) OVER w AS cnt FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (START UP+) DEFINE START AS TRUE, UP AS val > PREV(val)); +CREATE VIEW navns_fn AS + SELECT id, count(*) OVER w AS cnt FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS rpr_navns.prev(val) = -999); +SELECT pg_get_viewdef('navns_nav'); +SELECT pg_get_viewdef('navns_fn'); +DROP VIEW navns_nav, navns_fn; + +-- Attribute notation is field selection only, never a function fallback +CREATE TYPE rpr_navns_pair AS (first int, last int); +CREATE TABLE ct (id int, p rpr_navns_pair); +INSERT INTO ct VALUES (1, (10, 20)), (2, (30, 40)); +SELECT (p).last FROM ct ORDER BY id; +SELECT count(*) OVER w FROM ct + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS (p).last > 0); +SELECT count(*) OVER w FROM ct + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) DEFINE A AS (p).prev > 0); + +-- Navigation offset must not contain a navigation operation +SELECT id, val + FROM nt + WINDOW w AS (PARTITION BY g ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL + PATTERN (A+) + DEFINE A AS PREV(val, FIRST(1)) > 0) + ORDER BY id; + +DROP SCHEMA rpr_navns CASCADE; +RESET search_path; + -- ============================================================ -- SKIP TO / INITIAL Tests -- ============================================================ @@ -2420,6 +2609,68 @@ WINDOW w AS ( DEFINE A AS PREV(FIRST(PREV(v))) > 0 ); +-- A navigation offset must be a run-time constant, not a navigation operation +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1)) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1) + 1) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, NEXT(1, 0)) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(FIRST(v), LAST(1)) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(v)) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS NEXT(v, PREV(v, 1)) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(FIRST(v, LAST(1)), 2) > 0); +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, FIRST(1::bigint)) > 0); + +-- An unknown literal argument resolves to text; it must still reference a column +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV('foo') = 'bar'); +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV('foo')); +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(NULL) IS NULL); +PREPARE rpr_navarg AS SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV($1) IS NULL); + +-- An int2 offset is coerced to int8 like any implicit cast (same as plain 0) +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, 0::smallint) = v); +SELECT count(*) OVER w +FROM generate_series(1,5) s(v) +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v, 0) = v); + -- ============================================================ -- Window Deduplication Tests -- ============================================================ -- 2.50.1 (Apple Git-155)