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)
