Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUivs-0006Bl-Lu for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Feb 2023 06:48:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUivr-0002o9-C0 for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Feb 2023 06:48:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUivq-0002o0-UG for pgsql-bugs@lists.postgresql.org; Wed, 22 Feb 2023 06:48:51 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUivo-0007UQ-Tx for pgsql-bugs@lists.postgresql.org; Wed, 22 Feb 2023 06:48:50 +0000 Received: from [192.168.28.53] (cyclops.postgrespro.ru [93.174.131.138]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id B259821C9794 for ; Wed, 22 Feb 2023 09:48:47 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1677048527; bh=uUcAfHK2a2+0ASq0LJcynzmI8yyrkFFn0l92lTPfNJg=; h=Date:From:Subject:To; b=duIj2yVUDxvis1Idr758HHeIAUfDOT8QDHoBYoJbOsPMxooc0JC/KMP4aGRypst5X lE/lRnYvIdY3GeztAHHTSH5aQQEOdLk77v1DD0F+g4lOVdG73AMOuKIXfTbnmrlO4i A9a8yHhIfYWIurncKW0oRjzHgz8uXFGOq5KipPNk= Message-ID: <0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru> Date: Wed, 22 Feb 2023 12:48:45 +0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.7.2 Content-Language: en-US From: Andrey Lepikhov Subject: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware) Organization: Postgres Professional To: PostgreSQL mailing lists Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, sqlancer benchmark raised an issue with pushing down clauses in LEFT JOINs. Example: DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE; CREATE TABLE t1 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t2 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t3 AS SELECT true AS x FROM generate_series(0,1) x; CREATE TABLE t4 AS SELECT true AS x FROM generate_series(0,1) x; ANALYZE; EXPLAIN (ANALYZE, COSTS OFF) SELECT ALL t1.x FROM t1, t2 LEFT OUTER JOIN t3 ON t3.x LEFT OUTER JOIN t4 ON t3.x WHERE t4.x ISNULL; We should get zero tuples, right? But we have the explain: Nested Loop (actual time=0.024..0.028 rows=4 loops=1) ... REL_15_STABLE works correctly. As I remember, it could be induces by new machinery, introduced by the 'Making Vars outer-join aware' patch. Sorry for flood, if you are working on that issue. -- regards, Andrey Lepikhov Postgres Professional