Обсуждение: BUG #18284: Filter in left lateral join not respected

The following bug has been logged on the website:

Bug reference:      18284
Logged by:          Holger Reise
Email address:      holger.reise@vitagroup.ag
PostgreSQL version: 16.1
Operating system:   Alpine 13.2.1, 64-bit
Description:

The following script returns two rows with `666`, whereas `null` is to be
expected:

WITH r1 AS (VALUES(null)),
r2 AS (VALUES(null), (null))
SELECT ljl.val_filtered
FROM r1
LEFT JOIN(
    SELECT j666.val FROM r2
    JOIN (SELECT 666 AS val) as j666 ON true
)AS lj_r2 ON true
LEFT JOIN LATERAL(
    SELECT lj_r2.val AS val_filtered
    WHERE false
) AS ljl ON true;

PG Bug reporting form <noreply@postgresql.org> writes:
> The following script returns two rows with `666`, whereas `null` is to be
> expected:

> WITH r1 AS (VALUES(null)),
> r2 AS (VALUES(null), (null))
> SELECT ljl.val_filtered
> FROM r1
> LEFT JOIN(
>     SELECT j666.val FROM r2
>     JOIN (SELECT 666 AS val) as j666 ON true
> )AS lj_r2 ON true
> LEFT JOIN LATERAL(
>     SELECT lj_r2.val AS val_filtered
>     WHERE false
> ) AS ljl ON true;

Hmph ... this has been broken for a good long while.  Bisecting
shows it gave the right answer before

4be058fe9ec5e630239b656af21fc083371f30ed is the first bad commit
commit 4be058fe9ec5e630239b656af21fc083371f30ed
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon Jan 28 17:54:10 2019 -0500

    In the planner, replace an empty FROM clause with a dummy RTE.

so I'm betting that missed a condition about when it is safe to
flatten RTE_RESULT RTEs.  Will look, thanks for the report!

            regards, tom lane

I wrote:
> Hmph ... this has been broken for a good long while.  Bisecting
> shows it gave the right answer before
> commit 4be058fe9ec5e630239b656af21fc083371f30ed
> so I'm betting that missed a condition about when it is safe to
> flatten RTE_RESULT RTEs.  Will look, thanks for the report!

Huh.  It looks like the oversight is actually even more ancient than
that, dating clear back to 9e7e29c75 of 2013-08-17.  That commit
recognized that lateral-reference Vars had to be wrapped in
PlaceHolderVars during subquery pullup, but failed to make the same
conclusion for PlaceHolderVars.  Somehow that didn't cause any visible
problems before 4be058fe9, or more likely we just didn't get any
relevant trouble reports.  This seems to be quite a rare situation:
spot testing says that we never reach this code for a PHV with
target_rte->lateral true in any of our regression tests.

The attached seems to be enough to fix it, though of course it needs
a regression test.

            regards, tom lane

diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index d0df5374ef..aa83dd3636 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2435,8 +2435,13 @@ pullup_replace_vars_callback(Var *var,
             else if (newnode && IsA(newnode, PlaceHolderVar) &&
                      ((PlaceHolderVar *) newnode)->phlevelsup == 0)
             {
-                /* No need to wrap a PlaceHolderVar with another one, either */
-                wrap = false;
+                /* The same rules apply for a PlaceHolderVar */
+                if (rcon->target_rte->lateral &&
+                    !bms_is_subset(((PlaceHolderVar *) newnode)->phrels,
+                                   rcon->relids))
+                    wrap = true;
+                else
+                    wrap = false;
             }
             else
             {

Richard Guo <guofenglinux@gmail.com> writes:
> On Fri, Jan 12, 2024 at 1:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Somehow that didn't cause any visible
>> problems before 4be058fe9, or more likely we just didn't get any
>> relevant trouble reports.

> I believe it should be the latter case.  It's not hard to show this
> problem before 4be058fe9.

> create table t (a int);
> insert into t values (1);

> # select t2a_lateral from t t1
>     left join (select coalesce(t2.a) as a from t t2) s on true
>     left join lateral (select s.a as t2a_lateral from t t3) ss on false;
>  t2a_lateral
> -------------
>            1
> (1 row)

Ah, of course.  The previous test case had FROM-less subselects,
so prior to 4be058fe9 we couldn't flatten those and the bug didn't
manifest.  But with ordinary FROM clauses, boom!

Now I'm fairly astonished that this was never reported before.
You'd think in ten years somebody would've noticed.

            regards, tom lane