Incorrect results: GROUP BY column dropped based on 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 and #23818: the same nullable-`UNIQUE` confusion also affects `GROUP BY`.
A grouping column is dropped when the remaining grouping columns already determine it. That is valid for a `PRIMARY KEY`, but not for a `UNIQUE` column, which permits multiple `NULL` rows and therefore does not determine anything across them. Dropping it merges groups that should stay separate, so rows go missing from the result.
```sql
CREATE TABLE t (x INT UNIQUE, y INT) AS VALUES (NULL, 2), (NULL, 1), (1, 3);
SELECT x FROM t GROUP BY x, y;
-- returns 2 rows:
-- 1
-- NULL
--
-- should return 3 rows (the two NULL rows differ in `y`):
-- 1
-- NULL
-- NULL
```
The plan shows `y` is gone from the GROUP BY entirely:
```sql
EXPLAIN SELECT x FROM t GROUP BY x, y;
-- logical_plan
-- 01)Aggregate: groupBy=[[t.x]], aggr=[[]] <-- `y` dropped
-- 02)--TableScan: t projection=[x]
-- physical_plan
-- 01)AggregateExec: mode=FinalPartitioned, gby=[x@0 as x], aggr=[]
-- 02)--RepartitionExec: partitioning=Hash([x@0], 16), input_partitions=1
-- 03)----AggregateExec: mode=Partial, gby=[x@0 as x], aggr=[]
-- 04)------DataSourceExec: partitions=1, partition_sizes=[1]
```
Verified against duckdb:
```
❯ duckdb -c "create table t (x int unique, y int); insert into t values (null,2),(null,1),(1,3); select x from t group by x, y;"
┌───────┐
│ x │
│ int32 │
├───────┤
│ NULL │
│ NULL │
│ 1 │
└───────┘
```
Note that `y` has to be absent from the select list to trigger this — `SELECT x, y FROM t GROUP BY x, y` returns the correct three rows.
### Describe the solution you'd like
Fix the bug.
### Describe alternatives you've considered
### Additional context
Related:
- #23634
- #23818
- #23548
- #23636
Contributor guide
Assessment
This issue has not been assessed yet.