nocfbot-0058-Compare-varno-when-preserving-DEFINE-referenced-c.txt

From 2488c283c953e9421b7b3fd7fc795ad47c272f8f Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
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)