Incorrect results: GROUP BY returns two rows for the NULL group with a nullable UNIQUE constraint
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
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 `NULL`s in a single group, so it can return at most one row for `x = NULL`. This query returns two:
```sql
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:
```sql
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:
- #23634
- #23818
- #23819
- #23548
- #23636
Contributor guide
Assessment
This issue has not been assessed yet.