From 5023b19ccc8c8afa6d8e12b1707d7512d007d384 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 28 Sep 2026 15:53:21 +0900 Subject: [PATCH 04/10] Move DEFINE input tracking from parse analysis to the planner This commit makes the planner track the columns a DEFINE clause reads, and fixes several problems that go with it. 1. Tracking DEFINE inputs in the planner A DEFINE clause is evaluated by the WindowAgg but is not part of the target list, so the ordinary target list machinery never asks for the columns it reads. Until now parse analysis dealt with that by adding the Vars the clause reads to the query's target list as resjunk entries. That rewrote the user's target list, and it was not enough: the planner's copy of the clause can take a different shape from the one the parser saw. When a composite value arrives through a pulled-up subquery, eval_const_expressions() splits an IS [NOT] NULL test on it into one test per field. If the window's PARTITION BY or ORDER BY holds the same value, its sortgroupref keeps the input target from flattening it, so those fields never reached the WindowAgg input on their own and planning failed with "variable not found in subplan target list". Handle the clause the way havingQual is handled. - build_base_rel_tlists() marks what a DEFINE clause reads as needed, so the columns reach the top of the join tree. - make_window_input_target() adds to the window input target whatever the preprocessed clause reads that the target does not already offer. The walk stops at an expression the target computes whole, since setrefs.c resolves the DEFINE copy against that column; this is what keeps a DEFINE clause reading a GROUP BY expression working, whose underlying Vars the grouping step does not produce. - The parser's Var planting is removed, and with it the targetlist argument of transformRPR(). - Each DEFINE condition is preprocessed as a qual, as havingQual is. ExecInitWindowAgg() asserts that the DEFINE list is in the variable order buildRPRPattern() assigned. - In EXPLAIN VERBOSE, a column that only a DEFINE clause reads now appears on the WindowAgg's input but no longer on its output. 2. A DEFINE clause may spell a GROUP BY expression Under GROUP BY val + 1, one repeating val + 1 used to offer the bare val underneath it to the grouping check, which rejected it with "column ... must appear in the GROUP BY clause or be used in an aggregate function". It is accepted now, as a function call, a cast, a navigation argument or under a grouping set, while a bare column that is not grouped is still rejected. 3. No row pattern special cases in remove_unused_subquery_outputs() The function refused to replace an unused window function output whose window had a DEFINE clause, on the premise that such a window must still run, and it kept every bare-Var output that a DEFINE clause read. Neither is needed. select_active_windows() makes no such exception, so a row pattern window in a subquery that nothing reads is now removed along with its WindowAgg, as a plain window is. And a DEFINE clause reads a relation column of its own query level, never the subquery's output entry: with the input tracking above, replacing an unread output with NULL changes what the subquery returns, not what the pattern match sees. The function is back to its upstream form. Its DEFINE check also failed with "Upper-level Var found where not expected", or the PlaceHolderVar counterpart, when inlining a SQL function called in LATERAL had planted an outer reference in a DEFINE clause; that failure goes with it. 4. Emptying the DEFINE clause of a window that will not run grouping_planner() now empties the DEFINE clause of every window clause that will not run, right after select_active_windows(); for a subquery this happens when the subquery is planned, before its own join removal. Emptying the clause, rather than teaching later passes to skip it, matters because query_tree_walker() visits defineClause and join removal requires that no Var of a removed relation remain anywhere in the tree; an outer join to a relation only such a clause reads can now be removed. Only defineClause is cleared: winref indexes windowClause, so the clause itself must stay, and rpPattern is what marks it as a row pattern window, which is how find_window_run_conditions() and optimize_window_clauses() now recognize one. A comment in query_tree_walker() records the rule. 5. No row-independent value in a navigation argument The argument of PREV/NEXT/FIRST/LAST is evaluated at the row the navigation lands on, but pulling up a one-row VALUES list, a subquery or a function RTE that folded to a constant replaced the column with that value, and constant folding could then raise an error at plan time that execution would never reach, as with PREV(v / 0) when there is no previous row. replace_rte_variables_mutator() now flags the argument of an RPRNavExpr, and pullup_replace_vars_callback() treats a replacement made there as needing a PlaceHolderVar, so one that does not read the row is wrapped instead of folded in; a replacement that still reads the row is left as it is. 6. Crash on a navigation offset A navigation offset spelled like an expression the window input already carries, such as a window ORDER BY key or a GROUP BY expression, crashed. fix_upper_expr() replaced the offset with a reference to that input column, and the executor, which resolves offsets once before any input row is read, dereferenced a null slot. fix_upper_expr_mutator() now handles RPRNavExpr itself, fixing the offsets with fix_scan_expr() as set_plan_refs() does for the WindowAgg frame offsets. rpr_base and rpr_integration gain tests for each of the above. Author: Henson Choi Author: jian he --- src/backend/executor/nodeWindowAgg.c | 12 + src/backend/nodes/nodeFuncs.c | 9 + src/backend/optimizer/path/allpaths.c | 92 +-- src/backend/optimizer/plan/initsplan.c | 29 + src/backend/optimizer/plan/planner.c | 122 ++- src/backend/optimizer/plan/setrefs.c | 22 + src/backend/optimizer/prep/prepjointree.c | 8 +- src/backend/parser/parse_clause.c | 2 +- src/backend/parser/parse_rpr.c | 55 +- src/backend/rewrite/rewriteManip.c | 27 + src/include/parser/parse_rpr.h | 2 +- src/include/rewrite/rewriteManip.h | 1 + src/test/regress/expected/rpr_base.out | 556 +++++++++++++- src/test/regress/expected/rpr_integration.out | 699 ++++++++++++++++-- src/test/regress/sql/rpr_base.sql | 360 ++++++++- src/test/regress/sql/rpr_integration.sql | 380 ++++++++-- 16 files changed, 2112 insertions(+), 264 deletions(-) diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 7b106c9d743..615bff94029 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -3083,6 +3083,18 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) { ExprState *exprstate; + /* + * That index is established in buildRPRPattern() and consumed + * here, with nothing in between checking it. Every step that + * touches the list preserves its order today, but a reorder would + * evaluate one variable's search condition for another and give a + * wrong answer with nothing to show for it, so check the name the + * pattern holds for this position against the entry's own. + */ + Assert(foreach_current_index(te) < node->rpPattern->numVars); + Assert(strcmp(node->rpPattern->varNames[foreach_current_index(te)], + te->resname) == 0); + exprstate = ExecInitQual(make_ands_implicit(te->expr), (PlanState *) winstate); winstate->defineClauseExprs = diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index a290a1aabf1..aab609f406e 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2795,6 +2795,15 @@ query_tree_walker_impl(Query *query, /* * But we need to walk the expressions under WindowClause nodes even * if we're not interested in SortGroupClause nodes. + * + * Note that defineClause (row pattern recognition) is an expression + * tree owned by the window clause itself, not a reference into the + * targetlist the way partitionClause and orderClause are. Every + * Query-wide walker and rewriter therefore reaches its Vars and must + * be able to treat them as live. Whoever decides that a window + * clause will not be executed is responsible for emptying + * defineClause at that moment, rather than expecting later scans to + * skip it. */ ListCell *lc; diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index f08a47e705a..07596d5dd75 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -2458,14 +2458,14 @@ find_window_run_conditions(Query *subquery, AttrNumber attno, wfunc->winref - 1); /* - * If a DEFINE clause exists, we cannot push down a run condition. In the - * case, a window partition (or frame) is divided into multiple reduced - * frames and each frame should be evaluated to the end of the partition - * (or full frame end). This means we cannot apply the run condition - * optimization because it stops evaluation window functions in certain - * cases. + * If this is a row pattern recognition window, we cannot push down a run + * condition. In the case, a window partition (or frame) is divided into + * multiple reduced frames and each frame should be evaluated to the end + * of the partition (or full frame end). This means we cannot apply the + * run condition optimization because it stops evaluation window functions + * in certain cases. */ - if (wclause->defineClause != NIL) + if (wclause->rpPattern != NULL) return false; req.type = T_SupportRequestWFuncMonotonic; @@ -4919,84 +4919,6 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, if (contain_volatile_functions(texpr)) continue; - /* - * If any RPR (Row Pattern Recognition) window clause references this - * column in its DEFINE clause, don't remove it. The DEFINE - * expression needs these columns in the tuplestore slot for pattern - * matching evaluation, even if the outer query doesn't reference - * them. - */ - if (IsA(texpr, Var)) - { - Var *var = (Var *) texpr; - bool needed_by_define = false; - - foreach_node(WindowClause, wc, subquery->windowClause) - { - if (wc->defineClause != NIL) - { - /* - * flags == 0 is safe: DEFINE rejects aggregates, window - * functions and subqueries at parse time, and this runs - * before any PlaceHolderVar could be planted. - */ - List *vars = pull_var_clause((Node *) wc->defineClause, 0); - - foreach_node(Var, dvar, vars) - { - - /* - * Match varno as well as varattno: a Var pulled from - * a DEFINE clause can share an attribute number with - * an unrelated output column of a different relation, - * which would otherwise be over-retained. Checking - * varlevelsup is just paranoia, since outer - * references in DEFINE are rejected during parse - * analysis. - */ - if (dvar->varno == var->varno && - dvar->varattno == var->varattno && - dvar->varlevelsup == var->varlevelsup) - { - needed_by_define = true; - break; - } - } - list_free(vars); - if (needed_by_define) - break; - } - } - if (needed_by_define) - continue; - } - - /* - * If it's a window function referencing a window clause with RPR, - * don't remove it. Even when the window function result is unused by - * the outer query, the RPR pattern matching (frame reduction via - * DEFINE/PATTERN) must still execute. Replacing this with NULL would - * leave no active window functions for the WindowClause, causing the - * planner to omit the WindowAgg node entirely. - */ - if (IsA(texpr, WindowFunc)) - { - bool is_rpr = false; - WindowFunc *wfunc = (WindowFunc *) texpr; - - foreach_node(WindowClause, wc, subquery->windowClause) - { - if (wc->winref == wfunc->winref && wc->defineClause != NIL) - { - is_rpr = true; - break; - } - } - - if (is_rpr) - continue; - } - /* * OK, we don't need it. Replace the expression with a NULL constant. * Preserve the exposed type of the expression, in case something diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 199e5d0a580..6994c5d478c 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -280,6 +280,35 @@ build_base_rel_tlists(PlannerInfo *root, List *final_tlist) list_free(having_vars); } } + + /* + * A row pattern DEFINE clause is not in the target list, so nothing above + * has asked for the columns it reads. The WindowAgg evaluates it all the + * same, and setrefs.c has to resolve it against the window's input, so + * mark those columns needed here and let them propagate up through the + * join steps the way the target list's own columns do. + */ + foreach_node(WindowClause, wc, root->parse->windowClause) + { + List *define_vars; + + if (wc->defineClause == NIL) + continue; + + /* + * PVC_INCLUDE_PLACEHOLDERS is the only flag needed: DEFINE rejects + * aggregates, window functions and subqueries at parse time. + */ + define_vars = pull_var_clause((Node *) wc->defineClause, + PVC_INCLUDE_PLACEHOLDERS); + + if (define_vars != NIL) + { + add_vars_to_targetlist(root, define_vars, + bms_make_singleton(0)); + list_free(define_vars); + } + } } /* diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 145ffc76d40..f84815bf1a3 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -246,6 +246,7 @@ static void optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists); static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists); static void name_active_windows(List *activeWindows); +static bool add_define_inputs_walker(Node *node, PathTarget *input_target); static PathTarget *make_window_input_target(PlannerInfo *root, PathTarget *final_target, List *activeWindows); @@ -1044,9 +1045,16 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, EXPRKIND_LIMIT); wc->endOffset = preprocess_expression(root, wc->endOffset, EXPRKIND_LIMIT); - wc->defineClause = (List *) preprocess_expression(root, - (Node *) wc->defineClause, - EXPRKIND_TARGET); + foreach_node(TargetEntry, tle, wc->defineClause) + { + List *qual; + + qual = (List *) preprocess_expression(root, + (Node *) tle->expr, + EXPRKIND_QUAL); + + tle->expr = make_ands_explicit(qual); + } /* * Reject volatile expressions in an RPR DEFINE clause. This is done @@ -1942,6 +1950,36 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, parse->hasWindowFuncs = false; } + /* + * Empty the DEFINE clause of every window clause that will not be + * executed. Whoever settles that a window clause is not executed is + * responsible for this: build_base_rel_tlists() marks what a DEFINE + * clause reads as needed at relation 0, and a column needed by + * nothing that runs keeps an outer join from being removed. A + * Query-wide rewriter cannot tell a dead window's Vars from a live + * one's either: join removal deletes a relid from the whole parse + * tree with ChangeVarNodes(..., INVALID_VAR, ...) once nothing needs + * the relation, which requires that no ordinary Var of it be left + * anywhere. This runs for a subquery too, when the subquery is + * planned, and so before its own query_planner() removes any join. + * + * Only defineClause is cleared, not the window clause itself: winref + * is a one-based index into windowClause, so the list must keep its + * length and its order. rpPattern stays too. It holds no Vars, a + * pattern variable with no definition means TRUE, and it is what + * marks the clause as a row pattern window. + * + * This covers a window clause no window function names, and equally a + * query whose window functions were all folded away above, where + * activeWindows stays empty. + */ + foreach_node(WindowClause, wc, parse->windowClause) + { + if (wc->defineClause != NIL && + !list_member_ptr(activeWindows, wc)) + wc->defineClause = NIL; + } + /* * Preprocess MIN/MAX aggregates, if any. Note: be careful about * adding logic between here and the query_planner() call. Anything @@ -6159,11 +6197,11 @@ optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists) continue; /* - * If a DEFINE clause exists, do not let support functions replace the - * frame with a non-RPR-compatible one. RPR windows require ROWS - * BETWEEN CURRENT ROW AND ... + * Do not let support functions replace the frame of a row pattern + * recognition window with a non-RPR-compatible one. RPR windows + * require ROWS BETWEEN CURRENT ROW AND ... */ - if (wc->defineClause != NIL) + if (wc->rpPattern != NULL) continue; foreach(lc2, wflists->windowFuncs[wc->winref]) @@ -6464,6 +6502,59 @@ common_prefix_cmp(const void *a, const void *b) return 0; } +/* + * add_define_inputs_walker + * Add to a WindowAgg's input target whatever a DEFINE clause reads that + * the target does not offer yet. + * + * This is the window's counterpart of the HAVING handling a few functions up: + * build_base_rel_tlists() marks the columns needed so they reach the top of + * the join tree, and the node's own input target has to ask for them again + * because the upper planner projects through explicit targets rather than + * propagating attr_needed. make_group_input_target() does the same for + * havingQual. + * + * The walk stops at any expression the target already computes whole, since + * setrefs.c resolves the DEFINE copy of it against that column. Stopping + * matters rather than merely saving work: under GROUP BY the Vars underneath + * a grouping expression are not available on their own, so descending into + * one would ask the grouping step for a column it cannot produce. This is + * the one rule HAVING does not need, the Agg's input target sitting below + * the grouping step rather than above it. + */ +static bool +add_define_inputs_walker(Node *node, PathTarget *input_target) +{ + if (node == NULL) + return false; + + /* + * XXX this list_member() match can miss a DEFINE expression that is + * semantically the same as one already in input_target, producing a + * redundant column below the WindowAgg. Seen with a join-alias + * expression exposed through a pulled-up subquery: flattening it once for + * the target list and once for this DEFINE clause each wraps a fresh, + * independently-numbered PlaceHolderVar around an otherwise identical + * copy (make_placeholder_expr() never checks for an existing equivalent + * one), and PlaceHolderVar's equal() compares that number, not the + * wrapped expression, so the two never match. Wasteful, not known to be + * incorrect. Not specific to DEFINE -- make_group_input_target() can + * build the same kind of duplicate for GROUP BY/HAVING with no RPR + * involved. + */ + if (list_member(input_target->exprs, node)) + return false; + + if (IsA(node, Var) || IsA(node, PlaceHolderVar)) + { + add_new_column_to_pathtarget(input_target, (Expr *) node); + return false; + } + + return expression_tree_walker(node, add_define_inputs_walker, + input_target); +} + /* * make_window_input_target * Generate appropriate PathTarget for initial input to WindowAgg nodes. @@ -6597,6 +6688,23 @@ make_window_input_target(PlannerInfo *root, PVC_INCLUDE_PLACEHOLDERS); add_new_columns_to_pathtarget(input_target, flattenable_vars); + /* + * A row pattern DEFINE clause is evaluated by the WindowAgg itself, so + * everything it reads has to reach this target too. Nothing above has a + * reason to put it here: DEFINE is not part of the query's final target + * list, and the window's own PARTITION BY/ORDER BY entries are added + * whole, which does not make the Vars inside them available separately. + * Add what is missing now, once the clause has the shape it will be + * executed with. + */ + foreach(lc, activeWindows) + { + WindowClause *wc = lfirst_node(WindowClause, lc); + + if (wc->defineClause != NIL) + add_define_inputs_walker((Node *) wc->defineClause, input_target); + } + /* clean up cruft */ list_free(flattenable_vars); list_free(flattenable_cols); diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 9dc191ad68e..2ee3c8baac2 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -3403,6 +3403,28 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context) /* XXX can we assert something about phnullingrels? */ return fix_upper_expr_mutator((Node *) phv->phexpr, context); } + if (IsA(node, RPRNavExpr)) + { + RPRNavExpr *nav = (RPRNavExpr *) node; + RPRNavExpr *newnav = makeNode(RPRNavExpr); + + memcpy(newnav, nav, sizeof(RPRNavExpr)); + + /* + * The offsets are resolved once per scan, before the outer slot is + * set, so they cannot reference it the way arg does. Same treatment + * as the WindowAgg frame offsets. + */ + newnav->arg = (Expr *) + fix_upper_expr_mutator((Node *) nav->arg, context); + newnav->offset_arg = (Expr *) + fix_scan_expr(context->root, (Node *) nav->offset_arg, + context->rtoffset, context->num_exec); + newnav->compound_offset_arg = (Expr *) + fix_scan_expr(context->root, (Node *) nav->compound_offset_arg, + context->rtoffset, context->num_exec); + return (Node *) newnav; + } /* Try matching more complex expressions too, if tlist has any */ if (context->subplan_itlist->has_non_vars) { diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 8ca14e6bed3..cc675042f49 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -2805,9 +2805,15 @@ pullup_replace_vars_callback(const Var *var, * a Var or PlaceHolderVar that we can just add the nullingrels to). We * also need one if the caller has instructed us that certain expression * replacements need to be wrapped for identification purposes. + * + * A Var below the argument of a row pattern navigation operation needs + * one too, so that a replacement that does not depend on the row is not + * folded in: that argument reads the row the navigation lands on, not + * this one. */ need_phv = (var->varnullingrels != NULL) || - (rcon->wrap_option != REPLACE_WRAP_NONE); + (rcon->wrap_option != REPLACE_WRAP_NONE) || + context->in_rpr_nav_arg; /* * If PlaceHolderVars are needed, we cache the modified expressions in diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 3a0210b652e..16217f0ac9f 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -2962,7 +2962,7 @@ transformWindowDefinitions(ParseState *pstate, windef->endOffset); /* Process Row Pattern Recognition related clauses */ - transformRPR(pstate, wc, windef, targetlist); + transformRPR(pstate, wc, windef); wc->winref = winref; diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index e10260f2f7c..81cd3736341 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -53,8 +53,7 @@ typedef struct /* Forward declarations */ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node, List **varNames); -static List *transformDefineClause(ParseState *pstate, WindowDef *windef, - List **targetlist); +static List *transformDefineClause(ParseState *pstate, WindowDef *windef); static bool define_walker(Node *node, void *context); static bool rpr_frame_is_supported(int frameOptions); @@ -72,8 +71,7 @@ static bool rpr_frame_is_supported(int frameOptions); * Returns early if windef has no rpCommonSyntax (non-RPR window). */ void -transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, - List **targetlist) +transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef) { /* Nothing to do unless the window carries a row pattern */ if (windef->rpCommonSyntax == NULL) @@ -119,7 +117,7 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo; /* Transform DEFINE clause into list of TargetEntry's */ - wc->defineClause = transformDefineClause(pstate, windef, targetlist); + wc->defineClause = transformDefineClause(pstate, windef); /* Store PATTERN parse tree for deparsing */ wc->rpPattern = windef->rpCommonSyntax->rpPattern; @@ -238,8 +236,7 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node, * parse_expr.c via the p_rpr_pattern_vars check. */ static List * -transformDefineClause(ParseState *pstate, WindowDef *windef, - List **targetlist) +transformDefineClause(ParseState *pstate, WindowDef *windef) { List *defineClause = NIL; List *patternVarNames = NIL; @@ -317,16 +314,12 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, { TargetEntry *teDefine; Node *expr; - List *vars; /* - * Transform the DEFINE expression and coerce it to boolean. We must - * NOT add the whole expression to the query targetlist, because it - * may contain RPRNavExpr nodes (PREV/NEXT/FIRST/LAST) that can only - * be evaluated inside the owning WindowAgg. Coercing here, before - * pull_var_clause, keeps pull_var_clause operating on the final - * expression form and surfaces a type mismatch before the targetlist - * is touched. + * Transform the DEFINE expression and coerce it to boolean. The + * result belongs in wc->defineClause, never in the query targetlist + * as a whole: it may contain RPRNavExpr nodes (PREV/NEXT/FIRST/LAST) + * that only the owning WindowAgg can evaluate. */ expr = transformExpr(pstate, restarget->val, EXPR_KIND_RPR_DEFINE); @@ -340,38 +333,6 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, /* build transformed DEFINE clause (list of TargetEntry) */ defineClause = lappend(defineClause, teDefine); - - /* - * Pull out Var nodes from the transformed expression and ensure each - * one is present in the targetlist. This is needed so the planner - * propagates the referenced columns through the plan tree, making - * them available to the WindowAgg's DEFINE evaluation. - */ - vars = pull_var_clause(expr, 0); - foreach_node(Var, var, vars) - { - bool found = false; - - foreach_node(TargetEntry, tle, *targetlist) - { - if (equal(tle->expr, var)) - { - found = true; - break; - } - } - if (!found) - { - TargetEntry *newtle; - - newtle = makeTargetEntry((Expr *) copyObject(var), - (AttrNumber) pstate->p_next_resno++, - NULL, - true); - *targetlist = lappend(*targetlist, newtle); - } - } - list_free(vars); } pstate->p_rpr_define = false; pstate->p_rpr_pattern_vars = NIL; diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c index 7011275f8c6..0caac225982 100644 --- a/src/backend/rewrite/rewriteManip.c +++ b/src/backend/rewrite/rewriteManip.c @@ -1449,6 +1449,7 @@ replace_rte_variables(Node *node, int target_varno, int sublevels_up, context.callback_arg = callback_arg; context.target_varno = target_varno; context.sublevels_up = sublevels_up; + context.in_rpr_nav_arg = false; /* * We try to initialize inserted_sublink to true if there is no need to @@ -1507,6 +1508,32 @@ replace_rte_variables_mutator(Node *node, } /* otherwise fall through to copy the var normally */ } + else if (IsA(node, RPRNavExpr)) + { + /* + * The argument of a row pattern navigation operation is evaluated at + * the row the navigation lands on, so flag it for the callback. The + * offsets beside it are ordinary expressions. + */ + RPRNavExpr *nav = (RPRNavExpr *) node; + RPRNavExpr *newnode = makeNode(RPRNavExpr); + bool save_in_rpr_nav_arg = context->in_rpr_nav_arg; + + memcpy(newnode, nav, sizeof(RPRNavExpr)); + + context->in_rpr_nav_arg = true; + newnode->arg = (Expr *) + replace_rte_variables_mutator((Node *) nav->arg, context); + context->in_rpr_nav_arg = save_in_rpr_nav_arg; + + newnode->offset_arg = (Expr *) + replace_rte_variables_mutator((Node *) nav->offset_arg, context); + newnode->compound_offset_arg = (Expr *) + replace_rte_variables_mutator((Node *) nav->compound_offset_arg, + context); + + return (Node *) newnode; + } else if (IsA(node, Query)) { /* Recurse into RTE subquery or not-yet-planned sublink subquery */ diff --git a/src/include/parser/parse_rpr.h b/src/include/parser/parse_rpr.h index 7fab6f292aa..ff5037e585e 100644 --- a/src/include/parser/parse_rpr.h +++ b/src/include/parser/parse_rpr.h @@ -17,6 +17,6 @@ #include "parser/parse_node.h" extern void transformRPR(ParseState *pstate, WindowClause *wc, - WindowDef *windef, List **targetlist); + WindowDef *windef); #endif /* PARSE_RPR_H */ diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h index 2d15d8f8f95..a8af1820477 100644 --- a/src/include/rewrite/rewriteManip.h +++ b/src/include/rewrite/rewriteManip.h @@ -32,6 +32,7 @@ struct replace_rte_variables_context int target_varno; /* RTE index to search for */ int sublevels_up; /* (current) nesting depth */ bool inserted_sublink; /* have we inserted a SubLink? */ + bool in_rpr_nav_arg; /* below a row pattern navigation argument? */ }; typedef enum ReplaceVarsNoMatchOption diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index adcdcaffd7f..83e653241a6 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -2136,6 +2136,29 @@ SELECT id, count(*) OVER w AS cnt FROM rpr_nav t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS NEXT(val / 0) > 0); ERROR: division by zero +-- A constant subexpression of the argument is folded away, so the pattern +-- does not recompute it per row. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(val + 2 * 3) > 0); + id | cnt +----+----- + 1 | 0 + 2 | 4 + 3 | 0 + 4 | 0 + 5 | 0 +(5 rows) + +-- Folding a constant subexpression can raise where the whole argument would +-- not have: unlike val / 0 above, 1 / 0 does not depend on the row, so it is +-- reached at plan time even on the row PREV misses on. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav t +WHERE id = 1 +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(val + 1 / 0) > 0); +ERROR: division by zero -- Here the null reaches the DEFINE predicate itself instead of an IS NULL -- An all-NULL target row would have made v IS NULL true and matched the -- first row, so this pins the predicate side of the same behaviour. @@ -2149,9 +2172,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTER 2 | 0 (2 rows) --- Constant folding can leave a navigation argument with no column reference --- at all (v folds to 10, so PREV(v IS NULL) becomes PREV(false)), which the --- planner has to accept rather than re-run the parse-time rejection. +-- Pulling up the VALUES substitutes 10 for v, which is not what the column +-- stood for under a navigation: the argument reads the row the navigation +-- lands on, not this one. The replacement is wrapped in a PlaceHolderVar +-- rather than folded through. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t @@ -2161,14 +2185,191 @@ WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE 1 | 0 (1 row) --- XXX Folding evaluates the argument while planning, with the current row's --- value standing in for the target row's, so this divides by zero even though --- PREV has no row to navigate to. +-- That wrapping is what keeps this one from raising: the divisor is constant +-- but the dividend is not folded through, so the division stands until +-- execution, where PREV has no row to navigate to and never reaches it. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); + id | cnt +----+----- + 1 | 0 +(1 row) + +-- A pulled-up subquery and a function RTE that folded to a constant reach a +-- navigation argument the same way, so both are wrapped as well. +SELECT id, count(*) OVER w AS cnt +FROM (SELECT 1 AS id, 10 AS v) t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); + id | cnt +----+----- + 1 | 0 +(1 row) + +SELECT count(*) OVER w AS cnt +FROM abs(-10) AS v +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); + cnt +----- + 0 +(1 row) + +-- Only the argument is protected. One level outside the navigation the same +-- column is replaced and folded as it is anywhere else, and the division +-- raises at plan time -- as it does for the same WHERE clause over the same +-- one-row VALUES, with no pattern in sight. +WITH t(id, v) AS (VALUES (1, 10)) +SELECT id, count(*) OVER w AS cnt +FROM t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS v / 0 > 0); ERROR: division by zero +-- A replacement that still depends on the row is left unwrapped, because it +-- is what the column meant at whichever row the navigation lands on. +SELECT id, count(*) OVER w AS cnt +FROM (SELECT id, val + 1 AS v FROM rpr_nav) t +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v) > 0); + id | cnt +----+----- + 1 | 0 + 2 | 4 + 3 | 0 + 4 | 0 + 5 | 0 +(5 rows) + +-- Nesting: the inner navigation's argument is below the outer one's, so the +-- column there is wrapped too. +WITH t(id, v) AS (VALUES (1, 10)) +SELECT id, count(*) OVER w AS cnt +FROM t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) DEFINE A AS PREV(LAST(v / 0, 1), 2) > 0); + id | cnt +----+----- + 1 | 0 +(1 row) + +-- eval_const_expressions() must perform a few rewrites on every expression +-- it is handed -- a CollateExpr becomes a RelabelType, named arguments become +-- positional, omitted defaults are inserted -- and preprocess_expression() +-- documents them as mandatory, not as optimizations. Each of the three below +-- reaches the executor only if those rewrites reach inside a navigation +-- argument, and each returns what the same expression one level outside the +-- navigation returns. +CREATE TABLE rpr_nav_txt (id int, s text); +INSERT INTO rpr_nav_txt VALUES (1, 'b'), (2, 'c'), (3, 'a'); +CREATE FUNCTION rpr_nav_named(a int, b int) RETURNS int + LANGUAGE sql IMMUTABLE AS 'SELECT $1 * 10 + $2'; +CREATE FUNCTION rpr_nav_dflt(a int, b int DEFAULT 100) RETURNS int + LANGUAGE sql IMMUTABLE AS 'SELECT $2'; +-- COLLATE under a navigation: the executor has no CollateExpr step, so the +-- RelabelType rewrite has to reach here. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav_txt +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(s COLLATE "C") > 'a'); + id | cnt +----+----- + 1 | 0 + 2 | 2 + 3 | 0 +(3 rows) + +-- Named arguments under a navigation: the executor has no NamedArgExpr step. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(rpr_nav_named(b => 7, a => val)) > 0); + id | cnt +----+----- + 1 | 0 + 2 | 4 + 3 | 0 + 4 | 0 + 5 | 0 +(5 rows) + +-- An omitted default under a navigation: without the insertion the call is +-- initialized with one fewer argument than the callee reads. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(rpr_nav_dflt(val)) = 100); + id | cnt +----+----- + 1 | 0 + 2 | 4 + 3 | 0 + 4 | 0 + 5 | 0 +(5 rows) + +DROP FUNCTION rpr_nav_dflt(int, int); +DROP FUNCTION rpr_nav_named(int, int); +DROP TABLE rpr_nav_txt; +-- A navigation offset is resolved once at the top of the scan, before any +-- input row has been read, so it must not be matched to the window input the +-- way the navigated argument is. These two spell the offset the same as a +-- window ORDER BY key and as a GROUP BY expression, which is what makes the +-- match available. +CREATE TABLE rpr_navoff (id int, val int); +INSERT INTO rpr_navoff VALUES (1, 10), (2, 20), (3, 15), (4, 30), (5, 5); +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 1))); + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 30 | 0 + 5 | 5 | 0 +(5 rows) + +-- Control: an offset that matches nothing in the window input. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 2))); + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 2 | 20 | 3 + 3 | 15 | 0 + 4 | 30 | 0 + 5 | 5 | 0 +(5 rows) + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +GROUP BY GROUPING SETS ((id, val, ((random() * 0)::bigint + 1)), + (id, ((random() * 0)::bigint + 1))) +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (random() * 0)::bigint + 1)) +ORDER BY id, val; + id | val | cnt +----+-----+----- + 1 | 10 | 0 + 1 | | 0 + 2 | 20 | 0 + 2 | | 0 + 3 | 15 | 0 + 3 | | 0 + 4 | 30 | 0 + 4 | | 0 + 5 | 5 | 0 + 5 | | 0 +(10 rows) + +DROP TABLE rpr_navoff; -- PREV function - reference previous row in pattern SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -2479,13 +2680,22 @@ ORDER BY id; 5 (5 rows) --- ERROR: OFFSET 0 keeps the subquery, so its DEFINE is checked +-- OFFSET 0 keeps the subquery, but still no OVER references the window, so +-- the planner withdraws the DEFINE clause of a window it will not run and the +-- check finds nothing left to reject SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS random() > 0.5) OFFSET 0) sub; ERROR: DEFINE clause cannot contain volatile functions +-- ERROR: a subquery window that does run keeps its DEFINE, so it is checked +SELECT id, c FROM ( + SELECT id, count(*) OVER w AS c FROM nt + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS random() > 0.5)) sub; +ERROR: DEFINE clause cannot contain volatile functions -- WHERE false makes the subquery rel dummy, so the planner never plans it -- and nothing looks at its DEFINE SELECT id FROM ( @@ -4849,6 +5059,95 @@ WINDOW w AS ( DROP TABLE rpr_composite; DROP TYPE rpr_item; +-- A composite value that reaches DEFINE by way of a subquery Var only takes +-- its ROW(...) shape after pullup, and the ORDER BY copy's sortgroupref +-- keeps it from being flattened. make_window_input_target() adds the fields +-- the split leaves behind. +CREATE TABLE rpr_ordrow (a int, b int); +INSERT INTO rpr_ordrow SELECT g, g % 4 FROM generate_series(1, 10) g; +SELECT count(*) OVER w AS c +FROM (SELECT ROW(a, b) AS x FROM rpr_ordrow) s +WINDOW w AS (ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL); + c +---- + 10 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 +(10 rows) + +-- Control: without ORDER BY, x is flattened normally and this succeeds too. +SELECT count(*) OVER w AS c +FROM (SELECT ROW(a, b) AS x FROM rpr_ordrow) s +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL); + c +---- + 10 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 +(10 rows) + +DROP TABLE rpr_ordrow; +-- The same split by way of a pulled-up composite target, both as a plain +-- subquery and as a view. +CREATE TABLE rpr_partrow (a int, b int); +INSERT INTO rpr_partrow VALUES (1, 1), (2, 2), (3, 3); +SELECT count(*) OVER w +FROM (SELECT b, row(a, 1) AS k FROM rpr_partrow) s +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (p q+) DEFINE q AS k IS NOT NULL); + count +------- + 0 + 0 + 0 +(3 rows) + +CREATE TYPE rpr_partrow_t AS (x int, y int); +CREATE VIEW rpr_partrow_v AS SELECT b, row(a, 1)::rpr_partrow_t AS k FROM rpr_partrow; +SELECT count(*) OVER w FROM rpr_partrow_v +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (p q+) DEFINE q AS k IS NOT NULL); + count +------- + 0 + 0 + 0 +(3 rows) + +-- Control: PATTERN/DEFINE aside, the same window clause runs fine. +SELECT count(*) OVER w +FROM (SELECT b, row(a, 1) AS k FROM rpr_partrow) s +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + count +------- + 1 + 1 + 1 +(3 rows) + +DROP VIEW rpr_partrow_v; +DROP TYPE rpr_partrow_t; +DROP TABLE rpr_partrow; -- ERROR: undefined column in DEFINE SELECT COUNT(*) OVER w FROM rpr_err @@ -8178,6 +8477,109 @@ ORDER BY k; (3 rows) DROP TABLE rpr_join5, rpr_join6; +-- A DEFINE clause reading a USING column whose two sides differ in typmod. +-- The merged column stays a join alias Var, pullup leaves its joinaliasvars +-- entry a non-trivial expression, and the outer join's nullingrels wrap that +-- in a PlaceHolderVar. The target list copy and the DEFINE copy are wrapped +-- by separate calls, so their phids differ and equal() does not match them -- +-- the window input has to carry the DEFINE clause's own PlaceHolderVar. +CREATE TABLE rpr_phv_src (n int); +CREATE TABLE rpr_phv_dim (c varchar(10), tdate date); +CREATE TABLE rpr_phv_out (k varchar); +INSERT INTO rpr_phv_src VALUES (2), (4); +INSERT INTO rpr_phv_dim VALUES ('zz', '2024-01-01'), ('zzzz', '2024-01-02'); +INSERT INTO rpr_phv_out VALUES ('zz'), ('zzzz'); +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(5) AS c FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o1.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + c | tdate | cnt +------+------------+----- + zz | 01-01-2024 | 2 + zzzz | 01-02-2024 | 0 +(2 rows) + +-- The same with one more join level above it. Reaching the window input is +-- not enough on its own: an intermediate join emits only what something above +-- has declared a need for, so what a DEFINE clause reads is marked needed at +-- relation 0 the way the target list's own columns are. +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN rpr_phv_out o2 ON o1.k = o2.k + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(5) AS c FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o2.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + c | tdate | cnt +------+------------+----- + zz | 01-01-2024 | 2 + zzzz | 01-02-2024 | 0 +(2 rows) + +-- Control: with both sides of USING at the same typmod the merged column is a +-- plain Var of one side, no PlaceHolderVar is built, and neither shape above +-- needs any of this. +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN rpr_phv_out o2 ON o1.k = o2.k + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(10) AS c + FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o2.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + c | tdate | cnt +------+------------+----- + zz | 01-01-2024 | 2 + zzzz | 01-02-2024 | 0 +(2 rows) + +DROP TABLE rpr_phv_src, rpr_phv_dim, rpr_phv_out; +-- A WINDOW clause no window function names is never executed, so its DEFINE +-- clause is emptied before build_base_rel_tlists() could mark what it reads +-- as needed at relation 0, which would keep the outer join from being +-- removed. The three plans below are the assertion: no WINDOW clause, a +-- plain one and a row pattern one all lose the join alike. +CREATE TABLE rpr_jr (id int, v int); +CREATE TABLE rpr_jr_u (id int PRIMARY KEY, uval int); +INSERT INTO rpr_jr SELECT g, g * 10 FROM generate_series(1, 5) g; +INSERT INTO rpr_jr_u SELECT g, g * 100 FROM generate_series(1, 5) g; +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id; + QUERY PLAN +---------------------- + Seq Scan on rpr_jr t +(1 row) + +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id +WINDOW w AS (ORDER BY t.id); + QUERY PLAN +---------------------- + Seq Scan on rpr_jr t +(1 row) + +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS uval > PREV(uval)); + QUERY PLAN +---------------------- + Seq Scan on rpr_jr t +(1 row) + +DROP TABLE rpr_jr, rpr_jr_u; -- ============================================================ -- Complex Expression Tests -- ============================================================ @@ -8989,6 +9391,89 @@ SELECT * FROM rpr_grp_v2 ORDER BY category NULLS LAST; (3 rows) DROP VIEW rpr_grp_v2; +-- A DEFINE clause may spell a GROUP BY expression. Planting stops at one +-- rather than offering the columns underneath it to the grouping logic on +-- their own, which is not how grouping makes them available; the target list +-- entry holding the same expression is what both copies end up naming. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0) +ORDER BY bumped; + bumped | cnt +--------+----- + 11 | 1 + 21 | 1 +(2 rows) + +-- The same for a function call +SELECT upper(category) AS u, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY upper(category) +WINDOW w AS ( + ORDER BY upper(category) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS upper(category) = 'A') +ORDER BY u; + u | cnt +---+----- + A | 1 + B | 0 +(2 rows) + +-- The same for a cast +SELECT val::text AS t, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val::text +WINDOW w AS ( + ORDER BY val::text + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val::text > '0') +ORDER BY t; + t | cnt +----+----- + 10 | 1 + 20 | 1 +(2 rows) + +-- And through a navigation operation, whose argument is read the same way +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(val + 1) > 0) +ORDER BY bumped; + bumped | cnt +--------+----- + 11 | 2 + 21 | 0 +(2 rows) + +-- The same under a grouping set, where the row the set nulls leaves the +-- predicate unknown and so unmatched. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY ROLLUP(val + 1) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0); + bumped | cnt +--------+----- + | 0 + 11 | 1 + 21 | 1 +(3 rows) + -- A DEFINE clause may repeat an expression the window itself orders by, with -- no grouping in sight. Planting bare Vars is what makes this hold: the -- DEFINE copy of ROW(val, 1) IS NOT NULL is broken into per field tests before @@ -9184,6 +9669,63 @@ SELECT v, cnt FROM rpr_srf_inline(3) ORDER BY v; DROP TABLE rpr_srf_t; DROP FUNCTION rpr_srf_inline(int); DROP TABLE rpr_planner; +-- A DEFINE clause reading a compound GROUP BY expression. After grouping +-- only the expression itself exists, so make_window_input_target() has to +-- take it whole and stop: asking for the Vars underneath would ask the +-- grouping step for columns it cannot produce. "((a + b))" alone on the +-- Output lines, with no bare a or b anywhere above the HashAggregate, is the +-- assertion. +CREATE TABLE rpr_gexp (a int, b int); +INSERT INTO rpr_gexp VALUES (1, 1), (2, 2), (3, 3), (4, 4); +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a + b > 2); + ab | c +----+--- + 2 | 0 + 4 | 3 + 6 | 0 + 8 | 0 +(4 rows) + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a + b > 2); + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + WindowAgg + Output: ((a + b)), count(*) OVER w + Window: w AS (ORDER BY ((rpr_gexp.a + rpr_gexp.b)) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: x+# + -> Sort + Output: ((a + b)) + Sort Key: ((rpr_gexp.a + rpr_gexp.b)) + -> HashAggregate + Output: ((a + b)) + Group Key: (rpr_gexp.a + rpr_gexp.b) + -> Seq Scan on public.rpr_gexp + Output: (a + b) +(12 rows) + +-- Reaching below the grouping expression is rejected, as it would be in any +-- other clause evaluated after grouping. +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a > 2); +ERROR: column "rpr_gexp.a" must appear in the GROUP BY clause or be used in an aggregate function +LINE 6: PATTERN (X+) DEFINE X AS a > 2); + ^ +DROP TABLE rpr_gexp; -- ============================================================ -- Stress Tests -- ============================================================ diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 00ed80bce3d..2875afd5897 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -34,7 +34,7 @@ -- B8. RPR + Incremental sort -- B9. RPR + Volatile function in DEFINE -- B10. RPR + Correlated subquery in WHERE --- B11. RPR + Junk targetlist pruning +-- B11. RPR + DEFINE-only column pruning -- B12. RPR + Correlated navigation offsets -- B13. RPR + DEFINE-only parameter caching -- B14. RPR + Multiple window definitions @@ -400,13 +400,12 @@ ORDER BY id; (10 rows) -- ============================================================ --- A5. Unused window removal prevention +-- A5. Unused output removal around an RPR window -- ============================================================ --- Verify that remove_unused_subquery_outputs() does not drop an RPR --- window function when the outer query does not reference its result. --- The WindowAgg node performs the pattern match itself; without it, --- the match would be silently skipped. The plan must contain a --- WindowAgg node beneath the outer Aggregate. +-- the outer query only counts rows and never reads count(*) OVER w, so the +-- window function is replaced with NULL, the window becomes inactive, and its +-- WindowAgg is dropped -- leaving a plain Aggregate over the scan. The row +-- count (and thus count(*)) is unchanged. EXPLAIN (COSTS OFF) SELECT count(*) FROM ( SELECT count(*) OVER w FROM rpr_integ @@ -415,15 +414,11 @@ SELECT count(*) FROM ( PATTERN (A+) DEFINE A AS val > PREV(val)) ) t; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +----------------------------- Aggregate - -> WindowAgg - Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+# - Nav Mark Lookback: 1 - -> Seq Scan on rpr_integ -(6 rows) + -> Seq Scan on rpr_integ +(2 rows) SELECT count(*) FROM ( SELECT count(*) OVER w FROM rpr_integ @@ -437,13 +432,10 @@ SELECT count(*) FROM ( 10 (1 row) --- The DEFINE expression references PREV(val), so the window must be --- preserved even if the outer query only aggregates over the count. --- The plan must still contain a WindowAgg with the PATTERN/DEFINE --- intact. -EXPLAIN (COSTS OFF) -SELECT count(*), sum(c) FROM ( - SELECT count(*) OVER w AS c FROM rpr_integ +-- sum(cnt) reads the window function's value, so the column cannot be removed. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT count(*), sum(cnt) FROM ( + SELECT count(*) OVER w as cnt FROM rpr_integ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) @@ -452,15 +444,18 @@ SELECT count(*), sum(c) FROM ( QUERY PLAN ------------------------------------------------------------------------- Aggregate + Output: count(*), sum((count(*) OVER w)) -> WindowAgg + Output: count(*) OVER w Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+# Nav Mark Lookback: 1 - -> Seq Scan on rpr_integ -(6 rows) + -> Seq Scan on public.rpr_integ + Output: rpr_integ.val +(9 rows) -SELECT count(*), sum(c) FROM ( - SELECT count(*) OVER w AS c FROM rpr_integ +SELECT count(*), sum(cnt) FROM ( + SELECT count(*) OVER w as cnt FROM rpr_integ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) @@ -471,9 +466,11 @@ SELECT count(*), sum(c) FROM ( 10 | 6 (1 row) --- The DEFINE expression contains no navigation, but the RPR window --- must still be preserved because the match structure itself affects --- the count. The plan must retain the WindowAgg. +-- Navigation-free DEFINE: DEFINE A AS TRUE matches every row, so PATTERN (A+) +-- still reduces the frame (to the whole remaining partition) even without a +-- PREV/NEXT navigation. sum(c) reads the window value, so the WindowAgg is +-- retained; this checks that a trivial DEFINE still drives frame reduction +-- and yields the expected counts. EXPLAIN (COSTS OFF) SELECT count(*), sum(c) FROM ( SELECT count(*) OVER w AS c FROM rpr_integ @@ -503,49 +500,488 @@ SELECT count(*), sum(c) FROM ( 10 | 10 (1 row) --- XXX: "val" is non-resjunk in the subquery output and is not --- referenced by the outer query. Without a guard, --- remove_unused_subquery_outputs() would replace it with NULL in --- the subquery output, and that replacement propagates to the --- scan's targetlist -- DEFINE would then evaluate with NULL --- inputs. The targetlist has no way to distinguish "exposed to --- the outer query" from "referenced only by DEFINE", so the --- optimization cannot be applied selectively. The column guard --- in allpaths.c blocks this replacement for any column referenced --- by an RPR DEFINE clause, keeping the WindowAgg with DEFINE --- active in the plan. -EXPLAIN (COSTS OFF) +-- "val" is a non-resjunk subquery output that the outer query never reads, so +-- remove_unused_subquery_outputs() would replace it with NULL and DEFINE would +-- then compare NULLs. The guard in allpaths.c keeps it. +EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ( - SELECT val, count(*) OVER w FROM rpr_integ + SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS val > PREV(val)) -) t; - QUERY PLAN ------------------------------------------------------------------------------------------------ +) t WHERE c > 0; + QUERY PLAN +----------------------------------------------------------------------------------------------------- Aggregate - -> WindowAgg - Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b+ - Nav Mark Lookback: 1 - -> Sort - Sort Key: rpr_integ.id - -> Seq Scan on rpr_integ -(8 rows) + Output: count(*) + -> Subquery Scan on t + Output: t.val, t.c, rpr_integ.id + Filter: (t.c > 0) + -> WindowAgg + Output: NULL::integer, count(*) OVER w, rpr_integ.id + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> Sort + Output: rpr_integ.id, rpr_integ.val + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: rpr_integ.id, rpr_integ.val +(15 rows) SELECT count(*) FROM ( - SELECT val, count(*) OVER w FROM rpr_integ + SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS val > PREV(val)) -) t; +) t WHERE c > 0; count ------- - 10 + 4 (1 row) +-- The same column has to survive at the top level, where +-- remove_unused_subquery_outputs() never runs at all: "val" is referenced only +-- by DEFINE, so build_base_rel_tlists() marking it needed is the only thing +-- carrying it up the join tree, and make_window_input_target() is what asks +-- for it again. "val" on the Sort and Seq Scan Output lines, below a +-- WindowAgg that does not output it, is the assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + QUERY PLAN +----------------------------------------------------------------------------------------- + WindowAgg + Output: id, count(*) OVER w + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> Sort + Output: id, val + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: id, val +(10 rows) + +SELECT id, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + id | cnt +----+----- + 1 | 2 + 2 | 0 + 3 | 2 + 4 | 0 + 5 | 3 + 6 | 0 + 7 | 0 + 8 | 3 + 9 | 0 + 10 | 0 +(10 rows) + +-- The same retention has to survive join removal: nulling "uv" would leave +-- rpr_integ_u referenced by nothing, the LEFT JOIN would be dropped, and the +-- DEFINE Var would then point at a relation no longer in the plan. +CREATE TABLE rpr_integ_u (id INT PRIMARY KEY, uval INT); +INSERT INTO rpr_integ_u SELECT i, i * 10 FROM generate_series(1, 5) i; +EXPLAIN (COSTS OFF) +SELECT id, c FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + QUERY PLAN +--------------------------------------------------------------------------------------- + Subquery Scan on s + -> WindowAgg + Window: w AS (ORDER BY t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> Sort + Sort Key: t.id + -> Hash Left Join + Hash Cond: (t.id = u.id) + -> Seq Scan on rpr_integ t + -> Hash + -> Seq Scan on rpr_integ_u u +(12 rows) + +SELECT id, c FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + id | c +----+--- + 1 | 5 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 + 10 | 0 +(10 rows) + +-- A flattened subquery output that an outer join makes nullable reaches the +-- DEFINE clause as a PlaceHolderVar rather than a Var. The parser's targetlist +-- entry is rewritten the same way, so the expression still reaches the +-- WindowAgg's input: the trailing "(COALESCE(rpr_integ_u.uval, 0))" is the +-- assertion. coalesce() is deliberate and must not be simplified away: a +-- strict expression such as "uval + 1" goes to NULL on its own when the join +-- finds no match, so pullup does not wrap it and the case degenerates into an +-- ordinary Var. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t.id, count(*) OVER w AS c +FROM rpr_integ t + LEFT JOIN (SELECT id AS uid, coalesce(uval, 0) AS uv1 FROM rpr_integ_u) s + ON t.id = s.uid +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uv1 > PREV(uv1)); + QUERY PLAN +--------------------------------------------------------------------------------- + WindowAgg + Output: t.id, count(*) OVER w + Window: w AS (ORDER BY t.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> Sort + Output: t.id, (COALESCE(rpr_integ_u.uval, 0)) + Sort Key: t.id + -> Hash Left Join + Output: t.id, (COALESCE(rpr_integ_u.uval, 0)) + Inner Unique: true + Hash Cond: (t.id = rpr_integ_u.id) + -> Seq Scan on public.rpr_integ t + Output: t.id, t.val + -> Hash + Output: rpr_integ_u.id, (COALESCE(rpr_integ_u.uval, 0)) + -> Seq Scan on public.rpr_integ_u + Output: rpr_integ_u.id, COALESCE(rpr_integ_u.uval, 0) +(18 rows) + +SELECT t.id, count(*) OVER w AS c +FROM rpr_integ t + LEFT JOIN (SELECT id AS uid, coalesce(uval, 0) AS uv1 FROM rpr_integ_u) s + ON t.id = s.uid +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uv1 > PREV(uv1)); + id | c +----+--- + 1 | 5 + 2 | 0 + 3 | 0 + 4 | 0 + 5 | 0 + 6 | 0 + 7 | 0 + 8 | 0 + 9 | 0 + 10 | 0 +(10 rows) + +-- The same shape with the window dead: nothing reads count(*) OVER w, so its +-- entry goes, w goes with it, and "uv" is no longer held by a DEFINE clause +-- that will run. That was rpr_integ_u's last reference, so join removal takes +-- the LEFT JOIN too and the scan is left alone. Retaining "uv" here on the +-- strength of a window that will not run would keep the join alive for nothing. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s; + QUERY PLAN +--------------------------------------------------- + Subquery Scan on s + Output: s.id + -> Seq Scan on public.rpr_integ t + Output: t.id, NULL::integer, NULL::bigint +(4 rows) + +SELECT id FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + id +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- A live window and a dead RPR window in one subquery. Emptying the dead +-- one's DEFINE clause must not disturb winref, which is a position in +-- windowClause, nor the live window's result. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id, c1 FROM ( + SELECT t.id AS id, u.uval AS uv, + count(*) OVER w1 AS c1, count(*) OVER w2 AS c2 + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w1 AS (ORDER BY t.id), + w2 AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s; + QUERY PLAN +--------------------------------------------------------------------- + Subquery Scan on s + Output: s.id, s.c1 + -> WindowAgg + Output: t.id, NULL::integer, count(*) OVER w1, NULL::bigint + Window: w1 AS (ORDER BY t.id) + -> Sort + Output: t.id + Sort Key: t.id + -> Seq Scan on public.rpr_integ t + Output: t.id +(10 rows) + +SELECT id, c1 FROM ( + SELECT t.id AS id, u.uval AS uv, + count(*) OVER w1 AS c1, count(*) OVER w2 AS c2 + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w1 AS (ORDER BY t.id), + w2 AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + id | c1 +----+---- + 1 | 1 + 2 | 2 + 3 | 3 + 4 | 4 + 5 | 5 + 6 | 6 + 7 | 7 + 8 | 8 + 9 | 9 + 10 | 10 +(10 rows) + +DROP TABLE rpr_integ_u; +-- w2 is declared and no window function references it, so select_active_windows() +-- drops it when the subquery is planned. Its DEFINE must not keep "val" alive +-- for a window that never runs: the subquery output for val becomes a null Const. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + QUERY PLAN +--------------------------------------------------------------- + Subquery Scan on t + Output: t.c + -> WindowAgg + Output: count(*) OVER w1, NULL::integer, rpr_integ.id + Window: w1 AS (ORDER BY rpr_integ.id) + -> Sort + Output: rpr_integ.id + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: rpr_integ.id +(10 rows) + +-- Here w2 does have a window function, but the outer query does not read it, so +-- this call replaces that entry with a null Const and w2 goes inactive as well. +-- Which windows are active therefore has to be read after that substitution: +-- read before it, w2 still looks active and "val" is retained for a window that +-- will not run. Both null Consts on the WindowAgg's Output line are the +-- assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, count(*) OVER w2 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + QUERY PLAN +----------------------------------------------------------------------------- + Subquery Scan on t + Output: t.c + -> WindowAgg + Output: count(*) OVER w1, NULL::bigint, NULL::integer, rpr_integ.id + Window: w1 AS (ORDER BY rpr_integ.id) + -> Sort + Output: rpr_integ.id + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: rpr_integ.id +(10 rows) + +-- The same shape with the window function one level down, inside an +-- expression. The live set is read off the entries that survive, so a +-- window function nested in one of them is seen and one in an entry about to +-- be replaced is not; reading it from the entries' top-level nodes instead +-- would report w2 live here and hold "val" for a window that goes inactive +-- anyway. This plan matching the one above is the assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, (count(*) OVER w2) + 1 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + QUERY PLAN +----------------------------------------------------------------------------- + Subquery Scan on t + Output: t.c + -> WindowAgg + Output: count(*) OVER w1, NULL::bigint, NULL::integer, rpr_integ.id + Window: w1 AS (ORDER BY rpr_integ.id) + -> Sort + Output: rpr_integ.id + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: rpr_integ.id +(10 rows) + +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, (count(*) OVER w2) + 1 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + c +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +CREATE TABLE rpr_integ_two (id int, v1 int, v2 int); +INSERT INTO rpr_integ_two SELECT i, i * 10, i * 100 FROM generate_series(1, 5) i; +-- Whether a window is active is decided per window clause, not for row pattern +-- recognition as a whole: w3's function goes, and the column only w3's DEFINE +-- names goes with it, while w2 keeps its own. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c2 FROM ( + SELECT count(*) OVER w2 AS c2, count(*) OVER w3 AS c3, v1, v2 + FROM rpr_integ_two + WINDOW w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v1 > PREV(v1)), + w3 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v2 > PREV(v2)) +) t; + QUERY PLAN +---------------------------------------------------------------------------------------------------- + Subquery Scan on t + Output: t.c2 + -> WindowAgg + Output: count(*) OVER w2, NULL::bigint, NULL::integer, NULL::integer, rpr_integ_two.id + Window: w2 AS (ORDER BY rpr_integ_two.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> Sort + Output: rpr_integ_two.id, rpr_integ_two.v1 + Sort Key: rpr_integ_two.id + -> Seq Scan on public.rpr_integ_two + Output: rpr_integ_two.id, rpr_integ_two.v1 +(12 rows) + +-- A window function entry can be kept for a reason other than the upper query +-- reading it -- here the subquery's own ORDER BY -- and then its window stays +-- active and its DEFINE column is retained. The pass that settles the window +-- function entries therefore has to apply every condition the loop after it +-- applies, not just the one about the upper query. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, count(*) OVER w2 AS ord, v1 + FROM rpr_integ_two + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v1 > PREV(v1)) + ORDER BY 2 +) t; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Subquery Scan on t + Output: t.c + -> Sort + Output: (count(*) OVER w1), (count(*) OVER w2), NULL::integer, rpr_integ_two.id + Sort Key: (count(*) OVER w2) + -> WindowAgg + Output: (count(*) OVER w1), count(*) OVER w2, NULL::integer, rpr_integ_two.id + Window: w2 AS (ORDER BY rpr_integ_two.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookback: 1 + -> WindowAgg + Output: rpr_integ_two.id, rpr_integ_two.v1, count(*) OVER w1 + Window: w1 AS (ORDER BY rpr_integ_two.id) + -> Sort + Output: rpr_integ_two.id, rpr_integ_two.v1 + Sort Key: rpr_integ_two.id + -> Seq Scan on public.rpr_integ_two + Output: rpr_integ_two.id, rpr_integ_two.v1 +(18 rows) + +DROP TABLE rpr_integ_two; -- Whole-row Var in DEFINE is not allowed SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ @@ -560,9 +996,9 @@ 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 +-- and one of them can be a whole-row Var (attribute number 0). The window +-- input target takes it 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. @@ -580,7 +1016,7 @@ SELECT sum(c) FROM ( Aggregate Output: sum((count(*) OVER w)) -> WindowAgg - Output: NULL::integer, count(*) OVER w, r.id, r.* + Output: NULL::integer, count(*) OVER w, r.id Window: w AS (ORDER BY r.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ -> Sort @@ -603,6 +1039,42 @@ SELECT sum(c) FROM ( 10 (1 row) +-- The walk that decides which windows are still live runs on a targetlist +-- subquery_planner() has not preprocessed yet, so a SubLink is still a SubLink +-- there. OFFSET 0 keeps the subquery unflattened, which is what puts +-- remove_unused_subquery_outputs() on the path at all. +SELECT count(*) FROM ( + SELECT id, (SELECT 1) AS s, count(*) OVER w AS c + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + OFFSET 0 +) t; + count +------- + 10 +(1 row) + +-- A window function may also sit in a sub-select's test expression, where it +-- belongs to this query level rather than the sub-select's. The walk reads it +-- there; a window function written inside the sub-select itself would count +-- against that query's own window clauses and must not be read here. +SELECT count(*) FROM ( + SELECT id, (count(*) OVER w) IN (SELECT 1) AS m + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + OFFSET 0 +) t; + count +------- + 10 +(1 row) + -- ============================================================ -- A6. Inverse transition bypass -- ============================================================ @@ -778,7 +1250,7 @@ WINDOW QUERY PLAN --------------------------------------------------------------------------------------------------- WindowAgg - Output: (count(*) OVER w_rpr), count(*) OVER w_normal, id, val + Output: (count(*) OVER w_rpr), count(*) OVER w_normal, id Window: w_normal AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) -> WindowAgg Output: id, val, count(*) OVER w_rpr @@ -1440,8 +1912,8 @@ ORDER BY o.id, r.id; -- A lateral outer reference can share varno and varattno with a DEFINE-only -- column: here o.b and y are both attribute 2 at their own query levels. --- Only varlevelsup separates them, so the junk targetlist entry for y has to --- be added even though a Var with the same varno and varattno is present. +-- Only varlevelsup separates them, so the window input target has to take y +-- even though a Var with the same varno and varattno is present. CREATE TABLE rpr_lat_o (a int, b int); CREATE TABLE rpr_lat_i (x int, y int); INSERT INTO rpr_lat_o VALUES (1, 10); @@ -1688,10 +2160,10 @@ ORDER BY o.id; (10 rows) -- ============================================================ --- B11. RPR + Junk targetlist pruning +-- B11. RPR + DEFINE-only column pruning -- ============================================================ --- Verify that the junk targetlist entry planted for a DEFINE-only --- column does not keep an unrelated column alive. DEFINE references +-- Verify that carrying a DEFINE-only column to the WindowAgg's input +-- does not keep an unrelated column alive. DEFINE references -- a (rpr_over1); c (rpr_over2) carries the same attribute number but -- is unused, so the plan must drop it. CREATE TABLE rpr_over1 (a int); @@ -1726,6 +2198,101 @@ SELECT cnt FROM ( (15 rows) DROP TABLE rpr_over1, rpr_over2; +-- A DEFINE clause can hold a Var, or a PlaceHolderVar, of an outer query +-- level by the time this pruning runs, although none may be written in one: +-- inlining a SQL function substitutes the call's actual arguments into the +-- body and raises the level of what it plants there, and subquery pull-up may +-- wrap that in a PlaceHolderVar. Reading the clause has to pass those by. +-- Each query below prunes an output, and is followed by the same query +-- reading that output, which prunes nothing and so never meets them. +CREATE TABLE rpr_up (p int, x int); +INSERT INTO rpr_up SELECT g, 100 + g FROM generate_series(1, 6) g; +CREATE TABLE rpr_drv (k int); +INSERT INTO rpr_drv VALUES (2), (4); +-- an outer Var, in a clause that does not read the pruned column: +CREATE FUNCTION rpr_up_f(th int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS p > th) +$$; +SELECT d.k, max(g.cnt) FROM rpr_drv d, LATERAL rpr_up_f(d.k) g +GROUP BY d.k ORDER BY 1; + k | max +---+----- + 2 | 4 + 4 | 2 +(2 rows) + +SELECT d.k, max(g.cnt), max(g.x) FROM rpr_drv d, LATERAL rpr_up_f(d.k) g +GROUP BY d.k ORDER BY 1; + k | max | max +---+-----+----- + 2 | 4 | 106 + 4 | 2 | 106 +(2 rows) + +-- the same, in a clause that does read it: the column has to be held for the +-- window even though nothing above the subquery reads it. +CREATE FUNCTION rpr_up_h(th int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS x > th + 100) +$$; +SELECT d.k, max(g.cnt) FROM rpr_drv d, LATERAL rpr_up_h(d.k) g +GROUP BY d.k ORDER BY 1; + k | max +---+----- + 2 | 4 + 4 | 2 +(2 rows) + +SELECT d.k, max(g.cnt), max(g.x) FROM rpr_drv d, LATERAL rpr_up_h(d.k) g +GROUP BY d.k ORDER BY 1; + k | max | max +---+-----+----- + 2 | 4 | 106 + 4 | 2 | 106 +(2 rows) + +-- and with a constant argument, where no outer reference arises at all: +SELECT max(cnt) FROM rpr_up_h(2); + max +----- + 4 +(1 row) + +SELECT max(cnt) FROM rpr_up_h(4); + max +----- + 2 +(1 row) + +-- an outer PlaceHolderVar, which pulling up the subquery that supplies the +-- argument puts there in place of the Var: +CREATE FUNCTION rpr_up_n(int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (A+) DEFINE A AS PREV(p + $1) > 0) +$$; +SELECT s.k, max(g.cnt) FROM (SELECT 3 AS k FROM rpr_drv) s, + LATERAL rpr_up_n(s.k) g GROUP BY s.k; + k | max +---+----- + 3 | 5 +(1 row) + +SELECT s.k, max(g.cnt), max(g.x) FROM (SELECT 3 AS k FROM rpr_drv) s, + LATERAL rpr_up_n(s.k) g GROUP BY s.k; + k | max | max +---+-----+----- + 3 | 5 | 106 +(1 row) + +DROP FUNCTION rpr_up_f(int), rpr_up_h(int), rpr_up_n(int); +DROP TABLE rpr_up, rpr_drv; -- ============================================================ -- B12. RPR + Correlated navigation offsets -- ============================================================ diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 4dfa0a609a2..7c3c9f54cd1 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1540,6 +1540,21 @@ SELECT id, count(*) OVER w AS cnt FROM rpr_nav t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS NEXT(val / 0) > 0); +-- A constant subexpression of the argument is folded away, so the pattern +-- does not recompute it per row. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(val + 2 * 3) > 0); + +-- Folding a constant subexpression can raise where the whole argument would +-- not have: unlike val / 0 above, 1 / 0 does not depend on the row, so it is +-- reached at plan time even on the row PREV misses on. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav t +WHERE id = 1 +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(val + 1 / 0) > 0); + -- Here the null reaches the DEFINE predicate itself instead of an IS NULL -- An all-NULL target row would have made v IS NULL true and matched the -- first row, so this pins the predicate side of the same behaviour. @@ -1548,22 +1563,129 @@ SELECT id, count(*) OVER w AS cnt FROM t WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v IS NULL)); --- Constant folding can leave a navigation argument with no column reference --- at all (v folds to 10, so PREV(v IS NULL) becomes PREV(false)), which the --- planner has to accept rather than re-run the parse-time rejection. +-- Pulling up the VALUES substitutes 10 for v, which is not what the column +-- stood for under a navigation: the argument reads the row the navigation +-- lands on, not this one. The replacement is wrapped in a PlaceHolderVar +-- rather than folded through. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v IS NULL)); --- XXX Folding evaluates the argument while planning, with the current row's --- value standing in for the target row's, so this divides by zero even though --- PREV has no row to navigate to. +-- That wrapping is what keeps this one from raising: the divisor is constant +-- but the dividend is not folded through, so the division stands until +-- execution, where PREV has no row to navigate to and never reaches it. WITH t(id, v) AS (VALUES (1, 10)) SELECT id, count(*) OVER w AS cnt FROM t WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); +-- A pulled-up subquery and a function RTE that folded to a constant reach a +-- navigation argument the same way, so both are wrapped as well. +SELECT id, count(*) OVER w AS cnt +FROM (SELECT 1 AS id, 10 AS v) t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); + +SELECT count(*) OVER w AS cnt +FROM abs(-10) AS v +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS PREV(v / 0) > 0); + +-- Only the argument is protected. One level outside the navigation the same +-- column is replaced and folded as it is anywhere else, and the division +-- raises at plan time -- as it does for the same WHERE clause over the same +-- one-row VALUES, with no pattern in sight. +WITH t(id, v) AS (VALUES (1, 10)) +SELECT id, count(*) OVER w AS cnt +FROM t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A) DEFINE A AS v / 0 > 0); + +-- A replacement that still depends on the row is left unwrapped, because it +-- is what the column meant at whichever row the navigation lands on. +SELECT id, count(*) OVER w AS cnt +FROM (SELECT id, val + 1 AS v FROM rpr_nav) t +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(v) > 0); + +-- Nesting: the inner navigation's argument is below the outer one's, so the +-- column there is wrapped too. +WITH t(id, v) AS (VALUES (1, 10)) +SELECT id, count(*) OVER w AS cnt +FROM t +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) DEFINE A AS PREV(LAST(v / 0, 1), 2) > 0); + +-- eval_const_expressions() must perform a few rewrites on every expression +-- it is handed -- a CollateExpr becomes a RelabelType, named arguments become +-- positional, omitted defaults are inserted -- and preprocess_expression() +-- documents them as mandatory, not as optimizations. Each of the three below +-- reaches the executor only if those rewrites reach inside a navigation +-- argument, and each returns what the same expression one level outside the +-- navigation returns. +CREATE TABLE rpr_nav_txt (id int, s text); +INSERT INTO rpr_nav_txt VALUES (1, 'b'), (2, 'c'), (3, 'a'); +CREATE FUNCTION rpr_nav_named(a int, b int) RETURNS int + LANGUAGE sql IMMUTABLE AS 'SELECT $1 * 10 + $2'; +CREATE FUNCTION rpr_nav_dflt(a int, b int DEFAULT 100) RETURNS int + LANGUAGE sql IMMUTABLE AS 'SELECT $2'; + +-- COLLATE under a navigation: the executor has no CollateExpr step, so the +-- RelabelType rewrite has to reach here. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav_txt +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(s COLLATE "C") > 'a'); + +-- Named arguments under a navigation: the executor has no NamedArgExpr step. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(rpr_nav_named(b => 7, a => val)) > 0); + +-- An omitted default under a navigation: without the insertion the call is +-- initialized with one fewer argument than the callee reads. +SELECT id, count(*) OVER w AS cnt +FROM rpr_nav +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS PREV(rpr_nav_dflt(val)) = 100); + +DROP FUNCTION rpr_nav_dflt(int, int); +DROP FUNCTION rpr_nav_named(int, int); +DROP TABLE rpr_nav_txt; + +-- A navigation offset is resolved once at the top of the scan, before any +-- input row has been read, so it must not be matched to the window input the +-- way the navigated argument is. These two spell the offset the same as a +-- window ORDER BY key and as a GROUP BY expression, which is what makes the +-- match available. +CREATE TABLE rpr_navoff (id int, val int); +INSERT INTO rpr_navoff VALUES (1, 10), (2, 20), (3, 15), (4, 30), (5, 5); + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 1))); + +-- Control: an offset that matches nothing in the window input. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +WINDOW w AS (ORDER BY (extract(hour from localtimestamp)::int * 0 + 1), id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (extract(hour from localtimestamp)::int * 0 + 2))); + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_navoff +GROUP BY GROUPING SETS ((id, val, ((random() * 0)::bigint + 1)), + (id, ((random() * 0)::bigint + 1))) +WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val, (random() * 0)::bigint + 1)) +ORDER BY id, val; + +DROP TABLE rpr_navoff; + -- PREV function - reference previous row in pattern SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -1748,13 +1870,22 @@ SELECT id FROM ( PATTERN (A+) DEFINE A AS random() > 0.5)) s ORDER BY id; --- ERROR: OFFSET 0 keeps the subquery, so its DEFINE is checked +-- OFFSET 0 keeps the subquery, but still no OVER references the window, so +-- the planner withdraws the DEFINE clause of a window it will not run and the +-- check finds nothing left to reject SELECT id FROM ( SELECT id FROM nt WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS random() > 0.5) OFFSET 0) sub; +-- ERROR: a subquery window that does run keeps its DEFINE, so it is checked +SELECT id, c FROM ( + SELECT id, count(*) OVER w AS c FROM nt + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS random() > 0.5)) sub; + -- WHERE false makes the subquery rel dummy, so the planner never plans it -- and nothing looks at its DEFINE SELECT id FROM ( @@ -3133,9 +3264,52 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS ROW((items).*) IS NOT NULL ); + DROP TABLE rpr_composite; DROP TYPE rpr_item; +-- A composite value that reaches DEFINE by way of a subquery Var only takes +-- its ROW(...) shape after pullup, and the ORDER BY copy's sortgroupref +-- keeps it from being flattened. make_window_input_target() adds the fields +-- the split leaves behind. +CREATE TABLE rpr_ordrow (a int, b int); +INSERT INTO rpr_ordrow SELECT g, g % 4 FROM generate_series(1, 10) g; +SELECT count(*) OVER w AS c +FROM (SELECT ROW(a, b) AS x FROM rpr_ordrow) s +WINDOW w AS (ORDER BY x + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL); +-- Control: without ORDER BY, x is flattened normally and this succeeds too. +SELECT count(*) OVER w AS c +FROM (SELECT ROW(a, b) AS x FROM rpr_ordrow) s +WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL); +DROP TABLE rpr_ordrow; + +-- The same split by way of a pulled-up composite target, both as a plain +-- subquery and as a view. +CREATE TABLE rpr_partrow (a int, b int); +INSERT INTO rpr_partrow VALUES (1, 1), (2, 2), (3, 3); +SELECT count(*) OVER w +FROM (SELECT b, row(a, 1) AS k FROM rpr_partrow) s +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (p q+) DEFINE q AS k IS NOT NULL); +CREATE TYPE rpr_partrow_t AS (x int, y int); +CREATE VIEW rpr_partrow_v AS SELECT b, row(a, 1)::rpr_partrow_t AS k FROM rpr_partrow; +SELECT count(*) OVER w FROM rpr_partrow_v +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (p q+) DEFINE q AS k IS NOT NULL); +-- Control: PATTERN/DEFINE aside, the same window clause runs fine. +SELECT count(*) OVER w +FROM (SELECT b, row(a, 1) AS k FROM rpr_partrow) s +WINDOW w AS (PARTITION BY k ORDER BY b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); +DROP VIEW rpr_partrow_v; +DROP TYPE rpr_partrow_t; +DROP TABLE rpr_partrow; + -- ERROR: undefined column in DEFINE SELECT COUNT(*) OVER w FROM rpr_err @@ -4905,6 +5079,86 @@ ORDER BY k; DROP TABLE rpr_join5, rpr_join6; +-- A DEFINE clause reading a USING column whose two sides differ in typmod. +-- The merged column stays a join alias Var, pullup leaves its joinaliasvars +-- entry a non-trivial expression, and the outer join's nullingrels wrap that +-- in a PlaceHolderVar. The target list copy and the DEFINE copy are wrapped +-- by separate calls, so their phids differ and equal() does not match them -- +-- the window input has to carry the DEFINE clause's own PlaceHolderVar. +CREATE TABLE rpr_phv_src (n int); +CREATE TABLE rpr_phv_dim (c varchar(10), tdate date); +CREATE TABLE rpr_phv_out (k varchar); +INSERT INTO rpr_phv_src VALUES (2), (4); +INSERT INTO rpr_phv_dim VALUES ('zz', '2024-01-01'), ('zzzz', '2024-01-02'); +INSERT INTO rpr_phv_out VALUES ('zz'), ('zzzz'); + +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(5) AS c FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o1.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + +-- The same with one more join level above it. Reaching the window input is +-- not enough on its own: an intermediate join emits only what something above +-- has declared a need for, so what a DEFINE clause reads is marked needed at +-- relation 0 the way the target list's own columns are. +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN rpr_phv_out o2 ON o1.k = o2.k + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(5) AS c FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o2.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + +-- Control: with both sides of USING at the same typmod the merged column is a +-- plain Var of one side, no PlaceHolderVar is built, and neither shape above +-- needs any of this. +SELECT j.c, j.tdate, count(*) OVER w AS cnt +FROM rpr_phv_out o1 + LEFT JOIN rpr_phv_out o2 ON o1.k = o2.k + LEFT JOIN ( (SELECT n, repeat('z', n)::varchar(10) AS c + FROM rpr_phv_src) s + JOIN rpr_phv_dim USING (c) ) j + ON o2.k = j.c +WINDOW w AS (ORDER BY j.tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (p q+) + DEFINE p AS TRUE, q AS c > ''); + +DROP TABLE rpr_phv_src, rpr_phv_dim, rpr_phv_out; + +-- A WINDOW clause no window function names is never executed, so its DEFINE +-- clause is emptied before build_base_rel_tlists() could mark what it reads +-- as needed at relation 0, which would keep the outer join from being +-- removed. The three plans below are the assertion: no WINDOW clause, a +-- plain one and a row pattern one all lose the join alike. +CREATE TABLE rpr_jr (id int, v int); +CREATE TABLE rpr_jr_u (id int PRIMARY KEY, uval int); +INSERT INTO rpr_jr SELECT g, g * 10 FROM generate_series(1, 5) g; +INSERT INTO rpr_jr_u SELECT g, g * 100 FROM generate_series(1, 5) g; + +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id; + +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id +WINDOW w AS (ORDER BY t.id); + +EXPLAIN (COSTS OFF) +SELECT t.id FROM rpr_jr t LEFT JOIN rpr_jr_u u ON t.id = u.id +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS uval > PREV(uval)); + +DROP TABLE rpr_jr, rpr_jr_u; + -- ============================================================ -- Complex Expression Tests -- ============================================================ @@ -5470,6 +5724,63 @@ SELECT pg_get_viewdef('rpr_grp_v2'::regclass, true); SELECT * FROM rpr_grp_v2 ORDER BY category NULLS LAST; DROP VIEW rpr_grp_v2; +-- A DEFINE clause may spell a GROUP BY expression. Planting stops at one +-- rather than offering the columns underneath it to the grouping logic on +-- their own, which is not how grouping makes them available; the target list +-- entry holding the same expression is what both copies end up naming. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0) +ORDER BY bumped; + +-- The same for a function call +SELECT upper(category) AS u, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY upper(category) +WINDOW w AS ( + ORDER BY upper(category) + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS upper(category) = 'A') +ORDER BY u; + +-- The same for a cast +SELECT val::text AS t, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val::text +WINDOW w AS ( + ORDER BY val::text + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val::text > '0') +ORDER BY t; + +-- And through a navigation operation, whose argument is read the same way +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY val + 1 +WINDOW w AS ( + ORDER BY val + 1 + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B*) + DEFINE B AS PREV(val + 1) > 0) +ORDER BY bumped; + +-- The same under a grouping set, where the row the set nulls leaves the +-- predicate unknown and so unmatched. +SELECT val + 1 AS bumped, count(*) OVER w AS cnt +FROM rpr_grp +GROUP BY ROLLUP(val + 1) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS val + 1 > 0); + -- A DEFINE clause may repeat an expression the window itself orders by, with -- no grouping in sight. Planting bare Vars is what makes this hold: the -- DEFINE copy of ROW(val, 1) IS NOT NULL is broken into per field tests before @@ -5611,6 +5922,41 @@ DROP FUNCTION rpr_srf_inline(int); DROP TABLE rpr_planner; +-- A DEFINE clause reading a compound GROUP BY expression. After grouping +-- only the expression itself exists, so make_window_input_target() has to +-- take it whole and stop: asking for the Vars underneath would ask the +-- grouping step for columns it cannot produce. "((a + b))" alone on the +-- Output lines, with no bare a or b anywhere above the HashAggregate, is the +-- assertion. +CREATE TABLE rpr_gexp (a int, b int); +INSERT INTO rpr_gexp VALUES (1, 1), (2, 2), (3, 3), (4, 4); + +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a + b > 2); + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a + b > 2); + +-- Reaching below the grouping expression is rejected, as it would be in any +-- other clause evaluated after grouping. +SELECT a + b AS ab, count(*) OVER w AS c +FROM rpr_gexp +GROUP BY a + b +WINDOW w AS (ORDER BY a + b + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a > 2); + +DROP TABLE rpr_gexp; + -- ============================================================ -- Stress Tests -- ============================================================ diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 4f96186ddd7..36c8a6a0fe8 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -34,7 +34,7 @@ -- B8. RPR + Incremental sort -- B9. RPR + Volatile function in DEFINE -- B10. RPR + Correlated subquery in WHERE --- B11. RPR + Junk targetlist pruning +-- B11. RPR + DEFINE-only column pruning -- B12. RPR + Correlated navigation offsets -- B13. RPR + DEFINE-only parameter caching -- B14. RPR + Multiple window definitions @@ -271,13 +271,12 @@ FROM rpr_integ ORDER BY id; -- ============================================================ --- A5. Unused window removal prevention +-- A5. Unused output removal around an RPR window -- ============================================================ --- Verify that remove_unused_subquery_outputs() does not drop an RPR --- window function when the outer query does not reference its result. --- The WindowAgg node performs the pattern match itself; without it, --- the match would be silently skipped. The plan must contain a --- WindowAgg node beneath the outer Aggregate. +-- the outer query only counts rows and never reads count(*) OVER w, so the +-- window function is replaced with NULL, the window becomes inactive, and its +-- WindowAgg is dropped -- leaving a plain Aggregate over the scan. The row +-- count (and thus count(*)) is unchanged. EXPLAIN (COSTS OFF) SELECT count(*) FROM ( SELECT count(*) OVER w FROM rpr_integ @@ -295,30 +294,29 @@ SELECT count(*) FROM ( DEFINE A AS val > PREV(val)) ) t; --- The DEFINE expression references PREV(val), so the window must be --- preserved even if the outer query only aggregates over the count. --- The plan must still contain a WindowAgg with the PATTERN/DEFINE --- intact. -EXPLAIN (COSTS OFF) -SELECT count(*), sum(c) FROM ( - SELECT count(*) OVER w AS c FROM rpr_integ +-- sum(cnt) reads the window function's value, so the column cannot be removed. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT count(*), sum(cnt) FROM ( + SELECT count(*) OVER w as cnt FROM rpr_integ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS val > PREV(val)) ) t; -SELECT count(*), sum(c) FROM ( - SELECT count(*) OVER w AS c FROM rpr_integ +SELECT count(*), sum(cnt) FROM ( + SELECT count(*) OVER w as cnt FROM rpr_integ WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS val > PREV(val)) ) t; --- The DEFINE expression contains no navigation, but the RPR window --- must still be preserved because the match structure itself affects --- the count. The plan must retain the WindowAgg. +-- Navigation-free DEFINE: DEFINE A AS TRUE matches every row, so PATTERN (A+) +-- still reduces the frame (to the whole remaining partition) even without a +-- PREV/NEXT navigation. sum(c) reads the window value, so the WindowAgg is +-- retained; this checks that a trivial DEFINE still drives frame reduction +-- and yields the expected counts. EXPLAIN (COSTS OFF) SELECT count(*), sum(c) FROM ( SELECT count(*) OVER w AS c FROM rpr_integ @@ -336,34 +334,248 @@ SELECT count(*), sum(c) FROM ( DEFINE A AS TRUE) ) t; --- XXX: "val" is non-resjunk in the subquery output and is not --- referenced by the outer query. Without a guard, --- remove_unused_subquery_outputs() would replace it with NULL in --- the subquery output, and that replacement propagates to the --- scan's targetlist -- DEFINE would then evaluate with NULL --- inputs. The targetlist has no way to distinguish "exposed to --- the outer query" from "referenced only by DEFINE", so the --- optimization cannot be applied selectively. The column guard --- in allpaths.c blocks this replacement for any column referenced --- by an RPR DEFINE clause, keeping the WindowAgg with DEFINE --- active in the plan. -EXPLAIN (COSTS OFF) +-- "val" is a non-resjunk subquery output that the outer query never reads, so +-- remove_unused_subquery_outputs() would replace it with NULL and DEFINE would +-- then compare NULLs. The guard in allpaths.c keeps it. +EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ( - SELECT val, count(*) OVER w FROM rpr_integ + SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS val > PREV(val)) -) t; +) t WHERE c > 0; SELECT count(*) FROM ( - SELECT val, count(*) OVER w FROM rpr_integ + SELECT val, count(*) OVER w AS c FROM rpr_integ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS val > PREV(val)) +) t WHERE c > 0; + +-- The same column has to survive at the top level, where +-- remove_unused_subquery_outputs() never runs at all: "val" is referenced only +-- by DEFINE, so build_base_rel_tlists() marking it needed is the only thing +-- carrying it up the join tree, and make_window_input_target() is what asks +-- for it again. "val" on the Sort and Seq Scan Output lines, below a +-- WindowAgg that does not output it, is the assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + +SELECT id, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + +-- The same retention has to survive join removal: nulling "uv" would leave +-- rpr_integ_u referenced by nothing, the LEFT JOIN would be dropped, and the +-- DEFINE Var would then point at a relation no longer in the plan. +CREATE TABLE rpr_integ_u (id INT PRIMARY KEY, uval INT); +INSERT INTO rpr_integ_u SELECT i, i * 10 FROM generate_series(1, 5) i; + +EXPLAIN (COSTS OFF) +SELECT id, c FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + +SELECT id, c FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + +-- A flattened subquery output that an outer join makes nullable reaches the +-- DEFINE clause as a PlaceHolderVar rather than a Var. The parser's targetlist +-- entry is rewritten the same way, so the expression still reaches the +-- WindowAgg's input: the trailing "(COALESCE(rpr_integ_u.uval, 0))" is the +-- assertion. coalesce() is deliberate and must not be simplified away: a +-- strict expression such as "uval + 1" goes to NULL on its own when the join +-- finds no match, so pullup does not wrap it and the case degenerates into an +-- ordinary Var. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t.id, count(*) OVER w AS c +FROM rpr_integ t + LEFT JOIN (SELECT id AS uid, coalesce(uval, 0) AS uv1 FROM rpr_integ_u) s + ON t.id = s.uid +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uv1 > PREV(uv1)); + +SELECT t.id, count(*) OVER w AS c +FROM rpr_integ t + LEFT JOIN (SELECT id AS uid, coalesce(uval, 0) AS uv1 FROM rpr_integ_u) s + ON t.id = s.uid +WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uv1 > PREV(uv1)); + +-- The same shape with the window dead: nothing reads count(*) OVER w, so its +-- entry goes, w goes with it, and "uv" is no longer held by a DEFINE clause +-- that will run. That was rpr_integ_u's last reference, so join removal takes +-- the LEFT JOIN too and the scan is left alone. Retaining "uv" here on the +-- strength of a window that will not run would keep the join alive for nothing. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s; + +SELECT id FROM ( + SELECT t.id AS id, u.uval AS uv, count(*) OVER w AS c + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + +-- A live window and a dead RPR window in one subquery. Emptying the dead +-- one's DEFINE clause must not disturb winref, which is a position in +-- windowClause, nor the live window's result. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT id, c1 FROM ( + SELECT t.id AS id, u.uval AS uv, + count(*) OVER w1 AS c1, count(*) OVER w2 AS c2 + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w1 AS (ORDER BY t.id), + w2 AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s; + +SELECT id, c1 FROM ( + SELECT t.id AS id, u.uval AS uv, + count(*) OVER w1 AS c1, count(*) OVER w2 AS c2 + FROM rpr_integ t LEFT JOIN rpr_integ_u u ON t.id = u.id + WINDOW w1 AS (ORDER BY t.id), + w2 AS (ORDER BY t.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS uval > PREV(uval)) +) s ORDER BY id; + +DROP TABLE rpr_integ_u; + +-- w2 is declared and no window function references it, so select_active_windows() +-- drops it when the subquery is planned. Its DEFINE must not keep "val" alive +-- for a window that never runs: the subquery output for val becomes a null Const. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + +-- Here w2 does have a window function, but the outer query does not read it, so +-- this call replaces that entry with a null Const and w2 goes inactive as well. +-- Which windows are active therefore has to be read after that substitution: +-- read before it, w2 still looks active and "val" is retained for a window that +-- will not run. Both null Consts on the WindowAgg's Output line are the +-- assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, count(*) OVER w2 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + +-- The same shape with the window function one level down, inside an +-- expression. The live set is read off the entries that survive, so a +-- window function nested in one of them is seen and one in an entry about to +-- be replaced is not; reading it from the entries' top-level nodes instead +-- would report w2 live here and hold "val" for a window that goes inactive +-- anyway. This plan matching the one above is the assertion. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, (count(*) OVER w2) + 1 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, (count(*) OVER w2) + 1 AS unread, val + FROM rpr_integ + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t; + +CREATE TABLE rpr_integ_two (id int, v1 int, v2 int); +INSERT INTO rpr_integ_two SELECT i, i * 10, i * 100 FROM generate_series(1, 5) i; + +-- Whether a window is active is decided per window clause, not for row pattern +-- recognition as a whole: w3's function goes, and the column only w3's DEFINE +-- names goes with it, while w2 keeps its own. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c2 FROM ( + SELECT count(*) OVER w2 AS c2, count(*) OVER w3 AS c3, v1, v2 + FROM rpr_integ_two + WINDOW w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v1 > PREV(v1)), + w3 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v2 > PREV(v2)) ) t; +-- A window function entry can be kept for a reason other than the upper query +-- reading it -- here the subquery's own ORDER BY -- and then its window stays +-- active and its DEFINE column is retained. The pass that settles the window +-- function entries therefore has to apply every condition the loop after it +-- applies, not just the one about the upper query. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT c FROM ( + SELECT count(*) OVER w1 AS c, count(*) OVER w2 AS ord, v1 + FROM rpr_integ_two + WINDOW w1 AS (ORDER BY id), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS v1 > PREV(v1)) + ORDER BY 2 +) t; + +DROP TABLE rpr_integ_two; + -- Whole-row Var in DEFINE is not allowed SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ @@ -375,9 +587,9 @@ SELECT sum(c) FROM ( -- 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 +-- and one of them can be a whole-row Var (attribute number 0). The window +-- input target takes it 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. @@ -400,6 +612,34 @@ SELECT sum(c) FROM ( DEFINE B AS r IS NOT NULL) ) t; +-- The walk that decides which windows are still live runs on a targetlist +-- subquery_planner() has not preprocessed yet, so a SubLink is still a SubLink +-- there. OFFSET 0 keeps the subquery unflattened, which is what puts +-- remove_unused_subquery_outputs() on the path at all. +SELECT count(*) FROM ( + SELECT id, (SELECT 1) AS s, count(*) OVER w AS c + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + OFFSET 0 +) t; + +-- A window function may also sit in a sub-select's test expression, where it +-- belongs to this query level rather than the sub-select's. The walk reads it +-- there; a window function written inside the sub-select itself would count +-- against that query's own window clauses and must not be read here. +SELECT count(*) FROM ( + SELECT id, (count(*) OVER w) IN (SELECT 1) AS m + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + OFFSET 0 +) t; + -- ============================================================ -- A6. Inverse transition bypass -- ============================================================ @@ -907,8 +1147,8 @@ ORDER BY o.id, r.id; -- A lateral outer reference can share varno and varattno with a DEFINE-only -- column: here o.b and y are both attribute 2 at their own query levels. --- Only varlevelsup separates them, so the junk targetlist entry for y has to --- be added even though a Var with the same varno and varattno is present. +-- Only varlevelsup separates them, so the window input target has to take y +-- even though a Var with the same varno and varattno is present. CREATE TABLE rpr_lat_o (a int, b int); CREATE TABLE rpr_lat_i (x int, y int); INSERT INTO rpr_lat_o VALUES (1, 10); @@ -1082,10 +1322,10 @@ FROM rpr_integ o ORDER BY o.id; -- ============================================================ --- B11. RPR + Junk targetlist pruning +-- B11. RPR + DEFINE-only column pruning -- ============================================================ --- Verify that the junk targetlist entry planted for a DEFINE-only --- column does not keep an unrelated column alive. DEFINE references +-- Verify that carrying a DEFINE-only column to the WindowAgg's input +-- does not keep an unrelated column alive. DEFINE references -- a (rpr_over1); c (rpr_over2) carries the same attribute number but -- is unused, so the plan must drop it. CREATE TABLE rpr_over1 (a int); @@ -1103,6 +1343,62 @@ SELECT cnt FROM ( ) s; DROP TABLE rpr_over1, rpr_over2; +-- A DEFINE clause can hold a Var, or a PlaceHolderVar, of an outer query +-- level by the time this pruning runs, although none may be written in one: +-- inlining a SQL function substitutes the call's actual arguments into the +-- body and raises the level of what it plants there, and subquery pull-up may +-- wrap that in a PlaceHolderVar. Reading the clause has to pass those by. +-- Each query below prunes an output, and is followed by the same query +-- reading that output, which prunes nothing and so never meets them. +CREATE TABLE rpr_up (p int, x int); +INSERT INTO rpr_up SELECT g, 100 + g FROM generate_series(1, 6) g; +CREATE TABLE rpr_drv (k int); +INSERT INTO rpr_drv VALUES (2), (4); + +-- an outer Var, in a clause that does not read the pruned column: +CREATE FUNCTION rpr_up_f(th int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS p > th) +$$; +SELECT d.k, max(g.cnt) FROM rpr_drv d, LATERAL rpr_up_f(d.k) g +GROUP BY d.k ORDER BY 1; +SELECT d.k, max(g.cnt), max(g.x) FROM rpr_drv d, LATERAL rpr_up_f(d.k) g +GROUP BY d.k ORDER BY 1; + +-- the same, in a clause that does read it: the column has to be held for the +-- window even though nothing above the subquery reads it. +CREATE FUNCTION rpr_up_h(th int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS x > th + 100) +$$; +SELECT d.k, max(g.cnt) FROM rpr_drv d, LATERAL rpr_up_h(d.k) g +GROUP BY d.k ORDER BY 1; +SELECT d.k, max(g.cnt), max(g.x) FROM rpr_drv d, LATERAL rpr_up_h(d.k) g +GROUP BY d.k ORDER BY 1; +-- and with a constant argument, where no outer reference arises at all: +SELECT max(cnt) FROM rpr_up_h(2); +SELECT max(cnt) FROM rpr_up_h(4); + +-- an outer PlaceHolderVar, which pulling up the subquery that supplies the +-- argument puts there in place of the Var: +CREATE FUNCTION rpr_up_n(int) RETURNS TABLE (cnt bigint, x int) +LANGUAGE sql STABLE AS $$ + SELECT count(*) OVER w, x FROM rpr_up + WINDOW w AS (ORDER BY p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + INITIAL PATTERN (A+) DEFINE A AS PREV(p + $1) > 0) +$$; +SELECT s.k, max(g.cnt) FROM (SELECT 3 AS k FROM rpr_drv) s, + LATERAL rpr_up_n(s.k) g GROUP BY s.k; +SELECT s.k, max(g.cnt), max(g.x) FROM (SELECT 3 AS k FROM rpr_drv) s, + LATERAL rpr_up_n(s.k) g GROUP BY s.k; + +DROP FUNCTION rpr_up_f(int), rpr_up_h(int), rpr_up_n(int); +DROP TABLE rpr_up, rpr_drv; + -- ============================================================ -- B12. RPR + Correlated navigation offsets -- ============================================================ -- 2.54.0 (Apple Git-157)