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

Hi,

PG Bug reporting form <noreply(at)postgresql(dot)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