apache / apache/datafusion

Incorrect results: GROUP BY column dropped based on a nullable UNIQUE constraint

Open
#23,819 1 comment 0 reactions 1 assignee Claimed by @buraksenn View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.