Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbw8f-00035b-P8 for pgsql-hackers@arkaria.postgresql.org; Mon, 19 Feb 2024 05:24:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rbw8d-00CmIn-E4 for pgsql-hackers@arkaria.postgresql.org; Mon, 19 Feb 2024 05:24:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbw8c-00CmIf-Rw for pgsql-hackers@lists.postgresql.org; Mon, 19 Feb 2024 05:24:23 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbw8Y-007TEx-VI for pgsql-hackers@lists.postgresql.org; Mon, 19 Feb 2024 05:24:21 +0000 Received: from [192.168.1.58] (node-qpq.pool-125-24.dynamic.totinternet.net [125.24.135.62]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.lepikhov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id C4DF4E20E3B; Mon, 19 Feb 2024 08:24:13 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1708320255; bh=98rW0T88V1vPcH75dYQN9wAo+86kmGBIpD2I9MaREV4=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=kp18j9sklM4s8RQoeGqQeRmbndFx9OcdkA95AbE8Tmhq1ZAA9JxfPbsMhKnbAuCBw RTVFpB28wq67+hR3hwBy+GMtK53d5WY5mHakalCwD8Bh5rtfh1aWNrtRXhZDfpi5+5 uexurA0h0JR0g+ffntIDHomfbetlpZdzlhu0PTr/jG78ZFQavk5518S1XhzAss+2Lq 42YWpPFAfLr7zpcWdejls5GzdxfFFYwWjkGyZBg08fGz+pOhe92Q4iGs24LJJ9O2eD cagTvjRJuJgVPzr/l18A40tWMMQQNMPLRINZl4zzG1w+pS/RO8a2hVXbJLIlNJt3hR u2SEa9aAin3IQ== Content-Type: multipart/mixed; boundary="------------CCUzwyBDkqZY6Oc0k0EEU96G" Message-ID: <5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru> Date: Mon, 19 Feb 2024 12:24:09 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Removing unneeded self joins Content-Language: en-US To: Alexander Korotkov , Alexander Lakhin Cc: "Gregory Stark (as CFM)" , =?UTF-8?Q?Micha=C5=82_K=C5=82eczek?= , PostgreSQL Hackers , Richard Guo References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru> <1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru> <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru> <107682e0-137a-d0fb-2733-7fc6e9562690@gmail.com> <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru> <336c748b-4ffe-5915-3d3c-ece6d19e021c@gmail.com> <4788f781-31bd-9796-d7d6-588a751c8787@gmail.com> <56ee4520-e9d1-d519-54fe-c8bff880ce9b@gmail.com> From: Andrei Lepikhov Organization: Postgres Professional In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------CCUzwyBDkqZY6Oc0k0EEU96G Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 18/2/2024 23:18, Alexander Korotkov wrote: > On Sun, Feb 18, 2024 at 5:04 PM Alexander Korotkov wrote: >> On Sun, Feb 18, 2024 at 3:00 PM Alexander Lakhin wrote: >>> 09.01.2024 01:09, Alexander Korotkov wrote: >>> >>> Fixed in 30b4955a46. >>> >>> >>> Please look at the following query which fails with an error since >>> d3d55ce57: >>> >>> create table t (i int primary key); >>> >>> select t3.i from t t1 >>> join t t2 on t1.i = t2.i, >>> lateral (select t1.i limit 1) t3; >>> >>> ERROR: non-LATERAL parameter required by subquery >> >> Thank you for spotting. I'm looking at this. > > Attached is a draft patch fixing this query. Could you, please, recheck? I reviewed this patch. Why do you check only the target list? I guess these links can be everywhere. See the patch in the attachment with the elaborated test and slightly changed code. -- regards, Andrei Lepikhov Postgres Professional --------------CCUzwyBDkqZY6Oc0k0EEU96G Content-Type: text/plain; charset=UTF-8; name="0001-Replace-relids-in-lateral-subquery-target-list-du-v2.patch" Content-Disposition: attachment; filename*0="0001-Replace-relids-in-lateral-subquery-target-list-du-v2.pa"; filename*1="tch" Content-Transfer-Encoding: base64 RnJvbSA3Zjk0YTNjOTZmZDQxMDUyMmI4N2U1NzAyNDBjZGI5NmIzMDBkZDMxIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmV5IFYuIExlcGlraG92IiA8YS5sZXBpa2hv dkBwb3N0Z3Jlc3Byby5ydT4KRGF0ZTogTW9uLCAxOSBGZWIgMjAyNCAxMjoxNzo1NSArMDcw MApTdWJqZWN0OiBbUEFUQ0hdIFJlcGxhY2UgcmVsaWRzIGluIGxhdGVyYWwgc3VicXVlcnkg dGFyZ2V0IGxpc3QgZHVyaW5nIFNKRQoKLS0tCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxh bi9hbmFseXplam9pbnMuYyB8IDI5ICsrKysrKysrKysrKysrLQogc3JjL3Rlc3QvcmVncmVz cy9leHBlY3RlZC9qb2luLm91dCAgICAgICAgfCA0NCArKysrKysrKysrKysrKysrKysrKysr Kwogc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwgICAgICAgICAgICAgfCAxMiArKysr KysrCiAzIGZpbGVzIGNoYW5nZWQsIDg0IGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkK CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9hbmFseXplam9pbnMu YyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL2FuYWx5emVqb2lucy5jCmluZGV4IGU0 OTRhY2Q1MWEuLjA3MjI5OGY2NmMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXpl ci9wbGFuL2FuYWx5emVqb2lucy5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFu L2FuYWx5emVqb2lucy5jCkBAIC0zOTUsNyArMzk1LDM0IEBAIHJlbW92ZV9yZWxfZnJvbV9x dWVyeShQbGFubmVySW5mbyAqcm9vdCwgUmVsT3B0SW5mbyAqcmVsLAogCQl9CiAKIAkJLyog VXBkYXRlIGxhdGVyYWwgcmVmZXJlbmNlcy4gKi8KLQkJcmVwbGFjZV92YXJubygoTm9kZSAq KSBvdGhlcnJlbC0+bGF0ZXJhbF92YXJzLCByZWxpZCwgc3Vic3QpOworCQlpZiAocm9vdC0+ aGFzTGF0ZXJhbFJURXMpCisJCXsKKwkJCVJhbmdlVGJsRW50cnkgKnJ0ZSA9IHJvb3QtPnNp bXBsZV9ydGVfYXJyYXlbcnRpXTsKKwkJCVJlcGxhY2VWYXJub0NvbnRleHQgY3R4ID0gey5m cm9tID0gcmVsaWQsLnRvID0gc3Vic3R9OworCisJCQlpZiAocnRlLT5sYXRlcmFsKQorCQkJ eworCQkJCXJlcGxhY2VfdmFybm8oKE5vZGUgKikgb3RoZXJyZWwtPmxhdGVyYWxfdmFycywg cmVsaWQsIHN1YnN0KTsKKworCQkJCS8qCisJCQkJICogQWx0aG91Z2ggd2UgcGFzcyByb290 LT5wYXJzZSB0aHJvdWdoIGNsZWFudXAgcHJvY2VkdXJlLAorCQkJCSAqIGJ1dCBwYXJzZS0+ cnRhYmxlIGFuZCBydGUgY29udGFpbnMgcmVmcyB0byBkaWZmZXJlbnQgY29waWVzCisJCQkJ ICogb2YgdGhlIHN1YnF1ZXJ5LgorCQkJCSAqLworCQkJCWlmIChvdGhlcnJlbC0+cnRla2lu ZCA9PSBSVEVfU1VCUVVFUlkpCisJCQkJCXF1ZXJ5X3RyZWVfd2Fsa2VyKHJ0ZS0+c3VicXVl cnksIHJlcGxhY2VfdmFybm9fd2Fsa2VyLCAmY3R4LAorCQkJCQkJCQkJICBRVFdfRVhBTUlO RV9TT1JUR1JPVVApOworI2lmZGVmIFVTRV9BU1NFUlRfQ0hFQ0tJTkcKKwkJCQkvKiBKdXN0 IGNoZWNrIHBvc3NpYmx5IGhpZGRlbiBub24tcmVwbGFjZWQgcmVsaWRzICovCisJCQkJQXNz ZXJ0KCFibXNfaXNfbWVtYmVyKHJlbGlkLCBwdWxsX3Zhcm5vcyhyb290LCAoTm9kZSAqKSBy dGUtPnRhYmxlc2FtcGxlKSkpOworCQkJCUFzc2VydCghYm1zX2lzX21lbWJlcihyZWxpZCwg cHVsbF92YXJub3Mocm9vdCwgKE5vZGUgKikgcnRlLT5mdW5jdGlvbnMpKSk7CisJCQkJQXNz ZXJ0KCFibXNfaXNfbWVtYmVyKHJlbGlkLCBwdWxsX3Zhcm5vcyhyb290LCAoTm9kZSAqKSBy dGUtPnRhYmxlZnVuYykpKTsKKwkJCQlBc3NlcnQoIWJtc19pc19tZW1iZXIocmVsaWQsIHB1 bGxfdmFybm9zKHJvb3QsIChOb2RlICopIHJ0ZS0+dmFsdWVzX2xpc3RzKSkpOworI2VuZGlm CisJCQl9CisJCX0KKworCiAJfQogCiAJLyoKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jl c3MvZXhwZWN0ZWQvam9pbi5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW4u b3V0CmluZGV4IDBjMmNiYTg5MjEuLmQ1NjBhNGE2YjkgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0 L3JlZ3Jlc3MvZXhwZWN0ZWQvam9pbi5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9leHBl Y3RlZC9qb2luLm91dApAQCAtNjM0OSw2ICs2MzQ5LDUwIEBAIG9uIHRydWU7CiAgICAgICAg ICAgICAgICAtPiAgU2VxIFNjYW4gb24gaW50OF90YmwgeQogKDcgcm93cykKIAorLS0gVGVz dCBwcm9jZXNzaW5nIHRhcmdldCBsaXN0cyBpbiBsYXRlcmFsIHN1YnF1ZXJpZXMKK2V4cGxh aW4gKHZlcmJvc2UsIGNvc3RzIG9mZikKK1NFTEVDVCB0My5hIEZST00gc2ogdDEsIHNqIHQy LAorTEFURVJBTCAoU0VMRUNUIHQxLmEgV0hFUkUgdDEuYSA8PiAxCisJCSBHUk9VUCBCWSAo dDEuYSkgSEFWSU5HIHQxLmEgPiAwIE9SREVSIEJZIHQxLmEgTElNSVQgMSkgdDMsCitMQVRF UkFMIChTRUxFQ1QgdDEuYSx0My5hIFdIRVJFIHQxLmEgPD4gdDMuYSt0Mi5hCisJCSBHUk9V UCBCWSAodDMuYSkgSEFWSU5HIHQxLmEgPiB0My5hKnQzLmErdDIuYS90MS5hIExJTUlUIDIp IHQ0LAorTEFURVJBTCAoU0VMRUNUICogRlJPTSBzaiBUQUJMRVNBTVBMRSBiZXJub3VsbGko dDEuYS90Mi5hKQorCQkgUkVQRUFUQUJMRSAodDEuYSt0Mi5hKSkgdDUsCitMQVRFUkFMIGdl bmVyYXRlX3NlcmllcygxLCB0MS5hICsgdDIuYSkgQVMgdDYKK1dIRVJFIHQxLmEgPSB0Mi5h OworICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIE5lc3RlZCBM b29wCisgICBPdXRwdXQ6ICh0Mi5hKQorICAgLT4gIE5lc3RlZCBMb29wCisgICAgICAgICBP dXRwdXQ6IHQyLmEsICh0Mi5hKQorICAgICAgICAgLT4gIE5lc3RlZCBMb29wCisgICAgICAg ICAgICAgICBPdXRwdXQ6IHQyLmEsICh0Mi5hKQorICAgICAgICAgICAgICAgLT4gIE5lc3Rl ZCBMb29wCisgICAgICAgICAgICAgICAgICAgICBPdXRwdXQ6IHQyLmEsICh0Mi5hKQorICAg ICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHB1YmxpYy5zaiB0MgorICAgICAg ICAgICAgICAgICAgICAgICAgICAgT3V0cHV0OiB0Mi5hLCB0Mi5iLCB0Mi5jCisgICAgICAg ICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6ICh0Mi5hIElTIE5PVCBOVUxMKQorICAgICAg ICAgICAgICAgICAgICAgLT4gIExpbWl0CisgICAgICAgICAgICAgICAgICAgICAgICAgICBP dXRwdXQ6ICh0Mi5hKQorICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEdyb3VwCisg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBPdXRwdXQ6IHQyLmEKKyAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBSZXN1bHQKKyAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIE9uZS1UaW1lIEZpbHRlcjogKCh0Mi5hIDw+IDEpIEFO RCAodDIuYSA+IDApKQorICAgICAgICAgICAgICAgLT4gIExpbWl0CisgICAgICAgICAgICAg ICAgICAgICBPdXRwdXQ6IE5VTEw6OmludGVnZXIsICgodDIuYSkpCisgICAgICAgICAgICAg ICAgICAgICAtPiAgR3JvdXAKKyAgICAgICAgICAgICAgICAgICAgICAgICAgIE91dHB1dDog TlVMTDo6aW50ZWdlciwgKHQyLmEpCisgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAg UmVzdWx0CisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBPbmUtVGltZSBGaWx0 ZXI6ICgodDIuYSA8PiAoKHQyLmEpICsgdDIuYSkpIEFORCAodDIuYSA+ICgoKHQyLmEpICog KHQyLmEpKSArICh0Mi5hIC8gdDIuYSkpKSkKKyAgICAgICAgIC0+ICBTYW1wbGUgU2NhbiBv biBwdWJsaWMuc2oKKyAgICAgICAgICAgICAgIE91dHB1dDogc2ouYSwgc2ouYiwgc2ouYwor ICAgICAgICAgICAgICAgU2FtcGxpbmc6IGJlcm5vdWxsaSAoKHQyLmEgLyB0Mi5hKSkgUkVQ RUFUQUJMRSAoKHQyLmEgKyB0Mi5hKSkKKyAgIC0+ICBGdW5jdGlvbiBTY2FuIG9uIHBnX2Nh dGFsb2cuZ2VuZXJhdGVfc2VyaWVzIHQ2CisgICAgICAgICBPdXRwdXQ6IHQ2LnQ2CisgICAg ICAgICBGdW5jdGlvbiBDYWxsOiBnZW5lcmF0ZV9zZXJpZXMoMSwgKHQyLmEgKyB0Mi5hKSkK KygyOSByb3dzKQorCiAtLSBDaGVjayB1cGRhdGluZyBvZiBMYXRlcmFsIGxpbmtzIGZyb20g dG9wLWxldmVsIHF1ZXJ5IHRvIHRoZSByZW1vdmluZyByZWxhdGlvbgogZXhwbGFpbiAoQ09T VFMgT0ZGKQogU0VMRUNUICogRlJPTSBwZ19hbSBhbSBXSEVSRSBhbS5hbW5hbWUgSU4gKApk aWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwgYi9zcmMvdGVzdC9y ZWdyZXNzL3NxbC9qb2luLnNxbAppbmRleCAyNTdmNzI3YTJiLi45MzQxZjgwYmJjIDEwMDY0 NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9qb2luLnNxbAorKysgYi9zcmMvdGVzdC9y ZWdyZXNzL3NxbC9qb2luLnNxbApAQCAtMjQwNiw2ICsyNDA2LDE4IEBAIGxlZnQgam9pbiAo c2VsZWN0IGNvYWxlc2NlKHkucTEsIDEpIGZyb20gaW50OF90YmwgeQogCW9uIHRydWUpIHoK IG9uIHRydWU7CiAKKy0tIFRlc3QgcHJvY2Vzc2luZyB0YXJnZXQgbGlzdHMgaW4gbGF0ZXJh bCBzdWJxdWVyaWVzCitleHBsYWluICh2ZXJib3NlLCBjb3N0cyBvZmYpCitTRUxFQ1QgdDMu YSBGUk9NIHNqIHQxLCBzaiB0MiwKK0xBVEVSQUwgKFNFTEVDVCB0MS5hIFdIRVJFIHQxLmEg PD4gMQorCQkgR1JPVVAgQlkgKHQxLmEpIEhBVklORyB0MS5hID4gMCBPUkRFUiBCWSB0MS5h IExJTUlUIDEpIHQzLAorTEFURVJBTCAoU0VMRUNUIHQxLmEsdDMuYSBXSEVSRSB0MS5hIDw+ IHQzLmErdDIuYQorCQkgR1JPVVAgQlkgKHQzLmEpIEhBVklORyB0MS5hID4gdDMuYSp0My5h K3QyLmEvdDEuYSBMSU1JVCAyKSB0NCwKK0xBVEVSQUwgKFNFTEVDVCAqIEZST00gc2ogVEFC TEVTQU1QTEUgYmVybm91bGxpKHQxLmEvdDIuYSkKKwkJIFJFUEVBVEFCTEUgKHQxLmErdDIu YSkpIHQ1LAorTEFURVJBTCBnZW5lcmF0ZV9zZXJpZXMoMSwgdDEuYSArIHQyLmEpIEFTIHQ2 CitXSEVSRSB0MS5hID0gdDIuYTsKKwogLS0gQ2hlY2sgdXBkYXRpbmcgb2YgTGF0ZXJhbCBs aW5rcyBmcm9tIHRvcC1sZXZlbCBxdWVyeSB0byB0aGUgcmVtb3ZpbmcgcmVsYXRpb24KIGV4 cGxhaW4gKENPU1RTIE9GRikKIFNFTEVDVCAqIEZST00gcGdfYW0gYW0gV0hFUkUgYW0uYW1u YW1lIElOICgKLS0gCjIuNDMuMAoK --------------CCUzwyBDkqZY6Oc0k0EEU96G--