v3-0002-make-SJE-to-apply-DML-MERGE-UPDATE-INSERT-DELETE.patch

From bc5511acfa7acefad676ba6d15c4b181f2edcc5f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 26 Jun 2024 14:02:01 +0800
Subject: [PATCH v3 2/3] make SJE to apply DML (MERGE, UPDATE, INSERT, DELETE)

replace_varno and replace_varno_walker didn't replace
Query->resultRelation, Query->mergeTargetRelation
as ChangeVarNodes did.
So replace_varno will have problem with DELETE, UPDATE, MERGE.
ChangeVarNodes solved this problem.

* from the INSERT synopsis, we can see that  INSERT cannot join with another table,
so SJE cannot be used.

DELETE/UPDATE
excerpt from the synopsis:

UPDATE table_name FROM from_item
DELETE FROM table_name USING from_item

as you can see UPDATE/DELETE, the join order is fixed. so we only need to check
if the table_name is totally replaced by from_item or not.

MERGE command: (merge_insert, merge_update, merge_delete).

for merge_insert: SJE cannot to applicable. it will out in follow code,
for which, i cannot fully explain the reasoning:
```
			/*
			 * It is impossible to eliminate join of two relations if they
			 * belong to different rules of order. Otherwise planner can't be
			 * able to find any variants of correct query plan.
			 */
			foreach(lc, root->join_info_list)
			{
				SpecialJoinInfo *info = (SpecialJoinInfo *) lfirst(lc);

				if ((bms_is_member(k, info->syn_lefthand) ^
					 bms_is_member(r, info->syn_lefthand)) ||
					(bms_is_member(k, info->syn_righthand) ^
					 bms_is_member(r, info->syn_righthand)))
				{
					jinfo_check = false;
					break;
				}
			}
			if (!jinfo_check)
				continue;
```
merge_delete and merge_update works fine with base table and updateable view.
but SJE cannot work with non-updatable view.
---
 src/backend/optimizer/plan/analyzejoins.c     |  21 +-
 src/test/regress/expected/join.out            | 353 +++++++++++++++++-
 src/test/regress/expected/updatable_views.out |  17 +-
 src/test/regress/sql/join.sql                 | 122 ++++++
 4 files changed, 470 insertions(+), 43 deletions(-)

diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index bb145977..eab39ee0 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);
@@ -2391,6 +2376,12 @@ remove_useless_self_joins(PlannerInfo *root, List *joinlist)
 		(list_length(joinlist) == 1 && !IsA(linitial(joinlist), List)))
 		return joinlist;
 
+	/*
+	 * we don't accept RETURNING clause with SJE.
+	*/
+	if (root->parse && root->parse->returningList != NIL)
+		return joinlist;
+
 	/*
 	 * Merge pairs of relations participated in self-join. Remove unnecessary
 	 * range table entries.
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 16327779..9f89b351 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6385,6 +6385,329 @@ 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)
+
+------------------- 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)
+
+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)
@@ -7003,29 +7326,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 1d1f568b..dd5b3cd4 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 0cc6c692..4afbaf79 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2426,6 +2426,128 @@ 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;
+
+------------------- 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;
+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.34.1