On Fri, Nov 29, 2024 at 10:44 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Richard Guo <guofengli...@gmail.com> writes: > > On Fri, Nov 29, 2024 at 7:33 AM Tom Lane <t...@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.
> Ah, thanks for the test case. I'll look into it tomorrow.
I spent some time looking into this issue. For the Vars/PHVs of the
subquery, we should mark them as being nulled by all the outer joins
in var->varnullingrels, for sure. For the Vars/PHVs of lateral
references, it seems that how we mark them depends on whether they are
under the same lowest nulling outer join as the subquery.
First of all, the lateral references cannot be outside of the lowest
outer join above the subquery; otherwise, is_simple_subquery() would
consider the subquery not eligible for pull-up. So, IIUC, the lateral
references should be marked either with the full var->varnullingrels,
or with that set excluding lowest_outer_join->rtindex, depending on
whether they are under the same lowest nulling outer join as the
subquery or not.
For example:
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;
In this query, the lowest_outer_join is t2/t3 join {4}, and the
varnullingrels of 'x' is {4, 5}. For the lateral reference variable
't2.a', it is NOT under the same lowest nulling outer join, so we
should mark it with {5}.
explain (costs off)
select x from
t t1 left join
(t t2 inner join
lateral (select t2.a+t3.a as x, * from t t3) t3 on t2.a <> t3.a)
on t1.b = t2.b;
In this query, the lateral reference variable 't2.a' is under the same
lowest nulling outer join as the subquery, so we should just mark it
with var->varnullingrels.
I've drafted a patch based on this idea. I borrowed the concept of
lowest_nullable_relids from another patch of mine [1], which uses
lowest_nullable_relids to avoid wrapping lateral references that are
under the same lowest nulling outer join.
Please ignore all the comments and formatting stuff in the patch. I
haven't worked on those, and I haven't included a test case yet.
[1]
https://postgr.es/m/CAMbWs4_eDRaJ9vv5zmNV=tbgkwtkxvmcvn7xpxcjksukc7n...@mail.gmail.com
Thanks
Richard
v1-0001-Fix-wrong-varnullingrels.patch
Description: Binary data
