Ramin Gharib created FLINK-41281:
------------------------------------
Summary: Allow CAST from a VARIANT timestamp to the other temporal
types
Key: FLINK-41281
URL: https://issues.apache.org/jira/browse/FLINK-41281
Project: Flink
Issue Type: Improvement
Components: Table SQL / API
Reporter: Ramin Gharib
Fix For: 2.5.0
h3. Problem
A cast from {{VARIANT}} accepts a timestamp only for its own type. A variant
{{TIMESTAMP_LTZ}} casts to {{TIMESTAMP_LTZ(p)}} and fails for every other
temporal target. With the session time zone {{Europe/Berlin}} and
{{v_timestamp_ltz = CAST(TO_TIMESTAMP_LTZ(1704151800123, 3) AS VARIANT)}}, the
instant {{2024-01-01 23:30:00.123Z}}:
||Query||Today||Proposed||
|{{CAST(v_timestamp_ltz AS TIMESTAMP(3))}}|fails with {{Cannot cast a VARIANT
TIMESTAMP_LTZ value to TIMESTAMP(3). A VARIANT cast does not change the type of
the stored value, so cast it to its own type first and then convert with a
regular cast.}}|{{2024-01-02 00:30:00.123}}|
|{{CAST(v_timestamp_ltz AS DATE)}}|fails with {{VariantTypeException: Expected
type DATE but got TIMESTAMP_LTZ}}|{{2024-01-02}}|
|{{CAST(v_timestamp_ltz AS TIME(3))}}|fails with {{Cannot cast a VARIANT
TIMESTAMP_LTZ value to TIME(3). ...}}|{{00:30:00.123}}|
Today a user has to know the stored kind and nest two casts, such as
{{CAST(CAST(v AS TIMESTAMP_LTZ(3)) AS DATE)}}. A column that mixes
{{TIMESTAMP}} and {{TIMESTAMP_LTZ}} values needs a chain of {{TRY_CAST}} calls.
h3. Proposal
A stored {{TIMESTAMP}} or {{TIMESTAMP_LTZ}}, including the nanosecond kinds,
casts to the other temporal types the same way a regular cast from that type
does, using the session time zone.
||Stored kind||Today||Adds||
|{{TIMESTAMP}}, {{TIMESTAMP_NS}}|{{TIMESTAMP(p)}}|{{TIMESTAMP_LTZ(p)}},
{{DATE}}, {{TIME(p)}}|
|{{TIMESTAMP_LTZ}}, {{TIMESTAMP_LTZ_NS}}|{{TIMESTAMP_LTZ(p)}}|{{TIMESTAMP(p)}},
{{DATE}}, {{TIME(p)}}|
|{{DATE}}|{{DATE}}|nothing|
|{{TIME}}|{{TIME(p)}}|nothing|
* {{DATE}} and {{TIME}} stay own-type only. Widening them would add something
the value never had: a time of day, a date, or a zone offset. The regular
{{TIME}} to {{TIMESTAMP}} cast shows the problem, since it fills in 1970-01-01
(FLINK-24423).
* A {{DATE}} target that gets any other kind fails with the standard VARIANT
cast message instead of the raw {{VariantTypeException}}.
* {{VARIANT_GET}} and {{TRY_VARIANT_GET}} follow the same rules.
* Values are read through {{java.time}}, not the millisecond helpers of the
regular casts, which are wrong before 1970 (see the linked bug).
* The VARIANT section of {{data-types.md}} is updated.
Trade-off: casts to {{DATE}} and {{TIME}} drop part of the timestamp, while the
numeric rule rejects {{7.2}} to {{INT}}. Cutting off fractional seconds beyond
the target precision is already allowed, and Spark, Snowflake and Trino apply
their regular cast rules here.
h3. Tests
* The queries above, for {{TIMESTAMP}} and {{TIMESTAMP_LTZ}} with the micro and
nanosecond kinds, in two session time zones.
* A value before 1970 to {{DATE}} and {{TIME}}.
* Stored {{DATE}} and {{TIME}} still fail for the other targets, and
{{TRY_CAST}} returns NULL.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
