0001-Replace-relids-in-lateral-subquery-target-list-du-v1.patch

From 6eac70fb171b33d23b1afce0363257bbef4c13dd Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sun, 18 Feb 2024 18:15:43 +0200
Subject: [PATCH] Replace relids in lateral subquery target list during SJE

Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/CAPpHfdsJ%3DMZNFw0L2T_k-shOk%2B4dPNx9j5RZzicy3NW%2ByiiQVg%40mail.gmail.com
---
 src/backend/optimizer/plan/analyzejoins.c |  3 +++
 src/test/regress/expected/join.out        | 14 ++++++++++++++
 src/test/regress/sql/join.sql             |  6 ++++++
 3 files changed, 23 insertions(+)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index e494acd51a6..88b124d99fc 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -372,6 +372,7 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
 	for (rti = 1; rti < root->simple_rel_array_size; rti++)
 	{
 		RelOptInfo *otherrel = root->simple_rel_array[rti];
+		RangeTblEntry *rte = root->simple_rte_array[rti];
 		int			attroff;
 
 		/* there may be empty slots corresponding to non-baserel RTEs */
@@ -396,6 +397,8 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
 
 		/* Update lateral references. */
 		replace_varno((Node *) otherrel->lateral_vars, relid, subst);
+		if (otherrel->rtekind == RTE_SUBQUERY && rte->lateral)
+			replace_varno((Node *) rte->subquery->targetList, relid, subst);
 	}
 
 	/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0c2cba89213..70789eaf462 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6349,6 +6349,20 @@ on true;
                ->  Seq Scan on int8_tbl y
 (7 rows)
 
+-- Test processing target lists in lateral subqueries
+explain (costs off)
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a,
+ lateral (select t1.a limit 1) t3;
+           QUERY PLAN            
+---------------------------------
+ Nested Loop
+   ->  Seq Scan on sj t2
+         Filter: (a IS NOT NULL)
+   ->  Limit
+         ->  Result
+(5 rows)
+
 -- Check updating of Lateral links from top-level query to the removing relation
 explain (COSTS OFF)
 SELECT * FROM pg_am am WHERE am.amname IN (
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 257f727a2be..c307b500402 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2406,6 +2406,12 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
 	on true) z
 on true;
 
+-- Test processing target lists in lateral subqueries
+explain (costs off)
+select t3.a from sj t1
+ join sj t2 on t1.a = t2.a,
+ lateral (select t1.a limit 1) t3;
+
 -- Check updating of Lateral links from top-level query to the removing relation
 explain (COSTS OFF)
 SELECT * FROM pg_am am WHERE am.amname IN (
-- 
2.39.3 (Apple Git-145)