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]