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

On Fri, Jan 12, 2024 at 1:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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.


Exactly.


> Somehow that didn't cause any visible
> problems before 4be058fe9, or more likely we just didn't get any
> relevant trouble reports.


I believe it should be the latter case.  It's not hard to show this
problem before 4be058fe9.

create table t (a int);
insert into t values (1);

# select t2a_lateral from t t1
    left join (select coalesce(t2.a) as a from t t2) s on true
    left join lateral (select s.a as t2a_lateral from t t3) ss on false;
 t2a_lateral
-------------
           1
(1 row)

The t2a_lateral is supposed to be NULL not 1.

Thanks
Richard