v1-0001-Fix-how-SJE-replaces-join-clauses.patch

From 8669edb991f4cb9d48fe8f85bec86636eef24652 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 14 Nov 2023 18:55:24 +0800
Subject: [PATCH v1] Fix how SJE replaces join clauses

---
 src/backend/optimizer/plan/analyzejoins.c |  9 +++++++--
 src/test/regress/expected/join.out        | 15 +++++++++++++++
 src/test/regress/sql/join.sql             |  6 ++++++
 3 files changed, 28 insertions(+), 2 deletions(-)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index b9be19a687..f94449df94 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1518,12 +1518,17 @@ replace_relid(Relids relids, int oldId, int newId)
 	if (oldId < 0)
 		return relids;
 
+	/* Delete relid without substitution. */
 	if (newId < 0)
-		/* Delete relid without substitution. */
 		return bms_del_member(relids, oldId);
 
+	/*
+	 * Substitute newId for oldId.  We must make a copy of the original relids
+	 * before starting the substitution, because the same pointer to a
+	 * Bitmapset structure might be shared among different places.
+	 */
 	if (bms_is_member(oldId, relids))
-		return bms_add_member(bms_del_member(relids, oldId), newId);
+		return bms_add_member(bms_del_member(bms_copy(relids), oldId), newId);
 
 	return relids;
 }
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2c73270143..5ceecc4413 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6836,6 +6836,21 @@ select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join
    ->  Function Scan on generate_series t3
 (6 rows)
 
+-- Check that SJE replaces join clauses involving the removed rel correctly
+explain (costs off)
+select * from emp1 t1
+   inner join emp1 t2 on t1.id = t2.id
+    left join emp1 t3 on t1.id > 1 and t1.id < 2;
+                  QUERY PLAN                  
+----------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((t2.id > 1) AND (t2.id < 2))
+   ->  Seq Scan on emp1 t2
+         Filter: (id IS NOT NULL)
+   ->  Materialize
+         ->  Seq Scan on emp1 t3
+(6 rows)
+
 -- We can remove the join even if we find the join can't duplicate rows and
 -- the base quals of each side are different.  In the following case we end up
 -- moving quals over to s1 to make it so it can't match any rows.
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 8a8a63bd2f..c3ec340e2d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2606,6 +2606,12 @@ explain (costs off)
 select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join
     lateral (select t1.id as t1id, * from generate_series(1,1) t3) s on true;
 
+-- Check that SJE replaces join clauses involving the removed rel correctly
+explain (costs off)
+select * from emp1 t1
+   inner join emp1 t2 on t1.id = t2.id
+    left join emp1 t3 on t1.id > 1 and t1.id < 2;
+
 -- We can remove the join even if we find the join can't duplicate rows and
 -- the base quals of each side are different.  In the following case we end up
 -- moving quals over to s1 to make it so it can't match any rows.
-- 
2.31.0