Re: LATERAL quals revisited

Antonin Houska <antonin(dot)houska(at)gmail(dot)com> writes:
> Whether I'm right or not, I seem to have found another problem while
> trying to enforce such a PHV:

> postgres=# SELECT i.*, j.* FROM i LEFT JOIN LATERAL (SELECT COALESCE(i)
> FROM j WHERE (i.n = j.n)) j ON true;
> The connection to the server was lost. Attempting reset: Failed.

I've been poking at this problem, and have found out that there are
several other multi-legged creatures underneath this rock. LATERAL
references turn out to have many more interactions with PlaceHolderVars
than I'd previously thought. I think the existing code was okay in
the initial cut at LATERAL, when we never tried to flatten any LATERAL
subqueries into the parent query --- but now that we allow such flattening
to happen, it's possible that a PlaceHolderVar that's been wrapped
around a pulled-up subquery output expression will contain a lateral
reference. There was a previous report of problems with that sort of
thing, which I tried to fix in a quick-hack way in commit
4da6439bd8553059766011e2a42c6e39df08717f, but that was totally wrong and
in fact caused the Assert you show above. The right way to think about
it is that a PlaceHolderVar should be evaluated at its syntactic
location, but if it contains a lateral reference then that creates an
outer-reference requirement for the scan or join level at which it gets
evaluated.

So attached is a draft patch for this. It's not complete yet because
there are various comments that are now wrong and need to be updated;
but I think the code is functioning correctly. Also the
lateral_vars/lateral_relids stuff seems a bit crude and Rube Goldbergish
now, because it considers *only* lateral references occurring at relation
scan level, which I now see is just part of the problem. I'm not sure
if there's a good way to generalize that or if it's best left alone.

Note that the original join-qual-misplacement problem reported by Jeremy
Evans is not fixed yet; this is just addressing PlaceHolderVar issues.

Comments?

regards, tom lane