Re: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

On Thu, Feb 23, 2023 at 11:29 AM Richard Guo <guofenglinux@gmail.com> wrote:

> On Wed, Feb 22, 2023 at 6:24 PM Richard Guo <guofenglinux@gmail.com>
> wrote:
>
>> ISTM that for outer join identity 3, if we are given form
>>     (A leftjoin B on (Pab)) leftjoin C on (Pbc)
>> then references to C Vars in higher qual levels would be marked with the
>> B/C join.  If we've transformed it to form
>>     A leftjoin (B leftjoin C on (Pbc)) on (Pab)
>> then references to C Vars in higher qual levels should be adjusted to
>> include both B/C join and A/B join in their varnullingrels.
>>
>
> A quick hack that comes to my mind is that for a pushed down clause we
> check all outer join relids it mentions and add the outer joins'
> commute_below to the clause's required_relids, so that after we've
> commuted the outer joins, the clause would still be placed in the right
> place.
>

I've realized this hack is not correct :-(.  References to A Vars and B
Vars in higher qual levels do not need this adjustment.  So this code
change would add unnecessary relids for clauses that involve A Vars or B
Vars but not C Vars, resulting them being placed at higher place than
needed.

Maybe what we need is to add in commute_below_l (assuming it has been
recorded in SpecialJoinInfo) rather than commute_below of outer joins
mentioned within the clause?

Thanks
Richard