v2-0004-Fix-self-join-elimination-work-with-PlaceHolderIn.patch

From f8f776c0338e7b923bed9fe80e2b3e5bb9326026 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Fri, 3 May 2024 01:51:13 +0300
Subject: [PATCH v2 4/4] Fix self-join elimination work with
 PlaceHolderInfo.ph_lateral

After removing the relation participating in the later, relation may end up
depending on itself according to the PlaceHolderInfo.ph_lateral.  This commit
makes remove_rel_from_query() revise PlaceHolderInfo.ph_lateral after
replacing relids there.

Discussion: https://postgr.es/m/CAMbWs49Q8g1LPVCeNHYv-Y-gFo826ertrg6nYNC9LL%3D8%3DzHP3g%40mail.gmail.com
Author: Richard Guo
Reviewed-by: Alexander Korotkov
---
 src/backend/optimizer/plan/analyzejoins.c |  7 +++++++
 src/test/regress/expected/join.out        | 13 +++++++++++++
 src/test/regress/sql/join.sql             |  8 ++++++++
 3 files changed, 28 insertions(+)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index d82d86b37f4..b52cce05da8 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -462,7 +462,14 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
 			phinfo->ph_needed = replace_relid(phinfo->ph_needed, ojrelid, subst);
 			/* ph_needed might or might not become empty */
 
+			/*
+			 * ph_lateral might contain rels mentioned in ph_eval_at after the
+			 * replacement.  We will remove them.  But first check they aren't
+			 * overlapping already.
+			 */
+			Assert(!bms_overlap(phinfo->ph_lateral, phinfo->ph_eval_at));
 			phinfo->ph_lateral = replace_relid(phinfo->ph_lateral, relid, subst);
+			phinfo->ph_lateral = bms_difference(phinfo->ph_lateral, phinfo->ph_eval_at);
 			/* ph_lateral might or might not be empty */
 
 			phv->phrels = replace_relid(phv->phrels, relid, subst);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 02765a7bc93..1b0b11c048d 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6234,6 +6234,19 @@ select * from sj t1
                Filter: (t1.a = a)
 (8 rows)
 
+-- Ensure that SJE does not form a self-referential lateral dependency
+explain (costs off)
+select * from sj t1
+    left join lateral
+      (select t1.a as t1a, * from sj t2) s
+    on true
+where t1.a = s.a;
+        QUERY PLAN         
+---------------------------
+ Seq Scan on sj t2
+   Filter: (a IS NOT NULL)
+(2 rows)
+
 -- Degenerated case.
 explain (costs off)
 select * from
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 0a4ea3df19c..a9844892067 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2360,6 +2360,14 @@ select * from sj t1
       (select * from sj tablesample system(t1.b)) s
     on t1.a = s.a;
 
+-- Ensure that SJE does not form a self-referential lateral dependency
+explain (costs off)
+select * from sj t1
+    left join lateral
+      (select t1.a as t1a, * from sj t2) s
+    on true
+where t1.a = s.a;
+
 -- Degenerated case.
 explain (costs off)
 select * from
-- 
2.39.3 (Apple Git-145)