From fcb8bccafe66cf7f1ded30e5237bb157a94c82e5 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sun, 7 Jun 2026 20:38:00 +0900 Subject: [PATCH 58/68] Compare varno when preserving DEFINE-referenced columns in row pattern recognition remove_unused_subquery_outputs keeps subquery output columns that an RPR DEFINE clause references. The check compared only varattno, so an unrelated output column of a different relation that happened to share an attribute number was over-retained and propagated through the plan. Also compare varno (and, as paranoia, varlevelsup), matching the pattern already used in set_function_pathlist. The result was already correct; this just stops retaining the needless column. --- src/backend/optimizer/path/allpaths.c | 13 ++++++- src/test/regress/expected/rpr_integration.out | 35 +++++++++++++++++++ src/test/regress/sql/rpr_integration.sql | 16 +++++++++ 3 files changed, 63 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index fb0506933cd..22339f7491f 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -4776,7 +4776,18 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel, { Var *dvar = (Var *) lfirst(vlc); - if (dvar->varattno == var->varattno) + /* + * 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; diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index b598ef95776..2133e2dfe13 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -1481,6 +1481,41 @@ ORDER BY o.id; 10 | 45 | 2 (10 rows) +-- A column referenced only by DEFINE must not keep an unrelated column that +-- merely shares its attribute number. DEFINE references a (rpr_over1); c +-- (rpr_over2) has the same attno but is unused, so it must be dropped. +CREATE TABLE rpr_over1 (a int); +CREATE TABLE rpr_over2 (c int); +INSERT INTO rpr_over1 VALUES (1),(2),(3); +INSERT INTO rpr_over2 VALUES (1),(2),(3); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT cnt FROM ( + SELECT a AS oa, c AS oc, count(*) OVER w AS cnt + FROM rpr_over1 CROSS JOIN rpr_over2 + WINDOW w AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a > 0) +) s; + QUERY PLAN +---------------------------------------------------------------------------------------------- + Subquery Scan on s + Output: s.cnt + -> WindowAgg + Output: rpr_over1.a, NULL::integer, count(*) OVER w + Window: w AS (ORDER BY rpr_over1.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: x+" + Nav Mark Lookback: 0 + -> Sort + Output: rpr_over1.a + Sort Key: rpr_over1.a + -> Nested Loop + Output: rpr_over1.a + -> Seq Scan on public.rpr_over1 + Output: rpr_over1.a + -> Materialize + -> Seq Scan on public.rpr_over2 +(16 rows) + +DROP TABLE rpr_over1, rpr_over2; -- Cleanup DROP TABLE rpr_integ; DROP TABLE rpr_integ2; diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 5f3853becba..24b0b1811b9 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -929,6 +929,22 @@ SELECT o.id, o.val, FROM rpr_integ o ORDER BY o.id; +-- A column referenced only by DEFINE must not keep an unrelated column that +-- merely shares its attribute number. DEFINE references a (rpr_over1); c +-- (rpr_over2) has the same attno but is unused, so it must be dropped. +CREATE TABLE rpr_over1 (a int); +CREATE TABLE rpr_over2 (c int); +INSERT INTO rpr_over1 VALUES (1),(2),(3); +INSERT INTO rpr_over2 VALUES (1),(2),(3); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT cnt FROM ( + SELECT a AS oa, c AS oc, count(*) OVER w AS cnt + FROM rpr_over1 CROSS JOIN rpr_over2 + WINDOW w AS (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (X+) DEFINE X AS a > 0) +) s; +DROP TABLE rpr_over1, rpr_over2; + -- Cleanup DROP TABLE rpr_integ; DROP TABLE rpr_integ2; -- 2.50.1 (Apple Git-155)