viirya opened a new issue, #25690:
URL: https://github.com/apache/datafusion/issues/25690
### Describe the bug
`date_bin` can map an individual coarse-precision timestamp to `NULL` when
scaling the value to nanoseconds overflows. However, its physical expression
properties still claim that the function preserves the source ordering. The
optimizer can therefore remove a required final sort and return `NULL` values
in the wrong position.
This became visible through `date_trunc` after typed `date_bin` bounds were
added in #25575, but the invalid property also affects direct consumers of
`date_bin` ordering.
### To Reproduce
```sql
SELECT date_trunc('second', date_bin(INTERVAL '1 second', ts)) AS b
FROM (
SELECT arrow_cast(column1, 'Timestamp(Second)') AS ts
FROM (VALUES
(-10000000000::bigint),
(0::bigint),
(10000000000::bigint)
)
ORDER BY ts
LIMIT 3
)
ORDER BY b ASC NULLS LAST;
```
On current `main`, the physical plan omits the final sort and returns:
```text
NULL
1970-01-01T00:00:00
NULL
```
### Expected behavior
The result must honor `ASC NULLS LAST`:
```text
1970-01-01T00:00:00
NULL
NULL
```
### Additional context
The affected source representations are `Timestamp(Second)`,
`Timestamp(Millisecond)`, `Timestamp(Microsecond)`, and `Time64(Microsecond)`,
whose conversion to nanoseconds can overflow. Ordering propagation should
remain available for representations that do not require an overflowing scale,
because it is required by sorted unbounded execution paths.
--
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]
