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

On 9/1/22 17:24, Richard Guo wrote:
> 
> On Wed, Aug 31, 2022 at 2:35 PM Andrey Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.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). It looks ugly. 
But, more important, does this feature deserve such efforts/changes?

-- 
Regards
Andrey Lepikhov
Postgres Professional