Message-ID: <300d7bb0-345c-4c0b-a0ef-4de573fcc94b@postgrespro.ru>
Date: Tue, 11 Feb 2025 18:59:55 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Ranier Vilela <ranier.vf@gmail.com>
References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru>
 <CAEudQAoD707uh5Pjpg5NMyF-QO=fzajA+BmtcoqQAeXN1C+TkQ@mail.gmail.com>
 <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru>
 <9b041978-06e3-4a50-8a5d-dacbb054f23e@tantorlabs.com>
 <8d7cbbf6-ddc0-4696-8af2-a68d740e14f2@postgrespro.ru>
 <6078f7e1-4b29-4b31-bd28-b84149e404e4@postgrespro.ru>
 <975a3736-a8b5-49b3-8009-4d4e86867aa1@postgrespro.ru>
 <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com>
Content-Language: en-US
From: Alena Rybakina <a.rybakina@postgrespro.ru>
In-Reply-To: <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/300d7bb0-345c-4c0b-a0ef-4de573fcc94b%40postgrespro.ru>
Precedence: bulk

On 10.02.2025 23:51, Ilia Evdokimov wrote:
>
> On 09.02.2025 18:14, Alena Rybakina wrote:
>> Hi! I found another example where the transformation worked 
>> incorrectly and reconsidered the idea.
>>
>> As for conversion of exists_sublink_to_ANY, we need to get the 
>> flattened implicit-AND list of clauses and pull out the chunks of the 
>> WHERE clause that belong to the parent query,
>> since we are called halfway through the parent's 
>> preprocess_expression() and earlier steps of preprocess_expression() 
>> wouldn't get applied to the pulled-up stuff unless we do them here.
>> We also do some processing for vars depending on which side the var 
>> is on - if it's in a subquery, we only need to lower its level 
>> (varlevel) because subquery will be flatted, while
>> for other vars that belong to the parent query, we need to do 
>> preparation to pull up the sub-select into top range table.
>>
>> For those expressions that we couldn't assign to either list, we 
>> define newWhere and apply both cases.
>>
>
> When I run 'make -C contrib/ check', tests of postgres_fdw extension 
> failed. I might be wrong, but you should be careful with LIMIT.
>
Thank you for the review, I'm working on it.

-- 
Regards,
Alena Rybakina
Postgres Professional