0002-fix-variable-not-found-in-subplan-target-lists.patch

From dad22e812b3147e159f0203a41e9bf042a1d25d4 Mon Sep 17 00:00:00 2001
From: Sergey Soloviev <sergey.soloviev@tantorlabs.ru>
Date: Mon, 25 Aug 2025 19:53:39 +0300
Subject: [PATCH] fix: variable not found in subplan target lists

When restoring 'attr_needed' after self-join removal we should also
consider EC with constants because some plan nodes can use such constant
attributes.

For example, after converting `EXISTS` to JOIN planner can create
`UniquePath` referencing attribute which is contained in EC with
constant. But because `attr_needed` is NULL then this attribute is not
returned by JOIN, so at `set_plan_references` fails to find this
attribute in targetlist of node below.
---
 src/backend/optimizer/path/equivclass.c | 21 +++++++----
 src/test/regress/expected/join.out      | 47 +++++++++++++++++++++++++
 src/test/regress/sql/join.sql           | 26 ++++++++++++++
 3 files changed, 88 insertions(+), 6 deletions(-)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 441f12f6c50..58ca75ab7a8 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2586,18 +2586,27 @@ rebuild_eclass_attr_needed(PlannerInfo *root)
 		 */
 		Assert(ec->ec_childmembers == NULL);
 
-		/* Need do anything only for a multi-member, no-const EC. */
-		if (list_length(ec->ec_members) > 1 && !ec->ec_has_const)
+		/*
+		 * Need do anything only for a multi-member. Note that some
+		 * wrapping plan nodes (i.e. UniquePath) can reference attributes
+		 * contained in EC with constants.
+		 */
+		if (list_length(ec->ec_members) > 1)
 		{
 			ListCell   *lc2;
 
 			foreach(lc2, ec->ec_members)
 			{
 				EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
-				List	   *vars = pull_var_clause((Node *) cur_em->em_expr,
-												   PVC_RECURSE_AGGREGATES |
-												   PVC_RECURSE_WINDOWFUNCS |
-												   PVC_INCLUDE_PLACEHOLDERS);
+				List	   *vars;
+
+				if (cur_em->em_is_const)
+					continue;
+
+				vars = pull_var_clause((Node *) cur_em->em_expr,
+										PVC_RECURSE_AGGREGATES |
+										PVC_RECURSE_WINDOWFUNCS |
+										PVC_INCLUDE_PLACEHOLDERS);
 
 				add_vars_to_attr_needed(root, vars, ec->ec_relids);
 				list_free(vars);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 98b05c94a11..71654f528b1 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6500,6 +6500,53 @@ where t1.a = s.c;
 ----------
 (0 rows)
 
+rollback;
+-- yet another join removal bug: EquivalenceClass cleanup must preserve
+-- dependency with JOINs above even if EC has constant
+begin;
+create table t (a int, b int);
+create unique index on t (a);
+explain (costs off)
+select t1.a from t t1
+  left join t t2 on t1.a = t2.a
+       join t t3 on true
+where exists (select 1 from t t4
+                join t t5 on t4.b = t5.b
+                join t t6 on t5.b = t6.b
+              where t1.a = t4.a and t3.a = t5.a and t4.a = 2);
+                                 QUERY PLAN                                 
+----------------------------------------------------------------------------
+ Nested Loop
+   ->  Nested Loop
+         ->  Index Only Scan using t_a_idx on t t1
+               Index Cond: (a = 2)
+         ->  HashAggregate
+               Group Key: t4.a, t5.a
+               ->  Hash Join
+                     Hash Cond: (t6.b = t4.b)
+                     ->  Seq Scan on t t6
+                     ->  Hash
+                           ->  Hash Join
+                                 Hash Cond: (t5.b = t4.b)
+                                 ->  Seq Scan on t t5
+                                 ->  Hash
+                                       ->  Index Scan using t_a_idx on t t4
+                                             Index Cond: (a = 2)
+   ->  Index Only Scan using t_a_idx on t t3
+         Index Cond: (a = t5.a)
+(18 rows)
+
+select t1.a from t t1
+  left join t t2 on t1.a = t2.a
+       join t t3 on true
+where exists (select 1 from t t4
+                join t t5 on t4.b = t5.b
+                join t t6 on t5.b = t6.b
+              where t1.a = t4.a and t3.a = t5.a and t4.a = 2);
+ a 
+---
+(0 rows)
+
 rollback;
 -- test cases where we can remove a join, but not a PHV computed at it
 begin;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 5f0a475894d..d62b4abd817 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2420,6 +2420,32 @@ where t1.a = s.c;
 
 rollback;
 
+-- yet another join removal bug: EquivalenceClass cleanup must preserve
+-- dependency with JOINs above even if EC has constant
+begin;
+
+create table t (a int, b int);
+create unique index on t (a);
+
+explain (costs off)
+select t1.a from t t1
+  left join t t2 on t1.a = t2.a
+       join t t3 on true
+where exists (select 1 from t t4
+                join t t5 on t4.b = t5.b
+                join t t6 on t5.b = t6.b
+              where t1.a = t4.a and t3.a = t5.a and t4.a = 2);
+
+select t1.a from t t1
+  left join t t2 on t1.a = t2.a
+       join t t3 on true
+where exists (select 1 from t t4
+                join t t5 on t4.b = t5.b
+                join t t6 on t5.b = t6.b
+              where t1.a = t4.a and t3.a = t5.a and t4.a = 2);
+
+rollback;
+
 -- test cases where we can remove a join, but not a PHV computed at it
 begin;
 
-- 
2.43.0