Re: pgsql: Avoid mislabeling of lateral references when pulling up a subque

On Fri, Nov 29, 2024 at 7:33 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It seems to be sufficient to just not mark lateral
> references at all in this case. (I have a nagging feeling that more
> complexity may be needed in cases where there are several levels of
> outer join, but some attempts to break it with that didn't succeed.)

You're right about your feeling. Here is a query that breaks it.

create table t (a int, b int);

explain (costs off)
select x from
t t1 left join
(t t2 left join
lateral (select t2.a+t3.a as x, * from t t3) t3 on t2.a <> t3.a)
on t1.b = t2.b;
ERROR: wrong varnullingrels (b) (expected (b 5)) for Var 2/1

'x' is nulled by ojrelids {4, 5}. When pulling up the subquery, it's
right that we should not mark the lateral reference variable 't2' as
being nulled by {4}, but we should mark it as being nulled by {5}.

Thanks
Richard