Horton

Re: BUG #18284: Filter in left lateral join not respected

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: holger.reise@vitagroup.ag
Cc: pgsql-bugs@lists.postgresql.org
Date: 2024-01-11T17:30:39Z
Lists: pgsql-bugs

Attachments

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

Commits

  1. Allow subquery pullup to wrap a PlaceHolderVar in another one.

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