Toby1009 opened a new issue, #25506:
URL: https://github.com/apache/datafusion/issues/25506
### Describe the bug
Casting a timestamp to `TIME` discards the date and is not order-preserving
across midnight. However, DataFusion can treat this cast as order-preserving
and remove a required sort, returning rows that do not satisfy the requested
`ORDER BY`.
### To Reproduce
```sql
SELECT ts, CAST(ts AS TIME) AS time_of_day
FROM (
SELECT column1 AS ts
FROM (
VALUES
(TIMESTAMP '1970-01-01 23:59:59'),
(TIMESTAMP '1970-01-02 00:00:00'),
(TIMESTAMP '1970-01-02 00:00:01')
)
ORDER BY ts
LIMIT 3
)
ORDER BY time_of_day;
```
DataFusion returns:
```text
+---------------------+-------------+
| ts | time_of_day |
+---------------------+-------------+
| 1970-01-01T23:59:59 | 23:59:59 |
| 1970-01-02T00:00:00 | 00:00:00 |
| 1970-01-02T00:00:01 | 00:00:01 |
+---------------------+-------------+
```
The `time_of_day` values are not ascending despite `ORDER BY time_of_day`.
Prefixing the query with `EXPLAIN` shows this physical plan:
```text
ProjectionExec: expr=[column1@0 as ts, CAST(column1@0 AS Time64(ns)) as
time_of_day]
SortExec: TopK(fetch=3), expr=[column1@0 ASC NULLS LAST],
preserve_partitioning=[false]
DataSourceExec: partitions=1, partition_sizes=[1]
```
Only the inner sort on the timestamp remains; there is no sort on the
resulting time of day.
### Expected behavior
The result should be ordered by the cast time value:
```text
+---------------------+-------------+
| ts | time_of_day |
+---------------------+-------------+
| 1970-01-02T00:00:00 | 00:00:00 |
| 1970-01-02T00:00:01 | 00:00:01 |
| 1970-01-01T23:59:59 | 23:59:59 |
+---------------------+-------------+
```
Timestamp-to-time casts should only propagate ordering when it is guaranteed
to be preserved.
### Additional context
Found while investigating #25465.
--
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]
