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]

Reply via email to