Message-ID: <e6cecb71-1b3c-4fba-826a-f176bc3639be@postgrespro.ru>
Date: Wed, 22 Nov 2023 09:32:06 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan
 target lists" triggered by JOIN
Content-Language: en-US
To: Richard Guo <guofenglinux@gmail.com>,
 Alexander Korotkov <aekorotkov@gmail.com>
Cc: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>
References: <18187-831da249cbd2ff8e@postgresql.org>
 <491881ef-f1c7-4a86-b5dd-dcad55a88ab4@postgrespro.ru>
 <CAMbWs4_yrqMYXd+zXc4Y6KjnyA8xPZE6aiAW6k2Y10WaNhwRhw@mail.gmail.com>
 <CAMbWs4-ns73VF9gi37q61G3dS6Xuos+HtryMaBh37WQn=BsaJw@mail.gmail.com>
 <CAMbWs4_WeARU0oYH1sMn03VYzw-qZQ_xwUNq7r6TbDTH+VgXng@mail.gmail.com>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAMbWs4_WeARU0oYH1sMn03VYzw-qZQ_xwUNq7r6TbDTH+VgXng@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e6cecb71-1b3c-4fba-826a-f176bc3639be%40postgrespro.ru>
Precedence: bulk

On 20/11/2023 15:49, Richard Guo wrote:
>     I'm wondering if we can relax this restriction because it seems to me
>     that a PHV evaluated/needed at or below the self join should not have
>     problem if we remove the self join.
> 
> 
> After some more thought, I think PHVs should not impose any constraints
> on removing self joins.  If the removed rel is contained in the relids
> that a PHV is evaluated/needed at or laterally references, it can just
> be replaced with the rel that is kept.
> 
> Attached is a patch to remove such constraints.  Any comments or
> feedback are welcome.

Carrying out excavations, I found that it was Teodor Sigaev who added 
this limitation in v.20 of the patch in an earlier stage of the development.
I also had analyzed this part of code and came to the conclusion that it 
doesn't needed. Only a paranoid approach and the absence of an 
alternative opinion are the reasons why this code is still exists.
Your patch is ok.
I think the comment, 'PHVs should not impose ...' looks redundant. It 
may be enough to have it in the commit comment.

-- 
regards,
Andrei Lepikhov
Postgres Professional