Stamatis Zampetakis created HIVE-27156: ------------------------------------------
Summary: Wrong results when CAST timestamp literal with timezone to TIMESTAMP Key: HIVE-27156 URL: https://issues.apache.org/jira/browse/HIVE-27156 Project: Hive Issue Type: Bug Components: HiveServer2 Affects Versions: 4.0.0-alpha-2 Reporter: Stamatis Zampetakis Assignee: Stamatis Zampetakis Casting a timestamp literal with an invalid timezone to the TIMESTAMP datatype results into a timestamp with the time part truncated to midnight (00:00:00). *Case I* {code:sql} select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp); {code} +Actual+ |2020-06-28 00:00:00| +Expected+ |NULL/ERROR/2020-06-28 22:17:33.123456| *Case II* {code:sql} select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as timestamp); {code} +Actual+ |2020-06-28 00:00:00| +Expected+ |NULL/ERROR/2020-06-28 22:17:33.123456| The existing documentation does not cover what should be the output in the cases above: * https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-TimestampstimestampTimestamps * https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types *Case III* Another subtle but important case is the following where the timestamp literal has a valid timezone but we are attempting a cast to a datatype that does not store the timezone. {code:sql} select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp); {code} +Actual+ |2020-06-28 22:17:33.123456| The correctness of the last result is debatable since someone would expect a NULL or ERROR. -- This message was sent by Atlassian Jira (v8.20.10#820010)