Skip to content

SQL lowering: folding CAST(<integer> AS TIMESTAMP) reads seconds as milliseconds #1265

Open

Description

@zzylol

Problem, by example

SELECT count(*) FROM metrics WHERE ts >= CAST(1700000000 AS TIMESTAMP)
Bound on ts
DataFusion (and PostgreSQL / DuckDB) 1,700,000,000 seconds = 2023-11-14 22:13:20 UTC
Planner today 1,700,000,000 milliseconds = 1970-01-20 16:13:20 UTC

The planner's filter keeps almost every row, where DataFusion keeps only rows after November 2023. Derived from the code at 73785fa (head of #1230) and DataFusion 54.1's SQL planner, not from a run.

Why it happens

DataFusion's SQL planner (datafusion-sql 54.1, sql_cast_to_expr in src/expr/mod.rs) reads an integer cast to TIMESTAMP as seconds, "to align with postgres / duckdb semantics": it lowers CAST(1700000000 AS TIMESTAMP) to CAST(CAST(1700000000 AS Timestamp(Second)) AS Timestamp(Nanosecond)).

The SQL frontend walks the unoptimized plan and folds a cast of a literal itself (crates/frontend-sql/src/sql/expr.rs L142-L167). For any Timestamp(..) target it ignores the target's unit and casts to dtype_to_arrow(&DataType::Timestamp), which is Timestamp(Millisecond) (crates/frontend-sql/src/sql/types.rs L207). So the inner cast CAST(1700000000 AS Timestamp(Second)) becomes Int64(1700000000).cast_to(Timestamp(Millisecond)): Arrow reinterprets the integer in the target unit, so the value becomes 1,700,000,000 ms. scalar_value_to_asap (types.rs L56-L62) then stores it as ScalarValue::Timestamp(1_700_000_000) (ms).

The same fold also loses sub-millisecond precision: CAST('2026-11-01 00:00:00.0005' AS TIMESTAMP) targets Timestamp(Nanosecond) but is folded to milliseconds. This does not change results when the literal is compared with a table column: every table's timestamp column is registered as Timestamp(Millisecond), and DataFusion coerces a comparison of two timestamp units to the coarser one (timeunit_coercion in datafusion-expr-common 54.1), which truncates to milliseconds too. It can differ only where the comparison stays at a finer unit, e.g. against now() (Timestamp(Nanosecond)).

Proposal

Fold a cast of a literal to a timestamp only when the result is exact in milliseconds:

  1. Cast the literal to the cast's own target type (c.field.data_type(), unit included), as DataFusion does.
  2. Convert that value to milliseconds (scalar_value_to_asap); fold only when the conversion is exact (no sub-millisecond remainder, no overflow).
  3. Otherwise keep the Cast, as the Err branch already does.

The nested cast then folds correctly bottom-up once the inner cast folds to TimestampSecond(1700000000) and the outer one converts seconds to milliseconds exactly.

Alternative: run DataFusion's SimplifyExpressions before lowering (#650), which folds these casts with DataFusion's own semantics; the frontend fold would then only see already-folded literals.

Scope and acceptance

In scope: the cast-of-literal fold in expr.rs. Out of scope: the general constant folding of #650.

Acceptance (fail today):

  • ts >= CAST(1700000000 AS TIMESTAMP) lowers to ts >= Timestamp(1_700_000_000_000).
  • ts >= CAST('2026-11-01 00:00:00.0005' AS TIMESTAMP) lowers either to a kept Cast or to a literal whose value equals DataFusion's own result for the same comparison; ts >= CAST('2026-11-01 00:00:00.001' AS TIMESTAMP) still folds.
  • The existing fold tests (e.g. ts >= '2026-11-01' and ts >= TIMESTAMP '2026-11-01 00:00:00' in crates/frontend-sql/tests/sql_lowering.rs L3213-L3214) keep passing.

Status and related work

Found by a semantic review of #1230 (PR 2 of #1207). Decided 2026-11-10: #1230 keeps only the core data-structure, interface and definition changes; case fixes go to follow-up issues and later PRs. Related: #650 (run SimplifyExpressions in SQL lowering).

Activity

  1. added
    bugSomething isn't working
    parserExternal or internal query parser behavior
    sqlSQL front-end lowering (DataFusion → L2)
    on Nov 10, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingparserExternal or internal query parser behaviorsqlSQL front-end lowering (DataFusion → L2)

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions