[FLINK-11935] Remove DateTimeUtils pull-in and fix datetime casting problem

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.10.0
• Component/s: Table SQL / API
• Labels: pull-request-available
• Assignee: Zhenghua Gao
• Reporter: Rong Rong
• Created: 15/Mar/19 17:21
• Updated: 20/Nov/19 03:44
• Resolved: 20/Nov/19 03:44

Description

This DateTimeUtils was pulled in in FLINK-7235.

Originally the time operation was not correctly done via the ymdToJulian function before the date 1970-01-01 thus we need the fix. similar to addressing this problem:

 Optimized :1017-12-05 22:58:58.998 
 Expected :1017-11-29 22:58:58.998
 Actual :1017-12-05 22:58:58.998

 

However, after pulling in avatica 1.13, I found out that the optimized plans of the time operations are actually correct. it is in fact the casting part that creates problem:

For example, the following:
(plus(-12000.months, cast('2017-11-29 22:58:58.998', TIMESTAMP))

result in a StringTestExpression of:
CAST(1017-11-29 22:58:58.998):VARCHAR(65536) CHARACTER SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary" NOT NULL

but the testing results are:

 Optimized :1017-11-29 22:58:58.998
 Expected :1017-11-29 22:58:58.998
 Actual :1017-11-23 22:58:58.998

 

Issue Links

• is caused by: CALCITE-2989 Use ISO-8601 calendar when converting between java.sql types and UNIX timestamps
• is duplicated by: FLINK-7237 Remove DateTimeUtils from Flink once Calcite is upgraded to 1.14
• is related to: FLINK-14805 Remove unixDateCeil related code
• is related to: FLINK-13185 Bump Calcite dependency to 1.20.0 in sql parser & flink planner
• links to: GitHub Pull Request #10208