spark-reviews mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From GitBox <...@apache.org>
Subject [GitHub] [spark] gengliangwang commented on a change in pull request #33640: [SPARK-36409][SQL][TESTS] Splitting test cases from datetime.sql
Date Thu, 05 Aug 2021 11:41:17 GMT

gengliangwang commented on a change in pull request #33640:
URL: https://github.com/apache/spark/pull/33640#discussion_r683144515



##########
File path: sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
##########
@@ -0,0 +1,525 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 60
+
+
+-- !query
+select date '2019-01-01\t'
+-- !query schema
+struct<DATE '2019-01-01':date>
+-- !query output
+2019-01-01
+
+
+-- !query
+select date '2020-01-01中文'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the DATE value: 2020-01-01中文(line 1, pos 7)
+
+== SQL ==
+select date '2020-01-01中文'
+-------^^^
+
+
+-- !query
+select date'999999-03-18', date'-0001-1-28', date'0015'
+-- !query schema
+struct<DATE '+999999-03-18':date,DATE '-0001-01-28':date,DATE '0015-01-01':date>
+-- !query output
++999999-03-18	-0001-01-28	0015-01-01
+
+
+-- !query
+select date'015'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the DATE value: 015(line 1, pos 7)
+
+== SQL ==
+select date'015'
+-------^^^
+
+
+-- !query
+select date'2021-4294967297-11'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the DATE value: 2021-4294967297-11(line 1, pos 7)
+
+== SQL ==
+select date'2021-4294967297-11'
+-------^^^
+
+
+-- !query
+select current_date = current_date
+-- !query schema
+struct<(current_date() = current_date()):boolean>
+-- !query output
+true
+
+
+-- !query
+select current_date() = current_date()
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+no viable alternative at input 'current_date'(line 1, pos 7)
+
+== SQL ==
+select current_date() = current_date()
+-------^^^
+
+
+-- !query
+select DATE_FROM_UNIX_DATE(0), DATE_FROM_UNIX_DATE(1000), DATE_FROM_UNIX_DATE(null)
+-- !query schema
+struct<date_from_unix_date(0):date,date_from_unix_date(1000):date,date_from_unix_date(NULL):date>
+-- !query output
+1970-01-01	1972-09-27	NULL
+
+
+-- !query
+select UNIX_DATE(DATE('1970-01-01')), UNIX_DATE(DATE('2020-12-04')), UNIX_DATE(null)
+-- !query schema
+struct<unix_date(1970-01-01):int,unix_date(2020-12-04):int,unix_date(NULL):int>
+-- !query output
+0	18600	NULL
+
+
+-- !query
+select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd')
+-- !query schema
+struct<to_date(NULL):date,to_date(2016-12-31):date,to_date(2016-12-31, yyyy-MM-dd):date>
+-- !query output
+NULL	2016-12-31	2016-12-31
+
+
+-- !query
+select to_date("16", "dd")
+-- !query schema
+struct<to_date(16, dd):date>
+-- !query output
+1970-01-16
+
+
+-- !query
+select to_date("02-29", "MM-dd")
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Invalid date 'February 29' as '1970' is not a leap year
+
+
+-- !query
+select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'),
+  dayofweek(null), dayofweek('1582-10-15 13:10:15'), dayofweek(timestamp'1582-10-15 13:10:15')
+-- !query schema
+struct<dayofweek(2007-02-03):int,dayofweek(2009-07-30):int,dayofweek(2017-05-27):int,dayofweek(NULL):int,dayofweek(1582-10-15
13:10:15):int,dayofweek(TIMESTAMP '1582-10-15 13:10:15'):int>
+-- !query output
+7	5	7	NULL	6	6
+
+
+-- !query
+select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'),
+  weekday(null), weekday('1582-10-15 13:10:15'), weekday(timestamp'1582-10-15 13:10:15')
+-- !query schema
+struct<weekday(2007-02-03):int,weekday(2009-07-30):int,weekday(2017-05-27):int,weekday(NULL):int,weekday(1582-10-15
13:10:15):int,weekday(TIMESTAMP '1582-10-15 13:10:15'):int>
+-- !query output
+5	3	5	NULL	4	4
+
+
+-- !query
+select year('1500-01-01'), year('1582-10-15 13:10:15'), year(timestamp'1582-10-15 13:10:15')
+-- !query schema
+struct<year(1500-01-01):int,year(1582-10-15 13:10:15):int,year(TIMESTAMP '1582-10-15 13:10:15'):int>
+-- !query output
+1500	1582	1582
+
+
+-- !query
+select month('1500-01-01'), month('1582-10-15 13:10:15'), month(timestamp'1582-10-15 13:10:15')
+-- !query schema
+struct<month(1500-01-01):int,month(1582-10-15 13:10:15):int,month(TIMESTAMP '1582-10-15
13:10:15'):int>
+-- !query output
+1	10	10
+
+
+-- !query
+select dayOfYear('1500-01-01'), dayOfYear('1582-10-15 13:10:15'), dayOfYear(timestamp'1582-10-15
13:10:15')
+-- !query schema
+struct<dayofyear(1500-01-01):int,dayofyear(1582-10-15 13:10:15):int,dayofyear(TIMESTAMP
'1582-10-15 13:10:15'):int>
+-- !query output
+1	288	288
+
+
+-- !query
+select next_day("2015-07-23", "Mon")
+-- !query schema
+struct<next_day(2015-07-23, Mon):date>
+-- !query output
+2015-07-27
+
+
+-- !query
+select next_day("2015-07-23", "xx")
+-- !query schema
+struct<>
+-- !query output
+java.lang.IllegalArgumentException
+Illegal input for day of week: xx
+
+
+-- !query
+select next_day("2015-07-23 12:12:12", "Mon")
+-- !query schema
+struct<next_day(2015-07-23 12:12:12, Mon):date>
+-- !query output
+2015-07-27
+
+
+-- !query
+select next_day(timestamp"2015-07-23 12:12:12", "Mon")

Review comment:
       I tried on Oracle:
   ```
   create table foo(ts timestamp);
   insert into foo values(timestamp'2015-07-23 12:12:12');
   select next_day(ts, 'Mon') from foo;
   ```
   The result is 
   ```
   2015-07-27T12:12:12Z
   ```
   Spark's current implementation for next_day(timestamp) is wrong.
   

##########
File path: sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
##########
@@ -0,0 +1,579 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 68
+
+
+-- !query
+select timestamp '2019-01-01\t'
+-- !query schema
+struct<TIMESTAMP_NTZ '2019-01-01 00:00:00':timestamp_ntz>
+-- !query output
+2019-01-01 00:00:00
+
+
+-- !query
+select timestamp '2019-01-01中文'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the TIMESTAMP value: 2019-01-01中文(line 1, pos 7)
+
+== SQL ==
+select timestamp '2019-01-01中文'
+-------^^^
+
+
+-- !query
+select timestamp'-1969-12-31 16:00:00', timestamp'0015-03-18 16:00:00', timestamp'-000001',
timestamp'99999-03-18T12:03:17'
+-- !query schema
+struct<TIMESTAMP_NTZ '-1969-12-31 16:00:00':timestamp_ntz,TIMESTAMP_NTZ '0015-03-18 16:00:00':timestamp_ntz,TIMESTAMP_NTZ
'-0001-01-01 00:00:00':timestamp_ntz,TIMESTAMP_NTZ '+99999-03-18 12:03:17':timestamp_ntz>
+-- !query output
+-1969-12-31 16:00:00	0015-03-18 16:00:00	-0001-01-01 00:00:00	+99999-03-18 12:03:17
+
+
+-- !query
+select timestamp'4294967297'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the TIMESTAMP value: 4294967297(line 1, pos 7)
+
+== SQL ==
+select timestamp'4294967297'
+-------^^^
+
+
+-- !query
+select timestamp'2021-01-01T12:30:4294967297.123456'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the TIMESTAMP value: 2021-01-01T12:30:4294967297.123456(line 1, pos 7)
+
+== SQL ==
+select timestamp'2021-01-01T12:30:4294967297.123456'
+-------^^^
+
+
+-- !query
+select current_timestamp = current_timestamp
+-- !query schema
+struct<(current_timestamp() = current_timestamp()):boolean>
+-- !query output
+true
+
+
+-- !query
+select current_timestamp() = current_timestamp()
+-- !query schema
+struct<(current_timestamp() = current_timestamp()):boolean>
+-- !query output
+true
+
+
+-- !query
+select localtimestamp() = localtimestamp()
+-- !query schema
+struct<(localtimestamp() = localtimestamp()):boolean>
+-- !query output
+true
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678):timestamp_ntz>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678, 'CET')
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678, CET):timestamp_ntz>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 60.007):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+create temporary view ttf1 as select * from values
+  (1, 2),
+  (2, 3)
+  as ttf1(current_date, current_timestamp)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+select current_date, current_timestamp from ttf1
+-- !query schema
+struct<current_date:int,current_timestamp:int>
+-- !query output
+1	2
+2	3
+
+
+-- !query
+create temporary view ttf2 as select * from values
+  (1, 2),
+  (2, 3)
+  as ttf2(a, b)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+select current_date = current_date(), current_timestamp = current_timestamp(), a, b from
ttf2
+-- !query schema
+struct<(current_date() = current_date()):boolean,(current_timestamp() = current_timestamp()):boolean,a:int,b:int>
+-- !query output
+true	true	1	2
+true	true	2	3
+
+
+-- !query
+select a, b from ttf2 order by a, current_date
+-- !query schema
+struct<a:int,b:int>
+-- !query output
+1	2
+2	3
+
+
+-- !query
+select UNIX_SECONDS(timestamp'2020-12-01 14:30:08Z'), UNIX_SECONDS(timestamp'2020-12-01 14:30:08.999999Z'),
UNIX_SECONDS(null)
+-- !query schema
+struct<unix_seconds(TIMESTAMP '2020-12-01 06:30:08'):bigint,unix_seconds(TIMESTAMP '2020-12-01
06:30:08.999999'):bigint,unix_seconds(NULL):bigint>
+-- !query output
+1606833008	1606833008	NULL
+
+
+-- !query
+select UNIX_MILLIS(timestamp'2020-12-01 14:30:08Z'), UNIX_MILLIS(timestamp'2020-12-01 14:30:08.999999Z'),
UNIX_MILLIS(null)
+-- !query schema
+struct<unix_millis(TIMESTAMP '2020-12-01 06:30:08'):bigint,unix_millis(TIMESTAMP '2020-12-01
06:30:08.999999'):bigint,unix_millis(NULL):bigint>
+-- !query output
+1606833008000	1606833008999	NULL
+
+
+-- !query
+select UNIX_MICROS(timestamp'2020-12-01 14:30:08Z'), UNIX_MICROS(timestamp'2020-12-01 14:30:08.999999Z'),
UNIX_MICROS(null)
+-- !query schema
+struct<unix_micros(TIMESTAMP '2020-12-01 06:30:08'):bigint,unix_micros(TIMESTAMP '2020-12-01
06:30:08.999999'):bigint,unix_micros(NULL):bigint>
+-- !query output
+1606833008000000	1606833008999999	NULL
+
+
+-- !query
+select to_timestamp(null), to_timestamp('2016-12-31 00:12:00'), to_timestamp('2016-12-31',
'yyyy-MM-dd')
+-- !query schema
+struct<to_timestamp(NULL):timestamp_ntz,to_timestamp(2016-12-31 00:12:00):timestamp_ntz,to_timestamp(2016-12-31,
yyyy-MM-dd):timestamp_ntz>
+-- !query output
+NULL	2016-12-31 00:12:00	2016-12-31 00:00:00
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12., yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.0', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.0, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.1', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.1, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.1
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.12', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.12, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.12
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.123UTC', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.123UTC, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.123
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.1234', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.1234, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.1234
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.12345CST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.12345CST, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.12345
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.123456PST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.123456PST, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.123456
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.1234567PST', 'yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.1234567PST, yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp('123456 2019-10-06 10:11:12.123456PST', 'SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(123456 2019-10-06 10:11:12.123456PST, SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.123456
+
+
+-- !query
+select to_timestamp('223456 2019-10-06 10:11:12.123456PST', 'SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]')
+-- !query schema
+struct<to_timestamp(223456 2019-10-06 10:11:12.123456PST, SSSSSS yyyy-MM-dd HH:mm:ss.SSSSSS[zzz]):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.1234', 'yyyy-MM-dd HH:mm:ss.[SSSSSS]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.1234, yyyy-MM-dd HH:mm:ss.[SSSSSS]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.1234
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.123', 'yyyy-MM-dd HH:mm:ss[.SSSSSS]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.123, yyyy-MM-dd HH:mm:ss[.SSSSSS]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.123
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12', 'yyyy-MM-dd HH:mm:ss[.SSSSSS]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12, yyyy-MM-dd HH:mm:ss[.SSSSSS]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11:12.12', 'yyyy-MM-dd HH:mm[:ss.SSSSSS]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11:12.12, yyyy-MM-dd HH:mm[:ss.SSSSSS]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.12
+
+
+-- !query
+select to_timestamp('2019-10-06 10:11', 'yyyy-MM-dd HH:mm[:ss.SSSSSS]')
+-- !query schema
+struct<to_timestamp(2019-10-06 10:11, yyyy-MM-dd HH:mm[:ss.SSSSSS]):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:00
+
+
+-- !query
+select to_timestamp("2019-10-06S10:11:12.12345", "yyyy-MM-dd'S'HH:mm:ss.SSSSSS")
+-- !query schema
+struct<to_timestamp(2019-10-06S10:11:12.12345, yyyy-MM-dd'S'HH:mm:ss.SSSSSS):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.12345
+
+
+-- !query
+select to_timestamp("12.12342019-10-06S10:11", "ss.SSSSyyyy-MM-dd'S'HH:mm")
+-- !query schema
+struct<to_timestamp(12.12342019-10-06S10:11, ss.SSSSyyyy-MM-dd'S'HH:mm):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.1234
+
+
+-- !query
+select to_timestamp("12.1232019-10-06S10:11", "ss.SSSSyyyy-MM-dd'S'HH:mm")
+-- !query schema
+struct<to_timestamp(12.1232019-10-06S10:11, ss.SSSSyyyy-MM-dd'S'HH:mm):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp("12.1232019-10-06S10:11", "ss.SSSSyy-MM-dd'S'HH:mm")
+-- !query schema
+struct<to_timestamp(12.1232019-10-06S10:11, ss.SSSSyy-MM-dd'S'HH:mm):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp("12.1234019-10-06S10:11", "ss.SSSSy-MM-dd'S'HH:mm")
+-- !query schema
+struct<to_timestamp(12.1234019-10-06S10:11, ss.SSSSy-MM-dd'S'HH:mm):timestamp_ntz>
+-- !query output
+0019-10-06 10:11:12.1234
+
+
+-- !query
+select to_timestamp("2019-10-06S", "yyyy-MM-dd'S'")
+-- !query schema
+struct<to_timestamp(2019-10-06S, yyyy-MM-dd'S'):timestamp_ntz>
+-- !query output
+2019-10-06 00:00:00
+
+
+-- !query
+select to_timestamp("S2019-10-06", "'S'yyyy-MM-dd")
+-- !query schema
+struct<to_timestamp(S2019-10-06, 'S'yyyy-MM-dd):timestamp_ntz>
+-- !query output
+2019-10-06 00:00:00
+
+
+-- !query
+select to_timestamp("2019-10-06T10:11:12'12", "yyyy-MM-dd'T'HH:mm:ss''SSSS")
+-- !query schema
+struct<to_timestamp(2019-10-06T10:11:12'12, yyyy-MM-dd'T'HH:mm:ss''SSSS):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12.12
+
+
+-- !query
+select to_timestamp("2019-10-06T10:11:12'", "yyyy-MM-dd'T'HH:mm:ss''")
+-- !query schema
+struct<to_timestamp(2019-10-06T10:11:12', yyyy-MM-dd'T'HH:mm:ss''):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12
+
+
+-- !query
+select to_timestamp("'2019-10-06T10:11:12", "''yyyy-MM-dd'T'HH:mm:ss")
+-- !query schema
+struct<to_timestamp('2019-10-06T10:11:12, ''yyyy-MM-dd'T'HH:mm:ss):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12
+
+
+-- !query
+select to_timestamp("P2019-10-06T10:11:12", "'P'yyyy-MM-dd'T'HH:mm:ss")
+-- !query schema
+struct<to_timestamp(P2019-10-06T10:11:12, 'P'yyyy-MM-dd'T'HH:mm:ss):timestamp_ntz>
+-- !query output
+2019-10-06 10:11:12
+
+
+-- !query
+select to_timestamp("16", "dd")
+-- !query schema
+struct<to_timestamp(16, dd):timestamp_ntz>
+-- !query output
+1970-01-16 00:00:00
+
+
+-- !query
+select to_timestamp("02-29", "MM-dd")
+-- !query schema
+struct<to_timestamp(02-29, MM-dd):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp("2019 40", "yyyy mm")
+-- !query schema
+struct<to_timestamp(2019 40, yyyy mm):timestamp_ntz>
+-- !query output
+2019-01-01 00:40:00
+
+
+-- !query
+select to_timestamp("2019 10:10:10", "yyyy hh:mm:ss")
+-- !query schema
+struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp_ntz>
+-- !query output
+2019-01-01 10:10:10
+
+
+-- !query
+select timestamp'2011-11-11 11:11:11' + interval '2' day
+-- !query schema
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp_ntz>
+-- !query output
+2011-11-13 11:11:11
+
+
+-- !query
+select timestamp'2011-11-11 11:11:11' - interval '2' day
+-- !query schema
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp_ntz>
+-- !query output
+2011-11-09 11:11:11
+
+
+-- !query
+select timestamp'2011-11-11 11:11:11' + interval '2' second
+-- !query schema
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp_ntz>
+-- !query output
+2011-11-11 11:11:13
+
+
+-- !query
+select timestamp'2011-11-11 11:11:11' - interval '2' second
+-- !query schema
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp_ntz>
+-- !query output
+2011-11-11 11:11:09
+
+
+-- !query
+select '2011-11-11 11:11:11' - interval '2' second
+-- !query schema
+struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+-- !query output
+2011-11-11 11:11:09
+
+
+-- !query
+select '1' - interval '2' second
+-- !query schema
+struct<1 - INTERVAL '02' SECOND:string>
+-- !query output
+NULL
+
+
+-- !query
+select 1 - interval '2' second
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '1 + (- INTERVAL '02' SECOND)' due to data type mismatch: argument 1 requires
(timestamp or timestamp without time zone) type, however, '1' is of int type.; line 1 pos
7
+
+
+-- !query
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+-- !query schema
+struct<(DATE '2020-01-01' - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to
second>
+-- !query output
+86 13:48:47.654322000
+
+
+-- !query
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to
second>
+-- !query output
+-86 13:48:47.654322000
+
+
+-- !query
+select timestamp'2019-10-06 10:11:12.345678' - null
+-- !query schema
+struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+-- !query output
+NULL
+
+
+-- !query
+select null - timestamp'2019-10-06 10:11:12.345678'
+-- !query schema
+struct<(NULL - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
+-- !query output
+NULL
+
+
+-- !query
+select to_timestamp('2019-10-06 A', 'yyyy-MM-dd GGGGG')
+-- !query schema
+struct<>
+-- !query output
+java.lang.RuntimeException
+Fail to recognize 'yyyy-MM-dd GGGGG' pattern in the DateTimeFormatter. You can form a valid
datetime pattern with the guide from https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html
+
+
+-- !query
+select to_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEEE')
+-- !query schema
+struct<>
+-- !query output
+java.lang.RuntimeException
+Fail to recognize 'dd MM yyyy EEEEEE' pattern in the DateTimeFormatter. You can form a valid
datetime pattern with the guide from https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html
+
+
+-- !query
+select to_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEE')
+-- !query schema
+struct<>
+-- !query output
+java.lang.RuntimeException
+Fail to recognize 'dd MM yyyy EEEEE' pattern in the DateTimeFormatter. You can form a valid
datetime pattern with the guide from https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html
+
+
+-- !query
+select unix_timestamp('22 05 2020 Friday', 'dd MM yyyy EEEEE')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.SparkUpgradeException
+You may get a different result due to the upgrading of Spark 3.0: Fail to recognize 'dd MM
yyyy EEEEE' pattern in the DateTimeFormatter. 1) You can set spark.sql.legacy.timeParserPolicy
to LEGACY to restore the behavior before Spark 3.0. 2) You can form a valid datetime pattern
with the guide from https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html
+
+
+-- !query
+select from_json('{"t":"26/October/2015"}', 't Timestamp', map('timestampFormat', 'dd/MMMMM/yyyy'))
+-- !query schema
+struct<from_json({"t":"26/October/2015"}):struct<t:timestamp_ntz>>
+-- !query output
+{"t":null}
+
+
+-- !query
+select from_csv('26/October/2015', 't Timestamp', map('timestampFormat', 'dd/MMMMM/yyyy'))
+-- !query schema
+struct<>
+-- !query output
+java.lang.Exception

Review comment:
       @beliefer Could you help investigate this one?

##########
File path: sql/core/src/test/resources/sql-tests/inputs/date.sql
##########
@@ -0,0 +1,106 @@
+-- date literals, functions and operations
+
+select date '2019-01-01\t';
+select date '2020-01-01中文';
+
+-- date with year outside [0000-9999]
+select date'999999-03-18', date'-0001-1-28', date'0015';
+-- invalid: year field must have at least 4 digits
+select date'015';
+-- invalid: month field can have at most 2 digits
+select date'2021-4294967297-11';
+
+select current_date = current_date;
+-- under ANSI mode, `current_date` can't be a function name.
+select current_date() = current_date();
+
+-- conversions between date and unix_date (number of days from epoch)
+select DATE_FROM_UNIX_DATE(0), DATE_FROM_UNIX_DATE(1000), DATE_FROM_UNIX_DATE(null);
+select UNIX_DATE(DATE('1970-01-01')), UNIX_DATE(DATE('2020-12-04')), UNIX_DATE(null);
+
+select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd');
+
+-- missing fields in `to_date`
+select to_date("16", "dd");
+-- invalid: there is no 29 in February, 1970
+select to_date("02-29", "MM-dd");
+
+-- `dayofweek` accepts both date and timestamp inputs.
+select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'),
+  dayofweek(null), dayofweek('1582-10-15 13:10:15'), dayofweek(timestamp'1582-10-15 13:10:15');
+
+-- `weekday` accepts both date and timestamp inputs.
+select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'),
+  weekday(null), weekday('1582-10-15 13:10:15'), weekday(timestamp'1582-10-15 13:10:15');
+
+-- `year` accepts both date and timestamp inputs.
+select year('1500-01-01'), year('1582-10-15 13:10:15'), year(timestamp'1582-10-15 13:10:15');
+
+-- `month` accepts both date and timestamp inputs.
+select month('1500-01-01'), month('1582-10-15 13:10:15'), month(timestamp'1582-10-15 13:10:15');
+
+-- `dayOfYear` accepts both date and timestamp inputs.
+select dayOfYear('1500-01-01'), dayOfYear('1582-10-15 13:10:15'), dayOfYear(timestamp'1582-10-15
13:10:15');
+
+-- next_day
+select next_day("2015-07-23", "Mon");
+select next_day("2015-07-23", "xx");
+select next_day("2015-07-23 12:12:12", "Mon");
+-- next_date does not accept timestamp input
+select next_day(timestamp"2015-07-23 12:12:12", "Mon");

Review comment:
       We need to test timestamp_ntz input as well.

##########
File path: sql/core/src/test/resources/sql-tests/inputs/date.sql
##########
@@ -0,0 +1,106 @@
+-- date literals, functions and operations
+
+select date '2019-01-01\t';
+select date '2020-01-01中文';
+
+-- date with year outside [0000-9999]
+select date'999999-03-18', date'-0001-1-28', date'0015';
+-- invalid: year field must have at least 4 digits
+select date'015';
+-- invalid: month field can have at most 2 digits
+select date'2021-4294967297-11';
+
+select current_date = current_date;
+-- under ANSI mode, `current_date` can't be a function name.
+select current_date() = current_date();
+
+-- conversions between date and unix_date (number of days from epoch)
+select DATE_FROM_UNIX_DATE(0), DATE_FROM_UNIX_DATE(1000), DATE_FROM_UNIX_DATE(null);
+select UNIX_DATE(DATE('1970-01-01')), UNIX_DATE(DATE('2020-12-04')), UNIX_DATE(null);
+
+select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd');
+
+-- missing fields in `to_date`
+select to_date("16", "dd");
+-- invalid: there is no 29 in February, 1970
+select to_date("02-29", "MM-dd");
+
+-- `dayofweek` accepts both date and timestamp inputs.
+select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'),
+  dayofweek(null), dayofweek('1582-10-15 13:10:15'), dayofweek(timestamp'1582-10-15 13:10:15');
+
+-- `weekday` accepts both date and timestamp inputs.
+select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'),
+  weekday(null), weekday('1582-10-15 13:10:15'), weekday(timestamp'1582-10-15 13:10:15');

Review comment:
       We need to test timestamp_ntz input as well.

##########
File path: sql/core/src/test/resources/sql-tests/inputs/date.sql
##########
@@ -0,0 +1,106 @@
+-- date literals, functions and operations
+
+select date '2019-01-01\t';
+select date '2020-01-01中文';
+
+-- date with year outside [0000-9999]
+select date'999999-03-18', date'-0001-1-28', date'0015';
+-- invalid: year field must have at least 4 digits
+select date'015';
+-- invalid: month field can have at most 2 digits
+select date'2021-4294967297-11';
+
+select current_date = current_date;
+-- under ANSI mode, `current_date` can't be a function name.
+select current_date() = current_date();
+
+-- conversions between date and unix_date (number of days from epoch)
+select DATE_FROM_UNIX_DATE(0), DATE_FROM_UNIX_DATE(1000), DATE_FROM_UNIX_DATE(null);
+select UNIX_DATE(DATE('1970-01-01')), UNIX_DATE(DATE('2020-12-04')), UNIX_DATE(null);
+
+select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd');
+
+-- missing fields in `to_date`
+select to_date("16", "dd");
+-- invalid: there is no 29 in February, 1970
+select to_date("02-29", "MM-dd");
+
+-- `dayofweek` accepts both date and timestamp inputs.
+select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'),
+  dayofweek(null), dayofweek('1582-10-15 13:10:15'), dayofweek(timestamp'1582-10-15 13:10:15');
+
+-- `weekday` accepts both date and timestamp inputs.
+select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'),
+  weekday(null), weekday('1582-10-15 13:10:15'), weekday(timestamp'1582-10-15 13:10:15');
+
+-- `year` accepts both date and timestamp inputs.
+select year('1500-01-01'), year('1582-10-15 13:10:15'), year(timestamp'1582-10-15 13:10:15');

Review comment:
       ditto

##########
File path: sql/core/src/test/resources/sql-tests/inputs/date.sql
##########
@@ -0,0 +1,106 @@
+-- date literals, functions and operations
+
+select date '2019-01-01\t';
+select date '2020-01-01中文';
+
+-- date with year outside [0000-9999]
+select date'999999-03-18', date'-0001-1-28', date'0015';
+-- invalid: year field must have at least 4 digits
+select date'015';
+-- invalid: month field can have at most 2 digits
+select date'2021-4294967297-11';
+
+select current_date = current_date;
+-- under ANSI mode, `current_date` can't be a function name.
+select current_date() = current_date();
+
+-- conversions between date and unix_date (number of days from epoch)
+select DATE_FROM_UNIX_DATE(0), DATE_FROM_UNIX_DATE(1000), DATE_FROM_UNIX_DATE(null);
+select UNIX_DATE(DATE('1970-01-01')), UNIX_DATE(DATE('2020-12-04')), UNIX_DATE(null);
+
+select to_date(null), to_date('2016-12-31'), to_date('2016-12-31', 'yyyy-MM-dd');
+
+-- missing fields in `to_date`
+select to_date("16", "dd");
+-- invalid: there is no 29 in February, 1970
+select to_date("02-29", "MM-dd");
+
+-- `dayofweek` accepts both date and timestamp inputs.
+select dayofweek('2007-02-03'), dayofweek('2009-07-30'), dayofweek('2017-05-27'),
+  dayofweek(null), dayofweek('1582-10-15 13:10:15'), dayofweek(timestamp'1582-10-15 13:10:15');
+
+-- `weekday` accepts both date and timestamp inputs.
+select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'),
+  weekday(null), weekday('1582-10-15 13:10:15'), weekday(timestamp'1582-10-15 13:10:15');
+
+-- `year` accepts both date and timestamp inputs.
+select year('1500-01-01'), year('1582-10-15 13:10:15'), year(timestamp'1582-10-15 13:10:15');
+
+-- `month` accepts both date and timestamp inputs.
+select month('1500-01-01'), month('1582-10-15 13:10:15'), month(timestamp'1582-10-15 13:10:15');
+
+-- `dayOfYear` accepts both date and timestamp inputs.
+select dayOfYear('1500-01-01'), dayOfYear('1582-10-15 13:10:15'), dayOfYear(timestamp'1582-10-15
13:10:15');

Review comment:
       ditto

##########
File path: sql/core/src/test/resources/sql-tests/inputs/timestamp_ltz.sql
##########
@@ -0,0 +1,31 @@
+-- timestamp_ltz literals and constructors
+--CONFIG_DIM1 spark.sql.timestampType=TIMESTAMP_LTZ
+--CONFIG_DIM1 spark.sql.timestampType=TIMESTAMP_NTZ
+
+select timestamp_ltz'2016-12-31 00:12:00', timestamp_ltz'2016-12-31';
+
+select to_timestamp_ltz(null), to_timestamp_ltz('2016-12-31 00:12:00'), to_timestamp_ltz('2016-12-31',
'yyyy-MM-dd');
+-- `to_timestamp_ltz` can also take date input
+select to_timestamp_ltz(to_date(null)), to_timestamp_ltz(to_date('2016-12-31'));
+-- `to_timestamp_ltz` can also take timestamp_ntz input
+select to_timestamp_ltz(to_timestamp_ntz(null)), to_timestamp_ltz(to_timestamp_ntz('2016-12-31
00:12:00'));
+
+-- TimestampLTZ numeric fields constructor
+SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 45.678);
+SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 45.678, 'CET');
+SELECT make_timestamp_ltz(2021, 07, 11, 6, 30, 60.007);
+
+-- [SPARK-31710] TIMESTAMP_SECONDS, TIMESTAMP_MILLISECONDS and TIMESTAMP_MICROSECONDS that
always create timestamp_ltz
+select TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null);

Review comment:
       shall we move these test cases to `timestamp.sql`? So that this file is only for the
functions with `_ltz` and developer can have better judgment where the new cases should be.
   Also, there are overflow test cases below. Even though the current behavior is not related
to ANSI mode, we had better check the ANSI mode as well.

##########
File path: sql/hive-thriftserver/src/test/scala/org/apache/spark/sql/hive/thriftserver/ThriftServerQueryTestSuite.scala
##########
@@ -83,9 +83,12 @@ class ThriftServerQueryTestSuite extends SQLQueryTestSuite with SharedThriftServ
     "postgreSQL/case.sql",
     // SPARK-28624
     "date.sql",
-    "datetime.sql",
+    "timestamp.sql",
     "datetime-legacy.sql",
-    "ansi/datetime.sql",
+    "ansi/date.sql",

Review comment:
       Do we need to ignore all of these tests? I believe some of them can pass.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org


Mime
View raw message