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

On Wed, 11 Nov 2026 at 10:17, Tender Wang <tndrwang(at)gmail(dot)com> wrote:
>
> Tender Wang <tndrwang(at)gmail(dot)com> 于2026年11月11日周三 10:23写道：
> >
> > 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.
> >
>
> 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