Repository navigation
Incorrect results: GROUP BY returns two rows for the NULL group with a nullable UNIQUE constraint #23820
Description
Is your feature request related to a problem or challenge?
Follow-up to #23634, #23818 and #23819: another case where a nullable UNIQUE constraint is treated as a key.
A column that is not in the GROUP BY may still be selected when the grouping columns determine it. That holds for a PRIMARY KEY, but not for a UNIQUE column, which permits multiple NULL rows and therefore does not determine anything across them.
GROUP BY x puts all NULLs in a single group, so it can return at most one row for x = NULL. This query returns two:
CREATE TABLE t (x INT UNIQUE, y INT) AS VALUES (NULL, 2), (NULL, 1), (1, 3);
SELECT x, y FROM t GROUP BY x;
-- returns 3 rows:
-- NULL 1
-- 1 3
-- NULL 2
--
-- `GROUP BY x` has only two groups (`1` and `NULL`), so at most 2 rows
-- should come backThe plan shows y has been added to the GROUP BY, which is what splits the NULL group in two:
EXPLAIN SELECT x, y FROM t GROUP BY x;
-- logical_plan
-- 01)Aggregate: groupBy=[[t.x, t.y]], aggr=[[]] <-- `y` added
-- 02)--TableScan: t projection=[x, y]
-- physical_plan
-- 01)AggregateExec: mode=FinalPartitioned, gby=[x@0 as x, y@1 as y], aggr=[]
-- 02)--RepartitionExec: partitioning=Hash([x@0, y@1], 16), input_partitions=1
-- 03)----AggregateExec: mode=Partial, gby=[x@0 as x, y@1 as y], aggr=[]
-- 04)------DataSourceExec: partitions=1, partition_sizes=[1]Other engines reject the query outright rather than resolving y.
postgres 14 rejects it for a nullable UNIQUE column, while accepting the same query when x is a PRIMARY KEY:
postgres=# create table t_uniq (x int unique, y int);
postgres=# select x, y from t_uniq group by x;
ERROR: column "t_uniq.y" must appear in the GROUP BY clause or be used in an aggregate function
postgres=# create table t_pk (x int primary key, y int);
postgres=# insert into t_pk values (1,10),(2,20);
postgres=# select x, y from t_pk group by x;
x | y
---+----
2 | 20
1 | 10
(2 rows)
duckdb rejects both forms:
❯ duckdb -c "create table t (x int unique, y int); select x, y from t group by x;"
Binder Error: column "y" must appear in the GROUP BY clause or must be part of an aggregate function.
DataFusion accepts the query in both forms; the PRIMARY KEY form gives the right answer.
Describe the solution you'd like
Fix the bug.
Describe alternatives you've considered
Additional context
Related:
- Incorrect results with unique constraints #23634
- Incorrect results: ORDER BY drops a sort key based on a nullable UNIQUE constraint #23818
- Incorrect results: GROUP BY column dropped based on a nullable UNIQUE constraint #23819
- fix: do not remove DISTINCT when a unique key was downgraded by a join #23548
- fix: Do not mark GROUP BY derived unique keys as nullable #23636
Activity
LDM-A commentedon Jul 23, 2026
I would like to take some time this week to look at this issue
test: add functional_dependencies.slt covering functional dependency …
alamb commentedon Jul 24, 2026
Great -- thank you
I think given our experience with #23634 the current structure of the functional dependency code may need to be adjusted / updated to fix this bug (and its siblings) in a reasonable way
LDM-A commentedon Jul 25, 2026
I think so too, yesterday I was doing some work on this and made some changes to try and fix it with a minimal change. But it keeps breaking other pieces. I will work on this later tonight a bit more and I will show you where I have gotten so far. So you can make sure I'm going in the right direction
alamb commentedon Jul 25, 2026
I think trying to get some better encapsulation in place -- like perhaps encapsulating the functional dependecy more in a struct / methods rather than just a bunch of free functions that pass around related parameters
test: add functional_dependencies.slt covering functional dependency …