asolimando opened a new issue, #27619:
URL: https://github.com/apache/datafusion/issues/27619
### Describe the bug
A query on a table with a UNIQUE constraint on a nullable column and a
declared sort order (`WITH ORDER`) returns rows in the wrong order: the plan
has no `SortExec`, although the input is not sorted on the requested columns.
SQL `UNIQUE` allows several rows with NULL in a key column, so such rows can
tie on all the columns of the declared sort order. Only the remaining sort
columns can then order them.
### To Reproduce
```sql
SET datafusion.execution.target_partitions = 1;
COPY (VALUES (1, NULL, 0, 2), (1, NULL, 0, 1))
TO 'unique_nullable.csv' STORED AS CSV OPTIONS ('format.has_header' 'false');
CREATE EXTERNAL TABLE unique_nullable (a INT NOT NULL, b INT, c INT NOT
NULL, v INT NOT NULL, UNIQUE (a, b))
STORED AS CSV WITH ORDER (a ASC, b ASC, c ASC)
OPTIONS ('format.has_header' 'false')
LOCATION 'unique_nullable.csv';
SELECT a, b, c, v FROM unique_nullable ORDER BY a, b, c, v;
```
Observed (`v` is not sorted, the rows tie on `a, b, c`):
```
+---+------+---+---+
| a | b | c | v |
+---+------+---+---+
| 1 | NULL | 0 | 2 |
| 1 | NULL | 0 | 1 |
+---+------+---+---+
```
The physical plan has no `SortExec`, only the scan:
```
DataSourceExec: ..., projection=[a, b, c, v], output_ordering=[a@0 ASC NULLS
LAST, b@1 ASC NULLS LAST, c@2 ASC NULLS LAST], constraints=[Unique([0, 1])], ...
```
### Expected behavior
Rows sorted by `a, b, c`, then `v` (`v` is 1, then 2), with a `SortExec`
above the scan.
Without `UNIQUE (a, b)`, the result is correct and the `SortExec` stays.
### Additional context
Reproduced on `main` (`c6c1cc31d`). Reported by @shihchengwei-lab in
https://github.com/apache/datafusion/pull/27597#issuecomment-7036916326: #27597
makes this bug reachable from more projections, so the fix for this issue
should merge first.
--
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]