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 1rMvnG-007mYB-CA for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jan 2024 20:00:18 +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 1rMvnF-007IrE-C1 for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jan 2024 20:00:17 +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 1rMvnF-007Ir6-2s for pgsql-hackers@lists.postgresql.org; Mon, 08 Jan 2024 20:00:17 +0000 Received: from mail-lj1-x230.google.com ([2a00:1450:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rMvnC-000WoZ-Rz for pgsql-hackers@lists.postgresql.org; Mon, 08 Jan 2024 20:00:16 +0000 Received: by mail-lj1-x230.google.com with SMTP id 38308e7fff4ca-2cd0f4797aaso23616281fa.0 for ; Mon, 08 Jan 2024 12:00:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1704744014; x=1705348814; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:references:cc:to:from :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=fT11/yChkcFUqoN2OH42udpBcxsOM6lh1gHmW33Ll2g=; b=msJepq0ZNP0H64xmNsKi/M/imk2omFVZhbkknKyX8+kirPs/ukaBBZYJIp3IzLywgB Ktqif/0J0RIQPGW/hpzVA0Q42xomTU7GEtJkSAMEOz/P7Vrh266tV3B0WdwZ7c37fOxT dnDAmlm040TIpvwu2nQPDI5s0CGo/J+KjQRSrLQgQl1TuwtEOi05+7fBd3L8FJ+1qaZz /3sJKqarDvwizus5cHvgxaSOi86iCDCvZEmCwKoEtRzXz2kMG0gQSWsEnz4juUpRjQ6B aHal191KiqWh0t4J+GNMzWcQcQo1WLWgGuikEl7QDTXa+JkxMa1CNAjsRNrIm4aZIxfR yP0w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1704744014; x=1705348814; h=content-transfer-encoding:in-reply-to:references:cc:to:from :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=fT11/yChkcFUqoN2OH42udpBcxsOM6lh1gHmW33Ll2g=; b=TL3uE8jjXjGr4Wvdb4tbotTnXajALnhci6VyFg1O/uAINzvvjFoYhQl0k0JnaBPuHi N1F0vExVWXh7bpfYWStRsbGZEFMHkhBNbtmLGLgvKWRfY3KnwPNi78svnEh7Vzd78Eks AZ17te+jZA/a3qdci6W5FV1L4GWrKz2IsHwaiAVvaV755zIc9zpmOjBU7WOf3BH3RZ+S PYgK41sx14E0mpVqBIx+W589kJ+NFUmW3wcz/EjKaUlJJGdCJU5D17iSKRmDDWx9sEVb B6b2JvCKA1aauusxBmtJjEJFCwLjmz+NpT/zQgo5zb3YtUJ8+xlUB5ai0qRcUdlW5BN+ tMXg== X-Gm-Message-State: AOJu0YxWsD0OPqmeBpozr8wQDPZUkpLPXAenvqKIKUC4uFm0UWScU5rF gzFf5AFO81uoHirYdC8whnU= X-Google-Smtp-Source: AGHT+IHcvwex5cR/Cw+nkOlxfvVJPcw7UxEUjFk+LIN8XBvAor1mxSXJjAJCRMNK10WVuDbcHRi9CA== X-Received: by 2002:a2e:9cd4:0:b0:2cd:2acc:e9ff with SMTP id g20-20020a2e9cd4000000b002cd2acce9ffmr1548574ljj.26.1704744013562; Mon, 08 Jan 2024 12:00:13 -0800 (PST) Received: from [1.0.0.7] ([91.185.77.50]) by smtp.gmail.com with ESMTPSA id g6-20020a2eb0c6000000b002ccd39eeae4sm78306ljl.100.2024.01.08.12.00.02 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 08 Jan 2024 12:00:04 -0800 (PST) Message-ID: <4788f781-31bd-9796-d7d6-588a751c8787@gmail.com> Date: Mon, 8 Jan 2024 23: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 From: Alexander Lakhin To: Andrei Lepikhov , 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> <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> <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru> <336c748b-4ffe-5915-3d3c-ece6d19e021c@gmail.com> In-Reply-To: <336c748b-4ffe-5915-3d3c-ece6d19e021c@gmail.com> 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 Hello Andrei and Alexander, Please look at the following query which produces an incorrect result since d3d55ce57: CREATE TABLE t(a int PRIMARY KEY, b int); INSERT INTO t VALUES  (1, 1), (2, 1); SELECT * FROM t WHERE EXISTS (SELECT * FROM t t2 WHERE t2.a = t.b AND t2.b > 0);  a | b ---+---  1 | 1 (1 row) I think that the expected result is:  a | b ---+---  1 | 1  2 | 1 (2 rows) Best regards, Alexander