BUG #19579: Wrong results regression

Hi,

On Mon, 27 Jul 2026 at 15:53, PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the website:

Bug reference: 19579
Logged by: Viktor Leis
Email address: leis@in.tum.de
PostgreSQL version: 19beta2
Operating system: Ubuntu 26.04 LTS
Description:

Hi,

The following self-contained query returns rows on current master and on
19beta2, but should return nothing. PostgreSQL 18 and earlier are
unaffected.

select * from
(select 0 as c0 from
(select t1.c0 from
(select null::int as c0 from ((select 1) union all (select 2))
t0)
t1
full join (select 1 as c1 where false) t2 on true) t3
where c0 <> 7) t4
cross join (select * from (values (0::bigint)) v(x)
where now() is not null) t5;

c0 | x
----+---
0 | 0
0 | 0
(2 rows)

t1.c0 is a plain NULL::integer, so "c0 <> 7" is NULL for every row and
nothing should survive it. EXPLAIN (VERBOSE, COSTS OFF) shows that the
filter is simply gone:

Result
Output: 0, '0'::bigint
-> Append
-> Result
One-Time Filter: (now() IS NOT NULL)
-> Result
One-Time Filter: (now() IS NOT NULL)

Bisection points to commit f2bae51dfd5 ("Keep track of what RTIs a Result
node is scanning", 2025-09-23).

Thanks for the report and analysis.

I spent some time on this one. If I'm reading it right, the bisect to
f2bae51dfd5 seems to point at create_gating_plan(): when it stacks a
gating Result over an existing Result, it now absorbs the child
unconditionally, whereas before it only did that for a "trivial" child
(no subplan and no resconstantqual). So when the child carries a
one-time filter, that filter looks like it just gets dropped, which would
explain the missing "NULL <> 7" gate in the reported plan.

Restoring the old triviality guard (keep the child as our subplan when it
has a resconstantqual or a subplan) makes the query return no rows again
for me, and still keeps the relids/result_type attribution for the
trivial case.

Regards,
Ayush

On Tue, 28 Jul 2026 at 00:38, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:

I spent some time on this one. If I'm reading it right, the bisect to
f2bae51dfd5 seems to point at create_gating_plan(): when it stacks a
gating Result over an existing Result, it now absorbs the child
unconditionally, whereas before it only did that for a "trivial" child
(no subplan and no resconstantqual). So when the child carries a
one-time filter, that filter looks like it just gets dropped, which would
explain the missing "NULL <> 7" gate in the reported plan.

I was looking at this last night and came to the same conclusion about
the mistake in create_gating_plan().

The test case can be simplified to:

select * from (
select c0 from (select null::int as c0 from ((select 1) union all
(select 2))) t1
full join (select 1) t2 on true
where c0 is not null
) t3 where now() is not null;

I'm not sure it's worth verifying the EXPLAIN output in the test as
the important part here is the result. It might not be inconceivable
that someone might want to chain the resconstantqual in the future
with:

gplan->resconstantqual = (Node *) list_concat((List *)
gplan->resconstantqual, (List *) rplan->resconstantqual);

and reduce to a single Result node.

I've included Robert here as I'm not all that certain why this change
was made. I suspect it was a misunderstanding of the comment "We might
have had a trivial Result plan already", where "trivial" is not well
defined. If that's the case, then I think the comments need work. Two
paragraphs seem a little excessive. Wouldn't the following suffice?

/*
* See if we can reduce down stacked Result nodes to a single node. This
* is only possible when the nested Result has no subplan and no gating
* qual. If we do remove the nested Result, we maintain the relids and
* result_type for EXPLAIN.
*/

David

Hi,

On Tue, 28 Jul 2026 at 06:08, David Rowley <dgrowleyml@gmail.com> wrote:

On Tue, 28 Jul 2026 at 00:38, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

I spent some time on this one. If I'm reading it right, the bisect to
f2bae51dfd5 seems to point at create_gating_plan(): when it stacks a
gating Result over an existing Result, it now absorbs the child
unconditionally, whereas before it only did that for a "trivial" child
(no subplan and no resconstantqual). So when the child carries a
one-time filter, that filter looks like it just gets dropped, which would
explain the missing "NULL <> 7" gate in the reported plan.

I was looking at this last night and came to the same conclusion about
the mistake in create_gating_plan().

Thanks for the review!

The test case can be simplified to:

select * from (
select c0 from (select null::int as c0 from ((select 1) union all
(select 2))) t1
full join (select 1) t2 on true
where c0 is not null
) t3 where now() is not null;

I'm not sure it's worth verifying the EXPLAIN output in the test as
the important part here is the result. It might not be inconceivable
that someone might want to chain the resconstantqual in the future
with:

gplan->resconstantqual = (Node *) list_concat((List *)
gplan->resconstantqual, (List *) rplan->resconstantqual);

and reduce to a single Result node.

Sounds good, I changed it to just the select statement.

I've included Robert here as I'm not all that certain why this change
was made. I suspect it was a misunderstanding of the comment "We might
have had a trivial Result plan already", where "trivial" is not well
defined. If that's the case, then I think the comments need work. Two
paragraphs seem a little excessive. Wouldn't the following suffice?

/*
* See if we can reduce down stacked Result nodes to a single node. This
* is only possible when the nested Result has no subplan and no gating
* qual. If we do remove the nested Result, we maintain the relids and
* result_type for EXPLAIN.
*/

Modified the comment as suggested above.

v2 patch attached.

Regards,
Ayush

Hi,

On Tue, 28 Jul 2026 at 11:18, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

Hi,

On Tue, 28 Jul 2026 at 06:08, David Rowley <dgrowleyml@gmail.com> wrote:

On Tue, 28 Jul 2026 at 00:38, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

I spent some time on this one. If I'm reading it right, the bisect to
f2bae51dfd5 seems to point at create_gating_plan(): when it stacks a
gating Result over an existing Result, it now absorbs the child
unconditionally, whereas before it only did that for a "trivial" child
(no subplan and no resconstantqual). So when the child carries a
one-time filter, that filter looks like it just gets dropped, which

would

explain the missing "NULL <> 7" gate in the reported plan.

I was looking at this last night and came to the same conclusion about
the mistake in create_gating_plan().

Thanks for the review!

The test case can be simplified to:

select * from (
select c0 from (select null::int as c0 from ((select 1) union all
(select 2))) t1
full join (select 1) t2 on true
where c0 is not null
) t3 where now() is not null;

I'm not sure it's worth verifying the EXPLAIN output in the test as
the important part here is the result. It might not be inconceivable
that someone might want to chain the resconstantqual in the future
with:

gplan->resconstantqual = (Node *) list_concat((List *)
gplan->resconstantqual, (List *) rplan->resconstantqual);

and reduce to a single Result node.

Sounds good, I changed it to just the select statement.

I've included Robert here as I'm not all that certain why this change
was made. I suspect it was a misunderstanding of the comment "We might
have had a trivial Result plan already", where "trivial" is not well
defined. If that's the case, then I think the comments need work. Two
paragraphs seem a little excessive. Wouldn't the following suffice?

/*
* See if we can reduce down stacked Result nodes to a single node. This
* is only possible when the nested Result has no subplan and no gating
* qual. If we do remove the nested Result, we maintain the relids and
* result_type for EXPLAIN.
*/

Modified the comment as suggested above.

v2 patch attached.

I've opened a commitfest item for this:
https://commitfest.postgresql.org/patch/7078/

Should it be in the Pg 19 open list too?

Regards,
Ayush

On Thu, Jul 30, 2026 at 11:37:38PM +0530, Ayush Tiwari wrote:

I've opened a commitfest item for this:
https://commitfest.postgresql.org/patch/7078/

Should it be in the Pg 19 open list too?

Yes, the claim is about f2bae51dfd5, affecting v19 and newer
versions. Please add one to make sure that we track the problem.
This way, we avoid that the problem falls into the void and gets
forgotten. Open items should have the culprit commit and be assigned
to the committer who did the commit.

There is a lot of traffic on pgsql-bugs and pgsql-hackers combined,
things tend to be forgotten if not tracked.
--
Michael

On Tue, 28 Jul 2026 at 17:48, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:

On Tue, 28 Jul 2026 at 06:08, David Rowley <dgrowleyml@gmail.com> wrote:

/*
* See if we can reduce down stacked Result nodes to a single node. This
* is only possible when the nested Result has no subplan and no gating
* qual. If we do remove the nested Result, we maintain the relids and
* result_type for EXPLAIN.
*/

Modified the comment as suggested above.

v2 patch attached.

Thank you. Pushed.

David