Message-ID: <8fe2d19a-9c96-459d-871d-e8ec780e8927@postgrespro.ru>
Date: Tue, 9 Jan 2024 07:27:45 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18261: Inconsistent results of SELECT affected by joined
 subqueries
To: Richard Guo <guofenglinux@gmail.com>
Cc: Alexander Korotkov <aekorotkov@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
 zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org
References: <18261-2a75d748c928609b@postgresql.org>
 <CAMbWs48_EAPrc_C5qvK2WGvydHvht1JUnXwer9FVeU7t_zA+MQ@mail.gmail.com>
 <2887250.1703781175@sss.pgh.pa.us>
 <CAPpHfdtwTjDmg0J3g17drXdt6ROxW5HLueSMgpfy+MMHxBHFmw@mail.gmail.com>
 <ee0d17cc-240a-44bf-acad-58790efb0153@postgrespro.ru>
 <CAMbWs49igjcszLgicb4D1N21_5iNDoxheJ7KFmAcs_z=Lx6jhg@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAMbWs49igjcszLgicb4D1N21_5iNDoxheJ7KFmAcs_z=Lx6jhg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8fe2d19a-9c96-459d-871d-e8ec780e8927%40postgrespro.ru>
Precedence: bulk

On 8/1/2024 18:50, Richard Guo wrote:
> 
> On Fri, Jan 5, 2024 at 11:32 AM Andrei Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.ru>> wrote:
> 
>     The regression test shows where two fully equal join clauses,
>     applied on
>     different join levels, differ by the only required_relids. It covers
>     the
>     problem and can be helpful by itself. IMO, one line in this test is
>     redundant (see attachment).
> 
> 
> Hmm, I don't think the insert statement in the test case is redundant.
> It's needed to verify that the query in the test case gives the correct
> result.  Without the insert statement, the wrong plan would give the
> same result as the correct plan, i.e., an empty set in this case.
> 
> IMO, if we make some code changes and add a test case for that, we need
> to ensure the test can give a different (and correct of course) result
> than what came before.
Maybe. I personally support explain by an execution result if the code 
is related to executor and couldn't be detected by the form of query 
plan. But why not? anyway, Alexander already committed that.

-- 
regards,
Andrei Lepikhov
Postgres Professional