From: Tom Lane <tgl@sss.pgh.pa.us>
To: holger.reise@vitagroup.ag
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18284: Filter in left lateral join not respected
In-reply-to: <18284-47505a20c23647f8@postgresql.org>
References: <18284-47505a20c23647f8@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 11 Jan 2024 14:34:55 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <74949.1704988899.1@sss.pgh.pa.us>
Date: Thu, 11 Jan 2024 11:01:39 -0500
Message-ID: <74950.1704988899@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/74950.1704988899%40sss.pgh.pa.us>
Precedence: bulk

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