pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
4+ messages / 3 participants
[nested] [flat]

* BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
@ 2026-11-11 01:39 PG Bug reporting form <noreply@postgresql.org>
  2026-11-11 02:23 ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: PG Bug reporting form @ 2026-11-11 01:39 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: idrissaajalloh@gmail.com

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.








^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
  2026-11-11 01:39 BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-11 02:23 ` Tender Wang <tndrwang@gmail.com>
  2026-11-11 05:16   ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tender Wang @ 2026-11-11 02:23 UTC (permalink / raw)
  To: idrissaajalloh@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

PG Bug reporting form <noreply@postgresql.org> 于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

Attachments:

0001-Skip-SJE-if-jointree-containing-VOLATILE-function.patch (text/plain, 3.2K) download From dc4afaeeb7e2df8ace968038fa40836a80fb015f Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Wed, 11 Nov 2026 10:20:27 +0800 Subject: [PATCH] Skip SJE if jointree containing VOLATILE function --- src/backend/optimizer/plan/analyzejoins.c | 9 +++++-- src/test/regress/expected/join.out | 29 +++++++++++++++++++++++ src/test/regress/sql/join.sql | 19 +++++++++++++++ 3 files changed, 55 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 25a863efbde..b198d8f72f0 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -2144,9 +2144,14 @@ self_join_candidates_cmp(const void *a, const void *b) bool remove_useless_self_joins(PlannerInfo *root, List *joinlist) { - /* Skip if SJE is disabled, or if the joinlist has less than 2 members. */ + /* + * Skip if SJE is disabled, if the joinlist has less than 2 members, or if + * eliminating a relation could change the number or timing of volatile + * function evaluations. + */ if (!enable_self_join_elimination || joinlist == NIL || - (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List))) + (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List)) || + contain_volatile_functions((Node *) root->parse->jointree)) return false; /* Try to merge pairs of self-joined relations. */ diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 7773852e61b..314636903bf 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -13073,3 +13073,32 @@ WHERE s.x = s.c; (1 row) ROLLBACK; +-- Self-join elimination must preserve separately observable evaluations of +-- volatile quals, even when Var replacement makes the quals identical. +BEGIN; +CREATE TEMP TABLE sje_volatile (id int PRIMARY KEY); +CREATE TEMP SEQUENCE sje_volatile_seq; +INSERT INTO sje_volatile VALUES (1); +SELECT setval('sje_volatile_seq', 1, false); + setval +-------- + 1 +(1 row) + +SELECT count(*) +FROM sje_volatile a +JOIN sje_volatile b ON a.id = b.id +WHERE (nextval('sje_volatile_seq') + a.id) % 2 = 0 + AND (nextval('sje_volatile_seq') + b.id) % 2 = 0; + count +------- + 0 +(1 row) + +SELECT last_value FROM sje_volatile_seq; + last_value +------------ + 2 +(1 row) + +ROLLBACK; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index a49d343ec32..efae6809099 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -4978,3 +4978,22 @@ FROM (VALUES (1)) AS v(z) LEFT JOIN WHERE s.x = s.c; ROLLBACK; + +-- Self-join elimination must preserve separately observable evaluations of +-- volatile quals, even when Var replacement makes the quals identical. +BEGIN; + +CREATE TEMP TABLE sje_volatile (id int PRIMARY KEY); +CREATE TEMP SEQUENCE sje_volatile_seq; +INSERT INTO sje_volatile VALUES (1); + +SELECT setval('sje_volatile_seq', 1, false); +SELECT count(*) +FROM sje_volatile a +JOIN sje_volatile b ON a.id = b.id +WHERE (nextval('sje_volatile_seq') + a.id) % 2 = 0 + AND (nextval('sje_volatile_seq') + b.id) % 2 = 0; + +SELECT last_value FROM sje_volatile_seq; + +ROLLBACK; -- 2.43.0
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
  2026-11-11 01:39 BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result PG Bug reporting form <noreply@postgresql.org>
  2026-11-11 02:23 ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Tender Wang <tndrwang@gmail.com>
@ 2026-11-11 05:16   ` Tender Wang <tndrwang@gmail.com>
  2026-11-11 11:03     ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Kirill Reshke <reshkekirill@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tender Wang @ 2026-11-11 05:16 UTC (permalink / raw)
  To: idrissaajalloh@gmail.com; pgsql-bugs@lists.postgresql.org

Tender Wang <tndrwang@gmail.com> 于2026年11月11日周三 10:23写道:
>
> Hi,
>
> PG Bug reporting form <noreply@postgresql.org> 于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

Attachments:

