Repository navigation
Incorrect timestamp comparison with mixed time units #22756
Description
Describe the bug
When comparing two Timestamp values with different TimeUnits, DataFusion coerces both sides to the coarser time unit. This may result in a loss of precision and lead to incorrect comparison semantics.
And other timestamp operations, such as arithmetic operations and type-unification contexts like UNION, also choose the coarser time unit.
To Reproduce
- compare ts with different time unit.
SELECT
CAST(TIMESTAMP '2024-01-01 00:00:00' AS TIMESTAMP(0)) =
CAST(TIMESTAMP '2024-01-01 00:00:00.123' AS TIMESTAMP(3)) AS eq;Expected:
+---------------------------------------------------------------+
| Utf8("2024-01-01 00:00:00") = Utf8("2024-01-01 00:00:00.123") |
+---------------------------------------------------------------+
| false |
+---------------------------------------------------------------+
Actual:
+---------------------------------------------------------------+
| Utf8("2024-01-01 00:00:00") = Utf8("2024-01-01 00:00:00.123") |
+---------------------------------------------------------------+
| true |
+---------------------------------------------------------------+
- ts subtraction
SELECT
CAST(TIMESTAMP '2024-01-01 00:00:00.123' AS TIMESTAMP(3)) -
CAST(TIMESTAMP '2024-01-01 00:00:00' AS TIMESTAMP(0)) AS diff;Expected:
+----------+
| diff |
+----------+
| PT0.123S |
+----------+
Actual:
+------+
| diff |
+------+
| P0D |
+------+
- ts union
SELECT CAST(TIMESTAMP '2024-01-01 00:00:00' AS TIMESTAMP(0))
UNION ALL
SELECT CAST(TIMESTAMP '2024-01-01 00:00:00.123' AS TIMESTAMP(3));Expected:
+-----------------------------+
| Utf8("2024-01-01 00:00:00") |
+-----------------------------+
| 2024-01-01T00:00:00.123 |
| 2024-01-01T00:00:00 |
+-----------------------------+
Actual:
+-----------------------------+
| Utf8("2024-01-01 00:00:00") |
+-----------------------------+
| 2024-01-01T00:00:00 |
| 2024-01-01T00:00:00 |
+-----------------------------+
Expected behavior
No response
Additional context
I also ran a few similar queries in ClickHouse. It appears that ClickHouse
prefers the finer precision.
Activity
mkleen commentedon Jun 4, 2026
take
fengys1996 commentedon Jun 4, 2026
I've already submitted a PR for this issue. Feel free to take a look.
fix: preserve timestamp precision when coercing mixed time units (apa…