Github user maropu commented on a diff in the pull request:
https://github.com/apache/spark/pull/20433#discussion_r175349346
--- Diff: sql/core/src/test/resources/sql-tests/results/interval.sql.out ---
@@ -0,0 +1,375 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 31
+
+
+-- !query 0
+select
+ '1' second,
+ 2 seconds,
+ '1' minute,
+ 2 minutes,
+ '1' hour,
+ 2 hours,
+ '1' day,
+ 2 days,
+ '1' month,
+ 2 months,
+ '1' year,
+ 2 years
+-- !query 0 schema
+struct<interval 1 seconds:calendarinterval,interval 2
seconds:calendarinterval,interval 1 minutes:calendarinterval,interval 2
minutes:calendarinterval,interval 1 hours:calendarinterval,interval 2
hours:calendarinterval,interval 1 days:calendarinterval,interval 2
days:calendarinterval,interval 1 months:calendarinterval,interval 2
months:calendarinterval,interval 1 years:calendarinterval,interval 2
years:calendarinterval>
+-- !query 0 output
+interval 1 seconds interval 2 seconds interval 1 minutes
interval 2 minutes interval 1 hours interval 2 hours
interval 1 days interval 2 days interval 1 months interval 2 months
interval 1 years interval 2 years
+
+
+-- !query 1
+select
+ interval '10-11' year to month,
+ interval '10' year,
+ interval '11' month
+-- !query 1 schema
+struct<interval 10 years 11 months:calendarinterval,interval 10
years:calendarinterval,interval 11 months:calendarinterval>
+-- !query 1 output
+interval 10 years 11 months interval 10 years interval 11
months
+
+
+-- !query 2
+select
+ '10-11' year to month,
+ '10' year,
+ '11' month
+-- !query 2 schema
+struct<interval 10 years 11 months:calendarinterval,interval 10
years:calendarinterval,interval 11 months:calendarinterval>
+-- !query 2 output
+interval 10 years 11 months interval 10 years interval 11
months
+
+
+-- !query 3
+select
+ interval '10 9:8:7.987654321' day to second,
+ interval '10' day,
+ interval '11' hour,
+ interval '12' minute,
+ interval '13' second,
+ interval '13.123456789' second
+-- !query 3 schema
+struct<interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 987
milliseconds 654 microseconds:calendarinterval,interval 1 weeks 3
days:calendarinterval,interval 11 hours:calendarinterval,interval 12
minutes:calendarinterval,interval 13 seconds:calendarinterval,interval 13
seconds 123 milliseconds 456 microseconds:calendarinterval>
+-- !query 3 output
+interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 987 milliseconds 654
microseconds interval 1 weeks 3 days interval 11 hours interval 12
minutes interval 13 seconds interval 13 seconds 123 milliseconds 456
microseconds
+
+
+-- !query 4
+select
+ '10 9:8:7.987654321' day to second,
+ '10' day,
+ '11' hour,
+ '12' minute,
+ '13' second,
+ '13.123456789' second
+-- !query 4 schema
+struct<interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 987
milliseconds 654 microseconds:calendarinterval,interval 1 weeks 3
days:calendarinterval,interval 11 hours:calendarinterval,interval 12
minutes:calendarinterval,interval 13 seconds:calendarinterval,interval 13
seconds 123 milliseconds 456 microseconds:calendarinterval>
+-- !query 4 output
+interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 987 milliseconds 654
microseconds interval 1 weeks 3 days interval 11 hours interval 12
minutes interval 13 seconds interval 13 seconds 123 milliseconds 456
microseconds
+
+
+-- !query 5
+create temporary view interval_arithmetic as
+ select CAST(dateval AS date), CAST(tsval AS timestamp) from values
+ ('2012-01-01', '2012-01-01')
+ as interval_arithmetic(dateval, tsval)
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+select
+ dateval,
+ dateval - interval '2-2' year to month,
+ dateval - interval '-2-2' year to month,
+ dateval + interval '2-2' year to month,
+ dateval + interval '-2-2' year to month,
+ - interval '2-2' year to month + dateval,
+ interval '2-2' year to month + dateval
+from interval_arithmetic
+-- !query 6 schema
+struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - interval 2 years 2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) - interval -2 years -2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 2 years 2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval -2 years -2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- interval 2 years 2
months) AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 2 years 2
months AS DATE):date>
+-- !query 6 output
+2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
+
+
+-- !query 7
+select
+ dateval,
+ dateval - '2-2' year to month,
+ dateval - '-2-2' year to month,
+ dateval + '2-2' year to month,
+ dateval + '-2-2' year to month,
+ - '2-2' year to month + dateval,
+ '2-2' year to month + dateval
+from interval_arithmetic
+-- !query 7 schema
+struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - interval 2 years 2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) - interval -2 years -2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 2 years 2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval -2 years -2
months AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- interval 2 years 2
months) AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 2 years 2
months AS DATE):date>
+-- !query 7 output
+2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
+
+
+-- !query 8
+select
+ tsval,
+ tsval - interval '2-2' year to month,
+ tsval - interval '-2-2' year to month,
+ tsval + interval '2-2' year to month,
+ tsval + interval '-2-2' year to month,
+ - interval '2-2' year to month + tsval,
+ interval '2-2' year to month + tsval
+from interval_arithmetic
+-- !query 8 schema
+struct<tsval:timestamp,CAST(tsval - interval 2 years 2 months AS
TIMESTAMP):timestamp,CAST(tsval - interval -2 years -2 months AS
TIMESTAMP):timestamp,CAST(tsval + interval 2 years 2 months AS
TIMESTAMP):timestamp,CAST(tsval + interval -2 years -2 months AS
TIMESTAMP):timestamp,CAST(tsval + (- interval 2 years 2 months) AS
TIMESTAMP):timestamp,CAST(tsval + interval 2 years 2 months AS
TIMESTAMP):timestamp>
+-- !query 8 output
+2012-01-01 00:00:00 2009-11-01 00:00:00 2014-03-01 00:00:00
2014-03-01 00:00:00 2009-11-01 00:00:00 2009-11-01 00:00:00
2014-03-01 00:00:00
+
+
+-- !query 9
+select
+ tsval,
+ tsval - '2-2' year to month,
+ tsval - '-2-2' year to month,
+ tsval + '2-2' year to month,
+ tsval + '-2-2' year to month,
+ - '2-2' year to month + tsval,
+ '2-2' year to month + tsval
+from interval_arithmetic
+-- !query 9 schema
+struct<tsval:timestamp,CAST(tsval - interval 2 years 2 months AS
TIMESTAMP):timestamp,CAST(tsval - interval -2 years -2 months AS
TIMESTAMP):timestamp,CAST(tsval + interval 2 years 2 months AS
TIMESTAMP):timestamp,CAST(tsval + interval -2 years -2 months AS
TIMESTAMP):timestamp,CAST(tsval + (- interval 2 years 2 months) AS
TIMESTAMP):timestamp,CAST(tsval + interval 2 years 2 months AS
TIMESTAMP):timestamp>
+-- !query 9 output
+2012-01-01 00:00:00 2009-11-01 00:00:00 2014-03-01 00:00:00
2014-03-01 00:00:00 2009-11-01 00:00:00 2009-11-01 00:00:00
2014-03-01 00:00:00
+
+
+-- !query 10
+select
+ interval '2-2' year to month + interval '3-3' year to month,
+ interval '2-2' year to month - interval '3-3' year to month
+from interval_arithmetic
+-- !query 10 schema
+struct<(interval 2 years 2 months + interval 3 years 3
months):calendarinterval,(interval 2 years 2 months - interval 3 years 3
months):calendarinterval>
+-- !query 10 output
+interval 5 years 5 months interval -1 years -1 months
+
+
+-- !query 11
+select
+ '2-2' year to month + '3-3' year to month,
+ '2-2' year to month - '3-3' year to month
+from interval_arithmetic
+-- !query 11 schema
+struct<(interval 2 years 2 months + interval 3 years 3
months):calendarinterval,(interval 2 years 2 months - interval 3 years 3
months):calendarinterval>
+-- !query 11 output
+interval 5 years 5 months interval -1 years -1 months
+
+
+-- !query 12
+select
+ dateval,
+ dateval - interval '99 11:22:33.123456789' day to second,
+ dateval - interval '-99 11:22:33.123456789' day to second,
+ dateval + interval '99 11:22:33.123456789' day to second,
+ dateval + interval '-99 11:22:33.123456789' day to second,
+ -interval '99 11:22:33.123456789' day to second + dateval,
+ interval '99 11:22:33.123456789' day to second + dateval
+from interval_arithmetic
+-- !query 12 schema
+struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - interval 14 weeks 1
days 11 hours 22 minutes 33 seconds 123 milliseconds 456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) - interval -14 weeks -1 days -11
hours -22 minutes -33 seconds -123 milliseconds -456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 14 weeks 1 days 11 hours
22 minutes 33 seconds 123 milliseconds 456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval -14 weeks -1 days -11
hours -22 minutes -33 seconds -123 milliseconds -456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- interval 14 weeks 1 days 11
hours 22 minutes 33 seconds 123 milliseconds 456 microseconds) AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 14 weeks 1 days 11 hours
22 minutes 33 seconds 123 milliseconds 456 microseconds AS DATE):date>
+-- !query 12 output
+2012-01-01 2011-09-23 2012-04-09 2012-04-09 2011-09-23
2011-09-23 2012-04-09
+
+
+-- !query 13
+select
+ dateval,
+ dateval - '99 11:22:33.123456789' day to second,
+ dateval - '-99 11:22:33.123456789' day to second,
+ dateval + '99 11:22:33.123456789' day to second,
+ dateval + '-99 11:22:33.123456789' day to second,
+ - '99 11:22:33.123456789' day to second + dateval,
+ '99 11:22:33.123456789' day to second + dateval
+from interval_arithmetic
+-- !query 13 schema
+struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - interval 14 weeks 1
days 11 hours 22 minutes 33 seconds 123 milliseconds 456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) - interval -14 weeks -1 days -11
hours -22 minutes -33 seconds -123 milliseconds -456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 14 weeks 1 days 11 hours
22 minutes 33 seconds 123 milliseconds 456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval -14 weeks -1 days -11
hours -22 minutes -33 seconds -123 milliseconds -456 microseconds AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- interval 14 weeks 1 days 11
hours 22 minutes 33 seconds 123 milliseconds 456 microseconds) AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + interval 14 weeks 1 days 11 hours
22 minutes 33 seconds 123 milliseconds 456 microseconds AS DATE):date>
+-- !query 13 output
+2012-01-01 2011-09-23 2012-04-09 2012-04-09 2011-09-23
2011-09-23 2012-04-09
+
+
+-- !query 14
+select
+ tsval,
+ tsval - interval '99 11:22:33.123456789' day to second,
+ tsval - interval '-99 11:22:33.123456789' day to second,
+ tsval + interval '99 11:22:33.123456789' day to second,
+ tsval + interval '-99 11:22:33.123456789' day to second,
+ -interval '99 11:22:33.123456789' day to second + tsval,
+ interval '99 11:22:33.123456789' day to second + tsval
+from interval_arithmetic
+-- !query 14 schema
+struct<tsval:timestamp,CAST(tsval - interval 14 weeks 1 days 11 hours 22
minutes 33 seconds 123 milliseconds 456 microseconds AS
TIMESTAMP):timestamp,CAST(tsval - interval -14 weeks -1 days -11 hours -22
minutes -33 seconds -123 milliseconds -456 microseconds AS
TIMESTAMP):timestamp,CAST(tsval + interval 14 weeks 1 days 11 hours 22 minutes
33 seconds 123 milliseconds 456 microseconds AS TIMESTAMP):timestamp,CAST(tsval
+ interval -14 weeks -1 days -11 hours -22 minutes -33 seconds -123
milliseconds -456 microseconds AS TIMESTAMP):timestamp,CAST(tsval + (- interval
14 weeks 1 days 11 hours 22 minutes 33 seconds 123 milliseconds 456
microseconds) AS TIMESTAMP):timestamp,CAST(tsval + interval 14 weeks 1 days 11
hours 22 minutes 33 seconds 123 milliseconds 456 microseconds AS
TIMESTAMP):timestamp>
+-- !query 14 output
+2012-01-01 00:00:00 2011-09-23 13:37:26.876544 2012-04-09
12:22:33.123456 2012-04-09 12:22:33.123456 2011-09-23 13:37:26.876544
2011-09-23 13:37:26.876544 2012-04-09 12:22:33.123456
+
+
+-- !query 15
+select
+ tsval,
+ tsval - '99 11:22:33.123456789' day to second,
+ tsval - '-99 11:22:33.123456789' day to second,
+ tsval + '99 11:22:33.123456789' day to second,
+ tsval + '-99 11:22:33.123456789' day to second,
+ - '99 11:22:33.123456789' day to second + tsval,
+ '99 11:22:33.123456789' day to second + tsval
+from interval_arithmetic
+-- !query 15 schema
+struct<tsval:timestamp,CAST(tsval - interval 14 weeks 1 days 11 hours 22
minutes 33 seconds 123 milliseconds 456 microseconds AS
TIMESTAMP):timestamp,CAST(tsval - interval -14 weeks -1 days -11 hours -22
minutes -33 seconds -123 milliseconds -456 microseconds AS
TIMESTAMP):timestamp,CAST(tsval + interval 14 weeks 1 days 11 hours 22 minutes
33 seconds 123 milliseconds 456 microseconds AS TIMESTAMP):timestamp,CAST(tsval
+ interval -14 weeks -1 days -11 hours -22 minutes -33 seconds -123
milliseconds -456 microseconds AS TIMESTAMP):timestamp,CAST(tsval + (- interval
14 weeks 1 days 11 hours 22 minutes 33 seconds 123 milliseconds 456
microseconds) AS TIMESTAMP):timestamp,CAST(tsval + interval 14 weeks 1 days 11
hours 22 minutes 33 seconds 123 milliseconds 456 microseconds AS
TIMESTAMP):timestamp>
+-- !query 15 output
+2012-01-01 00:00:00 2011-09-23 13:37:26.876544 2012-04-09
12:22:33.123456 2012-04-09 12:22:33.123456 2011-09-23 13:37:26.876544
2011-09-23 13:37:26.876544 2012-04-09 12:22:33.123456
+
+
+-- !query 16
+select
+ interval '99 11:22:33.123456789' day to second + interval '10
9:8:7.123456789' day to second,
+ interval '99 11:22:33.123456789' day to second - interval '10
9:8:7.123456789' day to second
+from interval_arithmetic
+-- !query 16 schema
+struct<(interval 14 weeks 1 days 11 hours 22 minutes 33 seconds 123
milliseconds 456 microseconds + interval 1 weeks 3 days 9 hours 8 minutes 7
seconds 123 milliseconds 456 microseconds):calendarinterval,(interval 14 weeks
1 days 11 hours 22 minutes 33 seconds 123 milliseconds 456 microseconds -
interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 123 milliseconds 456
microseconds):calendarinterval>
+-- !query 16 output
+interval 15 weeks 4 days 20 hours 30 minutes 40 seconds 246 milliseconds
912 microseconds interval 12 weeks 5 days 2 hours 14 minutes 26 seconds
+
+
+-- !query 17
+select
+ '99 11:22:33.123456789' day to second + '10 9:8:7.123456789' day to
second,
+ '99 11:22:33.123456789' day to second - '10 9:8:7.123456789' day to
second
+from interval_arithmetic
+-- !query 17 schema
+struct<(interval 14 weeks 1 days 11 hours 22 minutes 33 seconds 123
milliseconds 456 microseconds + interval 1 weeks 3 days 9 hours 8 minutes 7
seconds 123 milliseconds 456 microseconds):calendarinterval,(interval 14 weeks
1 days 11 hours 22 minutes 33 seconds 123 milliseconds 456 microseconds -
interval 1 weeks 3 days 9 hours 8 minutes 7 seconds 123 milliseconds 456
microseconds):calendarinterval>
+-- !query 17 output
+interval 15 weeks 4 days 20 hours 30 minutes 40 seconds 246 milliseconds
912 microseconds interval 12 weeks 5 days 2 hours 14 minutes 26 seconds
+
+
+-- !query 18
+select 30 day
+-- !query 18 schema
+struct<interval 4 weeks 2 days:calendarinterval>
+-- !query 18 output
+interval 4 weeks 2 days
+
+
+-- !query 19
+select 30 day day
+-- !query 19 schema
+struct<day:calendarinterval>
+-- !query 19 output
+interval 4 weeks 2 days
+
+
+-- !query 20
+select 30 day day day
+-- !query 20 schema
+struct<>
+-- !query 20 output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+extraneous input 'day' expecting <EOF>(line 1, pos 18)
+
+== SQL ==
+select 30 day day day
+------------------^^^
+
+
+-- !query 21
+select date '2012-01-01' - 30 day
+-- !query 21 schema
+struct<CAST(CAST(DATE '2012-01-01' AS TIMESTAMP) - interval 4 weeks 2 days
AS DATE):date>
+-- !query 21 output
+2011-12-02
+
+
+-- !query 22
+select date '2012-01-01' - 30 day day
+-- !query 22 schema
+struct<day:date>
+-- !query 22 output
+2011-12-02
+
+
+-- !query 23
+select date '2012-01-01' - 30 day day day
+-- !query 23 schema
+struct<>
+-- !query 23 output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+extraneous input 'day' expecting <EOF>(line 1, pos 38)
+
+== SQL ==
+select date '2012-01-01' - 30 day day day
+--------------------------------------^^^
+
+
+-- !query 24
+select date '2012-01-01' + '-30' day
+-- !query 24 schema
+struct<CAST(CAST(DATE '2012-01-01' AS TIMESTAMP) + interval -4 weeks -2
days AS DATE):date>
+-- !query 24 output
+2011-12-02
+
+
+-- !query 25
+select date '2012-01-01' + interval '-30' day
+-- !query 25 schema
+struct<CAST(CAST(DATE '2012-01-01' AS TIMESTAMP) + interval -4 weeks -2
days AS DATE):date>
+-- !query 25 output
+2011-12-02
+
+
+-- !query 26
+select date '2012-01-01' + interval (-30) day
+-- !query 26 schema
+struct<>
+-- !query 26 output
+org.apache.spark.sql.AnalysisException
+Undefined function: 'interval'. This function is neither a registered
temporary function nor a permanent function registered in the database
'default'.; line 1 pos 27
+
+
+-- !query 27
+select date '2012-01-01' + (-30) day
+-- !query 27 schema
+struct<>
+-- !query 27 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(DATE '2012-01-01' + -30)' due to data type mismatch:
differing types in '(DATE '2012-01-01' + -30)' (date and int).; line 1 pos 7
+
+
+-- !query 28
+create temporary view t as select * from values (1), (2) as t(a)
+-- !query 28 schema
+struct<>
+-- !query 28 output
+
+
+
+-- !query 29
+select date '2012-01-01' + interval (a + 1) day from t
+-- !query 29 schema
+struct<>
+-- !query 29 output
+org.apache.spark.sql.AnalysisException
+Undefined function: 'interval'. This function is neither a registered
temporary function nor a permanent function registered in the database
'default'.; line 1 pos 27
+
+
+-- !query 30
+select date '2012-01-01' + (a + 1) day from t
+-- !query 30 schema
+struct<>
+-- !query 30 output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(DATE '2012-01-01' + (t.`a` + 1))' due to data type
mismatch: differing types in '(DATE '2012-01-01' + (t.`a` + 1))' (date and
int).; line 1 pos 7
--- End diff --
ok, I'll add tests to check
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
