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

On Fri, Jan 12, 2024 at 1:30 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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