v4-0002-Apply-SJE-to-DML-queries-with-RETURNING-clause.patch

From 3188ff2ee9db2f67290594fd7cdad0d3b1e5359d Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepihov@gmail.com>
Date: Tue, 9 Jul 2024 12:25:23 +0700
Subject: [PATCH 2/2] Apply SJE to DML queries with RETURNING clause

---
 src/backend/optimizer/plan/analyzejoins.c     |  32 +-
 src/test/regress/expected/join.out            | 687 +++++++++++++++++-
 src/test/regress/expected/updatable_views.out |  17 +-
 src/test/regress/sql/join.sql                 | 270 +++++++
 4 files changed, 963 insertions(+), 43 deletions(-)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index bb14597762..7fc59d80cf 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1860,10 +1860,6 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
 	/* restore the rangetblref in a proper order. */
 	restore_rangetblref((Node *) root->parse, toKeep->relid, toRemove->relid, 0, 0);
 
-	/* See remove_self_joins_one_group() */
-	Assert(root->parse->resultRelation != toRemove->relid);
-	Assert(root->parse->resultRelation != toKeep->relid);
-
 	/* Replace links in the planner info */
 	remove_rel_from_query(root, toRemove, toKeep->relid, NULL, NULL);
 
@@ -2046,14 +2042,6 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
 	{
 		RelOptInfo *inner = root->simple_rel_array[r];
 
-		/*
-		 * We don't accept result relation as either source or target relation
-		 * of SJE, because result relation has different behavior in
-		 * EvalPlanQual() and RETURNING clause.
-		 */
-		if (root->parse->resultRelation == r)
-			continue;
-
 		k = r;
 
 		while ((k = bms_next_member(relids, k)) > 0)
@@ -2069,9 +2057,6 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
 			PlanRowMark *imark = NULL;
 			List	   *uclauses = NIL;
 
-			if (root->parse->resultRelation == k)
-				continue;
-
 			/* A sanity check: the relations have the same Oid. */
 			Assert(root->simple_rte_array[k]->relid ==
 				   root->simple_rte_array[r]->relid);
@@ -2300,6 +2285,23 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist, Relids toRemove)
 
 				relids = bms_del_members(relids, group);
 
