On 5/9/2022 12:22, Richard Guo wrote:
>
> On Fri, Sep 2, 2022 at 7:09 PM Andrey Lepikhov
> <a(dot)lepikhov(at)postgrespro(dot)ru <mailto:a(dot)lepikhov(at)postgrespro(dot)ru>> wrote:
> > Hmm, I'm not sure this patch works correctly in all cases. It
> seems to
> > me this patch pulls up the subquery without checking the constraints
> > imposed by lateral references. If its quals contain any lateral
> > references to rels outside a higher outer join, we would need to
> > postpone quals from below an outer join to above it, which is
> probably
> > incorrect.
>
> Yeah, it's not easy-to-solve problem. If I correctly understand the
> code, to fix this problem we must implement the same logic, as
> pull_up_subqueries (lowest_outer_join/safe_upper_varnos).
>
> Yeah, I think we'd have to consider the restrictions from lateral
> references to guarantee correctness when we pull up subqueries. We need
> to avoid the situation where quals need to be postponed past outer join.
>
> However, even if we have taken care of that, there may be other issues
> with flattening direct-correlated ANY SubLink. The constraints imposed
> by LATERAL references may make it impossible for us to find any legal
> join orders, as discussed in [1].
To resolve both issues, lower outer join passes through pull_sublinks_*
into flattening routine (see attachment).
I've added these cases into subselect.sql
--
regards,
Andrey Lepikhov
Postgres Professional