alamb opened a new issue, #23820:
URL: https://github.com/apache/datafusion/issues/23820
### 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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
