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 1rN3Ib-008uTB-KR for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jan 2024 04:01:10 +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 1rN3Ia-009LbU-Or for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jan 2024 04:01:08 +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 1rN3Hb-009IRw-Fc for pgsql-hackers@lists.postgresql.org; Tue, 09 Jan 2024 04:00:07 +0000 Received: from mail-lj1-x234.google.com ([2a00:1450:4864:20::234]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rN3HZ-000aBi-BI for pgsql-hackers@lists.postgresql.org; Tue, 09 Jan 2024 04:00:06 +0000 Received: by mail-lj1-x234.google.com with SMTP id 38308e7fff4ca-2cd46e7ae8fso24787801fa.1 for ; Mon, 08 Jan 2024 20:00:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1704772804; x=1705377604; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=+NuMzdXJMiKM2Dg9XFnDf99KR++7xvjM9dU5Fy+69VE=; b=AcbrNOk4qvgk7j30VeZe8LnyQSwKCJ9A9Uu51cuAAWQohazmRV9RGXlH1JkG/Zc5yT B6NWlo7abx+vu5nxygJ67Raamk2zj72sBp8H1SVxwlEx3Sz7ob1i8S7tVTjnEH82MLUk cNKoiWYceT2bYsfZip+SeM4oOTvrilz5tnEZpxjvjEbvAtRJxQGMuwHlDElucqF1tLvH akdNXhc7KvLOhXIBbPw/wFFAfP9dBH9RqzhDxCYChD3ILepRkvYkEFfKaw2GRAwXTX5X hFrBbvdj5BFazRivT1Ik0z/ak9rv/6scIWTU3s1IT6Xnm3HZfsz8a9NL4nMfueY+MoPk XQ0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1704772804; x=1705377604; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=+NuMzdXJMiKM2Dg9XFnDf99KR++7xvjM9dU5Fy+69VE=; b=DFYhXpSMsxuvlrO7KIClwWScY9j+JBFCoxqmZxV2wrNp+K1dEYsA/XLeooYL4+JLwU 7u2mx2GXHsSfe4cxFDPjZOxwdpN6HBhXFqZoVAY5+plrKHQSWpAMlfNfhwZLiJvysuXP ARrVeQFffm62HMQWfHmof6USn3kjyEgzYHPqfC2FRzEGXlg66k3lzla5WNMTL3liqSro RFzdHshMs0YFW8221VCGxJXF/VZX10LbYpbud6VuPei87Aun+bdHGouFuX81oV5oYOT6 eMemcSivaQtygQIZEFWGOhVE2jb/Y2QunUcV/G3m1Ii6x9t3T3v2SVA9cWzRWDwoVoAo mrww== X-Gm-Message-State: AOJu0Yw5cMpotGF8YmGPBVliJfBcxFnLNnDzOyOvNAr/X2LHS258lOCC nm+I8ZYfa7Cn7SEhHxKXlC0= X-Google-Smtp-Source: AGHT+IFNbFcExm2ghO4glgWK0qlYkPqXJEcptI4pSBI9kwf5CbdXNpySnbL2zs9iuJqHNFTBGLcX+w== X-Received: by 2002:a2e:3213:0:b0:2cc:f6ac:a2db with SMTP id y19-20020a2e3213000000b002ccf6aca2dbmr1027133ljy.196.1704772803530; Mon, 08 Jan 2024 20:00:03 -0800 (PST) Received: from [1.0.0.7] ([91.185.77.50]) by smtp.gmail.com with ESMTPSA id a11-20020a2e860b000000b002cd38676699sm190433lji.66.2024.01.08.20.00.01 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 08 Jan 2024 20:00:02 -0800 (PST) Message-ID: Date: Tue, 9 Jan 2024 07:00:01 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Removing unneeded self joins Content-Language: en-US To: Alexander Korotkov Cc: Andrei Lepikhov , "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> <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> <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru> <336c748b-4ffe-5915-3d3c-ece6d19e021c@gmail.com> <4788f781-31bd-9796-d7d6-588a751c8787@gmail.com> From: Alexander Lakhin In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 09.01.2024 01:09, Alexander Korotkov wrote: > Fixed in 30b4955a46. Thank you for fixing that! I've found another anomaly coined with d3d55ce57. This query: CREATE TABLE t(a int PRIMARY KEY, b int); INSERT INTO t VALUES  (1, 1), (2, 1); WITH t1 AS (SELECT * FROM t) UPDATE t SET b = t1.b + 1 FROM t1 WHERE t.a = t1.a RETURNING t.a, t1.b; gives "ERROR:  variable not found in subplan target lists" on d3d55ce57, but starting from a7928a57b it gives an incorrect result:  a | b ---+---  1 | 2  2 | 2 (2 rows) Best regards, Alexander