Repository navigation
ORDER BY CAST(ts AS TIME) can return incorrectly ordered results across midnight #25506
Description
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
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:
+---------------------+-------------+
| 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:
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:
+---------------------+-------------+
| 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.
Metadata
Metadata
Assignees
Labels
No labels
Activity
Toby1009 commentedon Sep 19, 2026
take
fix: preserve sorts for non-monotonic temporal casts (apache#25526)