BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result

The following bug has been logged on the website:

Bug reference: 19861
Logged by: Wren
Email address: idrissaajalloh(at)gmail(dot)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.