pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: idrissaajalloh@gmail.com
Subject: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
Date: Wed, 11 Nov 2026 01:39:02 +0000
Message-ID: <19861-db2245414c5e093b@postgresql.org> (raw)

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:        

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 = 1;
END
$$;

SET enable_self_join_elimination = off;
SELECT setval('calls_seq', 1, false);
SELECT count(*) AS sje_off_count
FROM t1 AS a
JOIN t1 AS b ON a.id = 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 = on;
SELECT setval('calls_seq', 1, false);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(*)
FROM t1 AS a
JOIN t1 AS b ON a.id = 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 = 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 = 0 AND :off_sje_off_calls = 2
        AND :on_sje_on_count = 0 AND :on_sje_on_calls = 2)
         AS semantics_preserved,
       (:off_sje_off_count = 0 AND :off_sje_off_calls = 2
        AND :on_sje_on_count = 1 AND :on_sje_on_calls = 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.








view thread (4+ messages)  latest in thread

Message-ID: <19861-db2245414c5e093b@postgresql.org>
Permalink:  ../19861-db2245414c5e093b@postgresql.org/
Also on:    postgresql.org/message-id/19861-db2245414c5e093b@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, idrissaajalloh@gmail.com
  Subject: Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
  In-Reply-To: <19861-db2245414c5e093b@postgresql.org>
  References: <19861-db2245414c5e093b@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox