Incorrect results with unique constraints

[alamb]

Is your feature request related to a problem or challenge?

Follow-up to the discussion on #23548 (see #23548 (comment)).

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

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:

(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

• fix: do not remove DISTINCT based on a nullable UNIQUE constraint simonvandel/arrow-datafusion#1

Describe the solution you'd like

Fix the bug

Describe alternatives you've considered

Additional context

Related:

• fix: do not remove DISTINCT when a unique key was downgraded by a join #23548
• fix: optimize_projections failure with struct-field join keys #22903
• SELECT DISTINCT returns duplicate rows when a unique key is downgraded by a join #23626