BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
Hi,
PG Bug reporting form 于2026年11月11日周三 09:54写道:
>
> This occurs when self-join elimination is enabled for a primary-key
> self-join with alias-specific calls to the same VOLATILE predicate.
>
After one relation is removed, SJE rewrites its Vars to reference the
remaining relation.
In this case, that turns
is_odd_call(a.id)
is_odd_call(b.id)
into two structurally identical expressions referencing the same relation.
fixup_selfjoin_quals() then treats the second expression as a
duplicate and removes it.
That is not valid for volatile expressions, since the two occurrences
represent separately observable evaluations.
The attached patch conservatively disables SJE when the query jointree
contains volatile functions.
We could try to preserve volatile clauses while continuing to
eliminate the self-join.
However, SJE can both rewrite and relocate jointree quals, so avoiding
duplicate removal alone may not be sufficient to preserve the number
and timing of volatile evaluations.
Handling all such cases precisely would add a fair amount of
complexity for a rather uncommon optimization opportunity.
Since SJE is only an optimization, I do not think that complexity is
justified here.
Skipping SJE for queries with volatile jointree quals gives us a
simple correctness boundary, although it is admittedly conservative.
Any thoughts?
--
Thanks,
Tender Wang
Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
Tender Wang 于2026年11月11日周三 10:23写道: > > Hi, > > PG Bug reporting form 于2026年11月11日周三 09:54写道: > > > > This occurs when self-join elimination is enabled for a primary-key > > self-join with alias-specific calls to the same VOLATILE predicate. > > > > After one relation is removed, SJE rewrites its Vars to reference the > remaining relation. > In this case, that turns > > is_odd_call(a.id) > is_odd_call(b.id) > > into two structurally identical expressions referencing the same relation. > fixup_selfjoin_quals() then treats the second expression as a > duplicate and removes it. > That is not valid for volatile expressions, since the two occurrences > represent separately observable evaluations. > > The attached patch conservatively disables SJE when the query jointree > contains volatile functions. > Checking in remove_useless_self_joins() seems too strict; it can skip some self joins that have no volatile functions in quals. I moved the check into remove_self_joins_one_group() as attached v2. -- Thanks, Tender Wang
Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
От:
Kirill Reshke <reshkekirill@gmail.com>
Дата:
On Wed, 11 Nov 2026 at 10:17, Tender Wang wrote:
>
> Tender Wang 于2026年11月11日周三 10:23写道:
> >
> > Hi,
> >
> > PG Bug reporting form 于2026年11月11日周三 09:54写道:
> > >
> > > This occurs when self-join elimination is enabled for a primary-key
> > > self-join with alias-specific calls to the same VOLATILE predicate.
> > >
> >
> > After one relation is removed, SJE rewrites its Vars to reference the
> > remaining relation.
> > In this case, that turns
> >
> > is_odd_call(a.id)
> > is_odd_call(b.id)
> >
> > into two structurally identical expressions referencing the same relation.
> > fixup_selfjoin_quals() then treats the second expression as a
> > duplicate and removes it.
> > That is not valid for volatile expressions, since the two occurrences
> > represent separately observable evaluations.
> >
> > The attached patch conservatively disables SJE when the query jointree
> > contains volatile functions.
> >
>
> Checking in remove_useless_self_joins() seems too strict; it can skip
> some self joins that have no volatile functions in quals.
> I moved the check into remove_self_joins_one_group() as attached v2.
>
>
> --
> Thanks,
> Tender Wang
I tested v2 on master, It fixes the report, however, there are
remaining target-list cases where SJE changes the result.
CREATE TABLE sje_target (id int PRIMARY KEY);
CREATE SEQUENCE sje_seq;
INSERT INTO sje_target VALUES (0);
SELECT nextval('sje_seq') + a.id AS x,
nextval('sje_seq') + b.id AS y
FROM sje_target a JOIN sje_target b ON a.id = b.id
ORDER BY x;
With SJE off this returns (1,2), with last_value=2. With v2 and SJE on
it returns (1,1), with last_value=1.
The second case changes the row count:
SELECT generate_series(1, (nextval('sje_seq') + a.id)::int) + 0 AS x,
generate_series(1, (nextval('sje_seq') + b.id)::int) + 0 AS y
FROM sje_target a JOIN sje_target b ON a.id = b.id;
SJE off returns (1,1) and (NULL,2). v2 SJE fix /master on returns just (1,1).
So, ChangeVarNodes rewrites their correlated references before their
targets are planned, checking only root->processed_tlist would miss
the this case?
--
Best regards,
Kirill Reshke