+				/*
+				 * if one of relations is in the RETURNING statement
+				 * we can only apply this optimisation if returning list
+				 * contains only resultRelation relid (we don't apply it to
+				 * partitioned relations so far) or doesn't contain result
+				 * relation at all.
+				 */
+				if (root->parse && root->parse->returningList != NIL)
+				{
+					Relids rets = pull_varnos_of_level(
+								root, (Node *) root->parse->returningList, 0);
+
+					if (bms_is_member(root->parse->resultRelation, rets) &&
+						bms_num_members(rets) != 1)
+						continue;
+				}
+
 				/*
 				 * Try to remove self-joins from a group of identical entries.
 				 * Make the next attempt iteratively - if something is deleted
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 4e4cec633a..b889b2889c 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6440,6 +6440,663 @@ on true;
                ->  Seq Scan on int8_tbl y
 (7 rows)
 
+--truncate sj, refill data.
+truncate sj;
+insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g;
+analyze sj;
+------------------- UPDATE SJE (self join elimination) applicable cases.
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2);
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Update on sj sz
+   ->  Seq Scan on sj sz
+         Filter: ((a IS NOT NULL) AND ((b = 2) OR (a = 2)))
+(3 rows)
+
+UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2);
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Update on sj
+   ->  Seq Scan on sj
+         Filter: ((b IS NOT NULL) AND (a = 3))
+(3 rows)
+
+WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3;
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4;
+       QUERY PLAN        
+-------------------------
+ Update on sj
+   ->  Seq Scan on sj
+         Filter: (a = 4)
+(3 rows)
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4;
+select * from sj order by a;
+ a  | b | c  
+----+---+----
+  1 | 1 | 11
+  2 | 4 | 12
+  5 | 5 | 15
+  6 | 6 | 16
+  7 | 7 | 17
+  8 | 8 | 18
+  9 | 9 | 19
+ 10 | 0 | 20
+ 12 | 4 | 14
+ 19 | 3 | 13
+(10 rows)
+
+------------------- UPDATE SJE not applicable cases.
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sz.a = sq.a or (sq.b = 2 or sq.a = 2);
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Update on sj sq
+   ->  Nested Loop
+         Join Filter: ((sz.a = sq.a) OR (sq.b = 2) OR (sq.a = 2))
+         ->  Seq Scan on sj sq
+         ->  Materialize
+               ->  Seq Scan on sj sz
+(6 rows)
+
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a;
+             QUERY PLAN             
+------------------------------------
+ Update on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.a = sz.b)
+         ->  Seq Scan on sj sq
+               Filter: (a = b)
+         ->  Seq Scan on sj sz
+(6 rows)
+
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3;
+             QUERY PLAN             
+------------------------------------
+ Update on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.b = sz.b)
+         ->  Seq Scan on sj sq
+               Filter: (a = 3)
+         ->  Seq Scan on sj sz
+               Filter: (a = 2)
+(7 rows)
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + 1 FROM t1 WHERE t1.b = sj.b and t1.a = 2 and sj.a = 3;
+              QUERY PLAN              
+--------------------------------------
+ Update on sj
+   ->  Nested Loop
+         Join Filter: (sj.b = sj_1.b)
+         ->  Seq Scan on sj
+               Filter: (a = 3)
+         ->  Seq Scan on sj sj_1
+               Filter: (a = 2)
+(7 rows)
+
+------------------- DELETE SJE applicable cases.
+EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5;
+       QUERY PLAN        
+-------------------------
+ Delete on sj sz
+   ->  Seq Scan on sj sz
+         Filter: (a = 5)
+(3 rows)
+
+DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5;
+EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113);
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Delete on sj sz
+   ->  Seq Scan on sj sz
+         Filter: ((a IS NOT NULL) AND ((a = 6) OR (b = 113)))
+(3 rows)
+
+DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113);
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7;
+                  QUERY PLAN                   
+-----------------------------------------------
+ Delete on sj t1
+   ->  Seq Scan on sj t1
+         Filter: ((b IS NOT NULL) AND (a = 7))
+(3 rows)
+
+WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7;
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8;
+       QUERY PLAN        
+-------------------------
+ Delete on sj
+   ->  Seq Scan on sj
+         Filter: (a = 8)
+(3 rows)
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8;
+select * from sj order by a;
+ a  | b | c  
+----+---+----
+  1 | 1 | 11
+  2 | 4 | 12
+  9 | 9 | 19
+ 10 | 0 | 20
+ 12 | 4 | 14
+ 19 | 3 | 13
+(6 rows)
+
+------------------- DELETE SJE not applicable cases.
+EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a;
+             QUERY PLAN             
+------------------------------------
+ Delete on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.a = sz.b)
+         ->  Seq Scan on sj sq
+               Filter: (a = b)
+         ->  Seq Scan on sj sz
+(6 rows)
+
+EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3;
+             QUERY PLAN             
+------------------------------------
+ Delete on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.b = sz.b)
+         ->  Seq Scan on sj sq
+               Filter: (a = 3)
+         ->  Seq Scan on sj sz
+               Filter: (a = 2)
+(7 rows)
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 3;
+             QUERY PLAN             
+------------------------------------
+ Delete on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.b = t1.b)
+         ->  Seq Scan on sj t1
+               Filter: (a = 3)
+         ->  Seq Scan on sj sq
+(6 rows)
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.a = sq.c;
+             QUERY PLAN              
+-------------------------------------
+ Delete on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.c = t1.a)
+         ->  Seq Scan on sj sq
+         ->  Materialize
+               ->  Seq Scan on sj t1
+(6 rows)
+
+---UPDATE/DELETE RETURNING SJE applicable cases.
+truncate sj;
+insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g;
+analyze sj;
+----test query that can use SJE
+explain(costs off)
+UPDATE sj sq
+SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2
+returning sq.a, sq.b, (select sq.c);
+       QUERY PLAN        
+-------------------------
+ Update on sj sz
+   ->  Seq Scan on sj sz
+         Filter: (a = 2)
+   SubPlan 1
+     ->  Result
+(5 rows)
+
+explain(costs off)
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.a and sz.a = 3
+returning sq.*;
+       QUERY PLAN        
+-------------------------
+ Update on sj s2
+   ->  Seq Scan on sj s2
+         Filter: (a = 3)
+(3 rows)
+
+explain(costs off)
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sz.a = 4 and sq.a = 4
+returning sq.*, (select sq.a);
+             QUERY PLAN             
+------------------------------------
+ Update on sj s1
+   ->  Nested Loop
+         Join Filter: (s1.b = s2.b)
+         ->  Seq Scan on sj s1
+               Filter: (a = 4)
+         ->  Seq Scan on sj s2
+   SubPlan 1
+     ->  Result
+(8 rows)
+
+explain(costs off)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5
+returning sq.a, sq.a * sq.b, (select sq.a + sq.b);
+       QUERY PLAN        
+-------------------------
+ Delete on sj
+   ->  Seq Scan on sj
+         Filter: (a = 5)
+   SubPlan 1
+     ->  Result
+(5 rows)
+
+explain(costs off)
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sq.a = sz.a and sq.a = 6
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+             QUERY PLAN             
+------------------------------------
+ Delete on sj s1
+   ->  Nested Loop
+         Join Filter: (s1.b = s2.b)
+         ->  Seq Scan on sj s1
+               Filter: (a = 6)
+         ->  Seq Scan on sj s2
+(6 rows)
+
+explain(costs off)
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.b and sz.a = 7
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+             QUERY PLAN             
+------------------------------------
+ Delete on sj sq
+   ->  Nested Loop
+         Join Filter: (sq.a = s2.b)
+         ->  Seq Scan on sj s2
+               Filter: (a = 7)
+         ->  Seq Scan on sj sq
+(6 rows)
+
+----actually execute the query to validate the result
+UPDATE sj sq
+SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2
+returning sq.a, sq.b, (select sq.c);
+ a  | b  | c  
+----+----+----
+ 17 | 17 | 32
+(1 row)
+
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.a and sz.a = 3
+returning sq.*;
+ a  | b  | c  
+----+----+----
+ 18 | 18 | 38
+(1 row)
+
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sz.a = 4 and sq.a = 4
+returning sq.*, (select sq.a);
+ a  | b  | c  | a  
+----+----+----+----
+ 19 | 19 | 44 | 19
+(1 row)
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5
+returning sq.a, sq.a * sq.b, (select sq.a + sq.b);
+ a | ?column? | ?column? 
+---+----------+----------
+ 5 |       25 |       10
+(1 row)
+
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sq.a = sz.a and sq.a = 6
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+ a | b | c  | tbl 
+---+---+----+-----
+ 6 | 6 | 16 | sj
+(1 row)
+
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.b and sz.a = 7
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+ a | b | c  | tbl 
+---+---+----+-----
+ 7 | 7 | 17 | sj
+(1 row)
+
+select * from sj order by a;
+ a  | b  | c  
+----+----+----
+  1 |  1 | 11
+  8 |  8 | 18
+  9 |  9 | 19
+ 10 |  0 | 20
+ 17 | 17 | 32
+ 18 | 18 | 38
+ 19 | 19 | 44
+(7 rows)
+
+------------------- MERGE SJE table setup
+CREATE TABLE sj_target (tid integer primary key, balance integer) WITH (autovacuum_enabled=off);
+INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60);
+create view rw_sj_target as select * from sj_target where tid >= 2;
+create or replace view no_rw_sj_target as select * from sj_target where balance = 60 limit 1;
+--cannot use SJE for RETURNING
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10
+    THEN update set balance = t.balance + 11 RETURNING *;
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop
+         ->  Seq Scan on sj_target t
+         ->  Index Scan using sj_target_pkey on sj_target s
+               Index Cond: (tid = t.tid)
+(5 rows)
+
+--cannot use SJE for merge insert
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN NOT MATCHED AND s.balance = 10
+    THEN INSERT VALUES (s.tid, s.balance);
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop Left Join
+         ->  Seq Scan on sj_target s
+         ->  Index Scan using sj_target_pkey on sj_target t
+               Index Cond: (tid = s.tid)
+(5 rows)
+
+--cannot use SJE for merge with non-updatable view
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 60
+    THEN update set balance = t.balance + 6;
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop
+         ->  Subquery Scan on s
+               ->  Limit
+                     ->  Seq Scan on sj_target
+                           Filter: (balance = 60)
+         ->  Index Scan using sj_target_pkey on sj_target t
+               Index Cond: (tid = s.tid)
+(8 rows)
+
+---- the following cases can apply SJE with merge.
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 20
+    THEN update set balance = t.balance + 2;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Merge on sj_target
+   ->  Bitmap Heap Scan on sj_target
+         Recheck Cond: (tid >= 2)
+         ->  Bitmap Index Scan on sj_target_pkey
+               Index Cond: (tid >= 2)
+(5 rows)
+
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10
+    THEN update set balance = t.balance + 2;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Merge on sj_target
+   ->  Bitmap Heap Scan on sj_target
+         Recheck Cond: (tid >= 2)
+         ->  Bitmap Index Scan on sj_target_pkey
+               Index Cond: (tid >= 2)
+(5 rows)
+
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 30
+    THEN update set balance = t.balance + 3;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Merge on sj_target
+   ->  Bitmap Heap Scan on sj_target
+         Recheck Cond: (tid >= 2)
+         ->  Bitmap Index Scan on sj_target_pkey
+               Index Cond: (tid >= 2)
+(5 rows)
+
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 40
+    THEN update set balance = t.balance + 4;
+          QUERY PLAN           
+-------------------------------
+ Merge on sj_target s
+   ->  Seq Scan on sj_target s
+(2 rows)
+
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50
+    THEN DELETE;
+          QUERY PLAN           
+-------------------------------
+ Merge on sj_target s
+   ->  Seq Scan on sj_target s
+(2 rows)
+
+--- and run the actual query.
+MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 60 THEN update set balance = t.balance + 6;
+MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 20 THEN update set balance = t.balance + 2;
+MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 30 THEN update set balance = t.balance + 3;
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 40 THEN update set balance = t.balance + 4;
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50 THEN DELETE;
+MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10 THEN update set balance = t.balance + 1;
+select * from sj_target order by tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      22
+   3 |      33
+   4 |      44
+   6 |      66
+(5 rows)
+
+-------------------MERGRE RETURNING SJE TEST--------------------------------
+TRUNCATE sj_target;
+INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60);
+--"when matched" and "when not matched" both specified
+--because of "when not matched" then can only use left join
+--therefore cannot apply SJE
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance, tid = t.tid + s.tid
+  WHEN NOT MATCHED BY SOURCE AND t.balance = 20 THEN DELETE
+  RETURNING t.*, (select t.balance), merge_action();
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop Left Join
+         ->  Seq Scan on sj_target t
+         ->  Index Scan using sj_target_pkey on sj_target s
+               Index Cond: (tid = t.tid)
+   SubPlan 1
+     ->  Result
+(7 rows)
+
+---"when not matched" using left join, SJE cannot be applied
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 10 THEN
+    DELETE
+returning t.*;
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop Left Join
+         ->  Seq Scan on sj_target t
+         ->  Index Scan using sj_target_pkey on sj_target s
+               Index Cond: (tid = t.tid)
+(5 rows)
+
+---"when not matched by target" using left join, SJE cannot be applied
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target  s ON t.tid = s.tid
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.tid, 11)
+  RETURNING t.*, merge_action();
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop Left Join
+         ->  Seq Scan on sj_target s
+         ->  Index Scan using sj_target_pkey on sj_target t
+               Index Cond: (tid = s.tid)
+(5 rows)
+
+---returning with multiple rel, merge returning cannot be applied.
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50
+    THEN UPDATE SET balance = t.balance + s.balance + 50
+    RETURNING s.*, t.balance, merge_action();
+                         QUERY PLAN                         
+------------------------------------------------------------
+ Merge on sj_target t
+   ->  Nested Loop
+         ->  Seq Scan on sj_target t
+         ->  Index Scan using sj_target_pkey on sj_target s
+               Index Cond: (tid = t.tid)
+(5 rows)
+
+--------the following (explain) query can use SJE.
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (select t.balance), merge_action();
+          QUERY PLAN           
+-------------------------------
+ Merge on sj_target s
+   ->  Seq Scan on sj_target s
+   SubPlan 1
+     ->  Result
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND s.balance = 20
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Merge on sj_target s0
+   ->  Nested Loop
+         Join Filter: (s0.balance = s1.balance)
+         ->  Seq Scan on sj_target s1
+         ->  Materialize
+               ->  Bitmap Heap Scan on sj_target s0
+                     Recheck Cond: (tid >= 2)
+                     ->  Bitmap Index Scan on sj_target_pkey
+                           Index Cond: (tid >= 2)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 30
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Merge on sj_target
+   ->  Nested Loop
+         Join Filter: (s0.balance = sj_target.balance)
+         ->  Seq Scan on sj_target s0
+         ->  Materialize
+               ->  Bitmap Heap Scan on sj_target
+                     Recheck Cond: (tid >= 2)
+                     ->  Bitmap Index Scan on sj_target_pkey
+                           Index Cond: (tid >= 2)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 40 AND s.balance = 40
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (SELECT t.tableoid::regclass), merge_action();
+                   QUERY PLAN                   
+------------------------------------------------
+ Merge on sj_target s0
+   ->  Nested Loop
+         Join Filter: (s0.balance = s1.balance)
+         ->  Seq Scan on sj_target s0
+         ->  Materialize
+               ->  Seq Scan on sj_target s1
+   SubPlan 1
+     ->  Result
+(8 rows)
+
+--------and actually running query validate the result.
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (select t.balance), merge_action();
+ tid | balance | balance | merge_action 
+-----+---------+---------+--------------
+  12 |     120 |     120 | UPDATE
+(1 row)
+
+MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND s.balance = 20
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+ tid | balance | merge_action 
+-----+---------+--------------
+  14 |     140 | UPDATE
+(1 row)
+
+MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 30
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+ tid | balance | merge_action 
+-----+---------+--------------
+  16 |     160 | UPDATE
+(1 row)
+
+MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 40 AND s.balance = 40
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (SELECT t.tableoid::regclass), merge_action();
+ tid | balance | tableoid  | merge_action 
+-----+---------+-----------+--------------
+  18 |     180 | sj_target | UPDATE
+(1 row)
+
+select * from sj_target;
+ tid | balance 
+-----+---------
+   5 |      50
+   6 |      60
+  12 |     120
+  14 |     140
+  16 |     160
+  18 |     180
+(6 rows)
+
+DROP VIEW no_rw_sj_target;
+DROP VIEW rw_sj_target;
+DROP TABLE sj_target;
+truncate sj;
+insert into sj values (1, null, 2), (null, 2, null), (2, 1, 1);
+analyze sj;
 -- Test that references to the removed rel in lateral subqueries are replaced
 -- correctly after join removal
 explain (verbose, costs off)
@@ -7058,29 +7715,23 @@ WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code;
 TRUNCATE emp1;
 EXPLAIN (COSTS OFF)
 UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.a;
-             QUERY PLAN              
--------------------------------------
- Update on sj sq
-   ->  Nested Loop
-         Join Filter: (sq.a = sz.a)
-         ->  Seq Scan on sj sq
-         ->  Materialize
-               ->  Seq Scan on sj sz
-(6 rows)
+           QUERY PLAN            
+---------------------------------
+ Update on sj sz
+   ->  Seq Scan on sj sz
+         Filter: (a IS NOT NULL)
+(3 rows)
 
 CREATE RULE sj_del_rule AS ON DELETE TO sj
   DO INSTEAD
     UPDATE sj SET a = 1 WHERE a = old.a;
 EXPLAIN (COSTS OFF) DELETE FROM sj;
-              QUERY PLAN              
---------------------------------------
- Update on sj sj_1
-   ->  Nested Loop
-         Join Filter: (sj.a = sj_1.a)
-         ->  Seq Scan on sj sj_1
-         ->  Materialize
-               ->  Seq Scan on sj
-(6 rows)
+           QUERY PLAN            
+---------------------------------
+ Update on sj
+   ->  Seq Scan on sj
+         Filter: (a IS NOT NULL)
+(3 rows)
 
 DROP RULE sj_del_rule ON sj CASCADE;
 -- Check that SJE does not mistakenly omit qual clauses (bug #18187)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 9c21b76800..2c6487818a 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3111,16 +3111,13 @@ SELECT * FROM rw_view1;
 (1 row)
 
 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
-                            QUERY PLAN                             
--------------------------------------------------------------------
- Update on base_tbl base_tbl_1
-   ->  Nested Loop
-         ->  Index Scan using base_tbl_pkey on base_tbl base_tbl_1
-               Index Cond: (id = 1)
-         ->  Index Scan using base_tbl_pkey on base_tbl
-               Index Cond: (id = 1)
-               Filter: ((NOT deleted) AND snoop(data))
-(7 rows)
+                    QUERY PLAN                    
+--------------------------------------------------
+ Update on base_tbl
+   ->  Index Scan using base_tbl_pkey on base_tbl
+         Index Cond: (id = 1)
+         Filter: ((NOT deleted) AND snoop(data))
+(4 rows)
 
 DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
 NOTICE:  snooped value: Row 1
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 3e94e0af53..981f9a8c47 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2450,6 +2450,276 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
 	on true) z
 on true;
 
+--truncate sj, refill data.
+truncate sj;
+insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g;
+analyze sj;
+------------------- UPDATE SJE (self join elimination) applicable cases.
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2);
+
+UPDATE sj sq SET b = sq.b + sz.a FROM sj as sz WHERE sz.a = sq.a and (sq.b = 2 or sq.a = 2);
+
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3;
+
+WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + t1.a + t1.c FROM t1 WHERE t1.b = sj.b and t1.a = 3 and sj.a = 3;
+
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4;
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) UPDATE sj SET a = sj.a + t1.b + t1.a FROM t1 WHERE t1.a = sj.a and t1.a = 4;
+
+select * from sj order by a;
+------------------- UPDATE SJE not applicable cases.
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sz.a = sq.a or (sq.b = 2 or sq.a = 2);
+
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a;
+
+EXPLAIN (COSTS OFF) UPDATE sj sq SET b = 1 FROM sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3;
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) UPDATE sj SET a = sj.a + 1 FROM t1 WHERE t1.b = sj.b and t1.a = 2 and sj.a = 3;
+
+------------------- DELETE SJE applicable cases.
+EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5;
+
+DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and sq.a = 5;
+
+EXPLAIN (COSTS OFF) DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113);
+
+DELETE FROM sj sq using sj as sz WHERE sz.a = sq.a and (sz.a = 6 or sz.b = 113);
+
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7;
+
+WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 7 and sq.a = 7;
+
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8;
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 8;
+
+select * from sj order by a;
+------------------- DELETE SJE not applicable cases.
+EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.a = sz.b and sq.b = sz.b and sz.b = sq.a;
+
+EXPLAIN (COSTS OFF) DELETE FROM sj sq USING sj as sz WHERE sq.b = sz.b and sz.a = 2 and sq.a = 3;
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.b = sq.b and t1.a = 3;
+
+EXPLAIN (COSTS OFF) WITH t1 AS (SELECT * FROM sj) DELETE FROM sj sq USING sj as t1 WHERE t1.a = sq.c;
+
+---UPDATE/DELETE RETURNING SJE applicable cases.
+truncate sj;
+insert into sj(a,b,c) select g, g % 10, g + 10 from generate_series(1, 10) g;
+analyze sj;
+
+----test query that can use SJE
+explain(costs off)
+UPDATE sj sq
+SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2
+returning sq.a, sq.b, (select sq.c);
+
+explain(costs off)
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.a and sz.a = 3
+returning sq.*;
+
+explain(costs off)
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sz.a = 4 and sq.a = 4
+returning sq.*, (select sq.a);
+
+explain(costs off)
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5
+returning sq.a, sq.a * sq.b, (select sq.a + sq.b);
+
+explain(costs off)
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sq.a = sz.a and sq.a = 6
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+
+explain(costs off)
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.b and sz.a = 7
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+
+----actually execute the query to validate the result
+UPDATE sj sq
+SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM sj sz WHERE sq.a = sz.a and sz.a = 2 and sq.a = 2
+returning sq.a, sq.b, (select sq.c);
+
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.a and sz.a = 3
+returning sq.*;
+
+UPDATE sj sq SET c = sz.a + sq.a + sz.b + sq.b + sz.c + sq.c, a = sz.a + 15, b = sq.a + 15
+FROM (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sz.a = 4 and sq.a = 4
+returning sq.*, (select sq.a);
+
+WITH t1 AS (SELECT *, (select count(*) from sj) FROM sj) DELETE FROM sj sq using t1 as sz where sq.a = sz.a and sz.a = 5
+returning sq.a, sq.a * sq.b, (select sq.a + sq.b);
+
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.b = s2.b) as sz
+WHERE sq.a = sz.a and sq.a = 6
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+
+delete from sj sq using (select s1.b, s1.a as a, s2.a as a1, s2.c from sj s1 join sj s2 on s1.a = s2.a) as sz
+WHERE sq.a = sz.b and sz.a = 7
+returning sq.a, sq.b, sq.c, sq.tableoid::regclass as tbl;
+
+select * from sj order by a;
+------------------- MERGE SJE table setup
+CREATE TABLE sj_target (tid integer primary key, balance integer) WITH (autovacuum_enabled=off);
+INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60);
+create view rw_sj_target as select * from sj_target where tid >= 2;
+create or replace view no_rw_sj_target as select * from sj_target where balance = 60 limit 1;
+
+--cannot use SJE for RETURNING
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10
+    THEN update set balance = t.balance + 11 RETURNING *;
+--cannot use SJE for merge insert
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN NOT MATCHED AND s.balance = 10
+    THEN INSERT VALUES (s.tid, s.balance);
+
+--cannot use SJE for merge with non-updatable view
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 60
+    THEN update set balance = t.balance + 6;
+
+---- the following cases can apply SJE with merge.
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 20
+    THEN update set balance = t.balance + 2;
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10
+    THEN update set balance = t.balance + 2;
+EXPLAIN (COSTS OFF) MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 30
+    THEN update set balance = t.balance + 3;
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 40
+    THEN update set balance = t.balance + 4;
+EXPLAIN (COSTS OFF) MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50
+    THEN DELETE;
+
+--- and run the actual query.
+MERGE INTO sj_target t USING no_rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 60 THEN update set balance = t.balance + 6;
+
+MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 20 THEN update set balance = t.balance + 2;
+
+MERGE INTO rw_sj_target t USING rw_sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 30 THEN update set balance = t.balance + 3;
+
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 40 THEN update set balance = t.balance + 4;
+
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50 THEN DELETE;
+
+MERGE INTO rw_sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 10 THEN update set balance = t.balance + 1;
+
+select * from sj_target order by tid;
+-------------------MERGRE RETURNING SJE TEST--------------------------------
+TRUNCATE sj_target;
+INSERT INTO sj_target VALUES (1, 10),(2, 20), (3, 30), (4, 40),(5, 50), (6, 60);
+
+--"when matched" and "when not matched" both specified
+--because of "when not matched" then can only use left join
+--therefore cannot apply SJE
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance, tid = t.tid + s.tid
+  WHEN NOT MATCHED BY SOURCE AND t.balance = 20 THEN DELETE
+  RETURNING t.*, (select t.balance), merge_action();
+
+---"when not matched" using left join, SJE cannot be applied
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+WHEN NOT MATCHED BY SOURCE AND t.balance = 10 THEN
+    DELETE
+returning t.*;
+
+---"when not matched by target" using left join, SJE cannot be applied
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target  s ON t.tid = s.tid
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.tid, 11)
+  RETURNING t.*, merge_action();
+
+---returning with multiple rel, merge returning cannot be applied.
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+    WHEN MATCHED AND t.balance = 50
+    THEN UPDATE SET balance = t.balance + s.balance + 50
+    RETURNING s.*, t.balance, merge_action();
+
+--------the following (explain) query can use SJE.
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (select t.balance), merge_action();
+
+EXPLAIN (COSTS OFF)
+MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND s.balance = 20
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+
+EXPLAIN (COSTS OFF)
+MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 30
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+
+EXPLAIN (COSTS OFF)
+MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 40 AND s.balance = 40
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (SELECT t.tableoid::regclass), merge_action();
+--------and actually running query validate the result.
+MERGE INTO sj_target t USING sj_target AS s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 10
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (select t.balance), merge_action();
+
+MERGE INTO rw_sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND s.balance = 20
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+
+MERGE INTO rw_sj_target t USING (select s1.tid, s1.balance from sj_target s0 join rw_sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 30
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, merge_action();
+
+MERGE INTO sj_target t USING (select s0.tid, s1.balance from sj_target s0 join sj_target s1 on s0.balance = s1.balance) s ON t.tid = s.tid
+  WHEN MATCHED AND t.balance = 40 AND s.balance = 40
+  THEN UPDATE SET balance = t.balance + s.balance + 100, tid = t.tid + s.tid + 10
+  RETURNING t.*, (SELECT t.tableoid::regclass), merge_action();
+
+select * from sj_target;
+
+DROP VIEW no_rw_sj_target;
+DROP VIEW rw_sj_target;
+DROP TABLE sj_target;
+
+truncate sj;
+insert into sj values (1, null, 2), (null, 2, null), (2, 1, 1);
+analyze sj;
 -- Test that references to the removed rel in lateral subqueries are replaced
 -- correctly after join removal
 explain (verbose, costs off)
-- 
2.39.2