apache / apache/datafusion

Incorrect results with unique constraints

Open
#23,634 0 comments 0 reactions 1 assignee Claimed by @alamb 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 the discussion on #23548 (see https://github.com/apache/datafusion/pull/23548#issuecomment-4990958946).

This query gets the wrong answer because a GroupBy is incorrectly removed

```sql
CREATE TABLE t_uniq (x INT UNIQUE) AS VALUES (NULL), (NULL), (1);
SELECT DISTINCT x FROM t_uniq ORDER BY x NULLS LAST;
-- returns: NULL, NULL, 1 (should be: 1, NULL)
```

Which I verified via duckdb:

```sql
(venv) andrewlamb@Andrews-MacBook-Pro-3:~/Software/datafusion$ duckdb
DuckDB v1.5.4 (Variegata)
Enter ".help" for usage hints.
memory D create table t (x int unique);
memory D insert into t values (null);
memory D insert into t values (null);
memory D insert into t values (1);
memory D select * from t;
┌───────┐
│ x │
│ int32 │
├───────┤
│ NULL │
│ NULL │
│ 1 │
└───────┘
memory D select distinct x from t;
┌───────┐
│ x │
│ int32 │
├───────┤
│ 1 │
│ NULL │
└───────┘
memory D select distinct x from t order by x nulls last;
┌───────┐
│ x │
│ int32 │
├───────┤
│ 1 │
│ NULL │
└───────┘
```

Here is a proposed fix targeting this branch for your review
- https://github.com/simonvandel/arrow-datafusion/pull/1

### Describe the solution you'd like
Fix the bug

### Describe alternatives you've considered

### Additional context

Related:
- #23548
- #22903
- #23626

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.