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 back
The 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:
Is your feature request related to a problem or challenge?
Follow-up to #23634, #23818 and #23819: another case where a nullable
UNIQUEconstraint is treated as a key.A column that is not in the
GROUP BYmay still be selected when the grouping columns determine it. That holds for aPRIMARY KEY, but not for aUNIQUEcolumn, which permits multipleNULLrows and therefore does not determine anything across them.GROUP BY xputs allNULLs in a single group, so it can return at most one row forx = NULL. This query returns two:The plan shows
yhas been added to theGROUP BY, which is what splits theNULLgroup in two:Other engines reject the query outright rather than resolving
y.postgres 14 rejects it for a nullable
UNIQUEcolumn, while accepting the same query whenxis aPRIMARY KEY:duckdb rejects both forms:
DataFusion accepts the query in both forms; the
PRIMARY KEYform gives the right answer.Describe the solution you'd like
Fix the bug.
Describe alternatives you've considered
Additional context
Related: