asolimando opened a new issue, #27596:
URL: https://github.com/apache/datafusion/issues/27596

   ### Describe the bug
   
   A query on a table with a PRIMARY KEY 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. This happens when the scan 
reads only some of the table columns.
   
   ### To Reproduce
   
   ```sql
   SET datafusion.execution.target_partitions = 1;
   
   COPY (VALUES (0, 3, 0, 10, 1), (0, 1, 0, 11, 1), (0, 2, 0, 12, 1), (0, 9, 0, 
13, 2), (0, 5, 0, 14, 2))
   TO 'pk_dropped_columns.csv' STORED AS CSV OPTIONS ('format.has_header' 
'false');
   
   CREATE EXTERNAL TABLE pk_dropped_columns (a0 INT, a INT, b INT, c INT, d 
INT, PRIMARY KEY (c))
   STORED AS CSV WITH ORDER (d ASC)
   OPTIONS ('format.has_header' 'false')
   LOCATION 'pk_dropped_columns.csv';
   
   SELECT a, b, c, d FROM pk_dropped_columns ORDER BY d, a;
   ```
   
   Observed (`a` is not sorted within each `d` value):
   
   ```
   +---+---+----+---+
   | a | b | c  | d |
   +---+---+----+---+
   | 3 | 0 | 10 | 1 |
   | 1 | 0 | 11 | 1 |
   | 2 | 0 | 12 | 1 |
   | 9 | 0 | 13 | 2 |
   | 5 | 0 | 14 | 2 |
   +---+---+----+---+
   ```
   
   The physical plan has no `SortExec`, only the scan:
   
   ```
   DataSourceExec: ..., projection=[a, b, c, d], output_ordering=[d@3 ASC NULLS 
LAST], ...
   ```
   
   ### Expected behavior
   
   Rows sorted by `d`, then `a` (`a` is 1, 2, 3 for `d = 1`, then 5, 9 for `d = 
2`), with `SortExec: expr=[d@3 ASC NULLS LAST, a@0 ASC NULLS LAST]` above the 
scan.
   
   Without `PRIMARY KEY (c)`, the result is correct and the `SortExec` stays.
   
   ### Additional context
   
   Reproduced on `main` (`c6c1cc31d`); 55.2.0 and `branch-56` have the same 
code. From SQL, the bug needs `CREATE EXTERNAL TABLE` with both a key and `WITH 
ORDER`; any `TableProvider` that reports constraints and an output ordering is 
also affected.
   


-- 
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