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: <74950.1704988899@sss.pgh.pa.us>
References: <18284-47505a20c23647f8@postgresql.org>
 <74950.1704988899@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 11 Jan 2024 11:01:39 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <229566.1704994216.0@sss.pgh.pa.us>
Date: Thu, 11 Jan 2024 12:30:39 -0500
Message-ID: <229608.1704994239@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/229608.1704994239%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <229566.1704994216.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="fix-PHV-wrap-rules-for-lateral-references.patch";
	charset="us-ascii"
Content-ID: <229566.1704994216.2@sss.pgh.pa.us>
Content-Description: fix-PHV-wrap-rules-for-lateral-references.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optim=
izer/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 =3D=3D 0)
 			{
-				/* No need to wrap a PlaceHolderVar with another one, either */
-				wrap =3D false;
+				/* The same rules apply for a PlaceHolderVar */
+				if (rcon->target_rte->lateral &&
+					!bms_is_subset(((PlaceHolderVar *) newnode)->phrels,
+								   rcon->relids))
+					wrap =3D true;
+				else
+					wrap =3D false;
 			}
 			else
 			{

------- =_aaaaaaaaaa0--