Datetime output cast format diverges between Calcite and DataFusion engines

[ahkcs]

Description

The DatetimeOutputCastRule introduced in #5408 wraps datetime
output columns in CAST(... AS VARCHAR) so PPL responses are
rendered as ISO strings. The string produced by that cast is
engine-dependent:

• PPL Calcite path (UnifiedQueryCompiler) — emits ANSI SQL
  format 2024-01-15 12:00:00 (space separator, no T),
  consistent with SparkSQL, PostgreSQL, MySQL, Oracle, and
  SQL Server.
• Analytics-engine path (DataFusion native runtime) — emits
  ISO 8601 format 2024-01-15T12:00:00 (with T separator).

This means the same PPL query against the same data returns
two different string formats depending on which execution
engine handled it, breaking the wire-format contract that
#5408 set out to enforce.

Reproduction

source=events | fields created_at

• Calcite engine → "2024-01-15 12:00:00"
• DataFusion engine → "2024-01-15T12:00:00"

Context

Called out as Note 2 of #5408. Filed as a follow-up so the
divergence is tracked outside that already-merged PR.

Possible directions

1. Replace the implicit CAST(... AS VARCHAR) with an explicit
   DATE_FORMAT(..., '<pattern>') (or to_char /
   format_datetime) that pins the wire format on both engines.
2. Push the formatting into the response formatter rather than
   the logical plan, so engine-side cast semantics never leak
   to the wire.
3. Add a DataFusion-side cast adapter that emits the ANSI SQL
   string format to match Calcite.

Related

• Normalize datetime types for unified query API #5408 — introduced DatetimeOutputCastRule
• [BUG] Unified query API fails with date types #5250 — parent issue tracking unified-query datetime work

[ahkcs]

Clarification on scope — this applies to TIMESTAMP (and likely TIMESTAMP_WITH_LOCAL_TIME_ZONE), not pure DATE / TIME:

Calcite type	Calcite (ANSI SQL) cast string	DataFusion (ISO 8601) cast string	Diverges?
DATE	2024-01-15	2024-01-15	no
TIME	12:00:00	12:00:00	no
TIMESTAMP	2024-01-15 12:00:00	2024-01-15T12:00:00	yes (separator)
TIMESTAMP_WITH_LOCAL_TIME_ZONE	2024-01-15 12:00:00 UTC (Calcite varies)	2024-01-15T12:00:00Z	yes (separator + zone marker)

DatetimeOutputCastRule wraps all three (EXPR_DATE/TIME/TIMESTAMP) in CAST(... AS VARCHAR), but only the timestamp cases produce engine-divergent strings. The fix needs to pin the format for the timestamp variants specifically; DATE/TIME can keep the implicit cast.

[dai-chen]

Will investigate further. Before that could you check if datafusion.format.timestamp_format works for this or not? Ref: https://datafusion.apache.org/user-guide/configs.html

[dai-chen]

I quick verified that datafusion.format.timestamp_format does not work for this case. This config seems only affect CLI display formatting, not the Arrow cast kernel used by CAST(timestamp AS VARCHAR). The output is still ISO 8601 with T separator (e.g. 2024-01-15T09:50:00). In this case, we need to explore other options, such as rewriting CAST(ts AS VARCHAR) to DataFusion-specific to_char(ts, '%Y-%m-%d %H:%M:%S') in the Substrait conversion layer.

The reason not to do this in UnifiedQueryPlanner is that there seems to be no standard time format function across engines. CAST is currently engine-agnostic and works for most databases which generate output following PPL spec. If so, the rewrite to to_char() should happen at the engine-specific layer (DataFusion Substrait conversion) rather than polluting the unified planning layer.