From 0655c03ef9cc6154b0b209059e758863dcf4e6b0 Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Tue, 20 Jun 2023 10:22:52 -0400
Subject: Centralize fixups for mismatched nullingrels in nestloop params.

It turns out that the fixes we applied in commits bfd332b3f
and 63e4f13d2 were not nearly enough to solve the problem.
We'd focused narrowly on subquery RTEs with lateral references,
but lateral references can occur in several other RTE kinds
such as function RTEs.  Putting the same hack into half a dozen
code paths seems quite unattractive.  Hence, revert the code changes
(but not the test cases) from those commits and instead solve it
centrally in identify_current_nestloop_params(), as Richard proposed
originally.  This is a bit annoying because it could mask erroneous
nullingrels in nestloop params that are generated from non-LATERAL
parameterized paths; but on balance I don't see a better way.
Maybe at some future time we'll be motivated to find a more rigorous
approach to nestloop params, but that's not happening for beta2.

Richard Guo and Tom Lane

Discussion: https://postgr.es/m/CAMbWs48Jcw-NvnxT23WiHP324wG44DvzcH1j4hc0Zn+3sR9cfg@mail.gmail.com
---
 src/test/regress/expected/join.out | 17 +++++++++++++++++
 src/test/regress/sql/join.sql      |  7 +++++++
 2 files changed, 24 insertions(+)

(limited to 'src/test')

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0643f50078f..35476a0d126 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2607,6 +2607,23 @@ select * from int8_tbl t1
                      Filter: (q1 = t2.q1)
 (8 rows)
 
+explain (costs off)
+select * from int8_tbl t1
+    left join int8_tbl t2 on true
+    left join lateral
+      (select * from generate_series(t2.q1, 100)) s
+      on t2.q1 = 1;
+                     QUERY PLAN                     
+----------------------------------------------------
+ Nested Loop Left Join
+   ->  Seq Scan on int8_tbl t1
+   ->  Materialize
+         ->  Nested Loop Left Join
+               Join Filter: (t2.q1 = 1)
+               ->  Seq Scan on int8_tbl t2
+               ->  Function Scan on generate_series
+(7 rows)
+
 explain (costs off)
 select * from onek t1
     left join onek t2 on true
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index adc2ef5b81d..d8d9579092d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -521,6 +521,13 @@ select * from int8_tbl t1
       (select * from int8_tbl t3 where t3.q1 = t2.q1 offset 0) s
       on t2.q1 = 1;
 
+explain (costs off)
+select * from int8_tbl t1
+    left join int8_tbl t2 on true
+    left join lateral
+      (select * from generate_series(t2.q1, 100)) s
+      on t2.q1 = 1;
+
 explain (costs off)
 select * from onek t1
     left join onek t2 on true
-- 
cgit v1.2.3