Re: BUG #18284: Filter in left lateral join not respected - Mailing list pgsql-bugs

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