alamb opened a new issue, #23819: URL: https://github.com/apache/datafusion/issues/23819
### 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 -- 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]
