Wrong results from GROUP BY column removal

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Wrong results from GROUP BY column removal
Date: 2026-11-11 07:53:49
Message-ID: CAMbWs484GmrQNb6nYV8cbV2sq2a7DKDDb=WwJS=2rtS8ma+Mxw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was reloading my memory of the various uniqueness proofs in our
code, in order to pick the UniqueKeys work back up, and I noticed a
thinko in remove_useless_groupby_columns().

/*
* We must insist that the index columns are all defined NOT
* NULL otherwise duplicate NULLs could exist. However, we
* can relax this check when the index is defined with NULLS
* NOT DISTINCT as there can only be 1 NULL row, therefore
* functional dependency on the unique columns is maintained,
* despite the NULL.
*/

This reasoning doesn't hold when the relation is on the nullable side
of an outer join. The rows null-extended by the join have NULLs in
the indexed columns too, so once the other columns are removed from
the GROUP BY, they end up in the same group as the relation's one real
NULL row, even though they differ from it in those columns. It's not
hard to come up with a query that gives wrong results because of this.

create table t1 (a int);
create table t2 (a int, b int not null);
create unique index on t2 (a) nulls not distinct;
insert into t1 values (1), (9);
insert into t2 values (null, 1);

select t2.a, t2.b, count(*) from t1 left join t2 on t1.a = t2.b group
by t2.a, t2.b;
a | b | count
---+---+-------
| 1 | 2
(1 row)

There should be two groups here, (NULL, 1) and (NULL, NULL).

I think we can fix this by not applying the NULLS NOT DISTINCT
relaxation when the relation's GROUP BY Vars can be nulled by an outer
join, which we can tell from their varnullingrels. See attached.

I think this needs to be back-patched to v18.

- Richard

Attachment Content-Type Size
v1-0001-Fix-GROUP-BY-column-removal-for-NULLS-NOT-DISTINC.patch application/octet-stream 4.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2026-11-11 08:03:19 Re: Credits For v19
Previous Message Michael Paquier 2026-11-11 07:48:31 Re: Bug report and fix for: COMMIT/ROLLBACK AND CHAIN after an error drops the isolation level