v1-0001-Fix-bug-18277.patch

From 69f1187aa85d34385fc23d07d32978be4efbd42d Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 9 Jan 2024 15:29:02 +0800
Subject: [PATCH v1] Fix bug 18277

---
 src/backend/nodes/nodeFuncs.c        |  6 ++++++
 src/test/regress/expected/window.out | 26 ++++++++++++++++++++++++++
 src/test/regress/sql/window.sql      |  9 +++++++++
 3 files changed, 41 insertions(+)

diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 030463cb42..e1a5bc7e95 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2283,6 +2283,8 @@ expression_tree_walker_impl(Node *node,
 					return true;
 				if (WALK(wc->endOffset))
 					return true;
+				if (WALK(wc->runCondition))
+					return true;
 			}
 			break;
 		case T_CTECycleClause:
@@ -2627,6 +2629,8 @@ query_tree_walker_impl(Query *query,
 				return true;
 			if (WALK(wc->endOffset))
 				return true;
+			if (WALK(wc->runCondition))
+				return true;
 		}
 	}
 
@@ -3312,6 +3316,7 @@ expression_tree_mutator_impl(Node *node,
 				MUTATE(newnode->orderClause, wc->orderClause, List *);
 				MUTATE(newnode->startOffset, wc->startOffset, Node *);
 				MUTATE(newnode->endOffset, wc->endOffset, Node *);
+				MUTATE(newnode->runCondition, wc->runCondition, List *);
 				return (Node *) newnode;
 			}
 			break;
@@ -3641,6 +3646,7 @@ query_tree_mutator_impl(Query *query,
 			FLATCOPY(newnode, wc, WindowClause);
 			MUTATE(newnode->startOffset, wc->startOffset, Node *);
 			MUTATE(newnode->endOffset, wc->endOffset, Node *);
+			MUTATE(newnode->runCondition, wc->runCondition, List *);
 
 			resultlist = lappend(resultlist, (Node *) newnode);
 		}
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 2201740c18..60de2cbf96 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4181,6 +4181,32 @@ SELECT * FROM
  sales     |     3 |   4800 | 08-01-2007  |  3 |  1 |  3 |  3 |  1
 (2 rows)
 
+-- Ensure we remove references to reduced outer joins as nulling rels in run
+-- conditions
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
+   FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
+   WHERE e1.empno = e2.empno) s
+WHERE s.c = 1;
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Subquery Scan on s
+   Filter: (s.c = 1)
+   ->  WindowAgg
+         Run Condition: (ntile(e2.salary) OVER (?) <= 1)
+         ->  Sort
+               Sort Key: e1.depname
+               ->  Merge Join
+                     Merge Cond: (e1.empno = e2.empno)
+                     ->  Sort
+                           Sort Key: e1.empno
+                           ->  Seq Scan on empsalary e1
+                     ->  Sort
+                           Sort Key: e2.empno
+                           ->  Seq Scan on empsalary e2
+(14 rows)
+
 -- Tests to ensure we don't push down the run condition when it's not valid to
 -- do so.
 -- Ensure we don't push down when the frame options show that the window
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 437e948d6c..678aa1a21c 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1368,6 +1368,15 @@ SELECT * FROM
    FROM empsalary
 ) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;
 
+-- Ensure we remove references to reduced outer joins as nulling rels in run
+-- conditions
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM
+  (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
+   FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
+   WHERE e1.empno = e2.empno) s
+WHERE s.c = 1;
+
 -- Tests to ensure we don't push down the run condition when it's not valid to
 -- do so.
 
-- 
2.31.0