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

On Fri, Sep 2, 2022 at 7:09 PM Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
wrote:

> On 9/1/22 17:24, Richard Guo wrote:
> > On Wed, Aug 31, 2022 at 2:35 PM Andrey Lepikhov
> > <a(dot)lepikhov(at)postgrespro(dot)ru <mailto:a(dot)lepikhov(at)postgrespro(dot)ru>> wrote:
> > Before flattening procedure we just look through the quals of
> subquery,
> > pull to the upper level OpExpr's containing variables from the upper
> > relation and replace their positions in the quals with true
> expression.
> > Further, the flattening machinery works as usual.
> >
> > 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].

[1]
https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=D5TY=4KGHEQcfHPZCXS1GVhkA@mail.gmail.com

Thanks
Richard