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 …
Phoenix500526 commentedon Nov 6, 2026
This appears to be fixed on
mainby #26220, merged as 9eeb66f. That PR linked #23819, but it also prevents nullableUNIQUEconstraints from incorrectly expandingGROUP BY.I checked the original reproducer with a local
v57.0.0-devCLI:It now correctly fails during planning because
yis neither grouped nor aggregated, instead of silently addingyto the grouping keys and returning three rows. The PR also adds regression coverage for this case infunctional_dependencies.slt.Could we close this issue as resolved?
alamb commentedon Nov 8, 2026
Thank you @Phoenix500526