Message-ID: <2663884e-769a-b298-55b0-014c5a1ee55c@postgrespro.ru>
Date: Tue, 28 Feb 2023 03:23:02 +0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.8.0
Subject: Re: Clause accidentally pushed down ( Possible bug in Making Vars
 outer-join aware)
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Guo <guofenglinux@gmail.com>,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
References: <0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru>
 <CAMbWs4_HJNuLv9HDReEBDrQrCPV-uqpVOecoJkurprnY+Do9Fg@mail.gmail.com>
 <CAMbWs4_8EZU4DetHyZGm1CwCxUhBdWpRnA9dtM-Z82i=bVKA6A@mail.gmail.com>
 <CAMbWs4_XcPHqc=A=36cxvJRhkjAgEeJtOFLOnBPEfQYGqnGOCw@mail.gmail.com>
 <efb33bee-145d-95a8-1048-2e7b8f282e2c@postgrespro.ru>
 <CAMbWs48FHw_tTXSbA-_MPaeu7S7JCt6Xs-gxwkx=pvLiwC6eTg@mail.gmail.com>
 <c69bd07e-1175-10da-9ac0-4bfe59235ac2@postgrespro.ru>
 <402433.1677524538@sss.pgh.pa.us>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <402433.1677524538@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2663884e-769a-b298-55b0-014c5a1ee55c%40postgrespro.ru>
Precedence: bulk

On 28/2/2023 00:02, Tom Lane wrote:
> Andrey Lepikhov <a.lepikhov@postgrespro.ru> writes:
>> On 27/2/2023 13:16, Richard Guo wrote:
>>> Do you mean function check_outerjoin_delay()?  Yes it has been removed
>>> in b448f1c8, since now we consider that outer joins listed in
>>> varnullingrels or phnullingrels are used in the clause, so that the
>>> clause would not be placed below outer joins that should null some of
>>> its vars.
>> I see. But these logics looks non-equivalent.
> Admittedly the new stuff is having more teething pains than I'd hoped
> for, but we'll work through it.  Most of the bugs are stemming from
> the fact that we now need a rigorous representation of what is
> happening when we commute outer joins per identity 3, and we're
> finding out that we are not quite there yet.
Ok, maybe my language still not so fluent, as I think sometimes. In 
accordance to the example above:
1. varnullingrels contains relids of entries which can null the var. In 
our case it (correctly) contains t3 and OJ(t3,t4)
2. Syntactic scope of the clause correctly contains all relations and OJs
3. In the distribute_qual_to_rels I don't see a code which should 
disallow pushing down a clause from higher syntactic level into nullable 
side of OJ. Where is the logic which should limit the lowest level of 
the clause push down?

-- 
regards,
Andrey Lepikhov
Postgres Professional