Re: [POC] Allow flattening of subquery with a link to upper query

On 5/9/2022 12:22, Richard Guo wrote:
> 
> On Fri, Sep 2, 2022 at 7:09 PM Andrey Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.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