[jira] [Created] (HIVE-27156) Wrong results when CAST timestamp literal with timezone to TIMESTAMP

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

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)