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.98.2) (envelope-from ) id 1xQxXO-00000000YOh-0anA for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 01:54:10 +0000 Received: from localhost ([127.0.0.1]:45176 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xQxXM-000000080fI-3L0l for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 01:54:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:59320) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xQxIr-00000007rJ8-2O9n for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 01:39:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xQxIn-00000000I2o-0RIV for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 01:39:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=Kc3NgS8kWO5PdECU8haVhbhNdSZK29ssnAm74Dh/fP4=; b=y9nM4Ogq4uGo+Gx4iyW4vY0bks t3BUO9oyH+ueFtcC5Y/mGYkg5Bsb7J5EiWxGN/E9CXipGaS4fn6k7lXxE43QCHh+92GhGoSLWhoPT DNKluoFZHK3qdMLm/NdRhaMJksjSPi8Lvwcf3/WVOKk/3lZWBXxfPnUMrTNnEleBpXAqmvjKo7pmo qe5bklxt7UeVwvT1BK0UDz43nA3b9rZqWpRAXB6p9KpvcC5L2ztkHrA/8wn7U496TV+RsDr7dk0WJ 8Pw3BDC2F3Ij5HL4jR203xbEqD9sHHk6e8e33HJfTJid+H4WiHXtefhT3oW7y0kiSvJFRsZ+L5u8Y aum32kfg==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xQxIm-00000000o5k-0GXw for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 01:39:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xQxIk-000000026qr-3io1 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 01:39:02 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: idrissaajalloh@gmail.com Reply-To: idrissaajalloh@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 01:39:02 +0000 Message-ID: <19861-db2245414c5e093b@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19861 Logged by: Wren Email address: idrissaajalloh@gmail.com PostgreSQL version: 18.6 Operating system: Linux/amd64 Description: =20 Enabling self-join elimination for a primary-key self-join reduces two alias-specific calls to the same VOLATILE predicate to one call. With the optimization disabled, the query returns 0 after two predicate calls; with it enabled, the query returns 1 after one call. Self-join elimination should preserve the query's semantics. The two alias-specific VOLATILE expressions have separately observable evaluations, so the enabled case should retain a count of 0 and two predicate calls. How to repeat: \set ON_ERROR_STOP on BEGIN; CREATE SEQUENCE calls_seq; CREATE TABLE t1 (id integer PRIMARY KEY); INSERT INTO t1 VALUES (1); CREATE FUNCTION is_odd_call(integer) RETURNS boolean LANGUAGE plpgsql VOLATILE AS $$ BEGIN RETURN nextval('calls_seq') % 2 =3D 1; END $$; SET enable_self_join_elimination =3D off; SELECT setval('calls_seq', 1, false); SELECT count(*) AS sje_off_count FROM t1 AS a JOIN t1 AS b ON a.id =3D b.id WHERE is_odd_call(a.id) AND is_odd_call(b.id) \gset off_ SELECT last_value AS sje_off_calls FROM calls_seq \gset off_ SET enable_self_join_elimination =3D on; SELECT setval('calls_seq', 1, false); EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM t1 AS a JOIN t1 AS b ON a.id =3D b.id WHERE is_odd_call(a.id) AND is_odd_call(b.id); SELECT count(*) AS sje_on_count FROM t1 AS a JOIN t1 AS b ON a.id =3D b.id WHERE is_odd_call(a.id) AND is_odd_call(b.id) \gset on_ SELECT last_value AS sje_on_calls FROM calls_seq \gset on_ SELECT :off_sje_off_count AS sje_off_count, :off_sje_off_calls AS sje_off_calls, :on_sje_on_count AS sje_on_count, :on_sje_on_calls AS sje_on_calls, (:off_sje_off_count =3D 0 AND :off_sje_off_calls =3D 2 AND :on_sje_on_count =3D 0 AND :on_sje_on_calls =3D 2) AS semantics_preserved, (:off_sje_off_count =3D 0 AND :off_sje_off_calls =3D 2 AND :on_sje_on_count =3D 1 AND :on_sje_on_calls =3D 1) AS bug_reproduced; ROLLBACK; SELECT to_regclass('t1') IS NULL AND to_regclass('calls_seq') IS NULL AS cleanup_ok; The recorded plan and result were: QUERY PLAN ------------------------------------------- Aggregate Output: count(*) -> Seq Scan on public.t1 b Output: b.id Filter: is_odd_call(b.id) (5 rows) sje_off_count | sje_off_calls | sje_on_count | sje_on_calls | semantics_preserved | bug_reproduced ---------------+---------------+--------------+--------------+-------------= --------+---------------- 0 | 2 | 1 | 1 | f | t (1 row) cleanup_ok ------------ t (1 row) The sequence records predicate evaluations, and the VOLATILE function alternates between true and false. Starting at 1, preserving both evaluations makes the conjunction false. The enabled plan contains only one predicate filter, while the result and sequence state show that only one evaluation occurred. The issue was reproduced on PostgreSQL 18.6 and PostgreSQL 20devel on Linux/amd64. The plan and call counts indicate that self-join elimination discarded one separately observable VOLATILE expression after replacing one relation alias with the other. This occurs when self-join elimination is enabled for a primary-key self-join with alias-specific calls to the same VOLATILE predicate.