From df22c0781f508beaf32d64e7d21b84e924a04cf7 Mon Sep 17 00:00:00 2001 From: jian he Date: Sat, 29 Aug 2026 09:54:28 +0900 Subject: [PATCH] Let an unreferenced RPR window and its DEFINE columns be removed remove_unused_subquery_outputs() held two answers to one question. A guard over WindowFunc entries refused to replace an unused one whenever its window clause carried a DEFINE, on the grounds that such a window cannot disappear. The DEFINE-column guard further down assumed the opposite: that a window no window function references is dropped when the subquery is planned, so its columns need not be held alive. select_active_windows() settles it. It keeps a window clause only while some window function references it, and makes no exception for a DEFINE clause or a row pattern, so the first guard's premise is false. Drop it. That leaves the second guard needing to know which windows survive, and this function is what decides: the loop replaces an unread WindowFunc entry with a null Const, and a window clause left with none becomes inactive. Reading the active set on entry would count entries this same call is about to remove. So settle the window function entries first and take the set from what they leave. find_window_functions() cannot supply that set. It asserts that no SubLink is left in the tree, which holds only after preprocess_expression(), and this runs on a subquery set_subquery_pathlist() has not planned yet, so a sub-select anywhere in the targetlist reaches the assertion. Collect the winrefs with a local walk instead. It leaves a sub-select alone, a window function written there answering to that query level's own window clauses, but it does read the test expression beside it, where the parser leaves the left-hand operand of an ANY or ALL comparison. The same answer settles the clause itself. DEFINE is the one field in which a window clause owns an expression tree, so query_tree_walker() walks it and a Query-wide rewriter cannot tell a dead window's Vars from a live one's. Join removal is the sharpest case: once nothing needs a relation it deletes the relid from the whole parse tree with ChangeVarNodes(..., INVALID_VAR, ...), which requires that no ordinary Var of it be left there. Withdraw the clause here, in the same breath as the columns it reads. Registering those Vars in attr_needed instead would hold the relation, and any join to it, alive for exactly the window this is dropping. Only defineClause goes, not the window clause: winref is a one-based index into windowClause and bounds the find_window_functions() that select_active_windows() reads, so the list keeps its length and its order. rpPattern stays too -- it holds no Vars, and leaving it keeps optimize_window_clauses() from taking an emptied RPR clause for a duplicate of a plain one. The conditions deciding whether an entry can go are the same in both passes and are now in subquery_output_is_unneeded(). Coverage goes to rpr_integration.sql's "A5. Unused output removal around an RPR window", the single checkpoint for this function. The DEFINE-column guard had none that would fail if it were removed; the WindowFunc guard had a plan-shape check, which this commit inverts. Beside them go a case pinning the withdrawal against winref -- a live plain window and a dead row pattern window in one subquery -- and one for a sub-select in the targetlist, which the local walk has to tolerate. One expected output moves. A subquery window that survives but runs no window function now escapes the volatile check in preprocess_expression(), its DEFINE clause being gone before that check runs. A companion query whose window does run keeps the coverage. Retention is still all-or-nothing per window clause, with no analysis of whether the match result has any consumer. That remains open. --- src/backend/nodes/nodeFuncs.c | 9 + src/backend/optimizer/path/allpaths.c | 252 +++++--- src/backend/parser/parse_rpr.c | 29 +- src/test/regress/expected/rpr_base.out | 19 +- src/test/regress/expected/rpr_integration.out | 541 ++++++++++++++++-- src/test/regress/sql/rpr_base.sql | 11 +- src/test/regress/sql/rpr_integration.sql | 291 ++++++++-- 7 files changed, 979 insertions(+), 173 deletions(-) diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 6ab227b2b60..0ae5a9df55f 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2847,6 +2847,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 62db28c0a42..f8eeed6fd21 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -4811,6 +4811,94 @@ recurse_push_qual(Node *setOp, Query *topquery, * SIMPLIFYING SUBQUERY TARGETLISTS *****************************************************************************/ +/* + * subquery_output_is_unneeded + * Can remove_unused_subquery_outputs() replace this entry with a null? + * + * This answers for the reasons that apply to any entry. The DEFINE-clause + * protection is not among them: it only ever holds on to a Var, and it needs + * an active window set that is not settled until the window function entries + * have been decided. + */ +static bool +subquery_output_is_unneeded(Query *subquery, TargetEntry *tle, + Bitmapset *attrs_used) +{ + Node *texpr = (Node *) tle->expr; + + /* + * If it has a sortgroupref number, it's used in some sort/group clause so + * we'd better not remove it. Also, don't remove any resjunk columns, + * since their reason for being has nothing to do with anybody reading the + * subquery's output. (It's likely that resjunk columns in a sub-SELECT + * would always have ressortgroupref set, but even if they don't, it seems + * imprudent to remove them.) + */ + if (tle->ressortgroupref || tle->resjunk) + return false; + + /* + * If it's used by the upper query, we can't remove it. + */ + if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, + attrs_used)) + return false; + + /* + * If it contains a set-returning function, we can't remove it since that + * could change the number of rows returned by the subquery. + */ + if (subquery->hasTargetSRFs && + expression_returns_set(texpr)) + return false; + + /* + * If it contains volatile functions, we daren't remove it for fear that + * the user is expecting their side-effects to happen. + */ + if (contain_volatile_functions(texpr)) + return false; + + return true; +} + +/* + * define_live_winrefs_walker + * Collect the winrefs that the WindowFuncs of this query level still + * carry. + * + * find_window_functions() cannot serve here. It asserts that no SubLink is + * left in the tree, which holds only once the query has been through + * preprocess_expression(), and remove_unused_subquery_outputs() runs on a + * subquery that set_subquery_pathlist() has not planned yet. + * + * A sub-select is not descended into: a window function written there + * references that query level's own window clauses, not ours. Its test + * expression is another matter, because the parser leaves the left-hand + * operand of an ANY/ALL comparison there and that operand belongs to this + * level. + */ +static bool +define_live_winrefs_walker(Node *node, Bitmapset **winrefs) +{ + if (node == NULL) + return false; + if (IsA(node, WindowFunc)) + { + *winrefs = bms_add_member(*winrefs, ((WindowFunc *) node)->winref); + + /* + * A window function may not appear in another's arguments or filter + * clause, so there is nothing below this node to collect. + */ + return false; + } + if (IsA(node, SubLink)) + return define_live_winrefs_walker(((SubLink *) node)->testexpr, + winrefs); + return expression_tree_walker(node, define_live_winrefs_walker, winrefs); +} + /* * remove_unused_subquery_outputs * Remove subquery targetlist items we don't need @@ -4838,6 +4926,7 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, { Bitmapset *attrs_used; ListCell *lc; + bool has_rpr = false; /* * Just point directly to extra_used_attrs. No need to bms_copy as none of @@ -4887,46 +4976,103 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, return; /* - * Run through the tlist and zap entries we don't need. It's okay to - * modify the tlist items in-place because set_subquery_pathlist made a - * copy of the subquery. + * If the subquery uses row pattern recognition, work out which of its + * window clauses will still be active once this function is done, so that + * the DEFINE guard below does not hold a column alive for one that will + * not be. select_active_windows() keeps a window clause only while some + * window function references it, and the loop below is what removes those + * references, so settle the window function entries first and read the + * active set off what they leave. Reading it from the targetlist as it + * stands now would count a window function this call is about to remove. + * The same answer decides which DEFINE clauses to withdraw outright; see + * below. */ - foreach(lc, subquery->targetList) + foreach_node(WindowClause, wc, subquery->windowClause) { - TargetEntry *tle = (TargetEntry *) lfirst(lc); - Node *texpr = (Node *) tle->expr; + if (wc->rpPattern != NULL) + { + has_rpr = true; + break; + } + } - /* - * If it has a sortgroupref number, it's used in some sort/group - * clause so we'd better not remove it. Also, don't remove any - * resjunk columns, since their reason for being has nothing to do - * with anybody reading the subquery's output. (It's likely that - * resjunk columns in a sub-SELECT would always have ressortgroupref - * set, but even if they don't, it seems imprudent to remove them.) - */ - if (tle->ressortgroupref || tle->resjunk) - continue; + if (has_rpr) + { + Bitmapset *live_winrefs = NULL; /* - * If it's used by the upper query, we can't remove it. + * A window function entry the upper query does not read is replaced + * with a null Const, exactly as the loop below would replace it. Its + * window clause keeps whatever references survive that. */ - if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, - attrs_used)) - continue; + foreach(lc, subquery->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + Node *texpr = (Node *) tle->expr; + + if (IsA(texpr, WindowFunc) && + subquery_output_is_unneeded(subquery, tle, attrs_used)) + tle->expr = (Expr *) makeNullConst(exprType(texpr), + exprTypmod(texpr), + exprCollation(texpr)); + } - /* - * If it contains a set-returning function, we can't remove it since - * that could change the number of rows returned by the subquery. - */ - if (subquery->hasTargetSRFs && - expression_returns_set(texpr)) - continue; + define_live_winrefs_walker((Node *) subquery->targetList, + &live_winrefs); /* - * If it contains volatile functions, we daren't remove it for fear - * that the user is expecting their side-effects to happen. + * Withdraw the DEFINE clause of every window clause no window + * function references any more. select_active_windows() will leave + * such a clause out, so nothing downstream ever evaluates its DEFINE + * expressions -- and the loop below is about to replace the + * targetlist entries that were holding their input columns with null + * Consts. Empty the clause in the same breath, so that the parse + * tree stops carrying expressions that read columns no part of the + * plan computes. + * + * The DEFINE clause is the one field in which a window clause owns an + * expression tree of its own: partitionClause and orderClause only + * point into the targetlist, and a frame offset may not contain Vars. + * query_tree_walker() therefore walks it, and a Query-wide rewriter + * cannot tell a dead window's Vars from a live one's. In particular + * join removal, once nothing needs a relation any more, deletes its + * relid from the whole parse tree with ChangeVarNodes(..., + * INVALID_VAR, ...), which requires that no ordinary Var of that + * relation be left anywhere in it. Compare subquery_planner(), which + * clears joinaliasvars once they stop agreeing with the rest of the + * tree rather than teaching every later scan of the tree to skip + * them. + * + * Only defineClause is cleared, not the window clause itself: winref + * is a one-based index into windowClause, and bounds the + * find_window_functions() that select_active_windows() reads later, + * 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 leaving it is what keeps optimize_window_clauses() from + * mistaking an emptied RPR clause for a duplicate of a plain one. + * + * Modifying the clause is as safe as modifying the targetlist just + * above: set_subquery_pathlist() gave us a copy of the subquery. */ - if (contain_volatile_functions(texpr)) + foreach_node(WindowClause, wc, subquery->windowClause) + { + if (wc->defineClause != NIL && + !bms_is_member(wc->winref, live_winrefs)) + wc->defineClause = NIL; + } + } + + /* + * Run through the tlist and zap entries we don't need. It's okay to + * modify the tlist items in-place because set_subquery_pathlist made a + * copy of the subquery. + */ + foreach(lc, subquery->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + Node *texpr = (Node *) tle->expr; + + if (!subquery_output_is_unneeded(subquery, tle, attrs_used)) continue; /* @@ -4934,7 +5080,8 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, * 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. + * them. This is the only protection: nothing downstream re-adds a + * DEFINE column to the WindowAgg's input target. */ if (IsA(texpr, Var)) { @@ -4943,6 +5090,11 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, foreach_node(WindowClause, wc, subquery->windowClause) { + /* + * Only a window clause that will actually run still has a + * DEFINE clause at this point; the loop above emptied the + * others. + */ if (wc->defineClause != NIL) { /* @@ -4954,15 +5106,11 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, 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. + * Match varno too: varattno alone can collide with an + * unrelated column of another relation. varlevelsup + * is paranoia, since DEFINE rejects outer references + * at parse time. */ if (dvar->varno == var->varno && dvar->varattno == var->varattno && @@ -4981,32 +5129,6 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, 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/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c index 0873875c24a..187666a59cd 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -355,19 +355,36 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, 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. + * A DEFINE expression lives in wc->defineClause, not in the + * targetlist, so make_window_input_target() never sees it when + * deciding what the WindowAgg's input must carry. Yet setrefs.c must + * resolve every Var in the DEFINE clause to a column of that input, + * and fails on any column nothing else put there. Hence what a + * DEFINE expression reads must be planted in the targetlist as + * resjunk entries. + * + * Plant bare Vars, not subexpressions. A subexpression the target + * list already carries looks like a shortcut, but the two copies are + * preprocessed independently: given DEFINE A AS ROW(v, 1) IS NOT + * NULL, eval_const_expressions() breaks the DEFINE copy into per + * field tests and leaves a bare v behind, with nothing in the input + * to resolve it against. A bare Var has no such shape to lose. + * + * Whatever is planted has to reach the WindowAgg's input on its own: + * make_window_input_target() derives that input from final_target and + * adds nothing of its own for DEFINE, and + * remove_unused_subquery_outputs() keeps a column alive only for an + * entry that is resjunk or bears a sortgroupref, or that its own + * DEFINE guard matches. A resjunk entry qualifies. * * The walk stops at a subexpression GROUP BY computes and plants * nothing for it. parseCheckAggregates() replaces such a * subexpression with the grouping step's Var on both sides -- here * and in the target list entry holding the same expression -- so the * two copies still meet, and that entry bears a sortgroupref, which - * is enough to keep its column alive. Planting the columns + * the paragraph above says is enough. Planting the columns * underneath it instead would offer them to the grouping logic on - * their own, which does not make them available that way and reports + * their own, which does not make them available that way, and reports * them as ungrouped. The stop reads groupClause rather than a * sortgroupref, or the window's own ORDER BY would trip it in a query * that does no grouping at all. diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 179817e9952..03eb329b415 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -2406,12 +2406,29 @@ 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; + id +---- + 1 + 2 + 3 + 4 + 5 +(5 rows) + +-- 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 diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 2d5edebb128..7084abcfd13 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -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, rpr_integ.val 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,55 +500,441 @@ 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: rpr_integ.val, 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 guard must also cover a whole-row Var. Writing the bare --- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose --- attribute number is 0. remove_unused_subquery_outputs() matches the --- guard on attribute number, so the whole-row Var is retained as a single --- entry while the unused scalar "val" output is still replaced with NULL; --- DEFINE evaluates against the intact row, and the match is unchanged. +-- 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 the parser's resjunk targetlist entry is the only thing +-- carrying it into the WindowAgg's input. The trailing "val" on the +-- WindowAgg's Output line 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, val + 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, (COALESCE(rpr_integ_u.uval, 0)) + 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) + +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, rpr_integ_two.v1, 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), rpr_integ_two.v1, rpr_integ_two.id + Sort Key: (count(*) OVER w2) + -> WindowAgg + Output: (count(*) OVER w1), count(*) OVER w2, rpr_integ_two.v1, 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. Writing the bare relation name (rpr_integ) in +-- DEFINE resolves to a whole-row Var (attribute number 0). The parser's junk +-- targetlist entry carries it into the WindowAgg's input like any other +-- DEFINE column, so the pattern match sees the full row regardless of what +-- the subquery projects. The unused scalar output "val" is therefore free to +-- be replaced with NULL (nothing reads it), while c is kept because sum(c) +-- reads it; the match result is unchanged. EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ @@ -587,6 +970,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 -- ============================================================ diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 54bfee5534f..93cb431c921 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1692,13 +1692,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 ( diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 4d60545c6cb..95b4a829417 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -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,40 +334,227 @@ 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 the parser's resjunk targetlist entry is the only thing +-- carrying it into the WindowAgg's input. The trailing "val" on the +-- WindowAgg's Output line 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 guard must also cover a whole-row Var. Writing the bare --- relation name (rpr_integ) in DEFINE resolves to a whole-row Var, whose --- attribute number is 0. remove_unused_subquery_outputs() matches the --- guard on attribute number, so the whole-row Var is retained as a single --- entry while the unused scalar "val" output is still replaced with NULL; --- DEFINE evaluates against the intact row, and the match is unchanged. +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. Writing the bare relation name (rpr_integ) in +-- DEFINE resolves to a whole-row Var (attribute number 0). The parser's junk +-- targetlist entry carries it into the WindowAgg's input like any other +-- DEFINE column, so the pattern match sees the full row regardless of what +-- the subquery projects. The unused scalar output "val" is therefore free to +-- be replaced with NULL (nothing reads it), while c is kept because sum(c) +-- reads it; the match result is unchanged. EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ @@ -387,6 +572,34 @@ SELECT sum(c) FROM ( DEFINE B AS rpr_integ 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 -- ============================================================