Content-Type: multipart/mixed; boundary="------------Up3jSg8gE8GBWb8ijT7sa0lD"
Message-ID: <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com>
Date: Mon, 10 Feb 2025 23:51:29 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
To: Alena Rybakina <a.rybakina@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: 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>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <975a3736-a8b5-49b3-8009-4d4e86867aa1@postgrespro.ru>
Archived-At: <https://www.postgresql.org/message-id/0adbf75a-2059-4bb7-b878-c33892dbc1f0%40tantorlabs.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Up3jSg8gE8GBWb8ijT7sa0lD
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


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.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.

--------------Up3jSg8gE8GBWb8ijT7sa0lD
Content-Type: text/plain; charset=UTF-8; name="regression.diffs"
Content-Disposition: attachment; filename="regression.diffs"
Content-Transfer-Encoding: base64

ZGlmZiAtVTMgL2hvbWUvaWxpYS9wb3N0Z3Jlcy9jb250cmliL3Bvc3RncmVzX2Zkdy9leHBl
Y3RlZC9wb3N0Z3Jlc19mZHcub3V0IC9ob21lL2lsaWEvcG9zdGdyZXMvY29udHJpYi9wb3N0
Z3Jlc19mZHcvcmVzdWx0cy9wb3N0Z3Jlc19mZHcub3V0Ci0tLSAvaG9tZS9pbGlhL3Bvc3Rn
cmVzL2NvbnRyaWIvcG9zdGdyZXNfZmR3L2V4cGVjdGVkL3Bvc3RncmVzX2Zkdy5vdXQJMjAy
NS0wMi0xMCAyMzozMToxOS4zNTMwNTk2NTAgKzAzMDAKKysrIC9ob21lL2lsaWEvcG9zdGdy
ZXMvY29udHJpYi9wb3N0Z3Jlc19mZHcvcmVzdWx0cy9wb3N0Z3Jlc19mZHcub3V0CTIwMjUt
MDItMTAgMjM6NDY6NDMuMjQ5NzQ1NjgzICswMzAwCkBAIC00ODM1LDEzICs0ODM1LDE1IEBA
CiAJICBFWElTVFMgKFNFTEVDVCAxIEZST00gZnQ0IFdIRVJFIGZ0NC5jMiA9IGZ0Mi5jMikp
CiAgIEFORCBmdDIuYzEgPiA5MDAKICAgT1JERVIgQlkgZnQyLmMxIExJTUlUIDEwOwotICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4g
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAotLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQotIEZvcmVpZ24g
U2NhbgorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQ
TEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKKy0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgTGltaXQKICAgIE91dHB1dDogZnQy
LmMxLCBmdDIuYzIsIGZ0Mi5jMywgZnQyLmM0LCBmdDIuYzUsIGZ0Mi5jNiwgZnQyLmM3LCBm
dDIuYzgKLSAgIFJlbGF0aW9uczogKHB1YmxpYy5mdDIpIFNFTUkgSk9JTiAoKHB1YmxpYy5m
dDIgZnQyXzEpIFNFTUkgSk9JTiAocHVibGljLmZ0NCkpCi0gICBSZW1vdGUgU1FMOiBTRUxF
Q1QgcjEuIkMgMSIsIHIxLmMyLCByMS5jMywgcjEuYzQsIHIxLmM1LCByMS5jNiwgcjEuYzcs
IHIxLmM4IEZST00gIlMgMSIuIlQgMSIgcjEgV0hFUkUgKChyMS4iQyAxIiA+IDkwMCkpIEFO
RCBFWElTVFMgKFNFTEVDVCBOVUxMIEZST00gIlMgMSIuIlQgMSIgcjMgV0hFUkUgKChyMS4i
QyAxIiA9IHIzLiJDIDEiKSkgQU5EIEVYSVNUUyAoU0VMRUNUIE5VTEwgRlJPTSAiUyAxIi4i
VCAzIiByNCBXSEVSRSAoKHIzLmMyID0gcjQuYzIpKSkpIE9SREVSIEJZIHIxLiJDIDEiIEFT
QyBOVUxMUyBMQVNUIExJTUlUIDEwOjpiaWdpbnQKLSg0IHJvd3MpCisgICAtPiAgRm9yZWln
biBTY2FuCisgICAgICAgICBPdXRwdXQ6IGZ0Mi5jMSwgZnQyLmMyLCBmdDIuYzMsIGZ0Mi5j
NCwgZnQyLmM1LCBmdDIuYzYsIGZ0Mi5jNywgZnQyLmM4CisgICAgICAgICBSZWxhdGlvbnM6
IChwdWJsaWMuZnQyKSBTRU1JIEpPSU4gKChwdWJsaWMuZnQyIGZ0Ml8xKSBTRU1JIEpPSU4g
KHB1YmxpYy5mdDQpKQorICAgICAgICAgUmVtb3RlIFNRTDogU0VMRUNUIHIxLiJDIDEiLCBy
MS5jMiwgcjEuYzMsIHIxLmM0LCByMS5jNSwgcjEuYzYsIHIxLmM3LCByMS5jOCBGUk9NICJT
IDEiLiJUIDEiIHIxIFdIRVJFICgocjEuIkMgMSIgPiA5MDApKSBBTkQgRVhJU1RTIChTRUxF
Q1QgTlVMTCBGUk9NICJTIDEiLiJUIDEiIHIzIFdIRVJFICgocjEuIkMgMSIgPSByMy4iQyAx
IikpIEFORCBFWElTVFMgKFNFTEVDVCBOVUxMIEZST00gIlMgMSIuIlQgMyIgcjQgV0hFUkUg
KChyMy5jMiA9IHI0LmMyKSkpKSBPUkRFUiBCWSByMS4iQyAxIiBBU0MgTlVMTFMgTEFTVAor
KDYgcm93cykKIAogU0VMRUNUIGZ0Mi4qIEZST00gZnQyIFdIRVJFCiAgIGMxID0gQU5ZICgK
QEAgLTQ4NzEsMTMgKzQ4NzMsMjAgQEAKIAkgIEVYSVNUUyAoU0VMRUNUIDEgRlJPTSBmdDQg
V0hFUkUgZnQ0LmMyID0gZnQyLmMyKSBBTkQgYzEgPSBmdHVwcGVyLmMxICkKICAgQU5EIGZ0
dXBwZXIuYzEgPiA5MDAKICAgT1JERVIgQlkgZnR1cHBlci5jMSBMSU1JVCAxMDsKLSAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KLSBGb3JlaWduIFNj
YW4KKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0KKyBMaW1pdAogICAgT3V0cHV0OiBmdHVwcGVyLmMxLCBmdHVwcGVyLmMyLCBm
dHVwcGVyLmMzLCBmdHVwcGVyLmM0LCBmdHVwcGVyLmM1LCBmdHVwcGVyLmM2LCBmdHVwcGVy
LmM3LCBmdHVwcGVyLmM4Ci0gICBSZWxhdGlvbnM6IChwdWJsaWMuZnQyIGZ0dXBwZXIpIFNF
TUkgSk9JTiAoKHB1YmxpYy5mdDIpIFNFTUkgSk9JTiAocHVibGljLmZ0NCkpCi0gICBSZW1v
dGUgU1FMOiBTRUxFQ1QgcjEuIkMgMSIsIHIxLmMyLCByMS5jMywgcjEuYzQsIHIxLmM1LCBy
MS5jNiwgcjEuYzcsIHIxLmM4IEZST00gIlMgMSIuIlQgMSIgcjEgV0hFUkUgKChyMS4iQyAx
IiA+IDkwMCkpIEFORCBFWElTVFMgKFNFTEVDVCBOVUxMIEZST00gIlMgMSIuIlQgMSIgcjIg
V0hFUkUgKChyMS4iQyAxIiA9IHIyLiJDIDEiKSkgQU5EIEVYSVNUUyAoU0VMRUNUIE5VTEwg
RlJPTSAiUyAxIi4iVCAzIiByMyBXSEVSRSAoKHIyLmMyID0gcjMuYzIpKSkpIE9SREVSIEJZ
IHIxLiJDIDEiIEFTQyBOVUxMUyBMQVNUIExJTUlUIDEwOjpiaWdpbnQKLSg0IHJvd3MpCisg
ICAtPiAgRm9yZWlnbiBTY2FuIG9uIHB1YmxpYy5mdDIgZnR1cHBlcgorICAgICAgICAgT3V0
cHV0OiBmdHVwcGVyLmMxLCBmdHVwcGVyLmMyLCBmdHVwcGVyLmMzLCBmdHVwcGVyLmM0LCBm
dHVwcGVyLmM1LCBmdHVwcGVyLmM2LCBmdHVwcGVyLmM3LCBmdHVwcGVyLmM4CisgICAgICAg
ICBGaWx0ZXI6IChBTlkgKGZ0dXBwZXIuYzEgPSAoaGFzaGVkIFN1YlBsYW4gMikuY29sMSkp
CisgICAgICAgICBSZW1vdGUgU1FMOiBTRUxFQ1QgIkMgMSIsIGMyLCBjMywgYzQsIGM1LCBj
NiwgYzcsIGM4IEZST00gIlMgMSIuIlQgMSIgV0hFUkUgKCgiQyAxIiA+IDkwMCkpIE9SREVS
IEJZICJDIDEiIEFTQyBOVUxMUyBMQVNUCisgICAgICAgICBTdWJQbGFuIDIKKyAgICAgICAg
ICAgLT4gIEZvcmVpZ24gU2NhbgorICAgICAgICAgICAgICAgICBPdXRwdXQ6IGZ0Mi5jMQor
ICAgICAgICAgICAgICAgICBSZWxhdGlvbnM6IChwdWJsaWMuZnQyKSBTRU1JIEpPSU4gKHB1
YmxpYy5mdDQpCisgICAgICAgICAgICAgICAgIFJlbW90ZSBTUUw6IFNFTEVDVCByMS4iQyAx
IiBGUk9NICJTIDEiLiJUIDEiIHIxIFdIRVJFIEVYSVNUUyAoU0VMRUNUIE5VTEwgRlJPTSAi
UyAxIi4iVCAzIiByMiBXSEVSRSAoKHIxLmMyID0gcjIuYzIpKSkKKygxMSByb3dzKQogCiBT
RUxFQ1QgKiBGUk9NIGZ0MiBmdHVwcGVyIFdIRVJFCiAgICBFWElTVFMgKAo=

--------------Up3jSg8gE8GBWb8ijT7sa0lD--