Wrong results from join removal with DISTINCT ON + SRF subquery

Started by Richard Guo2 days ago2 messageshackers
Beta feature

Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.

appliessuccessCI history

You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:

docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253518
psql -h localhost -U postgres

Built from patchset v1 (message #1), August 23, 2026 at 01:19 AM.

Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:

git clone --branch t253518_1 https://github.com/hackorum-dev/postgres.git

In a checkout you already have, add the fork once:

git remote add hackorum https://github.com/hackorum-dev/postgres.git

then, for this patchset and every later one:

git fetch hackorum t253518_1 && git checkout t253518_1

Patchset v1 (message #1) is on t253518_1

Jump to latest
#1Richard Guo
guofenglinux@gmail.com

I was working on the UniqueKeys patch, and then I got confused by the
output of a DISTINCT ON + SRF query:

create table t (a int);
insert into t values (10), (20);

select distinct on (a) a, generate_series(1, 2) g from t;
a | g
----+---
10 | 1
20 | 1
(2 rows)

select distinct on (a) a, generate_series(1, 2) g from t order by a;
a | g
----+---
10 | 1
10 | 2
20 | 1
20 | 2
(4 rows)

I find it kind of mind-boggling that the same query returns a
different number of rows depending on whether there is an ORDER BY.

Some investigation shows that this comes from make_sort_input_target().
When there is an ORDER BY, SRFs that are not in any sort/group column
are postponed to after the Sort, and since Unique can't project, that
also means after the Unique. Without ORDER BY no postponement happens
and the SRF is expanded below the Unique.

I'm not sure whether this is a bug or not. But I do notice that
tsrf.sql has test cases that pin down exactly this with-vs-without
ORDER BY behavior for DISTINCT ON, so maybe this is expected, and I'm
not proposing to change that here.

What I do think is a bug is that the planner elsewhere assumes a
DISTINCT ON subquery is unique over its DISTINCT ON columns.
query_is_distinct_for() says

/*
* DISTINCT (including DISTINCT ON) guarantees uniqueness if all the
* columns in the DISTINCT clause appear in colnos and operator semantics
* match. This is true even if there are SRFs in the DISTINCT columns or
* elsewhere in the tlist.
*/

which is no longer true for DISTINCT ON given the postponement above.
So join removal and unique-inner joins can produce wrong results. As
an example, consider:

select t1.a from t t1
left join (select distinct on (a) a, generate_series(1,2) g from t
order by a) ss
on t1.a = ss.a;

Attached is a patch to not rely on DISTINCT ON for distinctness if
there are any tlist SRFs. This is more conservative than necessary,
since the SRFs are only postponed when there is an ORDER BY and none
of them is in a sort/group column, but I don't think it's worth
duplicating that logic in analyzejoins.c.

Any thoughts?

- Richard

Attachments:

t253518_1
v1-0001-Don-t-assume-DISTINCT-ON-implies-uniqueness-when-.patchapplication/octet-stream; name=v1-0001-Don-t-assume-DISTINCT-ON-implies-uniqueness-when-.patchDownload+56-6
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Richard Guo (#1)
Re: Wrong results from join removal with DISTINCT ON + SRF subquery

Richard Guo <guofenglinux@gmail.com> writes:

Attached is a patch to not rely on DISTINCT ON for distinctness if
there are any tlist SRFs. This is more conservative than necessary,
since the SRFs are only postponed when there is an ORDER BY and none
of them is in a sort/group column, but I don't think it's worth
duplicating that logic in analyzejoins.c.

I didn't study the patch in any detail, but I concur with your
thinking here. SRFs in the tlist are kind of a legacy usage in the
first place, so I don't feel a need to expend lots of effort on
optimizing cases like this.

regards, tom lane