From 2bc8cf83f67252e4b7259979cb507c9ef7630990 Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 30 Jul 2026 15:42:11 +0900 Subject: [PATCH] Let an unreferenced RPR window function be removed remove_unused_subquery_outputs() refused to replace a window function with NULL when its window clause carried a DEFINE. The comment gave the reason: the pattern match had to run regardless, and nulling the only window function would leave the clause inactive and drop its WindowAgg. That reason does not hold. Row pattern recognition has no side effect; its only observable output is the window function's own result. If nothing reads that result, dropping the window function, the window clause and the WindowAgg with it changes no answer. Remove the guard, and rewrite the A5 tests to assert the plan that actually results: a plain Aggregate over the scan when the value is unused, and a WindowAgg when sum() reads it. The neighbouring guard, the one that keeps a subquery output only a DEFINE clause reads, stays. That guard and the junk targetlist entry the parser adds are between them the whole of what carries a DEFINE-only column into the WindowAgg's input; nothing further down puts such a column back. Say so in both places, and let the whole-row case in A5 name the same mechanism rather than the guard removed here. A5 also gains the two shapes that were missing. One is a top-level query, where remove_unused_subquery_outputs() does not run at all and the parser's entry stands alone. The other reaches the DEFINE clause with a PlaceHolderVar instead of a Var, by flattening a subquery output that an outer join makes nullable; coalesce() is what makes the wrapper appear at all, since a strict expression goes to NULL on its own and is substituted bare. One thing about this commit wants attention. The removed comment was wrong, and nothing records that. It asserted a side effect the executor does not have, and it stood long enough to shape the guard around it. The replacement comments describe the new plans but do not say that the old reasoning was mistaken, so the next reader has no way to tell the guard was removed on purpose rather than by oversight. --- src/backend/optimizer/path/allpaths.c | 29 +- src/backend/parser/parse_rpr.c | 6 + src/test/regress/expected/rpr_integration.out | 279 ++++++++++++++---- src/test/regress/sql/rpr_integration.sql | 145 ++++++--- 4 files changed, 331 insertions(+), 128 deletions(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index fc184c78816..e1b3dadbf39 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -4953,7 +4953,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)) { @@ -4998,32 +4999,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 6292cd0547f..34113699226 100644 --- a/src/backend/parser/parse_rpr.c +++ b/src/backend/parser/parse_rpr.c @@ -369,6 +369,12 @@ transformDefineClause(ParseState *pstate, WindowDef *windef, * 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. + * + * Every DEFINE Var must have a targetlist entry: + * make_window_input_target() derives the WindowAgg's input from + * final_target and adds nothing of its own for DEFINE, and + * remove_unused_subquery_outputs() relies on the entry being resjunk, + * or on its own guard, to keep the column alive. */ vars = pull_var_clause(expr, 0); foreach_node(Var, var, vars) diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 2d5edebb128..2b300d71801 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,215 @@ 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) + +DROP TABLE rpr_integ_u; +-- 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 diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 4d60545c6cb..498aa12b127 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,107 @@ 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; +) 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)); + +DROP TABLE rpr_integ_u; --- 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. +-- 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