v2-0001-Avoid-NullTest-deduction-for-clone-clauses.patch

From aa994337006e54b2bfbb15e37157eb332cb999ea Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 26 Feb 2025 10:39:14 +0900
Subject: [PATCH v2] Avoid NullTest deduction for clone clauses

In commit b262ad440, we introduced an optimization that reduces an IS
NOT NULL qual on a column defined as NOT NULL to constant true, and an
IS NULL qual on a NOT NULL column to constant false, provided we can
prove that the input expression of the NullTest is not nullable by any
outer join.  This deduction happens after we have generated multiple
clones of the same qual condition to cope with commuted-left-join
cases.

However, performing the NullTest deduction on clone clauses can be
unsafe, because we don't have a reliable way to determine if the input
expression of a NullTest is non-nullable for a clone clause.
Additionally, removing a clone clause because it's reduced to constant
true can break the assumption that each clone clause will always have
corresponding counterparts, which can lead to faulty plans, as we may
fail to find any clone of the qual condition to apply.

To fix, don't perform NullTest deduction for clone clauses.

While we've been fortunate not to encounter issues with IS NULL
deductions on clone clauses so far, this seems like a potential
problem for the future.  Therefore, also add the no-clone-quals
limitation in restriction_is_always_false.  This makes the fix in
f00ab1fd1 unnecessary, so also revert those changes.

Back-patch to v17 where this bug crept in.

Reported-by: Ronald Cruz <cruz@rentec.com>
Diagnosed-by: Tom Lane <tgl@sss.pgh.pa.us>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/f5320d3d-77af-4ce8-b9c3-4715ff33f213@rentec.com
Backpatch-through: 17
---
 src/backend/optimizer/plan/initsplan.c  |  30 +++--
 src/backend/optimizer/util/joininfo.c   |  11 +-
 src/test/regress/expected/predicate.out | 157 +++++++++++++-----------
 src/test/regress/sql/predicate.sql      |  47 ++++---
 4 files changed, 134 insertions(+), 111 deletions(-)

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 2cb0ae6d659..bfcd0556382 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -3008,18 +3008,12 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
 
 		/*
 		 * Substitute the origin qual with constant-FALSE if it is provably
-		 * always false.
-		 *
-		 * Note that we need to keep the same rinfo_serial, since it is in
-		 * practice the same condition.  We also need to reset the
-		 * last_rinfo_serial counter, which is essential to ensure that the
-		 * RestrictInfos for the "same" qual condition get identical serial
-		 * numbers (see deconstruct_distribute_oj_quals).
+		 * always false.  Note that we need to keep the same rinfo_serial,
+		 * since it is in practice the same condition.
 		 */
 		if (restriction_is_always_false(root, restrictinfo))
 		{
 			int			save_rinfo_serial = restrictinfo->rinfo_serial;
-			int			save_last_rinfo_serial = root->last_rinfo_serial;
 
 			restrictinfo = make_restrictinfo(root,
 											 (Expr *) makeBoolConst(false, false),
@@ -3032,7 +3026,6 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
 											 restrictinfo->incompatible_relids,
 											 restrictinfo->outer_relids);
 			restrictinfo->rinfo_serial = save_rinfo_serial;
-			root->last_rinfo_serial = save_last_rinfo_serial;
 		}
 	}
 
