v1-0001-Check-SubPlan-clause-for-nonnullable-rels-Vars.patch

From 79f59201c076b9e7f9e6370683e8f0b324931d84 Mon Sep 17 00:00:00 2001
From: pgsql-guo <richard.guo@openpie.com>
Date: Sun, 11 Sep 2022 10:32:52 +0000
Subject: [PATCH v1] Check SubPlan clause for nonnullable rels/Vars

---
 src/backend/optimizer/util/clauses.c | 14 +++++++
 src/test/regress/expected/join.out   | 56 ++++++++++++++++++++++++++++
 src/test/regress/sql/join.sql        | 28 ++++++++++++++
 3 files changed, 98 insertions(+)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 5c54171fee..c525ad9f71 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1534,6 +1534,13 @@ find_nonnullable_rels_walker(Node *node, bool top_level)
 			bms_membership(phv->phrels) == BMS_SINGLETON)
 			result = bms_add_members(result, phv->phrels);
 	}
+	else if (IsA(node, SubPlan))
+	{
+		/* descend through testexpr for ALL/ANY/ROWCOMPARE */
+		SubPlan *splan = (SubPlan *) node;
+
+		result = find_nonnullable_rels_walker(splan->testexpr, top_level);
+	}
 	return result;
 }
 
@@ -1742,6 +1749,13 @@ find_nonnullable_vars_walker(Node *node, bool top_level)
 
 		result = find_nonnullable_vars_walker((Node *) phv->phexpr, top_level);
 	}
+	else if (IsA(node, SubPlan))
+	{
+		/* descend through testexpr for ALL/ANY/ROWCOMPARE */
+		SubPlan *splan = (SubPlan *) node;
+
+		result = find_nonnullable_vars_walker(splan->testexpr, top_level);
+	}
 	return result;
 }
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2ed2e542a4..cc110eaf20 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6654,3 +6654,59 @@ where exists (select 1 from j3
 (13 rows)
 
 drop table j3;
+--
+-- test check of SubPlan clause for nonnullable rels/Vars when reducing outer joins
+--
+begin;
+create temp table a (i int, j int);
+create temp table b (i int, j int);
+create temp table c (i int, j int);
+insert into a values (1,1), (2,2), (3,3);
+insert into b values (2,2), (3,3), (4,4);
+insert into c values (3,3), (4,4), (5,5);
+analyze a;
+analyze b;
+analyze c;
+explain (costs off)
+select * from a left join b on a.i = b.i where b.i = ANY (select i from c where c.j = b.j);
+            QUERY PLAN             
+-----------------------------------
+ Hash Join
+   Hash Cond: (b.i = a.i)
+   ->  Seq Scan on b
+         Filter: (SubPlan 1)
+         SubPlan 1
+           ->  Seq Scan on c
+                 Filter: (j = b.j)
+   ->  Hash
+         ->  Seq Scan on a
+(9 rows)
+
+select * from a left join b on a.i = b.i where b.i = ANY (select i from c where c.j = b.j);
+ i | j | i | j 
+---+---+---+---
+ 3 | 3 | 3 | 3
+(1 row)
+
+explain (costs off)
+select * from a left join b on b.i = ANY (select i from c where c.j = a.j) where b.i is null;
+         QUERY PLAN          
+-----------------------------
+ Nested Loop Anti Join
+   Join Filter: (SubPlan 1)
+   ->  Seq Scan on a
+   ->  Materialize
+         ->  Seq Scan on b
+   SubPlan 1
+     ->  Seq Scan on c
+           Filter: (j = a.j)
+(8 rows)
+
+select * from a left join b on b.i = ANY (select i from c where c.j = a.j) where b.i is null;
+ i | j | i | j 
+---+---+---+---
+ 1 | 1 |   |  
+ 2 | 2 |   |  
+(2 rows)
+
+rollback;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 27e7e741a1..7fef18dbd7 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2341,3 +2341,31 @@ where exists (select 1 from j3
       and t1.unique1 < 1;
 
 drop table j3;
+
+--
+-- test check of SubPlan clause for nonnullable rels/Vars when reducing outer joins
+--
+
+begin;
+
+create temp table a (i int, j int);
+create temp table b (i int, j int);
+create temp table c (i int, j int);
+
+insert into a values (1,1), (2,2), (3,3);
+insert into b values (2,2), (3,3), (4,4);
+insert into c values (3,3), (4,4), (5,5);
+
+analyze a;
+analyze b;
+analyze c;
+
+explain (costs off)
+select * from a left join b on a.i = b.i where b.i = ANY (select i from c where c.j = b.j);
+select * from a left join b on a.i = b.i where b.i = ANY (select i from c where c.j = b.j);
+
+explain (costs off)
+select * from a left join b on b.i = ANY (select i from c where c.j = a.j) where b.i is null;
+select * from a left join b on b.i = ANY (select i from c where c.j = a.j) where b.i is null;
+
+rollback;
-- 
2.25.1