| # Licensed to the Apache Software Foundation (ASF) under one |
| # or more contributor license agreements. See the NOTICE file |
| # distributed with this work for additional information |
| # regarding copyright ownership. The ASF licenses this file |
| # to you under the Apache License, Version 2.0 (the |
| # "License"); you may not use this file except in compliance |
| # with the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, |
| # software distributed under the License is distributed on an |
| # "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| # KIND, either express or implied. See the License for the |
| # specific language governing permissions and limitations |
| # under the License. |
| |
| from tests.common.impala_test_suite import ImpalaTestSuite |
| from tests.common.test_dimensions import create_beeswax_hs2_dimension |
| |
| |
| class TestCastWithFormat(ImpalaTestSuite): |
| @classmethod |
| def get_workload(self): |
| return "functional-query" |
| |
| # Run the basic tests once for Beeaswax and once for HS2. The underlying functionality |
| # is independent of the file format so make sense to pick one format for testing. |
| @classmethod |
| def add_test_dimensions(cls): |
| super(TestCastWithFormat, cls).add_test_dimensions() |
| cls.ImpalaTestMatrix.add_constraint(lambda v: |
| v.get_value('table_format').file_format == 'parquet') |
| |
| cls.ImpalaTestMatrix.add_dimension(create_beeswax_hs2_dimension()) |
| |
| def test_basic_inputs_from_table(self, vector): |
| self.run_test_case('QueryTest/cast_format_from_table', vector) |
| |
| def test_basic_inputs_without_row(self, vector): |
| # Cast without format clause to cover the default format |
| result = self.client.execute("select cast('2017-05-01 01:23:45.678912345' as " |
| "timestamp)") |
| assert result.data == ["2017-05-01 01:23:45.678912345"] |
| |
| # Basic input to cover a datetime with timezone scenario |
| result = self.client.execute("select cast('2017-05-03 08:59:01.123456789PM 01:30'" |
| "as timestamp FORMAT 'YYYY-MM-DD HH12:MI:SS.FF9PM TZH:TZM')") |
| assert result.data == ["2017-05-03 20:59:01.123456789"] |
| |
| # Input that contains shuffled date without time |
| result = self.client.execute("select cast('12-2010-05' as timestamp format " |
| "'DD-YYYY-MM')") |
| assert result.data == ["2010-05-12 00:00:00"] |
| |
| # Shuffle the input timestamp and the format clause |
| result = self.client.execute("select cast('59 04-30-2017-05 01PM 01:08.123456789'" |
| "as timestamp FORMAT 'MI DD-TZM-YYYY-MM TZHPM SS:HH12.FF9')") |
| assert result.data == ["2017-05-04 20:59:01.123456789"] |
| |
| # Input and format without separators |
| # Note, 12:01 HH12 AM is 00:01 with the internal 0-23 representation. |
| result = self.client.execute("select cast('20170501120159123456789AM-0130' as " |
| "timestamp FORMAT 'YYYYDDMMHH12MISSFFAMTZHTZM')") |
| assert result.data == ["2017-01-05 00:01:59.123456789"] |
| |
| # Shuffled input without separators |
| result = self.client.execute("select cast('59043020170501PM0108123456789'" |
| "as timestamp FORMAT 'MIDDTZMYYYYMMTZHPMSSHH12FF9')") |
| assert result.data == ["2017-05-04 20:59:01.123456789"] |
| |
| # Separator section lengths differ between input and format |
| result = self.client.execute("select cast('--2017----05-01-' as " |
| "timestamp FORMAT '-YYYY--MM---DD---')") |
| assert result.data == ["2017-05-01 00:00:00"] |
| |
| # If the input string has unprocessed tokens |
| result = self.client.execute("select cast('2017-05-01 12:30' as " |
| "timestamp FORMAT 'YYYY-MM-DD')") |
| assert result.data == ["NULL"] |
| result = self.client.execute("select cast('2017-05-01-12:30' as " |
| "timestamp FORMAT 'YYYY-MM-DD-')") |
| assert result.data == ["NULL"] |
| |
| # If the format string has unprocessed tokens |
| result = self.client.execute("select cast('2017-05-01' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI')") |
| assert result.data == ["NULL"] |
| result = self.client.execute("select cast('2017-05-01-' as " |
| "timestamp FORMAT 'YYYY-MM-DD-HH12')") |
| assert result.data == ["NULL"] |
| |
| # Timestamp to string types formatting |
| result = self.client.execute( |
| "select cast(cast('2012-11-04 13:02:59.123456' as timestamp) " |
| "as string format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24')") |
| assert result.data == ["04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13"] |
| |
| result = self.client.execute( |
| "select cast(cast('2012-11-04 13:02:59.123456' as timestamp) " |
| "as varchar format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24')") |
| assert result.data == ["04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13"] |
| |
| result = self.client.execute( |
| "select cast(cast('2012-11-04 13:02:59.123456' as timestamp) " |
| "as char(50) format 'DD-MM-YYYY MI:HH12:SS A.M. FF9 DDD SSSSS HH12 HH24')") |
| assert result.data == ["04-11-2012 02:01:59 P.M. 123456000 309 46979 01 13"] |
| |
| # Cast NULL string to timestamp |
| result = self.client.execute("select cast(cast(NULL as string) as timestamp " |
| "FORMAT 'YYYY-MM-DD')") |
| assert result.data == ["NULL"] |
| |
| # Cast NULL timestamp to string |
| result = self.client.execute("select cast(cast(NULL as timestamp) as string " |
| "FORMAT 'YYYY-MM-DD')") |
| assert result.data == ["NULL"] |
| |
| def test_iso8601_format(self): |
| # Basic string to timestamp scenario |
| result = self.client.execute("select cast('2018-11-10T15:11:04Z' as " |
| "timestamp FORMAT 'YYYY-MM-DDTHH24:MI:SSZ')") |
| assert result.data == ["2018-11-10 15:11:04"] |
| |
| # ISO8601 format elements are case-insensitive |
| result = self.client.execute("select cast('2018-11-09t15:11:04Z' as " |
| "timestamp FORMAT 'YYYY-MM-DDTHH24:MI:SSz')") |
| assert result.data == ["2018-11-09 15:11:04"] |
| |
| result = self.client.execute("select cast('2018-11-08T15:11:04z' as " |
| "timestamp FORMAT 'YYYY-MM-DDtHH24:MI:SSZ')") |
| assert result.data == ["2018-11-08 15:11:04"] |
| |
| # Format path |
| result = self.client.execute("select cast(cast('2018-11-10 15:11:04' as " |
| "timestamp) as string format 'YYYY-MM-DDTHH24:MI:SSZ')") |
| assert result.data == ["2018-11-10T15:11:04Z"] |
| |
| def test_lowercase_format_elements(self): |
| result = self.client.execute("select cast('2019-11-20 15:59:44.123456789 01:01' as " |
| "timestamp format 'yyyy-mm-dd hh24:mi:ss.ff9 tzh-tzm')") |
| assert result.data == ["2019-11-20 15:59:44.123456789"] |
| |
| result = self.client.execute("select cast('2019-300 15:59:44.123456789 01:01' as " |
| "timestamp format 'yyyy-ddd hh24:mi:ss.ff9 tzh-tzm')") |
| assert result.data == ["2019-10-27 15:59:44.123456789"] |
| |
| result = self.client.execute("select cast('2019-11-21 11:59:44.123456789 p.m. 01:01' " |
| "as timestamp format 'yyyy-mm-dd hh12:mi:ss.ff9 am tzh-tzm')") |
| assert result.data == ["2019-11-21 23:59:44.123456789"] |
| |
| result = self.client.execute("select cast('2019-11-22 10000.123456789 02:02' " |
| "as timestamp format 'yyyy-mm-dd sssss ff9 tzh-tzm')") |
| assert result.data == ["2019-11-22 02:46:40.123456789"] |
| |
| def test_year(self): |
| # Test lower boundary of year |
| result = self.client.execute("select cast('1399-05-01' as " |
| "timestamp FORMAT 'YYYY-MM-DD')") |
| assert result.data == ["NULL"] |
| |
| # YYYY with less than 4 digits in the input |
| query_options = dict({'now_string': '2019-01-01 11:11:11'}) |
| |
| result = self.execute_query("select cast('095-01-31' as " |
| "timestamp FORMAT 'YYYY-MM-DD')", query_options) |
| assert result.data == ["2095-01-31 00:00:00"] |
| |
| result = self.execute_query("select cast('95-02-28' as " |
| "timestamp FORMAT 'YYYY-MM-DD')", query_options) |
| assert result.data == ["2095-02-28 00:00:00"] |
| |
| result = self.execute_query("select cast('5-03-31' as " |
| "timestamp FORMAT 'YYYY-MM-DD')", query_options) |
| assert result.data == ["2015-03-31 00:00:00"] |
| |
| # YYY with less than 3 digits in the input |
| result = self.execute_query("select cast('95-04-30' as " |
| "timestamp FORMAT 'YYY-MM-DD')", query_options) |
| assert result.data == ["2095-04-30 00:00:00"] |
| |
| result = self.execute_query("select cast('5-05-31' as " |
| "timestamp FORMAT 'YYY-MM-DD')", query_options) |
| assert result.data == ["2015-05-31 00:00:00"] |
| |
| # YY with 1 digits in the input |
| result = self.execute_query("select cast('5-06-30' as " |
| "timestamp FORMAT 'YY-MM-DD')", query_options) |
| assert result.data == ["2015-06-30 00:00:00"] |
| |
| # YYY, YY, Y tokens without separators |
| result = self.execute_query("select cast('0950731' as " |
| "timestamp FORMAT 'YYYMMDD')", query_options) |
| assert result.data == ["2095-07-31 00:00:00"] |
| |
| result = self.execute_query("select cast('950831' as " |
| "timestamp FORMAT 'YYMMDD')", query_options) |
| assert result.data == ["2095-08-31 00:00:00"] |
| |
| result = self.execute_query("select cast('50930' as " |
| "timestamp FORMAT 'YMMDD')", query_options) |
| assert result.data == ["2015-09-30 00:00:00"] |
| |
| # Timestamp to string formatting |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'YYYY')", query_options) |
| assert result.data == ["2019"] |
| |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'YYY')", query_options) |
| assert result.data == ["019"] |
| |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'YY')", query_options) |
| assert result.data == ["19"] |
| |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'Y')", query_options) |
| assert result.data == ["9"] |
| |
| def test_round_year(self): |
| query_options = dict({'now_string': '2019-01-01 11:11:11'}) |
| |
| # Test lower boundar of round year |
| result = self.client.execute("select cast('1399-05-01' as " |
| "timestamp FORMAT 'RRRR-MM-DD')") |
| assert result.data == ["NULL"] |
| |
| result = self.client.execute("select cast('1400-05-21' as " |
| "timestamp FORMAT 'RRRR-MM-DD')") |
| assert result.data == ["1400-05-21 00:00:00"] |
| |
| # RRRR with 4-digit year falls back to YYYY |
| result = self.execute_query("select cast('2017-05-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2017-05-31 00:00:00"] |
| |
| # RRRR with 3-digit year fills digits from current year |
| result = self.execute_query("select cast('017-01-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2017-01-31 00:00:00"] |
| |
| # RRRR wit 1-digit year fills digits from current year |
| result = self.execute_query("select cast('0-07-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2010-07-31 00:00:00"] |
| |
| # RR with 1-digit year fills digits from current year |
| result = self.execute_query("select cast('9-08-31' as " |
| "timestamp FORMAT 'RR-MM-DD')", query_options) |
| assert result.data == ["2019-08-31 00:00:00"] |
| |
| # Round year when last 2 digits of current year is less than 50 |
| query_options = dict({'now_string': '2049-01-01 11:11:11'}) |
| result = self.execute_query("select cast('49-03-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2049-03-31 00:00:00"] |
| |
| result = self.execute_query("select cast('50-03-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["1950-03-31 00:00:00"] |
| |
| query_options = dict({'now_string': '2000-01-01 11:11:11'}) |
| result = self.execute_query("select cast('49-03-31' as " |
| "timestamp FORMAT 'RR-MM-DD')", query_options) |
| assert result.data == ["2049-03-31 00:00:00"] |
| |
| result = self.execute_query("select cast('50-03-31' as " |
| "timestamp FORMAT 'RR-MM-DD')", query_options) |
| assert result.data == ["1950-03-31 00:00:00"] |
| |
| # Round year when last 2 digits of current year is greater than 49 |
| query_options = dict({'now_string': '2050-01-01 11:11:11'}) |
| result = self.execute_query("select cast('49-03-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2149-03-31 00:00:00"] |
| |
| result = self.execute_query("select cast('50-03-31' as " |
| "timestamp FORMAT 'RRRR-MM-DD')", query_options) |
| assert result.data == ["2050-03-31 00:00:00"] |
| |
| query_options = dict({'now_string': '2099-01-01 11:11:11'}) |
| result = self.execute_query("select cast('49-03-31' as " |
| "timestamp FORMAT 'RR-MM-DD')", query_options) |
| assert result.data == ["2149-03-31 00:00:00"] |
| |
| result = self.execute_query("select cast('50-03-31' as " |
| "timestamp FORMAT 'RR-MM-DD')", query_options) |
| assert result.data == ["2050-03-31 00:00:00"] |
| |
| # In a datetime to sting cast round year act like regular 'YYYY' or 'YY' tokens. |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'RRRR')", query_options) |
| assert result.data == ["2019"] |
| |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'RR')", query_options) |
| assert result.data == ["19"] |
| |
| def test_day_in_year(self): |
| # Test "day in year" token in a non leap year scenario |
| result = self.execute_query("select cast('2019 1' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2019-01-01 00:00:00"] |
| |
| result = self.execute_query("select cast('2019 31' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2019-01-31 00:00:00"] |
| |
| result = self.execute_query("select cast('2019 32' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2019-02-01 00:00:00"] |
| |
| result = self.execute_query("select cast('2019 60' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2019-03-01 00:00:00"] |
| |
| result = self.execute_query("select cast('2019 365' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2019-12-31 00:00:00"] |
| |
| result = self.execute_query("select cast('2019 366' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["NULL"] |
| |
| # Test "day in year" token in a leap year scenario |
| result = self.execute_query("select cast('2000 60' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2000-02-29 00:00:00"] |
| |
| result = self.execute_query("select cast('2000 61' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2000-03-01 00:00:00"] |
| |
| result = self.execute_query("select cast('2000 366' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["2000-12-31 00:00:00"] |
| |
| result = self.execute_query("select cast('2000 367' as timestamp FORMAT 'YYYY DDD')") |
| assert result.data == ["NULL"] |
| |
| # Test "day in year" token without separators |
| result = self.execute_query("select cast('20190011120' as timestamp " |
| "FORMAT 'YYYYDDDHH12MI')") |
| assert result.data == ["2019-01-01 11:20:00"] |
| |
| # Timestamp to string formatting |
| result = self.execute_query("select cast(cast('2019-01-01' as timestamp) as string " |
| "format'DDD')") |
| assert result.data == ["001"] |
| |
| result = self.execute_query("select cast(cast('2019-12-31' as timestamp) as string " |
| "format'DDD')") |
| assert result.data == ["365"] |
| |
| result = self.execute_query("select cast(cast('2000-12-31' as timestamp) as string " |
| "format'DDD')") |
| assert result.data == ["366"] |
| |
| result = self.execute_query("select cast(cast('2019 123' as timestamp " |
| "format 'YYYY DDD') as string format'DDD')") |
| assert result.data == ["123"] |
| |
| def test_second_of_day(self): |
| # Check boundaries |
| result = self.client.execute("select cast('2019-11-10 86399.11' as " |
| "timestamp FORMAT 'YYYY-MM-DD SSSSS.FF2')") |
| assert result.data == ["2019-11-10 23:59:59.110000000"] |
| |
| result = self.client.execute("select cast('2019-11-10 0' as " |
| "timestamp FORMAT 'YYYY-MM-DD SSSSS')") |
| assert result.data == ["2019-11-10 00:00:00"] |
| |
| # Without separators full 5-digit "second of day" has to be given |
| result = self.client.execute("select cast('11-10 036612019' as " |
| "timestamp FORMAT 'MM-DD SSSSSYYYY')") |
| assert result.data == ["2019-11-10 01:01:01"] |
| |
| # Check timezone offsets with "second of day" |
| result = self.client.execute("select cast('2019-11-10 036611010' as " |
| "timestamp FORMAT 'YYYY-MM-DD SSSSSTZHTZM')") |
| assert result.data == ["2019-11-10 01:01:01"] |
| |
| # Timestamp to string formatting |
| result = self.client.execute("select cast(cast('2019-01-01 01:01:01' as timestamp) " |
| "as string format 'SSSSS')") |
| assert result.data == ["03661"] |
| |
| result = self.client.execute("select cast(cast('2019-01-01' as timestamp) as string " |
| "format 'SSSSS')") |
| assert result.data == ["00000"] |
| |
| result = self.client.execute("select cast(cast('2019-01-01 23:59:59' as timestamp) " |
| "as string format 'SSSSS')") |
| assert result.data == ["86399"] |
| |
| def test_fraction_seconds(self): |
| result = self.execute_query("select cast('2019-11-08 123456789' as " |
| "timestamp FORMAT 'YYYY-MM-DD FF9')") |
| assert result.data == ["2019-11-08 00:00:00.123456789"] |
| |
| result = self.execute_query("select cast('2019-11-08 1' as " |
| "timestamp FORMAT 'YYYY-MM-DD FF')") |
| assert result.data == ["2019-11-08 00:00:00.100000000"] |
| |
| result = self.execute_query("select cast('2019-11-08 1234567890' as " |
| "timestamp FORMAT 'YYYY-MM-DD FF')") |
| assert result.data == ["NULL"] |
| |
| result = self.execute_query("select cast('2019-11-08' as " |
| "timestamp FORMAT 'YYYY-MM-DD FF')") |
| assert result.data == ["NULL"] |
| |
| self.run_fraction_test(1) |
| self.run_fraction_test(2) |
| self.run_fraction_test(3) |
| self.run_fraction_test(4) |
| self.run_fraction_test(5) |
| self.run_fraction_test(6) |
| self.run_fraction_test(7) |
| self.run_fraction_test(8) |
| self.run_fraction_test(9) |
| |
| def run_fraction_test(self, length): |
| MAX_LENGTH = 9 |
| fraction_part = "" |
| for x in range(length): |
| fraction_part += str(x + 1) |
| template_input = "select cast('2019-11-08 %s' as timestamp FORMAT 'YYYY-MM-DD FF%s')" |
| input_str = template_input % (fraction_part, length) |
| |
| expected = "2019-11-08 00:00:00." + fraction_part + ("0" * (MAX_LENGTH - length)) |
| result = self.execute_query(input_str) |
| assert result.data == [expected] |
| |
| input2_str = template_input % (fraction_part + str(length + 1), length) |
| result = self.execute_query(input2_str) |
| assert result.data == ["NULL"] |
| |
| def test_meridiem_indicator(self): |
| # Check 12 hour diff between AM and PM |
| result = self.client.execute("select cast('2017-05-03 08 AM' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12 AM')") |
| assert result.data == ["2017-05-03 08:00:00"] |
| |
| result = self.client.execute("select cast('2017-05-04 08 PM' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12 PM')") |
| assert result.data == ["2017-05-04 20:00:00"] |
| |
| # Check that any meridiem indicator in the pattern matches any meridiem indicator in |
| # the input |
| result = self.client.execute("select cast('2017-05-05 12AM' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12PM')") |
| assert result.data == ["2017-05-05 00:00:00"] |
| |
| result = self.client.execute("select cast('2017-05-06 P.M.12' as " |
| "timestamp FORMAT 'YYYY-MM-DD AMHH12')") |
| assert result.data == ["2017-05-06 12:00:00"] |
| |
| result = self.client.execute("select cast('2017-05-07 PM 01' as " |
| "timestamp FORMAT 'YYYY-MM-DD A.M. HH12')") |
| assert result.data == ["2017-05-07 13:00:00"] |
| |
| # Test lowercase indicator in input |
| result = self.client.execute("select cast('2017-05-08 pm09' as " |
| "timestamp FORMAT 'YYYY-MM-DD P.M.HH12')") |
| assert result.data == ["2017-05-08 21:00:00"] |
| |
| result = self.client.execute("select cast('2017-05-09 10a.m.' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12PM')") |
| assert result.data == ["2017-05-09 10:00:00"] |
| |
| # Test that '.' in indicator doesn't conflict with '.' as separator |
| result = self.client.execute("select cast('2017-05-11 9.AM.10' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12.P.M..MI')") |
| assert result.data == ["2017-05-11 09:10:00"] |
| |
| result = self.client.execute("select cast('2017-05-10.P.M..10' as " |
| "timestamp FORMAT 'YYYY-MM-DD.AM.HH12')") |
| assert result.data == ["2017-05-10 22:00:00"] |
| |
| # Timestamp to string formatting |
| result = self.client.execute("select cast(cast('2019-01-01 00:15:10' as timestamp) " |
| "as string format 'HH12 P.M.')") |
| assert result.data == ["12 A.M."] |
| |
| result = self.client.execute("select cast(cast('2019-01-01 12:15:10' as timestamp) " |
| "as string format 'HH12 AM')") |
| assert result.data == ["12 PM"] |
| |
| result = self.client.execute("select cast(cast('2019-01-01 13:15:10' as timestamp) " |
| "as string format 'HH12 a.m.')") |
| assert result.data == ["01 p.m."] |
| |
| result = self.client.execute("select cast(cast('2019-01-01 23:15:10' as timestamp) " |
| "as string format 'HH12 p.m.')") |
| assert result.data == ["11 p.m."] |
| |
| def test_timezone_offsets(self): |
| # Test positive timezone offset. |
| result = self.client.execute("select cast('2018-01-01 10:00 AM +15:59' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-01-01 10:00:00"] |
| |
| # Test negative timezone offset. |
| result = self.client.execute("select cast('2018-12-31 08:00 PM -15:59' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-12-31 20:00:00"] |
| |
| # Minus sign before TZM. |
| result = self.client.execute("select cast('2018-12-31 08:00 AM 01:-59' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| # Minus sign right before one digit TZH. |
| result = self.client.execute("select cast('2018-12-31 08:00 AM--1:10' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| result = self.client.execute("select cast('2018-12-31 08:00 AM-5:00' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M.TZH:TZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| # One digit negative TZH at the end of the input string. |
| result = self.client.execute("select cast('2018-12-31 12:01 -1' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI TZH')") |
| assert result.data == ["2018-12-31 12:01:00"] |
| |
| # Test timezone offset parsing without separators |
| result = self.client.execute("select cast('201812310800AM+0515' as " |
| "timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| result = self.client.execute("select cast('201812310800AM0515' as " |
| "timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| result = self.client.execute("select cast('201812310800AM-0515' as " |
| "timestamp FORMAT 'YYYYMMDDHH12MIA.M.TZHTZM')") |
| assert result.data == ["2018-12-31 08:00:00"] |
| |
| # Test signed zero TZH with not null TZM |
| result = self.client.execute("select cast('2018-01-01 10:00 AM +00:59' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-01-01 10:00:00"] |
| |
| result = self.client.execute("select cast('2018-01-01 10:00 AM -00:59' as " |
| "timestamp FORMAT 'YYYY-MM-DD HH12:MI A.M. TZH:TZM')") |
| assert result.data == ["2018-01-01 10:00:00"] |
| |
| # Shuffle TZH and TZM into other elements |
| result = self.client.execute("select cast('2018-01-01 15 10:00 1 AM' as " |
| "timestamp FORMAT 'YYYY-MM-DD TZM HH12:MI TZH A.M.')") |
| assert result.data == ["2018-01-01 10:00:00"] |
| |
| result = self.client.execute("select cast('2018-01-011510:00-01AM' as " |
| "timestamp FORMAT 'YYYY-MM-DDTZMHH12:MITZHA.M.')") |
| assert result.data == ["2018-01-01 10:00:00"] |
| |
| # Timezone offset with default time |
| result = self.client.execute("select cast('2018-01-01 01:30' as timestamp " |
| "FORMAT 'YYYY-MM-DD TZH:TZM')") |
| assert result.data == ["2018-01-01 00:00:00"] |
| |
| # Single minus sign before two digit TZH. |
| result = self.client.execute("select cast('2018-09-11 15:30:10-10' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI:SS-TZH')") |
| assert result.data == ["2018-09-11 15:30:10"] |
| |
| # Non-digit TZH and TZM. |
| result = self.client.execute("select cast('2018-09-11 17:30:10 ab:10' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM')") |
| assert result.data == ["NULL"] |
| |
| result = self.client.execute("select cast('2018-09-11 17:30:10 -ab:10' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM')") |
| assert result.data == ["NULL"] |
| |
| result = self.client.execute("select cast('2018-09-11 17:30:10 +ab:10' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM')") |
| assert result.data == ["NULL"] |
| |
| result = self.client.execute("select cast('2018-09-11 18:30:10 10:ab' as timestamp " |
| "FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM')") |
| assert result.data == ["NULL"] |
| |
| def test_format_parse_errors(self): |
| # Invalid format |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'XXXX-dd-MM')") |
| assert "Bad date/time conversion format: XXXX-dd-MM" in str(err) |
| |
| # Invalid use of SimpleDateFormat |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01 15:10' as timestamp format 'yyyy-MM-dd +hh:mm')") |
| assert "Bad date/time conversion format: yyyy-MM-dd +hh:mm" in str(err) |
| |
| # Duplicate format element |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD MM')") |
| assert "Invalid duplication of format element" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-YYYY')") |
| assert "Invalid duplication of format element" in str(err) |
| |
| # Multiple year token provided |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-YY')") |
| assert "Multiple year token provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYY-MM-DD-Y')") |
| assert "Multiple year token provided" in str(err) |
| |
| # Year and round year conflict |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YY-MM-DD-RRRR')") |
| assert "Both year and round year are provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'RR-MM-DD-YYY')") |
| assert "Both year and round year are provided" in str(err) |
| |
| # Day of year conflict |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DDD')") |
| assert "Day of year provided with day or month token" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-DD-DDD')") |
| assert "Day of year provided with day or month token" in str(err) |
| |
| # Conflict between hour tokens |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH:HH24')") |
| assert "Multiple hour tokens provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:HH24')") |
| assert "Multiple hour tokens provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:HH')") |
| assert "Multiple hour tokens provided" in str(err) |
| |
| # Conflict with median indicator |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD AM HH:MI A.M.')") |
| assert "Multiple median indicator tokens provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD PM HH:MI am')") |
| assert "Multiple median indicator tokens provided" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24:MI a.m.')") |
| assert "Conflict between median indicator and hour token" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD p.m.')") |
| assert "Missing hour token" in str(err) |
| |
| # Conflict with second of day |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SSSSS HH')") |
| assert "Second of day token conflicts with other token(s)" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH12:SSSSS')") |
| assert "Second of day token conflicts with other token(s)" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD HH24SSSSS')") |
| assert "Second of day token conflicts with other token(s)" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD MI SSSSS')") |
| assert "Second of day token conflicts with other token(s)" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD SS SSSSS')") |
| assert "Second of day token conflicts with other token(s)" in str(err) |
| |
| # Too long format |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format '" + |
| "{char: <101}".format(char="s") + "')") |
| assert "The input format is too long" in str(err) |
| |
| # Timezone offsets in a datetime to string formatting |
| err = self.execute_query_expect_failure(self.client, |
| "select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM') " |
| "as string format 'TZH')") |
| assert "Timezone offset not allowed in a datetime to string conversion" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM') " |
| "as string format 'TZM')") |
| assert "Timezone offset not allowed in a datetime to string conversion" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast(cast('2017-05-01 01:15' as timestamp format 'YYYY-MM-DD TZH:TZM') " |
| "as string format 'YYYY-MM-DD HH24:MI:SS TZH:TZM')") |
| assert "Timezone offset not allowed in a datetime to string conversion" in str(err) |
| |
| # TZM requires TZH |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-12-31 08:00 AM 59' as timestamp FORMAT " |
| "'YYYY-MM-DD HH12:MI A.M. TZM')") |
| assert "TZH token is required for TZM" in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-12-31 08:00 AM -59' as timestamp FORMAT " |
| "'YYYY-MM-DD HH12:MI A.M. TZM')") |
| assert "TZH token is required for TZM" in str(err) |
| |
| # Multiple fraction second token conflict |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-10-10' as timestamp format 'FF FF1')") |
| assert "Multiple fractional second token provided." in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-10-10' as timestamp format 'FF2 FF3')") |
| assert "Multiple fractional second token provided." in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-10-10' as timestamp format 'FF4 FF5')") |
| assert "Multiple fractional second token provided." in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-10-10' as timestamp format 'FF6 FF7')") |
| assert "Multiple fractional second token provided." in str(err) |
| |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2018-10-10' as timestamp format 'FF8 FF9')") |
| assert "Multiple fractional second token provided." in str(err) |
| |
| # Verify that conflict check is not skipped when format ends with separators. |
| err = self.execute_query_expect_failure(self.client, |
| "select cast('2017-05-01' as timestamp format 'YYYY-MM-DD-RR--')") |
| assert "Both year and round year are provided" in str(err) |
