`ORDER BY CAST(ts AS TIME)` can return incorrectly ordered results across midnight

[Toby1009]

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.

[Toby1009]

take