@@ -3091,6 +3084,17 @@ bool
 restriction_is_always_true(PlannerInfo *root,
 						   RestrictInfo *restrictinfo)
 {
+	/*
+	 * For a clone clause, we don't have a reliable way to determine if the
+	 * input expression of a NullTest is non-nullable, due to the
+	 * commuted-left-join cases.  Additionally, removing a clone clause will
+	 * break the assumption that each clone clause will always have
+	 * corresponding counterparts, which can lead to faulty plans, as we may
+	 * fail to find any clone of the qual condition to apply.
+	 */
+	if (restrictinfo->has_clone || restrictinfo->is_clone)
+		return false;
+
 	/* Check for NullTest qual */
 	if (IsA(restrictinfo->clause, NullTest))
 	{
@@ -3140,6 +3144,14 @@ bool
 restriction_is_always_false(PlannerInfo *root,
 							RestrictInfo *restrictinfo)
 {
+	/*
+	 * For a clone clause, we don't have a reliable way to determine if the
+	 * input expression of a NullTest is non-nullable, due to the
+	 * commuted-left-join cases.
+	 */
+	if (restrictinfo->has_clone || restrictinfo->is_clone)
+		return false;
+
 	/* Check for NullTest qual */
 	if (IsA(restrictinfo->clause, NullTest))
 	{
diff --git a/src/backend/optimizer/util/joininfo.c b/src/backend/optimizer/util/joininfo.c
index f26e38c6552..1bb36fa8edd 100644
--- a/src/backend/optimizer/util/joininfo.c
+++ b/src/backend/optimizer/util/joininfo.c
@@ -107,18 +107,12 @@ add_join_clause_to_rels(PlannerInfo *root,
 
 	/*
 	 * Substitute the origin qual with constant-FALSE if it is provably always
-	 * false.
-	 *
-	 * Note that we need to keep the same rinfo_serial, since it is in
-	 * practice the same condition.  We also need to reset the
-	 * last_rinfo_serial counter, which is essential to ensure that the
-	 * RestrictInfos for the "same" qual condition get identical serial
-	 * numbers (see deconstruct_distribute_oj_quals).
+	 * false.  Note that we need to keep the same rinfo_serial, since it is in
+	 * practice the same condition.
 	 */
 	if (restriction_is_always_false(root, restrictinfo))
 	{
 		int			save_rinfo_serial = restrictinfo->rinfo_serial;
-		int			save_last_rinfo_serial = root->last_rinfo_serial;
 
 		restrictinfo = make_restrictinfo(root,
 										 (Expr *) makeBoolConst(false, false),
@@ -131,7 +125,6 @@ add_join_clause_to_rels(PlannerInfo *root,
 										 restrictinfo->incompatible_relids,
 										 restrictinfo->outer_relids);
 		restrictinfo->rinfo_serial = save_rinfo_serial;
-		root->last_rinfo_serial = save_last_rinfo_serial;
 	}
 
 	cur_relid = -1;
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 965a3a76161..a32aff53f4a 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -97,55 +97,50 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
 -- and b) its Var is not nullable by any outer joins
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
-                   QUERY PLAN                    
--------------------------------------------------
+    LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
+             QUERY PLAN              
+-------------------------------------
  Nested Loop Left Join
    ->  Seq Scan on pred_tab t1
    ->  Materialize
-         ->  Nested Loop Left Join
-               ->  Seq Scan on pred_tab t2
-               ->  Materialize
-                     ->  Seq Scan on pred_tab t3
-(7 rows)
+         ->  Seq Scan on pred_tab t2
+(4 rows)
 
 -- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
 -- by an outer join
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON t1.a = 1
+    FULL JOIN pred_tab t2 ON t1.a = t2.a
     LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
                 QUERY PLAN                 
 -------------------------------------------
  Nested Loop Left Join
    Join Filter: (t2.a IS NOT NULL)
-   ->  Nested Loop Left Join
-         Join Filter: (t1.a = 1)
-         ->  Seq Scan on pred_tab t1
-         ->  Materialize
+   ->  Merge Full Join
+         Merge Cond: (t1.a = t2.a)
+         ->  Sort
+               Sort Key: t1.a
+               ->  Seq Scan on pred_tab t1
+         ->  Sort
+               Sort Key: t2.a
                ->  Seq Scan on pred_tab t2
    ->  Materialize
          ->  Seq Scan on pred_tab t3
-(9 rows)
+(12 rows)
 
 -- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
 -- NULL column, and b) its Var is not nullable by any outer joins
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
-                    QUERY PLAN                     
----------------------------------------------------
+    LEFT JOIN pred_tab t2 ON t1.a IS NULL;
+           QUERY PLAN           
+--------------------------------
  Nested Loop Left Join
+   Join Filter: false
    ->  Seq Scan on pred_tab t1
-   ->  Materialize
-         ->  Nested Loop Left Join
-               Join Filter: (false AND (t2.b = 1))
-               ->  Seq Scan on pred_tab t2
-               ->  Result
-                     One-Time Filter: false
-(8 rows)
+   ->  Result
+         One-Time Filter: false
+(5 rows)
 
 -- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
 -- nullable by an outer join
@@ -172,55 +167,50 @@ SELECT * FROM pred_tab t1
 -- Ensure the OR clause is ignored when an OR branch is provably always true
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
-                   QUERY PLAN                    
--------------------------------------------------
+    LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
+             QUERY PLAN              
+-------------------------------------
  Nested Loop Left Join
    ->  Seq Scan on pred_tab t1
    ->  Materialize
-         ->  Nested Loop Left Join
-               ->  Seq Scan on pred_tab t2
-               ->  Materialize
-                     ->  Seq Scan on pred_tab t3
-(7 rows)
+         ->  Seq Scan on pred_tab t2
+(4 rows)
 
 -- Ensure the NullTest is not ignored when the column is nullable by an outer
 -- join
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON t1.a = 1
+    FULL JOIN pred_tab t2 ON t1.a = t2.a
     LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
                     QUERY PLAN                     
 ---------------------------------------------------
  Nested Loop Left Join
    Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
-   ->  Nested Loop Left Join
-         Join Filter: (t1.a = 1)
-         ->  Seq Scan on pred_tab t1
-         ->  Materialize
+   ->  Merge Full Join
+         Merge Cond: (t1.a = t2.a)
+         ->  Sort
+               Sort Key: t1.a
+               ->  Seq Scan on pred_tab t1
+         ->  Sort
+               Sort Key: t2.a
                ->  Seq Scan on pred_tab t2
    ->  Materialize
          ->  Seq Scan on pred_tab t3
-(9 rows)
+(12 rows)
 
 -- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
 -- provably false
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
-                    QUERY PLAN                     
----------------------------------------------------
+    LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
+           QUERY PLAN           
+--------------------------------
  Nested Loop Left Join
+   Join Filter: false
    ->  Seq Scan on pred_tab t1
-   ->  Materialize
-         ->  Nested Loop Left Join
-               Join Filter: (false AND (t2.b = 1))
-               ->  Seq Scan on pred_tab t2
-               ->  Result
-                     One-Time Filter: false
-(8 rows)
+   ->  Result
+         One-Time Filter: false
+(5 rows)
 
 -- Ensure the OR clause is not reduced to constant-FALSE when a column is
 -- made nullable from an outer join
@@ -290,31 +280,50 @@ SELECT * FROM pred_parent WHERE a IS NULL;
 (2 rows)
 
 DROP TABLE pred_parent, pred_child;
--- Validate the additional constant-FALSE qual does not cause inconsistent
--- RestrictInfo serial numbers
-CREATE TABLE pred_tab (a int PRIMARY KEY, b int);
-INSERT INTO pred_tab SELECT i, i FROM generate_series(1, 10)i;
+-- Validate we do not reduce a clone clause to a constant true
+CREATE TABLE pred_tab (a int, b int);
+CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
+INSERT INTO pred_tab VALUES (1, 1);
+INSERT INTO pred_tab VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (3, 3);
 ANALYZE pred_tab;
+ANALYZE pred_tab_notnull;
+-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
 EXPLAIN (COSTS OFF)
-SELECT 1 FROM pred_tab t1
-    LEFT JOIN
-        (pred_tab t2 LEFT JOIN pred_tab t3 ON t2.a = t3.a) ON TRUE
-    LEFT JOIN pred_tab t4 ON t1.a IS NULL AND t1.b = 1
-    RIGHT JOIN pred_tab t5 ON t1.b = t5.b;
-                    QUERY PLAN                     
----------------------------------------------------
- Hash Right Join
-   Hash Cond: (t1.b = t5.b)
-   ->  Nested Loop Left Join
+SELECT * FROM pred_tab t1
+    LEFT JOIN pred_tab t2 ON TRUE
+    LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+    LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+                          QUERY PLAN                           
+---------------------------------------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on pred_tab t1
+   ->  Materialize
          ->  Nested Loop Left Join
-               Join Filter: (false AND (t1.b = 1))
-               ->  Seq Scan on pred_tab t1
-               ->  Result
-                     One-Time Filter: false
-         ->  Materialize
-               ->  Seq Scan on pred_tab t2
-   ->  Hash
-         ->  Seq Scan on pred_tab t5
+               Join Filter: (t3.b IS NOT NULL)
+               ->  Nested Loop Left Join
+                     Join Filter: (t2.a = t3.a)
+                     ->  Seq Scan on pred_tab t2
+                     ->  Materialize
+                           ->  Seq Scan on pred_tab_notnull t3
+               ->  Materialize
+                     ->  Seq Scan on pred_tab t4
 (12 rows)
 
+SELECT * FROM pred_tab t1
+    LEFT JOIN pred_tab t2 ON TRUE
+    LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+    LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+ a | b | a | b | a | b | a | b 
+---+---+---+---+---+---+---+---
+ 1 | 1 | 1 | 1 |   |   |   |  
+ 1 | 1 | 2 | 2 | 2 | 2 | 1 | 1
+ 1 | 1 | 2 | 2 | 2 | 2 | 2 | 2
+ 2 | 2 | 1 | 1 |   |   |   |  
+ 2 | 2 | 2 | 2 | 2 | 2 | 1 | 1
+ 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2
+(6 rows)
+
 DROP TABLE pred_tab;
+DROP TABLE pred_tab_notnull;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 661013ff7e4..3bda0155f9f 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -64,22 +64,20 @@ SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
 -- and b) its Var is not nullable by any outer joins
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+    LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
 
 -- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
 -- by an outer join
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON t1.a = 1
+    FULL JOIN pred_tab t2 ON t1.a = t2.a
     LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
 
 -- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
 -- NULL column, and b) its Var is not nullable by any outer joins
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
+    LEFT JOIN pred_tab t2 ON t1.a IS NULL;
 
 -- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
 -- nullable by an outer join
@@ -95,22 +93,20 @@ SELECT * FROM pred_tab t1
 -- Ensure the OR clause is ignored when an OR branch is provably always true
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+    LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
 
 -- Ensure the NullTest is not ignored when the column is nullable by an outer
 -- join
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON t1.a = 1
+    FULL JOIN pred_tab t2 ON t1.a = t2.a
     LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
 
 -- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
 -- provably false
 EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab t1
-    LEFT JOIN pred_tab t2 ON TRUE
-    LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
+    LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
 
 -- Ensure the OR clause is not reduced to constant-FALSE when a column is
 -- made nullable from an outer join
@@ -148,17 +144,30 @@ SELECT * FROM pred_parent WHERE a IS NULL;
 
 DROP TABLE pred_parent, pred_child;
 
--- Validate the additional constant-FALSE qual does not cause inconsistent
--- RestrictInfo serial numbers
-CREATE TABLE pred_tab (a int PRIMARY KEY, b int);
-INSERT INTO pred_tab SELECT i, i FROM generate_series(1, 10)i;
+-- Validate we do not reduce a clone clause to a constant true
+CREATE TABLE pred_tab (a int, b int);
+CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
+
+INSERT INTO pred_tab VALUES (1, 1);
+INSERT INTO pred_tab VALUES (2, 2);
+
+INSERT INTO pred_tab_notnull VALUES (2, 2);
+INSERT INTO pred_tab_notnull VALUES (3, 3);
+
 ANALYZE pred_tab;
+ANALYZE pred_tab_notnull;
 
+-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
 EXPLAIN (COSTS OFF)
-SELECT 1 FROM pred_tab t1
-    LEFT JOIN
-        (pred_tab t2 LEFT JOIN pred_tab t3 ON t2.a = t3.a) ON TRUE
-    LEFT JOIN pred_tab t4 ON t1.a IS NULL AND t1.b = 1
-    RIGHT JOIN pred_tab t5 ON t1.b = t5.b;
+SELECT * FROM pred_tab t1
+    LEFT JOIN pred_tab t2 ON TRUE
+    LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+    LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
+
+SELECT * FROM pred_tab t1
+    LEFT JOIN pred_tab t2 ON TRUE
+    LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
+    LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
 
 DROP TABLE pred_tab;
+DROP TABLE pred_tab_notnull;
-- 
2.43.0