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

On Fri, Nov 29, 2024 at 7:33 AM Tom Lane <tgl@sss.pgh.pa.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