[ https://issues.apache.org/jira/browse/HIVE-27156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17702790#comment-17702790 ]
Stamatis Zampetakis commented on HIVE-27156: -------------------------------------------- I did some small experiments in few other DBMS and here are the results. Note that the syntax is not entirely identical but I tried to find the most reasonable alternatives. *postgres:12* {noformat} postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp); timestamp ---------------------------- 2020-06-28 22:17:33.123456 (1 row) postgres=# select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp); ERROR: time zone "europe/amsterd" not recognized LINE 1: select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as t... ^ postgres=# select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as timestamp); ERROR: time zone "invalid/zone" not recognized LINE 1: select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as tim... ^ {noformat} *mysql:8.0.32* {noformat} mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6)); +--------------------------------------------------------------------+ | cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as datetime(6)) | +--------------------------------------------------------------------+ | 2020-06-28 22:17:33.123456 | +--------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6)); +------------------------------------------------------------------+ | cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as datetime(6)) | +------------------------------------------------------------------+ | 2020-06-28 22:17:33.123456 | +------------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6)); +----------------------------------------------------------------+ | cast('2020-06-28 22:17:33.123456 Invalid/Zone' as datetime(6)) | +----------------------------------------------------------------+ | 2020-06-28 22:17:33.123456 | +----------------------------------------------------------------+ 1 row in set, 2 warnings (0.00 sec) {noformat} *oracle:12.2.0.1-slim* {noformat} SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI.SS.FF'; Session altered. SQL> select cast('2020-06-28 22:17:33.123456' as timestamp) from dual; CAST('2020-06-2822:17:33.123456'ASTIMESTAMP) --------------------------------------------------------------------------- 2020-06-28 22:17.33.123456 SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual; select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp) from dual * ERROR at line 1: ORA-01830: date format picture ends before converting entire input string SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI.SS.FF TZR'; Session altered. SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterdam' as timestamp with time zone) from dual; CAST('2020-06-2822:17:33.123456EUROPE/AMSTERDAM'ASTIMESTAMPWITHTIMEZONE) --------------------------------------------------------------------------- 2020-06-28 22:17.33.123456 EUROPE/AMSTERDAM SQL> select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual; select cast('2020-06-28 22:17:33.123456 Europe/Amsterd' as timestamp with time zone) from dual * ERROR at line 1: ORA-01882: timezone region not found {noformat} Summing up: ||String literal||postgres:12||mysql:8.0.32||oracle:12.2.0.1-slim||Hive:4.0.0-alpha2|| |2020-06-28 22:17:33.123456 Invalid/Zone|ERROR|2020-06-28 22:17:33.123456|ERROR|2020-06-28 00:00:00| |2020-06-28 22:17:33.123456 Europe/Amsterdam|2020-06-28 22:17:33.123456|2020-06-28 22:17:33.123456|ERROR|2020-06-28 22:17:33.123456| > 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 > Priority: Major > > 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)