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 1rJ9dO-00BvoG-PG for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Dec 2023 09:58:31 +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 1rJ9dM-0078Ub-Fp for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Dec 2023 09:58:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rJ9dM-0078US-1n for pgsql-hackers@lists.postgresql.org; Fri, 29 Dec 2023 09:58:28 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rJ9dE-00ERzI-JK for pgsql-hackers@lists.postgresql.org; Fri, 29 Dec 2023 09:58:26 +0000 Received: from [172.20.10.3] (ppp-49-237-13-250.revip6.asianet.co.th [49.237.13.250]) (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 E3537E20FDB; Fri, 29 Dec 2023 12:58:16 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1703843899; bh=kfzz8KcCRR7ZHTo32P7qNeshxegqV31lNSu2/Z0bl0c=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=0iiyBO6jlb1FJdqWSOscJEJ2ohP9JhfqVcJAcZ+qJYRrKZw6WkYcdmMFPHb8FRuHn w/KTxaF6hP3x+TMW9b70q3IEMcAPaixff4BTIdUCWtJWvFurMgofG9OZUI7rUAG/F6 zZKIBqnKIz75UpSqcs1WE0eVzbXOr4o6rAFQZHGhYP2/2v5rb/YLZeP34gf9UKJRhK OWGAekMtkHOkgcww6eRiNyPogQQC/0r3LyS1QYouwwIMBY2d2MNAuJan8tW9aSjtM7 lpR9dr7BLY1GBqtkzDGXLKUGjdEa8O4oWLIpuARcgIhIaWk3vc4TREXJg2AuZNkpHN taT3vQPhY+qoQ== Content-Type: multipart/mixed; boundary="------------3Z1yyctEFr0Jgo6Ro00BZeiU" Message-ID: <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru> Date: Fri, 29 Dec 2023 16:58:11 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Removing unneeded self joins To: Alexander Lakhin , Alexander Korotkov 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> <05469e80-454b-41d7-d04f-421f42bbd4a9@postgrespro.ru> <48c11b23-9c23-440e-9a3c-020e05196449@postgrespro.ru> <8800ccf2-ae82-461c-92a1-d4c0a22f8cf2@postgrespro.ru> <1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru> <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru> <107682e0-137a-d0fb-2733-7fc6e9562690@gmail.com> Content-Language: en-US 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. --------------3Z1yyctEFr0Jgo6Ro00BZeiU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 29/12/2023 12:00, Alexander Lakhin wrote: > Hi Alexander, > > 23.10.2023 14:29, Alexander Korotkov wrote: >> Fixed all of the above. Thank you for catching this! > > I've discovered that starting from d3d55ce57 the following query: > CREATE TABLE t(a int PRIMARY KEY); > > WITH tt AS (SELECT * FROM t) > UPDATE t SET a = tt.a + 1 FROM tt > WHERE tt.a = t.a RETURNING t.a; > > triggers an error "variable not found in subplan target lists". > (Commits 8a8ed916f and b5fb6736e don't fix this, unfortunately.) Thanks for the report! The problem is with the resultRelation field. We forget to replace the relid here. Could you check your issue with the patch in the attachment? Does it resolve this case? -- regards, Andrei Lepikhov Postgres Professional --------------3Z1yyctEFr0Jgo6Ro00BZeiU Content-Type: text/plain; charset=UTF-8; name="quickfix.diff" Content-Disposition: attachment; filename="quickfix.diff" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL2FuYWx5emVqb2lucy5j IGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vYW5hbHl6ZWpvaW5zLmMKaW5kZXggNmMw MmZlODkwOC4uZjc5YzY3M2FmZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVy L3BsYW4vYW5hbHl6ZWpvaW5zLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4v YW5hbHl6ZWpvaW5zLmMKQEAgLTE4NjEsNiArMTg2MSw4IEBAIHJlbW92ZV9zZWxmX2pvaW5f cmVsKFBsYW5uZXJJbmZvICpyb290LCBQbGFuUm93TWFyayAqa21hcmssIFBsYW5Sb3dNYXJr ICpybWFyaywKIAkvKiBSZXBsYWNlIHZhcm5vIGluIGFsbCB0aGUgcXVlcnkgc3RydWN0dXJl cyAqLwogCXF1ZXJ5X3RyZWVfd2Fsa2VyKHJvb3QtPnBhcnNlLCByZXBsYWNlX3Zhcm5vX3dh bGtlciwgJmN0eCwKIAkJCQkJICBRVFdfRVhBTUlORV9TT1JUR1JPVVApOworCWlmIChyb290 LT5wYXJzZS0+cmVzdWx0UmVsYXRpb24gPT0gdG9SZW1vdmUtPnJlbGlkKQorCQlyb290LT5w YXJzZS0+cmVzdWx0UmVsYXRpb24gPSB0b0tlZXAtPnJlbGlkOwogCiAJLyogUmVwbGFjZSBs aW5rcyBpbiB0aGUgcGxhbm5lciBpbmZvICovCiAJcmVtb3ZlX3JlbF9mcm9tX3F1ZXJ5KHJv b3QsIHRvUmVtb3ZlLCB0b0tlZXAtPnJlbGlkLCBOVUxMLCBOVUxMKTsKQEAgLTE4NzAsNiAr MTg3Miw5IEBAIHJlbW92ZV9zZWxmX2pvaW5fcmVsKFBsYW5uZXJJbmZvICpyb290LCBQbGFu Um93TWFyayAqa21hcmssIFBsYW5Sb3dNYXJrICpybWFyaywKIAkJCQkgIHRvUmVtb3ZlLT5y ZWxpZCwgdG9LZWVwLT5yZWxpZCk7CiAJcmVwbGFjZV92YXJubygoTm9kZSAqKSByb290LT5w cm9jZXNzZWRfZ3JvdXBDbGF1c2UsCiAJCQkJICB0b1JlbW92ZS0+cmVsaWQsIHRvS2VlcC0+ cmVsaWQpOworCXJlcGxhY2VfcmVsaWQocm9vdC0+YWxsX3Jlc3VsdF9yZWxpZHMsIHRvUmVt b3ZlLT5yZWxpZCwgdG9LZWVwLT5yZWxpZCk7CisJcmVwbGFjZV9yZWxpZChyb290LT5sZWFm X3Jlc3VsdF9yZWxpZHMsIHRvUmVtb3ZlLT5yZWxpZCwgdG9LZWVwLT5yZWxpZCk7CisKIAog CS8qCiAJICogVGhlcmUgbWF5IGJlIHJlZmVyZW5jZXMgdG8gdGhlIHJlbCBpbiByb290LT5m a2V5X2xpc3QsIGJ1dCBpZiBzbywK --------------3Z1yyctEFr0Jgo6Ro00BZeiU--