THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).
Table of Contents
Motivation
Currently some temporal function behaviors are weird to users.
...
As we knew some functions' behavior is wrong currently, but after we correct these function, the we plan to drop legacy behavior should still work in old code.
This FLIP introduce an option for compatibility consideration, given two option names, I prefer the first one, the second one is like Impala style[1].
table.exec.fallback-legacy-time-function = false/true
table.exec.use-utc-for-unixtime-conversion = false/true
Proposed Changes
1. Correct the return value of time functions
immediately.
Proposed Changes
1. Correct the return value of time functions
I invested all Flink time-related functions current behavior I invested all Flink time-related functions current behavior and compared with other DB vendors like Pg,Presto, Hive, Spark, Snowflake, I made an excel [2] to organize them well.
...
function | current behavior | existed problem | migration plan |
CAST(44 AS TIMESTAMP) | TIMESTAMP(0) NOT NULL #session timezone: UTC 1970-01-01 00:00:44 #session timezone: UTC+8 1970-01-01 00:00:44 | The time in BIGINT type usually represents a unixtime semantic, which represents the elapsed time since java epoch(1970-01-01 00:00:00 UTC+0), when convert to a timestamp we should consider local time zone | This is an invalid behavior, disable the invalid CAST behavior, to get same behavior, user can workaround with: #session timezone: UTC
1970-01-01 00:00:44 #session timezone: UTC+8 TO_TIMESTAMP(FROM_UNIXTIME(44 - 8 * 60 * 60)) 1970-01-01 00:00:44 |
CAST(TIMESTAMP ‘1970-01-01 00:00:44’ AS BIGINT) | BIGINT NOT NULL #session timezone: UTC 44 #session timezone: UTC+8 44 | The inverse conversion of above, this conversion is used rarely. | UNIX_TIMESTAMP(TIMESTAMP ‘1970-01-01 00:00:44’) #session timezone: UTC 44 #session timezone: UTC+8 -28756 |
3.
...
Disable legacy behavior of theses time functions
The return values of functions
...
The default value of table.exec.fallback-legacy-time-function is 'false' which means:
(1) the return values of function
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- NOW()
- PROCTIME()
consider consider the local timezone and the return type of PROCTIME()/NOW()/CURRENT_TIMESTAMP is TIMESTAMP WITH LOCAL TIME ZONE_LTZ, the return type of CURRENT_DATE is DATE, the return type of CURRENT_TIME is TIME.
(2) The cast between NUMERIC and TIMESTAMP is forbidden
Users can set the option to 'true', at this moment:
(1) These functions would keep the legacy behavior.
(2) The cast between NUMERIC and TIMESTAMP is supportedThe legacy behavior of these function doesn't consider the local time zone, and the return type of PROCTIME()/NOW()/CURRENT_TIMESTAMP is TIMESTAMP, the return type of CURRENT_DATE is DATE, the return type of CURRENT_TIME is TIME.
4. Support defining row time attribute on TIMESTAMP WITH LOCAL TIME ZONE
...
proposed changes | note |
Support function TO_TIMESTAMP_LTZ(numeric_expr, [,scale]) return type: TIMESTAMP(3) WITH LOCAL TIME ZONE #session timezone: UTC TO_TIMESTAMP_LTZ(44) 1970-01-01 00:00:44 #session timezone: UTC+8 TO_TIMESTAMP_LTZ(-28756) 1970-01-01 00:00:44 | TO_TIMESTAMP_LTZ(numeric_expr [,scale]) TO_TIMESTAMP_LTZ(seconds, 0) TO_TIMESTAMP_LTZ(milliseconds, 3) |
5. Support more conversion classes for LocalZonedTimestampType(TIMESTAMP
...
_LTZ)
After we use type TIMESTAMP WITH LOCAL TIME ZONE in above functions, we can support all conversion classes like java.time.LocalDateTime, java.sql.Timestamp that TimestampType supported for LocalZonedTimestampType to resolve the UDF compatibility issue.
...
1.Change the codegen implementations for above five functions/cast conversions according to the value of introduced table option: table.exec.fallback-legacy-time-function.
22. Supports all conversion classes like java.time.LocalDateTime, java.sql.Timestamp that TimestampType supported for LocalZonedTimestampType to resolve the UDF compatibility issue
...
This is an incompatible change, we introduce SQL/API option table.exec.fallback-legacy-time-function for compacting current wrong behavior, and set it to ‘false’. If users want to keep the legacy behavior, they need to set this option value to ’true’ manually, this would be add to release note. that users must change their SQL if they used these time functions, user can also write UDF to obtain same of legacy time function return value.
Test Plan
Will add plan tests, unit tests, window operator harness tests as well as IT tests.
...