v2-0001-Skip-SJE-if-jointree-containing-VOLATILE-function.patch (text/plain, 3.9K) download From 375751aa514b4429527d9e1e37da68d29d63d99a Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Wed, 11 Nov 2026 10:20:27 +0800 Subject: [PATCH v2] Skip SJE if jointree containing VOLATILE function --- src/backend/optimizer/plan/analyzejoins.c | 38 +++++++++++++++++++++++ src/test/regress/expected/join.out | 29 +++++++++++++++++ src/test/regress/sql/join.sql | 19 ++++++++++++ 3 files changed, 86 insertions(+) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 25a863efbde..d68a4bc5d73 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1757,6 +1757,35 @@ match_unique_clauses(RelOptInfo *outer, List *uclauses, Index relid) return true; } +/* + * Check whether a relation has any restriction clauses containing volatile + * functions. Self-join elimination can rewrite or relocate such clauses, + * which could change the number or timing of their evaluations. + */ +static bool +rel_has_volatile_quals(RelOptInfo *rel) +{ + ListCell *lc; + + foreach(lc, rel->baserestrictinfo) + { + RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); + + if (contain_volatile_functions((Node *) rinfo)) + return true; + } + + foreach(lc, rel->joininfo) + { + RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); + + if (contain_volatile_functions((Node *) rinfo)) + return true; + } + + return false; +} + /* * Find and remove unique self-joins in a group of base relations that have * the same Oid. @@ -1804,6 +1833,15 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) Assert(root->simple_rte_array[k]->relid == root->simple_rte_array[r]->relid); + /* + * Removing either relation would rewrite or relocate its restriction + * clauses. Do not do that if any of them contain volatile functions, + * since their evaluation count or timing is observable. + */ + if (rel_has_volatile_quals(rrel) || + rel_has_volatile_quals(krel)) + continue; + /* * It is impossible to eliminate the join of two relations if they * are not on the same side of every outer join. Otherwise, the diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 7773852e61b..314636903bf 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -13073,3 +13073,32 @@ WHERE s.x = s.c; (1 row) ROLLBACK; +-- Self-join elimination must preserve separately observable evaluations of +-- volatile quals, even when Var replacement makes the quals identical. +BEGIN; +CREATE TEMP TABLE sje_volatile (id int PRIMARY KEY); +CREATE TEMP SEQUENCE sje_volatile_seq; +INSERT INTO sje_volatile VALUES (1); +SELECT setval('sje_volatile_seq', 1, false); + setval +-------- + 1 +(1 row) + +SELECT count(*) +FROM sje_volatile a +JOIN sje_volatile b ON a.id = b.id +WHERE (nextval('sje_volatile_seq') + a.id) % 2 = 0 + AND (nextval('sje_volatile_seq') + b.id) % 2 = 0; + count +------- + 0 +(1 row) + +SELECT last_value FROM sje_volatile_seq; + last_value +------------ + 2 +(1 row) + +ROLLBACK; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index a49d343ec32..efae6809099 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -4978,3 +4978,22 @@ FROM (VALUES (1)) AS v(z) LEFT JOIN WHERE s.x = s.c; ROLLBACK; + +-- Self-join elimination must preserve separately observable evaluations of +-- volatile quals, even when Var replacement makes the quals identical. +BEGIN; + +CREATE TEMP TABLE sje_volatile (id int PRIMARY KEY); +CREATE TEMP SEQUENCE sje_volatile_seq; +INSERT INTO sje_volatile VALUES (1); + +SELECT setval('sje_volatile_seq', 1, false); +SELECT count(*) +FROM sje_volatile a +JOIN sje_volatile b ON a.id = b.id +WHERE (nextval('sje_volatile_seq') + a.id) % 2 = 0 + AND (nextval('sje_volatile_seq') + b.id) % 2 = 0; + +SELECT last_value FROM sje_volatile_seq; + +ROLLBACK; -- 2.43.0
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result
  2026-11-11 01:39 BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result PG Bug reporting form <noreply@postgresql.org>
  2026-11-11 02:23 ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Tender Wang <tndrwang@gmail.com>
  2026-11-11 05:16   ` Re: BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result Tender Wang <tndrwang@gmail.com>
@ 2026-11-11 11:03     ` Kirill Reshke <reshkekirill@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Kirill Reshke @ 2026-11-11 11:03 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: idrissaajalloh@gmail.com; pgsql-bugs@lists.postgresql.org

On Wed, 11 Nov 2026 at 10:17, Tender Wang <tndrwang@gmail.com> wrote:
>
> Tender Wang <tndrwang@gmail.com> 于2026年11月11日周三 10:23写道:
> >
> > Hi,
> >
> > PG Bug reporting form <noreply@postgresql.org> 于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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-11-11 11:03 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 01:39 BUG #19861: Self-join elimination drops a VOLATILE predicate and changes the query result PG Bug reporting form <noreply@postgresql.org>
2026-11-11 02:23 ` Tender Wang <tndrwang@gmail.com>
2026-11-11 05:16   ` Tender Wang <tndrwang@gmail.com>
2026-11-11 11:03     ` Kirill Reshke <reshkekirill@gmail.com>

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