You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by we...@apache.org on 2021/08/12 04:53:15 UTC
[spark] branch branch-3.2 updated: [SPARK-36479][SQL][TEST] Improve
datetime test coverage in SQL files
This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-3.2
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.2 by this push:
new 3e3c33d [SPARK-36479][SQL][TEST] Improve datetime test coverage in SQL files
3e3c33d is described below
commit 3e3c33d2c96bc031a3f1d88e0f8801dbb1d41b9e
Author: Wenchen Fan <we...@databricks.com>
AuthorDate: Thu Aug 12 12:51:50 2021 +0800
[SPARK-36479][SQL][TEST] Improve datetime test coverage in SQL files
### What changes were proposed in this pull request?
This PR adds more datetime tests in `date.sql` and `timestamp.sql`, especially for string promotion.
### Why are the changes needed?
improve test coverage
### Does this PR introduce _any_ user-facing change?
no
### How was this patch tested?
N/A
Closes #33707 from cloud-fan/test.
Authored-by: Wenchen Fan <we...@databricks.com>
Signed-off-by: Wenchen Fan <we...@databricks.com>
(cherry picked from commit 00a4364f3869a844168f20374d2246f6bfc092e3)
Signed-off-by: Wenchen Fan <we...@databricks.com>
---
.../src/test/resources/sql-tests/inputs/date.sql | 67 +++--
.../test/resources/sql-tests/inputs/interval.sql | 114 +++-----
.../test/resources/sql-tests/inputs/timestamp.sql | 46 +--
.../resources/sql-tests/results/ansi/date.sql.out | 204 ++++++++-----
.../sql-tests/results/ansi/interval.sql.out | 225 ++++++++++-----
.../sql-tests/results/ansi/timestamp.sql.out | 101 ++++---
.../test/resources/sql-tests/results/date.sql.out | 212 +++++++++-----
.../sql-tests/results/datetime-legacy.sql.out | 316 +++++++++++++--------
.../resources/sql-tests/results/interval.sql.out | 225 ++++++++++-----
.../resources/sql-tests/results/timestamp.sql.out | 104 ++++---
.../results/timestampNTZ/timestamp-ansi.sql.out | 101 ++++---
.../results/timestampNTZ/timestamp.sql.out | 104 ++++---
12 files changed, 1135 insertions(+), 684 deletions(-)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/date.sql b/sql/core/src/test/resources/sql-tests/inputs/date.sql
index b32bb6a..bfa96ed1e 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/date.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/date.sql
@@ -1,5 +1,7 @@
-- date literals, functions and operations
+create temporary view date_view as select '2011-11-11' date_str, '1' int_str;
+
select date '2019-01-01\t';
select date '2020-01-01中文';
@@ -58,9 +60,10 @@ select next_day(null, "Mon");
select next_day(null, "xx");
-- date add
+select date_add(date'2011-11-11', 1);
+select date_add('2011-11-11', 1);
select date_add('2011-11-11', 1Y);
select date_add('2011-11-11', 1S);
-select date_add('2011-11-11', 1);
-- invalid cases: the second parameter can only be byte/short/int
select date_add('2011-11-11', 1L);
select date_add('2011-11-11', 1.0);
@@ -71,47 +74,65 @@ select date_add('2011-11-11', '1.2');
-- null input leads to null result.
select date_add(null, 1);
select date_add(date'2011-11-11', null);
--- `date_add` accepts both date and timestamp ltz/ntz inputs.
-select date_add(date'2011-11-11', 1);
+-- `date_add` accepts both date and timestamp ltz/ntz inputs (non-ANSI mode).
select date_add(timestamp_ltz'2011-11-11 12:12:12', 1);
select date_add(timestamp_ntz'2011-11-11 12:12:12', 1);
-- date sub
select date_sub(date'2011-11-11', 1);
+select date_sub('2011-11-11', 1);
+select date_sub('2011-11-11', 1Y);
+select date_sub('2011-11-11', 1S);
+-- invalid cases: the second parameter can only be byte/short/int
+select date_sub('2011-11-11', 1L);
+select date_sub('2011-11-11', 1.0);
+select date_sub('2011-11-11', 1E1);
-- the second parameter can be a string literal if it can be parsed to int
select date_sub(date'2011-11-11', '1');
select date_sub(date'2011-11-11', '1.2');
--- `date_sub` accepts both date and timestamp ltz/ntz inputs.
-select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1), date_sub(timestamp_ntz'2011-11-11 12:12:12', 1);
-- null input leads to null result.
select date_sub(null, 1);
select date_sub(date'2011-11-11', null);
+-- `date_sub` accepts both date and timestamp ltz/ntz inputs (non-ANSI mode).
+select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1);
+select date_sub(timestamp_ntz'2011-11-11 12:12:12', 1);
-- date add/sub with non-literal string column
-create temp view v as select '1' str;
-select date_add('2011-11-11', str) from v;
-select date_sub('2011-11-11', str) from v;
-
--- non-literal string column add/sub with integer
-create temp view v2 as select '2011-11-11' str;
-select date_add(str, 1) from v2;
-select date_sub(str, 1) from v2;
+select date_add('2011-11-11', int_str) from date_view;
+select date_sub('2011-11-11', int_str) from date_view;
+select date_add(date_str, 1) from date_view;
+select date_sub(date_str, 1) from date_view;
--- date add/sub operations
-select date'2011-11-11' + 1E1;
-select date'2011-11-11' + '1';
-select null + date '2001-09-28';
+-- date +/- number
+select date '2011-11-11' + 1E1;
select date '2001-09-28' + 7Y;
select 7S + date '2001-09-28';
select date '2001-10-01' - 7;
-select date '2001-10-01' - '7';
-select date '2001-09-28' + null;
+
+-- date - date
+select date '2001-10-01' - date '2001-09-28';
+-- if one side is string/null literal, promote it to date type.
+select date '2001-10-01' - '2001-09-28';
+select '2001-10-01' - date '2001-09-28';
select date '2001-09-28' - null;
-select '2011-11-11' - interval '2' day;
select null - date '2019-10-06';
-select date '2001-10-01' - date '2001-09-28';
-select '2011-11-11 11:11:11' - date'2011-11-11';
-select str - date'2011-11-11' from v2;
+-- invalid: non-literal string column
+select date_str - date '2001-09-28' from date_view;
+select date '2001-09-28' - date_str from date_view;
+
+-- invalid: date + string/null literal
+select date'2011-11-11' + '1';
+select '1' + date'2011-11-11';
+select date'2011-11-11' + null;
+select null + date'2011-11-11';
+
+-- date +/- interval and interval + date
+select date '2012-01-01' - interval '2-2' year to month,
+ date '2011-11-11' - interval '2' day,
+ date '2012-01-01' + interval '-2-2' year to month,
+ date '2011-11-11' + interval '-2' month,
+ - interval '2-2' year to month + date '2012-01-01',
+ interval '-2' day + date '2011-11-11';
-- Unsupported narrow text style
select to_date('26/October/2015', 'dd/MMMMM/yyyy');
diff --git a/sql/core/src/test/resources/sql-tests/inputs/interval.sql b/sql/core/src/test/resources/sql-tests/inputs/interval.sql
index a16d152..80df45d 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/interval.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/interval.sql
@@ -145,75 +145,54 @@ SELECT INTERVAL '178956970-8' YEAR TO MONTH;
SELECT INTERVAL '-178956970-8' YEAR TO MONTH;
SELECT INTERVAL -'178956970-8' YEAR TO MONTH;
--- Interval year-month arithmetic
-
-create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp), dateval as strval from values
- ('2012-01-01', '2012-01-01')
- as interval_arithmetic(dateval, tsval);
-
-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;
-
-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;
-
-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;
-
--- Interval day-time arithmetic
-
+-- interval +/- interval
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;
-
+ interval '2-2' year to month + interval '3' month,
+ interval '2' year - interval '3-3' year to month,
+ interval '99 11:22:33.123456789' day to second + interval '10 9:8' day to minute,
+ interval '22:33.123456789' minute to second - interval '10' day;
+-- if one side is string/null literal, promote it to interval type.
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;
-
--- datetimes(in string representation) + intervals
+ interval '2' year + '3-3 year to month',
+ interval '2' year - '3 month',
+ '3-2 year to month' + interval '2-2' year to month,
+ '3 year' - interval '2-2' year to month,
+ interval '99 11:22:33.123456789' day to second + '12:12 hour to second',
+ interval '99 11:22:33.123456789' day to second - '12 hour',
+ '4 day' + interval '10' day,
+ '4 22 day to hour' - interval '10' day;
select
- strval,
- strval - interval '99 11:22:33.123456789' day to second,
- strval - interval '-99 11:22:33.123456789' day to second,
- strval + interval '99 11:22:33.123456789' day to second,
- strval + interval '-99 11:22:33.123456789' day to second,
- -interval '99 11:22:33.123456789' day to second + strval,
- interval '99 11:22:33.123456789' day to second + strval
-from interval_arithmetic;
-
-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;
+ interval '2' year + null,
+ interval '2' year - null,
+ interval '2' hour + null,
+ interval '2' hour - null,
+ null + interval '2' year,
+ null - interval '2' year,
+ null + interval '2' hour,
+ null - interval '2' hour;
+-- invalid: malformed interval string
+select interval '2' year + '3-3';
+select interval '2' year - '4';
+select '4 11:11' - interval '4 22:12' day to minute;
+select '4 12:12:12' + interval '4 22:12' day to minute;
+-- invalid: non-literal string column
+create temporary view interval_view as select '1' str;
+select interval '2' year + str from interval_view;
+select interval '2' year - str from interval_view;
+select str - interval '4 22:12' day to minute from interval_view;
+select str + interval '4 22:12' day to minute from interval_view;
+
+-- invalid: mixed year-month and day-time interval
+select interval '2-2' year to month + interval '3' day;
+select interval '3' day + interval '2-2' year to month;
+select interval '2-2' year to month - interval '3' day;
+select interval '3' day - interval '2-2' year to month;
+
+-- invalid: number +/- interval
+select 1 - interval '2' second;
+select 1 + interval '2' month;
+select interval '2' second + 1;
+select interval '2' month - 1;
-- control characters as white spaces
select interval '\t interval 1 day';
@@ -229,8 +208,7 @@ select interval '中文 interval 1 day';
select interval 'interval中文 1 day';
select interval 'interval 1中文day';
-
--- interval overflow if (ansi) exception else NULL
+-- interval overflow: if (ansi) exception else NULL
select -(a) from values (interval '-2147483648 months', interval '2147483647 months') t(a, b);
select a - b from values (interval '-2147483648 months', interval '2147483647 months') t(a, b);
select b + interval '1 month' from values (interval '-2147483648 months', interval '2147483647 months') t(a, b);
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
index 180c71e..fc8416f 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
@@ -94,25 +94,37 @@ select to_timestamp("02-29", "MM-dd");
select to_timestamp("2019 40", "yyyy mm");
select to_timestamp("2019 10:10:10", "yyyy hh:mm:ss");
--- timestamp add/sub operations
-select timestamp'2011-11-11 11:11:11' + interval '2' day;
-select timestamp'2011-11-11 11:11:11' - interval '2' day;
-select timestamp'2011-11-11 11:11:11' + interval '2' second;
-select timestamp'2011-11-11 11:11:11' - interval '2' second;
-select '2011-11-11 11:11:11' - interval '2' second;
-select '1' - interval '2' second;
-select 1 - interval '2' second;
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10';
--- non-literal string column subtract interval
-create temporary view v3 as select '2011-11-11 11:11:11' str;
-select str - interval '2' second from v3;
-select str - date'2011-11-11' from v3;
-select str - timestamp'2011-11-11 11:11:10' from v3;
--- analyzer will cast date to timestamp automatically
+-- timestamp - timestamp
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10';
select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678';
select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01';
-select timestamp'2019-10-06 10:11:12.345678' - null;
-select null - timestamp'2019-10-06 10:11:12.345678';
+-- if one side is string/null literal, promote it to timestamp type.
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10';
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10';
+select timestamp'2011-11-11 11:11:11' - null;
+select null - timestamp'2011-11-11 11:11:11';
+-- invalid: non-literal string column
+create temporary view ts_view as select '2011-11-11 11:11:11' str;
+select str - timestamp'2011-11-11 11:11:11' from ts_view;
+select timestamp'2011-11-11 11:11:11' - str from ts_view;
+
+-- invalid: timestamp + string/null literal
+select timestamp'2011-11-11 11:11:11' + '1';
+select '1' + timestamp'2011-11-11 11:11:11';
+select timestamp'2011-11-11 11:11:11' + null;
+select null + timestamp'2011-11-11 11:11:11';
+
+-- timestamp +/- interval and interval + timestamp
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11';
+-- promote date to timestamp if the interval is hour to second.
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01';
-- Unsupported narrow text style
select to_timestamp('2019-10-06 A', 'yyyy-MM-dd GGGGG');
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out b/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
index ab9c779..46eaa68 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/date.sql.out
@@ -1,5 +1,13 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 69
+-- Number of queries: 77
+
+
+-- !query
+create temporary view date_view as select '2011-11-11' date_str, '1' int_str
+-- !query schema
+struct<>
+-- !query output
+
-- !query
@@ -252,7 +260,15 @@ NULL
-- !query
-select date_add('2011-11-11', 1Y)
+select date_add(date'2011-11-11', 1)
+-- !query schema
+struct<date_add(DATE '2011-11-11', 1):date>
+-- !query output
+2011-11-12
+
+
+-- !query
+select date_add('2011-11-11', 1)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -260,7 +276,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1S)
+select date_add('2011-11-11', 1Y)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -268,7 +284,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1)
+select date_add('2011-11-11', 1S)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -336,14 +352,6 @@ NULL
-- !query
-select date_add(date'2011-11-11', 1)
--- !query schema
-struct<date_add(DATE '2011-11-11', 1):date>
--- !query output
-2011-11-12
-
-
--- !query
select date_add(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
struct<>
@@ -370,123 +378,150 @@ struct<date_sub(DATE '2011-11-11', 1):date>
-- !query
-select date_sub(date'2011-11-11', '1')
+select date_sub('2011-11-11', 1)
-- !query schema
-struct<date_sub(DATE '2011-11-11', 1):date>
+struct<date_sub(2011-11-11, 1):date>
-- !query output
2011-11-10
-- !query
-select date_sub(date'2011-11-11', '1.2')
+select date_sub('2011-11-11', 1Y)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1S)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1L)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-The second argument of 'date_sub' function needs to be an integer.
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1L)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1L' is of bigint type.; line 1 pos 7
-- !query
-select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1), date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
+select date_sub('2011-11-11', 1.0)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(TIMESTAMP '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP '2011-11-11 12:12:12'' is of timestamp type.; line 1 pos 7
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1.0BD)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1.0BD' is of decimal(2,1) type.; line 1 pos 7
-- !query
-select date_sub(null, 1)
+select date_sub('2011-11-11', 1E1)
-- !query schema
-struct<date_sub(NULL, 1):date>
+struct<>
-- !query output
-NULL
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 10.0D)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '10.0D' is of double type.; line 1 pos 7
-- !query
-select date_sub(date'2011-11-11', null)
+select date_sub(date'2011-11-11', '1')
-- !query schema
-struct<date_sub(DATE '2011-11-11', NULL):date>
+struct<date_sub(DATE '2011-11-11', 1):date>
-- !query output
-NULL
+2011-11-10
-- !query
-create temp view v as select '1' str
+select date_sub(date'2011-11-11', '1.2')
-- !query schema
struct<>
-- !query output
+org.apache.spark.sql.AnalysisException
+The second argument of 'date_sub' function needs to be an integer.
+-- !query
+select date_sub(null, 1)
+-- !query schema
+struct<date_sub(NULL, 1):date>
+-- !query output
+NULL
+
-- !query
-select date_add('2011-11-11', str) from v
+select date_sub(date'2011-11-11', null)
-- !query schema
-struct<>
+struct<date_sub(DATE '2011-11-11', NULL):date>
-- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+NULL
-- !query
-select date_sub('2011-11-11', str) from v
+select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+cannot resolve 'date_sub(TIMESTAMP '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP '2011-11-11 12:12:12'' is of timestamp type.; line 1 pos 7
-- !query
-create temp view v2 as select '2011-11-11' str
+select date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(TIMESTAMP_NTZ '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP_NTZ '2011-11-11 12:12:12'' is of timestamp_ntz type.; line 1 pos 7
-- !query
-select date_add(str, 1) from v2
+select date_add('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(v2.str, 1)' due to data type mismatch: argument 1 requires date type, however, 'v2.str' is of string type.; line 1 pos 7
+cannot resolve 'date_add(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-select date_sub(str, 1) from v2
+select date_sub('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(v2.str, 1)' due to data type mismatch: argument 1 requires date type, however, 'v2.str' is of string type.; line 1 pos 7
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-select date'2011-11-11' + 1E1
+select date_add(date_str, 1) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(DATE '2011-11-11', 10.0D)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '10.0D' is of double type.; line 1 pos 7
+cannot resolve 'date_add(date_view.date_str, 1)' due to data type mismatch: argument 1 requires date type, however, 'date_view.date_str' is of string type.; line 1 pos 7
-- !query
-select date'2011-11-11' + '1'
+select date_sub(date_str, 1) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DATE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DATE)' is of date type.; line 1 pos 7
+cannot resolve 'date_sub(date_view.date_str, 1)' due to data type mismatch: argument 1 requires date type, however, 'date_view.date_str' is of string type.; line 1 pos 7
-- !query
-select null + date '2001-09-28'
+select date '2011-11-11' + 1E1
-- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
+struct<>
-- !query output
-NULL
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', 10.0D)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '10.0D' is of double type.; line 1 pos 7
-- !query
@@ -514,36 +549,35 @@ struct<date_sub(DATE '2001-10-01', 7):date>
-- !query
-select date '2001-10-01' - '7'
+select date '2001-10-01' - date '2001-09-28'
-- !query schema
-struct<>
+struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
-- !query output
-java.time.DateTimeException
-Cannot cast 7 to DateType.
+3 00:00:00.000000000
-- !query
-select date '2001-09-28' + null
+select date '2001-10-01' - '2001-09-28'
-- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
+struct<(DATE '2001-10-01' - 2001-09-28):interval day>
-- !query output
-NULL
+3 00:00:00.000000000
-- !query
-select date '2001-09-28' - null
+select '2001-10-01' - date '2001-09-28'
-- !query schema
-struct<date_sub(DATE '2001-09-28', NULL):date>
+struct<(2001-10-01 - DATE '2001-09-28'):interval day>
-- !query output
-NULL
+3 00:00:00.000000000
-- !query
-select '2011-11-11' - interval '2' day
+select date '2001-09-28' - null
-- !query schema
-struct<2011-11-11 - INTERVAL '2' DAY:string>
+struct<date_sub(DATE '2001-09-28', NULL):date>
-- !query output
-2011-11-09 00:00:00
+NULL
-- !query
@@ -555,28 +589,68 @@ NULL
-- !query
-select date '2001-10-01' - date '2001-09-28'
+select date_str - date '2001-09-28' from date_view
-- !query schema
-struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
+struct<>
-- !query output
-3 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(date_view.date_str - DATE '2001-09-28')' due to data type mismatch: argument 1 requires date type, however, 'date_view.date_str' is of string type.; line 1 pos 7
+
+
+-- !query
+select date '2001-09-28' - date_str from date_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(DATE '2001-09-28' - date_view.date_str)' due to data type mismatch: differing types in '(DATE '2001-09-28' - date_view.date_str)' (date and string).; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - date'2011-11-11'
+select date'2011-11-11' + '1'
-- !query schema
-struct<(2011-11-11 11:11:11 - DATE '2011-11-11'):interval day>
+struct<>
-- !query output
-0 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DATE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DATE)' is of date type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v2
+select '1' + date'2011-11-11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v2.str - DATE '2011-11-11')' due to data type mismatch: argument 1 requires date type, however, 'v2.str' is of string type.; line 1 pos 7
+cannot resolve 'date_add(CAST('1' AS DATE), DATE '2011-11-11')' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'DATE '2011-11-11'' is of date type.; line 1 pos 7
+
+
+-- !query
+select date'2011-11-11' + null
+-- !query schema
+struct<date_add(DATE '2011-11-11', NULL):date>
+-- !query output
+NULL
+
+
+-- !query
+select null + date'2011-11-11'
+-- !query schema
+struct<date_add(DATE '2011-11-11', NULL):date>
+-- !query output
+NULL
+
+
+-- !query
+select date '2012-01-01' - interval '2-2' year to month,
+ date '2011-11-11' - interval '2' day,
+ date '2012-01-01' + interval '-2-2' year to month,
+ date '2011-11-11' + interval '-2' month,
+ - interval '2-2' year to month + date '2012-01-01',
+ interval '-2' day + date '2011-11-11'
+-- !query schema
+struct<DATE '2012-01-01' - INTERVAL '2-2' YEAR TO MONTH:date,date_add(DATE '2011-11-11', (- extractansiintervaldays(INTERVAL '2' DAY))):date,DATE '2012-01-01' + INTERVAL '-2-2' YEAR TO MONTH:date,DATE '2011-11-11' + INTERVAL '-2' MONTH:date,DATE '2012-01-01' + (- INTERVAL '2-2' YEAR TO MONTH):date,date_add(DATE '2011-11-11', extractansiintervaldays(INTERVAL '-2' DAY)):date>
+-- !query output
+2009-11-01 2011-11-09 2009-11-01 2011-09-11 2009-11-01 2011-11-09
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out b/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
index 8595498..2b79fef 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/interval.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 211
+-- Number of queries: 223
-- !query
@@ -1136,117 +1136,200 @@ struct<INTERVAL '-178956970-8' YEAR TO MONTH:interval year to month>
-- !query
-create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp), dateval as strval from values
- ('2012-01-01', '2012-01-01')
- as interval_arithmetic(dateval, tsval)
+select
+ interval '2-2' year to month + interval '3' month,
+ interval '2' year - interval '3-3' year to month,
+ interval '99 11:22:33.123456789' day to second + interval '10 9:8' day to minute,
+ interval '22:33.123456789' minute to second - interval '10' day
-- !query schema
-struct<>
+struct<(INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' MONTH):interval year to month,(INTERVAL '2' YEAR - INTERVAL '3-3' YEAR TO MONTH):interval year to month,(INTERVAL '99 11:22:33.123456' DAY TO SECOND + INTERVAL '10 09:08' DAY TO MINUTE):interval day to second,(INTERVAL '22:33.123456' MINUTE TO SECOND - INTERVAL '10' DAY):interval day to second>
-- !query output
-
+2-5 -1-3 109 20:30:33.123456000 -9 23:37:26.876544000
-- !query
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
+ interval '2' year + '3-3 year to month',
+ interval '2' year - '3 month',
+ '3-2 year to month' + interval '2-2' year to month,
+ '3 year' - interval '2-2' year to month,
+ interval '99 11:22:33.123456789' day to second + '12:12 hour to second',
+ interval '99 11:22:33.123456789' day to second - '12 hour',
+ '4 day' + interval '10' day,
+ '4 22 day to hour' - interval '10' day
-- !query schema
-struct<dateval:date,dateval - INTERVAL '2-2' YEAR TO MONTH:date,dateval - INTERVAL '-2-2' YEAR TO MONTH:date,dateval + INTERVAL '2-2' YEAR TO MONTH:date,dateval + INTERVAL '-2-2' YEAR TO MONTH:date,dateval + (- INTERVAL '2-2' YEAR TO MONTH):date,dateval + INTERVAL '2-2' YEAR TO MONTH:date>
+struct<>
-- !query output
-2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01 2009-11-01 2014-03-01
+java.lang.IllegalArgumentException
+Interval string does not match year-month format of `[+|-]d`, `INTERVAL [+|-]'[+|-]d' DAY` when cast to interval year: 3-3 year to month
-- !query
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
+ interval '2' year + null,
+ interval '2' year - null,
+ interval '2' hour + null,
+ interval '2' hour - null,
+ null + interval '2' year,
+ null - interval '2' year,
+ null + interval '2' hour,
+ null - interval '2' hour
-- !query schema
-struct<tsval:timestamp,tsval - INTERVAL '2-2' YEAR TO MONTH:timestamp,tsval - INTERVAL '-2-2' YEAR TO MONTH:timestamp,tsval + INTERVAL '2-2' YEAR TO MONTH:timestamp,tsval + INTERVAL '-2-2' YEAR TO MONTH:timestamp,tsval + (- INTERVAL '2-2' YEAR TO MONTH):timestamp,tsval + INTERVAL '2-2' YEAR TO MONTH:timestamp>
+struct<>
-- !query 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
+org.apache.spark.sql.AnalysisException
+cannot resolve 'CAST(CAST(NULL AS TIMESTAMP) + INTERVAL '02' HOUR AS VOID)' due to data type mismatch: cannot cast timestamp to void; line 4 pos 2
-- !query
-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
+select interval '2' year + '3-3'
-- !query schema
-struct<(INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3-3' YEAR TO MONTH):interval year to month,(INTERVAL '2-2' YEAR TO MONTH - INTERVAL '3-3' YEAR TO MONTH):interval year to month>
+struct<>
-- !query output
-5-5 -1-1
+java.lang.IllegalArgumentException
+Interval string does not match year-month format of `[+|-]d`, `INTERVAL [+|-]'[+|-]d' DAY` when cast to interval year: 3-3
-- !query
-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
+select interval '2' year - '4'
-- !query schema
-struct<dateval:date,dateval - INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,dateval - INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND):timestamp,dateval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp>
+struct<(INTERVAL '2' YEAR - 4):interval year>
-- !query output
-2012-01-01 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456 2012-04-09 11:22:33.123456 2011-09-23 12:37:26.876544 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456
+-2-0
-- !query
-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
+select '4 11:11' - interval '4 22:12' day to minute
-- !query schema
-struct<tsval:timestamp,tsval - INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,tsval - INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND):timestamp,tsval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp>
+struct<>
-- !query output
-2012-01-01 00:00:00 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456 2012-04-09 11:22:33.123456 2011-09-23 12:37:26.876544 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456
+java.time.DateTimeException
+Cannot cast 4 11:11 to TimestampType.
-- !query
-select
- strval,
- strval - interval '99 11:22:33.123456789' day to second,
- strval - interval '-99 11:22:33.123456789' day to second,
- strval + interval '99 11:22:33.123456789' day to second,
- strval + interval '-99 11:22:33.123456789' day to second,
- -interval '99 11:22:33.123456789' day to second + strval,
- interval '99 11:22:33.123456789' day to second + strval
-from interval_arithmetic
+select '4 12:12:12' + interval '4 22:12' day to minute
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Cannot cast 4 12:12:12 to TimestampType.
+
+
+-- !query
+create temporary view interval_view as select '1' str
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+select interval '2' year + str from interval_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'interval_arithmetic.strval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'interval_arithmetic.strval' is of string type.; line 3 pos 2
+cannot resolve '(INTERVAL '2' YEAR + interval_view.str)' due to data type mismatch: differing types in '(INTERVAL '2' YEAR + interval_view.str)' (interval year and string).; line 1 pos 7
-- !query
-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
+select interval '2' year - str from interval_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR - interval_view.str)' due to data type mismatch: differing types in '(INTERVAL '2' YEAR - interval_view.str)' (interval year and string).; line 1 pos 7
+
+
+-- !query
+select str - interval '4 22:12' day to minute from interval_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'interval_view.str + (- INTERVAL '4 22:12' DAY TO MINUTE)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'interval_view.str' is of string type.; line 1 pos 7
+
+
+-- !query
+select str + interval '4 22:12' day to minute from interval_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'interval_view.str + INTERVAL '4 22:12' DAY TO MINUTE' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'interval_view.str' is of string type.; line 1 pos 7
+
+
+-- !query
+select interval '2-2' year to month + interval '3' day
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' DAY' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '3' day + interval '2-2' year to month
-- !query schema
-struct<(INTERVAL '99 11:22:33.123456' DAY TO SECOND + INTERVAL '10 09:08:07.123456' DAY TO SECOND):interval day to second,(INTERVAL '99 11:22:33.123456' DAY TO SECOND - INTERVAL '10 09:08:07.123456' DAY TO SECOND):interval day to second>
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' DAY' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '2-2' year to month - interval '3' day
+-- !query schema
+struct<>
-- !query output
-109 20:30:40.246912000 89 02:14:26.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + (- INTERVAL '3' DAY)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '3' day - interval '2-2' year to month
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '3' DAY - INTERVAL '2-2' YEAR TO MONTH)' due to data type mismatch: differing types in '(INTERVAL '3' DAY - INTERVAL '2-2' YEAR TO MONTH)' (interval day and interval year to month).; line 1 pos 7
+
+
+-- !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 1 + interval '2' month
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(1 + INTERVAL '2' MONTH)' due to data type mismatch: differing types in '(1 + INTERVAL '2' MONTH)' (int and interval month).; line 1 pos 7
+
+
+-- !query
+select interval '2' second + 1
+-- !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 interval '2' month - 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' MONTH - 1)' due to data type mismatch: differing types in '(INTERVAL '2' MONTH - 1)' (interval month and int).; line 1 pos 7
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
index 2bbd197..a136615 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
@@ -533,136 +533,145 @@ struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp>
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' day
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-13 11:11:11
+0 00:00:01.000000000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' day
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp>
+struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
-- !query output
-2011-11-09 11:11:11
+86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' second
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp>
+struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
-- !query output
-2011-11-11 11:11:13
+-86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - '2011-11-11 11:11:10')' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:10'' is of string type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - interval '2' second
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+struct<(2011-11-11 11:11:11 - TIMESTAMP '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-11 11:11:09
+0 00:00:01.000000000
-- !query
-select '1' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - null
-- !query schema
-struct<>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - NULL):interval day to second>
-- !query output
-java.time.DateTimeException
-Cannot cast 1 to TimestampType.
+NULL
-- !query
-select 1 - interval '2' second
+select null - timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<>
+struct<(NULL - TIMESTAMP '2011-11-11 11:11:11'):interval day to second>
-- !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
+NULL
-- !query
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
+create temporary view ts_view as select '2011-11-11 11:11:11' str
-- !query schema
-struct<(2011-11-11 11:11:11 - TIMESTAMP '2011-11-11 11:11:10'):interval day to second>
+struct<>
-- !query output
-0 00:00:01.000000000
+
-- !query
-create temporary view v3 as select '2011-11-11 11:11:11' str
+select str - timestamp'2011-11-11 11:11:11' from ts_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve '(ts_view.str - TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - interval '2' second from v3
+select timestamp'2011-11-11 11:11:11' - str from ts_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'v3.str + (- INTERVAL '02' SECOND)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - ts_view.str)' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v3
+select timestamp'2011-11-11 11:11:11' + '1'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - DATE '2011-11-11')' due to data type mismatch: argument 1 requires date type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS TIMESTAMP))' due to data type mismatch: '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS TIMESTAMP))' requires (numeric or interval or interval day to second or interval year to month) type, not timestamp; line 1 pos 7
-- !query
-select str - timestamp'2011-11-11 11:11:10' from v3
+select '1' + timestamp'2011-11-11 11:11:11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(CAST('1' AS TIMESTAMP) + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: '(CAST('1' AS TIMESTAMP) + TIMESTAMP '2011-11-11 11:11:11')' requires (numeric or interval or interval day to second or interval year to month) type, not timestamp; line 1 pos 7
-- !query
-select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+select timestamp'2011-11-11 11:11:11' + null
-- !query schema
-struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<>
-- !query output
-86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' due to data type mismatch: differing types in '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' (timestamp and void).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+select null + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
+struct<>
-- !query output
--86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(NULL + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(NULL + TIMESTAMP '2011-11-11 11:11:11')' (void and timestamp).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - null
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2-2' YEAR TO MONTH:timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '-02' SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '12:12:12.123456' HOUR TO SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' + (- INTERVAL '2' YEAR):timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '1 12' DAY TO HOUR:timestamp>
-- !query output
-NULL
+2011-11-13 11:11:11 2009-09-11 11:11:11 2011-11-11 11:11:09 2011-11-10 22:58:58.876544 2009-11-11 11:11:11 2011-11-12 23:11:11
-- !query
-select null - timestamp'2019-10-06 10:11:12.345678'
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01'
-- !query schema
-struct<(NULL - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<DATE '2012-01-01' - INTERVAL '03' HOUR:timestamp,DATE '2012-01-01' + INTERVAL '12:12:12' HOUR TO SECOND:timestamp,DATE '2012-01-01' + INTERVAL '02' MINUTE:timestamp>
-- !query output
-NULL
+2011-12-31 21:00:00 2012-01-01 12:12:12 2012-01-01 00:02:00
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/date.sql.out b/sql/core/src/test/resources/sql-tests/results/date.sql.out
index 457e84c..2eacb6c 100644
--- a/sql/core/src/test/resources/sql-tests/results/date.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/date.sql.out
@@ -1,5 +1,13 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 69
+-- Number of queries: 77
+
+
+-- !query
+create temporary view date_view as select '2011-11-11' date_str, '1' int_str
+-- !query schema
+struct<>
+-- !query output
+
-- !query
@@ -239,7 +247,15 @@ NULL
-- !query
-select date_add('2011-11-11', 1Y)
+select date_add(date'2011-11-11', 1)
+-- !query schema
+struct<date_add(DATE '2011-11-11', 1):date>
+-- !query output
+2011-11-12
+
+
+-- !query
+select date_add('2011-11-11', 1)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -247,7 +263,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1S)
+select date_add('2011-11-11', 1Y)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -255,7 +271,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1)
+select date_add('2011-11-11', 1S)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -323,14 +339,6 @@ NULL
-- !query
-select date_add(date'2011-11-11', 1)
--- !query schema
-struct<date_add(DATE '2011-11-11', 1):date>
--- !query output
-2011-11-12
-
-
--- !query
select date_add(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
struct<date_add(TIMESTAMP '2011-11-11 12:12:12', 1):date>
@@ -356,29 +364,71 @@ struct<date_sub(DATE '2011-11-11', 1):date>
-- !query
-select date_sub(date'2011-11-11', '1')
+select date_sub('2011-11-11', 1)
-- !query schema
-struct<date_sub(DATE '2011-11-11', 1):date>
+struct<date_sub(2011-11-11, 1):date>
-- !query output
2011-11-10
-- !query
-select date_sub(date'2011-11-11', '1.2')
+select date_sub('2011-11-11', 1Y)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1S)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1L)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-The second argument of 'date_sub' function needs to be an integer.
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1L)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1L' is of bigint type.; line 1 pos 7
+
+
+-- !query
+select date_sub('2011-11-11', 1.0)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1.0BD)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1.0BD' is of decimal(2,1) type.; line 1 pos 7
-- !query
-select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1), date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
+select date_sub('2011-11-11', 1E1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 10.0D)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '10.0D' is of double type.; line 1 pos 7
+
+
+-- !query
+select date_sub(date'2011-11-11', '1')
+-- !query schema
+struct<date_sub(DATE '2011-11-11', 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub(date'2011-11-11', '1.2')
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(TIMESTAMP_NTZ '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP_NTZ '2011-11-11 12:12:12'' is of timestamp_ntz type.; line 1 pos 56
+The second argument of 'date_sub' function needs to be an integer.
-- !query
@@ -398,57 +448,58 @@ NULL
-- !query
-create temp view v as select '1' str
+select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
-struct<>
+struct<date_sub(TIMESTAMP '2011-11-11 12:12:12', 1):date>
-- !query output
-
+2011-11-10
-- !query
-select date_add('2011-11-11', str) from v
+select date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+cannot resolve 'date_sub(TIMESTAMP_NTZ '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP_NTZ '2011-11-11 12:12:12'' is of timestamp_ntz type.; line 1 pos 7
-- !query
-select date_sub('2011-11-11', str) from v
+select date_add('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+cannot resolve 'date_add(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-create temp view v2 as select '2011-11-11' str
+select date_sub('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-select date_add(str, 1) from v2
+select date_add(date_str, 1) from date_view
-- !query schema
-struct<date_add(str, 1):date>
+struct<date_add(date_str, 1):date>
-- !query output
2011-11-12
-- !query
-select date_sub(str, 1) from v2
+select date_sub(date_str, 1) from date_view
-- !query schema
-struct<date_sub(str, 1):date>
+struct<date_sub(date_str, 1):date>
-- !query output
2011-11-10
-- !query
-select date'2011-11-11' + 1E1
+select date '2011-11-11' + 1E1
-- !query schema
struct<>
-- !query output
@@ -457,23 +508,6 @@ cannot resolve 'date_add(DATE '2011-11-11', 10.0D)' due to data type mismatch: a
-- !query
-select date'2011-11-11' + '1'
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
-
-
--- !query
-select null + date '2001-09-28'
--- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
--- !query output
-NULL
-
-
--- !query
select date '2001-09-28' + 7Y
-- !query schema
struct<date_add(DATE '2001-09-28', 7):date>
@@ -498,20 +532,28 @@ struct<date_sub(DATE '2001-10-01', 7):date>
-- !query
-select date '2001-10-01' - '7'
+select date '2001-10-01' - date '2001-09-28'
+-- !query schema
+struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
+-- !query output
+3 00:00:00.000000000
+
+
+-- !query
+select date '2001-10-01' - '2001-09-28'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(DATE '2001-10-01', CAST('7' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('7' AS DOUBLE)' is of double type.; line 1 pos 7
+cannot resolve 'date_sub(DATE '2001-10-01', CAST('2001-09-28' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('2001-09-28' AS DOUBLE)' is of double type.; line 1 pos 7
-- !query
-select date '2001-09-28' + null
+select '2001-10-01' - date '2001-09-28'
-- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
+struct<(2001-10-01 - DATE '2001-09-28'):interval day>
-- !query output
-NULL
+3 00:00:00.000000000
-- !query
@@ -523,43 +565,75 @@ NULL
-- !query
-select '2011-11-11' - interval '2' day
+select null - date '2019-10-06'
+-- !query schema
+struct<(NULL - DATE '2019-10-06'):interval day>
+-- !query output
+NULL
+
+
+-- !query
+select date_str - date '2001-09-28' from date_view
-- !query schema
-struct<2011-11-11 - INTERVAL '2' DAY:string>
+struct<(date_str - DATE '2001-09-28'):interval day>
-- !query output
-2011-11-09 00:00:00
+3696 00:00:00.000000000
-- !query
-select null - date '2019-10-06'
+select date '2001-09-28' - date_str from date_view
-- !query schema
-struct<(NULL - DATE '2019-10-06'):interval day>
+struct<>
-- !query output
-NULL
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(DATE '2001-09-28', CAST(date_view.date_str AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST(date_view.date_str AS DOUBLE)' is of double type.; line 1 pos 7
-- !query
-select date '2001-10-01' - date '2001-09-28'
+select date'2011-11-11' + '1'
-- !query schema
-struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
+struct<>
-- !query output
-3 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - date'2011-11-11'
+select '1' + date'2011-11-11'
-- !query schema
-struct<(2011-11-11 11:11:11 - DATE '2011-11-11'):interval day>
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
+
+
+-- !query
+select date'2011-11-11' + null
+-- !query schema
+struct<date_add(DATE '2011-11-11', NULL):date>
-- !query output
-0 00:00:00.000000000
+NULL
+
+
+-- !query
+select null + date'2011-11-11'
+-- !query schema
+struct<date_add(DATE '2011-11-11', NULL):date>
+-- !query output
+NULL
-- !query
-select str - date'2011-11-11' from v2
+select date '2012-01-01' - interval '2-2' year to month,
+ date '2011-11-11' - interval '2' day,
+ date '2012-01-01' + interval '-2-2' year to month,
+ date '2011-11-11' + interval '-2' month,
+ - interval '2-2' year to month + date '2012-01-01',
+ interval '-2' day + date '2011-11-11'
-- !query schema
-struct<(str - DATE '2011-11-11'):interval day>
+struct<DATE '2012-01-01' - INTERVAL '2-2' YEAR TO MONTH:date,date_add(DATE '2011-11-11', (- extractansiintervaldays(INTERVAL '2' DAY))):date,DATE '2012-01-01' + INTERVAL '-2-2' YEAR TO MONTH:date,DATE '2011-11-11' + INTERVAL '-2' MONTH:date,DATE '2012-01-01' + (- INTERVAL '2-2' YEAR TO MONTH):date,date_add(DATE '2011-11-11', extractansiintervaldays(INTERVAL '-2' DAY)):date>
-- !query output
-0 00:00:00.000000000
+2009-11-01 2011-11-09 2009-11-01 2011-09-11 2009-11-01 2011-11-09
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
index 785dc55..59797bc 100644
--- a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
@@ -1,5 +1,13 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 151
+-- Number of queries: 159
+
+
+-- !query
+create temporary view date_view as select '2011-11-11' date_str, '1' int_str
+-- !query schema
+struct<>
+-- !query output
+
-- !query
@@ -239,7 +247,15 @@ NULL
-- !query
-select date_add('2011-11-11', 1Y)
+select date_add(date'2011-11-11', 1)
+-- !query schema
+struct<date_add(DATE '2011-11-11', 1):date>
+-- !query output
+2011-11-12
+
+
+-- !query
+select date_add('2011-11-11', 1)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -247,7 +263,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1S)
+select date_add('2011-11-11', 1Y)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -255,7 +271,7 @@ struct<date_add(2011-11-11, 1):date>
-- !query
-select date_add('2011-11-11', 1)
+select date_add('2011-11-11', 1S)
-- !query schema
struct<date_add(2011-11-11, 1):date>
-- !query output
@@ -323,14 +339,6 @@ NULL
-- !query
-select date_add(date'2011-11-11', 1)
--- !query schema
-struct<date_add(DATE '2011-11-11', 1):date>
--- !query output
-2011-11-12
-
-
--- !query
select date_add(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
struct<date_add(TIMESTAMP '2011-11-11 12:12:12', 1):date>
@@ -356,29 +364,71 @@ struct<date_sub(DATE '2011-11-11', 1):date>
-- !query
-select date_sub(date'2011-11-11', '1')
+select date_sub('2011-11-11', 1)
-- !query schema
-struct<date_sub(DATE '2011-11-11', 1):date>
+struct<date_sub(2011-11-11, 1):date>
-- !query output
2011-11-10
-- !query
-select date_sub(date'2011-11-11', '1.2')
+select date_sub('2011-11-11', 1Y)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1S)
+-- !query schema
+struct<date_sub(2011-11-11, 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub('2011-11-11', 1L)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-The second argument of 'date_sub' function needs to be an integer.
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1L)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1L' is of bigint type.; line 1 pos 7
-- !query
-select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1), date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
+select date_sub('2011-11-11', 1.0)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(TIMESTAMP_NTZ '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP_NTZ '2011-11-11 12:12:12'' is of timestamp_ntz type.; line 1 pos 56
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 1.0BD)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '1.0BD' is of decimal(2,1) type.; line 1 pos 7
+
+
+-- !query
+select date_sub('2011-11-11', 1E1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), 10.0D)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, '10.0D' is of double type.; line 1 pos 7
+
+
+-- !query
+select date_sub(date'2011-11-11', '1')
+-- !query schema
+struct<date_sub(DATE '2011-11-11', 1):date>
+-- !query output
+2011-11-10
+
+
+-- !query
+select date_sub(date'2011-11-11', '1.2')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+The second argument of 'date_sub' function needs to be an integer.
-- !query
@@ -398,57 +448,58 @@ NULL
-- !query
-create temp view v as select '1' str
+select date_sub(timestamp_ltz'2011-11-11 12:12:12', 1)
-- !query schema
-struct<>
+struct<date_sub(TIMESTAMP '2011-11-11 12:12:12', 1):date>
-- !query output
-
+2011-11-10
-- !query
-select date_add('2011-11-11', str) from v
+select date_sub(timestamp_ntz'2011-11-11 12:12:12', 1)
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+cannot resolve 'date_sub(TIMESTAMP_NTZ '2011-11-11 12:12:12', 1)' due to data type mismatch: argument 1 requires date type, however, 'TIMESTAMP_NTZ '2011-11-11 12:12:12'' is of timestamp_ntz type.; line 1 pos 7
-- !query
-select date_sub('2011-11-11', str) from v
+select date_add('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), v.str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'v.str' is of string type.; line 1 pos 7
+cannot resolve 'date_add(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-create temp view v2 as select '2011-11-11' str
+select date_sub('2011-11-11', int_str) from date_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(CAST('2011-11-11' AS DATE), date_view.int_str)' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'date_view.int_str' is of string type.; line 1 pos 7
-- !query
-select date_add(str, 1) from v2
+select date_add(date_str, 1) from date_view
-- !query schema
-struct<date_add(str, 1):date>
+struct<date_add(date_str, 1):date>
-- !query output
2011-11-12
-- !query
-select date_sub(str, 1) from v2
+select date_sub(date_str, 1) from date_view
-- !query schema
-struct<date_sub(str, 1):date>
+struct<date_sub(date_str, 1):date>
-- !query output
2011-11-10
-- !query
-select date'2011-11-11' + 1E1
+select date '2011-11-11' + 1E1
-- !query schema
struct<>
-- !query output
@@ -457,23 +508,6 @@ cannot resolve 'date_add(DATE '2011-11-11', 10.0D)' due to data type mismatch: a
-- !query
-select date'2011-11-11' + '1'
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
-
-
--- !query
-select null + date '2001-09-28'
--- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
--- !query output
-NULL
-
-
--- !query
select date '2001-09-28' + 7Y
-- !query schema
struct<date_add(DATE '2001-09-28', 7):date>
@@ -498,20 +532,28 @@ struct<date_sub(DATE '2001-10-01', 7):date>
-- !query
-select date '2001-10-01' - '7'
+select date '2001-10-01' - date '2001-09-28'
+-- !query schema
+struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
+-- !query output
+3 00:00:00.000000000
+
+
+-- !query
+select date '2001-10-01' - '2001-09-28'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'date_sub(DATE '2001-10-01', CAST('7' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('7' AS DOUBLE)' is of double type.; line 1 pos 7
+cannot resolve 'date_sub(DATE '2001-10-01', CAST('2001-09-28' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('2001-09-28' AS DOUBLE)' is of double type.; line 1 pos 7
-- !query
-select date '2001-09-28' + null
+select '2001-10-01' - date '2001-09-28'
-- !query schema
-struct<date_add(DATE '2001-09-28', NULL):date>
+struct<(2001-10-01 - DATE '2001-09-28'):interval day>
-- !query output
-NULL
+3 00:00:00.000000000
-- !query
@@ -523,43 +565,75 @@ NULL
-- !query
-select '2011-11-11' - interval '2' day
+select null - date '2019-10-06'
-- !query schema
-struct<2011-11-11 - INTERVAL '2' DAY:string>
+struct<(NULL - DATE '2019-10-06'):interval day>
-- !query output
-2011-11-09 00:00:00
+NULL
-- !query
-select null - date '2019-10-06'
+select date_str - date '2001-09-28' from date_view
-- !query schema
-struct<(NULL - DATE '2019-10-06'):interval day>
+struct<(date_str - DATE '2001-09-28'):interval day>
-- !query output
-NULL
+3696 00:00:00.000000000
-- !query
-select date '2001-10-01' - date '2001-09-28'
+select date '2001-09-28' - date_str from date_view
-- !query schema
-struct<(DATE '2001-10-01' - DATE '2001-09-28'):interval day>
+struct<>
-- !query output
-3 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_sub(DATE '2001-09-28', CAST(date_view.date_str AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST(date_view.date_str AS DOUBLE)' is of double type.; line 1 pos 7
+
+
+-- !query
+select date'2011-11-11' + '1'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
+
+
+-- !query
+select '1' + date'2011-11-11'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'date_add(DATE '2011-11-11', CAST('1' AS DOUBLE))' due to data type mismatch: argument 2 requires (int or smallint or tinyint) type, however, 'CAST('1' AS DOUBLE)' is of double type.; line 1 pos 7
+
+
+-- !query
+select date'2011-11-11' + null
+-- !query schema
+struct<date_add(DATE '2011-11-11', NULL):date>
+-- !query output
+NULL
-- !query
-select '2011-11-11 11:11:11' - date'2011-11-11'
+select null + date'2011-11-11'
-- !query schema
-struct<(2011-11-11 11:11:11 - DATE '2011-11-11'):interval day>
+struct<date_add(DATE '2011-11-11', NULL):date>
-- !query output
-0 00:00:00.000000000
+NULL
-- !query
-select str - date'2011-11-11' from v2
+select date '2012-01-01' - interval '2-2' year to month,
+ date '2011-11-11' - interval '2' day,
+ date '2012-01-01' + interval '-2-2' year to month,
+ date '2011-11-11' + interval '-2' month,
+ - interval '2-2' year to month + date '2012-01-01',
+ interval '-2' day + date '2011-11-11'
-- !query schema
-struct<(str - DATE '2011-11-11'):interval day>
+struct<DATE '2012-01-01' - INTERVAL '2-2' YEAR TO MONTH:date,date_add(DATE '2011-11-11', (- extractansiintervaldays(INTERVAL '2' DAY))):date,DATE '2012-01-01' + INTERVAL '-2-2' YEAR TO MONTH:date,DATE '2011-11-11' + INTERVAL '-2' MONTH:date,DATE '2012-01-01' + (- INTERVAL '2-2' YEAR TO MONTH):date,date_add(DATE '2011-11-11', extractansiintervaldays(INTERVAL '-2' DAY)):date>
-- !query output
-0 00:00:00.000000000
+2009-11-01 2011-11-09 2009-11-01 2011-09-11 2009-11-01 2011-11-09
-- !query
@@ -1099,134 +1173,146 @@ struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp>
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' day
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-13 11:11:11
+0 00:00:01.000000000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' day
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp>
+struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
-- !query output
-2011-11-09 11:11:11
+86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' second
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp>
+struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
-- !query output
-2011-11-11 11:11:13
+-86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - '2011-11-11 11:11:10')' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:10'' is of string type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - interval '2' second
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
-- !query
-select '1' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - null
-- !query schema
-struct<1 - INTERVAL '02' SECOND:string>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - NULL):interval day to second>
-- !query output
NULL
-- !query
-select 1 - interval '2' second
+select null - timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<>
+struct<(NULL - TIMESTAMP '2011-11-11 11:11:11'):interval day to second>
-- !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
+NULL
-- !query
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
+create temporary view ts_view as select '2011-11-11 11:11:11' str
-- !query schema
struct<>
-- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
+
-- !query
-create temporary view v3 as select '2011-11-11 11:11:11' str
+select str - timestamp'2011-11-11 11:11:11' from ts_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve '(ts_view.str - TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - interval '2' second from v3
+select timestamp'2011-11-11 11:11:11' - str from ts_view
-- !query schema
-struct<str - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - ts_view.str)' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v3
+select timestamp'2011-11-11 11:11:11' + '1'
-- !query schema
-struct<(str - DATE '2011-11-11'):interval day>
+struct<>
-- !query output
-0 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' due to data type mismatch: differing types in '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' (timestamp and double).; line 1 pos 7
-- !query
-select str - timestamp'2011-11-11 11:11:10' from v3
+select '1' + timestamp'2011-11-11 11:11:11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(CAST('1' AS DOUBLE) + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(CAST('1' AS DOUBLE) + TIMESTAMP '2011-11-11 11:11:11')' (double and timestamp).; line 1 pos 7
-- !query
-select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+select timestamp'2011-11-11 11:11:11' + null
-- !query schema
-struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<>
-- !query output
-86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' due to data type mismatch: differing types in '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' (timestamp and void).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+select null + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
+struct<>
-- !query output
--86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(NULL + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(NULL + TIMESTAMP '2011-11-11 11:11:11')' (void and timestamp).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - null
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2-2' YEAR TO MONTH:timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '-02' SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '12:12:12.123456' HOUR TO SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' + (- INTERVAL '2' YEAR):timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '1 12' DAY TO HOUR:timestamp>
-- !query output
-NULL
+2011-11-13 11:11:11 2009-09-11 11:11:11 2011-11-11 11:11:09 2011-11-10 22:58:58.876544 2009-11-11 11:11:11 2011-11-12 23:11:11
-- !query
-select null - timestamp'2019-10-06 10:11:12.345678'
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01'
-- !query schema
-struct<(NULL - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<DATE '2012-01-01' - INTERVAL '03' HOUR:timestamp,DATE '2012-01-01' + INTERVAL '12:12:12' HOUR TO SECOND:timestamp,DATE '2012-01-01' + INTERVAL '02' MINUTE:timestamp>
-- !query output
-NULL
+2011-12-31 21:00:00 2012-01-01 12:12:12 2012-01-01 00:02:00
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/interval.sql.out b/sql/core/src/test/resources/sql-tests/results/interval.sql.out
index a15cc23..07b24a3 100644
--- a/sql/core/src/test/resources/sql-tests/results/interval.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/interval.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 211
+-- Number of queries: 223
-- !query
@@ -1135,116 +1135,197 @@ struct<INTERVAL '-178956970-8' YEAR TO MONTH:interval year to month>
-- !query
-create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp), dateval as strval from values
- ('2012-01-01', '2012-01-01')
- as interval_arithmetic(dateval, tsval)
+select
+ interval '2-2' year to month + interval '3' month,
+ interval '2' year - interval '3-3' year to month,
+ interval '99 11:22:33.123456789' day to second + interval '10 9:8' day to minute,
+ interval '22:33.123456789' minute to second - interval '10' day
-- !query schema
-struct<>
+struct<(INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' MONTH):interval year to month,(INTERVAL '2' YEAR - INTERVAL '3-3' YEAR TO MONTH):interval year to month,(INTERVAL '99 11:22:33.123456' DAY TO SECOND + INTERVAL '10 09:08' DAY TO MINUTE):interval day to second,(INTERVAL '22:33.123456' MINUTE TO SECOND - INTERVAL '10' DAY):interval day to second>
-- !query output
-
+2-5 -1-3 109 20:30:33.123456000 -9 23:37:26.876544000
-- !query
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
+ interval '2' year + '3-3 year to month',
+ interval '2' year - '3 month',
+ '3-2 year to month' + interval '2-2' year to month,
+ '3 year' - interval '2-2' year to month,
+ interval '99 11:22:33.123456789' day to second + '12:12 hour to second',
+ interval '99 11:22:33.123456789' day to second - '12 hour',
+ '4 day' + interval '10' day,
+ '4 22 day to hour' - interval '10' day
-- !query schema
-struct<dateval:date,dateval - INTERVAL '2-2' YEAR TO MONTH:date,dateval - INTERVAL '-2-2' YEAR TO MONTH:date,dateval + INTERVAL '2-2' YEAR TO MONTH:date,dateval + INTERVAL '-2-2' YEAR TO MONTH:date,dateval + (- INTERVAL '2-2' YEAR TO MONTH):date,dateval + INTERVAL '2-2' YEAR TO MONTH:date>
+struct<>
-- !query output
-2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01 2009-11-01 2014-03-01
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR + CAST('3-3 year to month' AS DOUBLE))' due to data type mismatch: differing types in '(INTERVAL '2' YEAR + CAST('3-3 year to month' AS DOUBLE))' (interval year and double).; line 2 pos 2
-- !query
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
+ interval '2' year + null,
+ interval '2' year - null,
+ interval '2' hour + null,
+ interval '2' hour - null,
+ null + interval '2' year,
+ null - interval '2' year,
+ null + interval '2' hour,
+ null - interval '2' hour
-- !query schema
-struct<tsval:timestamp,tsval - INTERVAL '2-2' YEAR TO MONTH:timestamp,tsval - INTERVAL '-2-2' YEAR TO MONTH:timestamp,tsval + INTERVAL '2-2' YEAR TO MONTH:timestamp,tsval + INTERVAL '-2-2' YEAR TO MONTH:timestamp,tsval + (- INTERVAL '2-2' YEAR TO MONTH):timestamp,tsval + INTERVAL '2-2' YEAR TO MONTH:timestamp>
+struct<>
-- !query 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
+org.apache.spark.sql.AnalysisException
+cannot resolve 'CAST(CAST(NULL AS TIMESTAMP) + INTERVAL '02' HOUR AS VOID)' due to data type mismatch: cannot cast timestamp to void; line 4 pos 2
-- !query
-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
+select interval '2' year + '3-3'
-- !query schema
-struct<(INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3-3' YEAR TO MONTH):interval year to month,(INTERVAL '2-2' YEAR TO MONTH - INTERVAL '3-3' YEAR TO MONTH):interval year to month>
+struct<>
-- !query output
-5-5 -1-1
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR + CAST('3-3' AS DOUBLE))' due to data type mismatch: differing types in '(INTERVAL '2' YEAR + CAST('3-3' AS DOUBLE))' (interval year and double).; line 1 pos 7
-- !query
-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
+select interval '2' year - '4'
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR - CAST('4' AS DOUBLE))' due to data type mismatch: differing types in '(INTERVAL '2' YEAR - CAST('4' AS DOUBLE))' (interval year and double).; line 1 pos 7
+
+
+-- !query
+select '4 11:11' - interval '4 22:12' day to minute
-- !query schema
-struct<dateval:date,dateval - INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,dateval - INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,dateval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND):timestamp,dateval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp>
+struct<4 11:11 - INTERVAL '4 22:12' DAY TO MINUTE:string>
-- !query output
-2012-01-01 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456 2012-04-09 11:22:33.123456 2011-09-23 12:37:26.876544 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456
+NULL
-- !query
-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
+select '4 12:12:12' + interval '4 22:12' day to minute
-- !query schema
-struct<tsval:timestamp,tsval - INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,tsval - INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + INTERVAL '-99 11:22:33.123456' DAY TO SECOND:timestamp,tsval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND):timestamp,tsval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:timestamp>
+struct<4 12:12:12 + INTERVAL '4 22:12' DAY TO MINUTE:string>
-- !query output
-2012-01-01 00:00:00 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456 2012-04-09 11:22:33.123456 2011-09-23 12:37:26.876544 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456
+NULL
-- !query
-select
- strval,
- strval - interval '99 11:22:33.123456789' day to second,
- strval - interval '-99 11:22:33.123456789' day to second,
- strval + interval '99 11:22:33.123456789' day to second,
- strval + interval '-99 11:22:33.123456789' day to second,
- -interval '99 11:22:33.123456789' day to second + strval,
- interval '99 11:22:33.123456789' day to second + strval
-from interval_arithmetic
+create temporary view interval_view as select '1' str
-- !query schema
-struct<strval:string,strval - INTERVAL '99 11:22:33.123456' DAY TO SECOND:string,strval - INTERVAL '-99 11:22:33.123456' DAY TO SECOND:string,strval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:string,strval + INTERVAL '-99 11:22:33.123456' DAY TO SECOND:string,strval + (- INTERVAL '99 11:22:33.123456' DAY TO SECOND):string,strval + INTERVAL '99 11:22:33.123456' DAY TO SECOND:string>
+struct<>
-- !query output
-2012-01-01 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456 2012-04-09 11:22:33.123456 2011-09-23 12:37:26.876544 2011-09-23 12:37:26.876544 2012-04-09 11:22:33.123456
+
-- !query
-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
+select interval '2' year + str from interval_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR + CAST(interval_view.str AS DOUBLE))' due to data type mismatch: differing types in '(INTERVAL '2' YEAR + CAST(interval_view.str AS DOUBLE))' (interval year and double).; line 1 pos 7
+
+
+-- !query
+select interval '2' year - str from interval_view
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' YEAR - CAST(interval_view.str AS DOUBLE))' due to data type mismatch: differing types in '(INTERVAL '2' YEAR - CAST(interval_view.str AS DOUBLE))' (interval year and double).; line 1 pos 7
+
+
+-- !query
+select str - interval '4 22:12' day to minute from interval_view
+-- !query schema
+struct<str - INTERVAL '4 22:12' DAY TO MINUTE:string>
+-- !query output
+NULL
+
+
+-- !query
+select str + interval '4 22:12' day to minute from interval_view
+-- !query schema
+struct<str + INTERVAL '4 22:12' DAY TO MINUTE:string>
+-- !query output
+NULL
+
+
+-- !query
+select interval '2-2' year to month + interval '3' day
-- !query schema
-struct<(INTERVAL '99 11:22:33.123456' DAY TO SECOND + INTERVAL '10 09:08:07.123456' DAY TO SECOND):interval day to second,(INTERVAL '99 11:22:33.123456' DAY TO SECOND - INTERVAL '10 09:08:07.123456' DAY TO SECOND):interval day to second>
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' DAY' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '3' day + interval '2-2' year to month
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + INTERVAL '3' DAY' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '2-2' year to month - interval '3' day
+-- !query schema
+struct<>
-- !query output
-109 20:30:40.246912000 89 02:14:26.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve 'INTERVAL '2-2' YEAR TO MONTH + (- INTERVAL '3' DAY)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'INTERVAL '2-2' YEAR TO MONTH' is of interval year to month type.; line 1 pos 7
+
+
+-- !query
+select interval '3' day - interval '2-2' year to month
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '3' DAY - INTERVAL '2-2' YEAR TO MONTH)' due to data type mismatch: differing types in '(INTERVAL '3' DAY - INTERVAL '2-2' YEAR TO MONTH)' (interval day and interval year to month).; line 1 pos 7
+
+
+-- !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 1 + interval '2' month
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(1 + INTERVAL '2' MONTH)' due to data type mismatch: differing types in '(1 + INTERVAL '2' MONTH)' (int and interval month).; line 1 pos 7
+
+
+-- !query
+select interval '2' second + 1
+-- !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 interval '2' month - 1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '2' MONTH - 1)' due to data type mismatch: differing types in '(INTERVAL '2' MONTH - 1)' (interval month and int).; line 1 pos 7
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
index e5d2f66..b3b7306 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
@@ -515,134 +515,146 @@ struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp>
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' day
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-13 11:11:11
+0 00:00:01.000000000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' day
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp>
+struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
-- !query output
-2011-11-09 11:11:11
+86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' second
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp>
+struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
-- !query output
-2011-11-11 11:11:13
+-86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - '2011-11-11 11:11:10')' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:10'' is of string type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - interval '2' second
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
-- !query
-select '1' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - null
-- !query schema
-struct<1 - INTERVAL '02' SECOND:string>
+struct<(TIMESTAMP '2011-11-11 11:11:11' - NULL):interval day to second>
-- !query output
NULL
-- !query
-select 1 - interval '2' second
+select null - timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<>
+struct<(NULL - TIMESTAMP '2011-11-11 11:11:11'):interval day to second>
-- !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
+NULL
-- !query
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
+create temporary view ts_view as select '2011-11-11 11:11:11' str
-- !query schema
struct<>
-- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
+
-- !query
-create temporary view v3 as select '2011-11-11 11:11:11' str
+select str - timestamp'2011-11-11 11:11:11' from ts_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve '(ts_view.str - TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - interval '2' second from v3
+select timestamp'2011-11-11 11:11:11' - str from ts_view
-- !query schema
-struct<str - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' - ts_view.str)' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v3
+select timestamp'2011-11-11 11:11:11' + '1'
-- !query schema
-struct<(str - DATE '2011-11-11'):interval day>
+struct<>
-- !query output
-0 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' due to data type mismatch: differing types in '(TIMESTAMP '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' (timestamp and double).; line 1 pos 7
-- !query
-select str - timestamp'2011-11-11 11:11:10' from v3
+select '1' + timestamp'2011-11-11 11:11:11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - TIMESTAMP '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(CAST('1' AS DOUBLE) + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(CAST('1' AS DOUBLE) + TIMESTAMP '2011-11-11 11:11:11')' (double and timestamp).; line 1 pos 7
-- !query
-select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+select timestamp'2011-11-11 11:11:11' + null
-- !query schema
-struct<(DATE '2020-01-01' - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<>
-- !query output
-86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' due to data type mismatch: differing types in '(TIMESTAMP '2011-11-11 11:11:11' + NULL)' (timestamp and void).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+select null + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
+struct<>
-- !query output
--86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(NULL + TIMESTAMP '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(NULL + TIMESTAMP '2011-11-11 11:11:11')' (void and timestamp).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - null
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+struct<TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2-2' YEAR TO MONTH:timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '-02' SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '12:12:12.123456' HOUR TO SECOND:timestamp,TIMESTAMP '2011-11-11 11:11:11' + (- INTERVAL '2' YEAR):timestamp,TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '1 12' DAY TO HOUR:timestamp>
-- !query output
-NULL
+2011-11-13 11:11:11 2009-09-11 11:11:11 2011-11-11 11:11:09 2011-11-10 22:58:58.876544 2009-11-11 11:11:11 2011-11-12 23:11:11
-- !query
-select null - timestamp'2019-10-06 10:11:12.345678'
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01'
-- !query schema
-struct<(NULL - TIMESTAMP '2019-10-06 10:11:12.345678'):interval day to second>
+struct<DATE '2012-01-01' - INTERVAL '03' HOUR:timestamp,DATE '2012-01-01' + INTERVAL '12:12:12' HOUR TO SECOND:timestamp,DATE '2012-01-01' + INTERVAL '02' MINUTE:timestamp>
-- !query output
-NULL
+2011-12-31 21:00:00 2012-01-01 12:12:12 2012-01-01 00:02:00
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
index 3a13486..7c179c7 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
@@ -533,136 +533,145 @@ struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp_ntz>
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' day
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp_ntz>
+struct<(TIMESTAMP_NTZ '2011-11-11 11:11:11' - TIMESTAMP_NTZ '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-13 11:11:11
+0 00:00:01.000000000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' day
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp_ntz>
+struct<(DATE '2020-01-01' - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
-- !query output
-2011-11-09 11:11:11
+86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' second
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp_ntz>
+struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
-- !query output
-2011-11-11 11:11:13
+-86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp_ntz>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' - '2011-11-11 11:11:10')' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:10'' is of string type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - interval '2' second
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+struct<(2011-11-11 11:11:11 - TIMESTAMP_NTZ '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-11 11:11:09
+0 00:00:01.000000000
-- !query
-select '1' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - null
-- !query schema
-struct<>
+struct<(TIMESTAMP_NTZ '2011-11-11 11:11:11' - NULL):interval day to second>
-- !query output
-java.time.DateTimeException
-Cannot cast 1 to TimestampType.
+NULL
-- !query
-select 1 - interval '2' second
+select null - timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<>
+struct<(NULL - TIMESTAMP_NTZ '2011-11-11 11:11:11'):interval day to second>
-- !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
+NULL
-- !query
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
+create temporary view ts_view as select '2011-11-11 11:11:11' str
-- !query schema
-struct<(2011-11-11 11:11:11 - TIMESTAMP_NTZ '2011-11-11 11:11:10'):interval day to second>
+struct<>
-- !query output
-0 00:00:01.000000000
+
-- !query
-create temporary view v3 as select '2011-11-11 11:11:11' str
+select str - timestamp'2011-11-11 11:11:11' from ts_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve '(ts_view.str - TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - interval '2' second from v3
+select timestamp'2011-11-11 11:11:11' - str from ts_view
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve 'v3.str + (- INTERVAL '02' SECOND)' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' - ts_view.str)' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v3
+select timestamp'2011-11-11 11:11:11' + '1'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - DATE '2011-11-11')' due to data type mismatch: argument 1 requires date type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + CAST('1' AS TIMESTAMP_NTZ))' due to data type mismatch: '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + CAST('1' AS TIMESTAMP_NTZ))' requires (numeric or interval or interval day to second or interval year to month) type, not timestamp_ntz; line 1 pos 7
-- !query
-select str - timestamp'2011-11-11 11:11:10' from v3
+select '1' + timestamp'2011-11-11 11:11:11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - TIMESTAMP_NTZ '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(CAST('1' AS TIMESTAMP_NTZ) + TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: '(CAST('1' AS TIMESTAMP_NTZ) + TIMESTAMP_NTZ '2011-11-11 11:11:11')' requires (numeric or interval or interval day to second or interval year to month) type, not timestamp_ntz; line 1 pos 7
-- !query
-select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+select timestamp'2011-11-11 11:11:11' + null
-- !query schema
-struct<(DATE '2020-01-01' - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
+struct<>
-- !query output
-86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + NULL)' due to data type mismatch: differing types in '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + NULL)' (timestamp_ntz and void).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+select null + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
+struct<>
-- !query output
--86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(NULL + TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(NULL + TIMESTAMP_NTZ '2011-11-11 11:11:11')' (void and timestamp_ntz).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - null
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '2-2' YEAR TO MONTH:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '-02' SECOND:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '12:12:12.123456' HOUR TO SECOND:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + (- INTERVAL '2' YEAR):timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '1 12' DAY TO HOUR:timestamp_ntz>
-- !query output
-NULL
+2011-11-13 11:11:11 2009-09-11 11:11:11 2011-11-11 11:11:09 2011-11-10 22:58:58.876544 2009-11-11 11:11:11 2011-11-12 23:11:11
-- !query
-select null - timestamp'2019-10-06 10:11:12.345678'
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01'
-- !query schema
-struct<(NULL - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
+struct<DATE '2012-01-01' - INTERVAL '03' HOUR:timestamp,DATE '2012-01-01' + INTERVAL '12:12:12' HOUR TO SECOND:timestamp,DATE '2012-01-01' + INTERVAL '02' MINUTE:timestamp>
-- !query output
-NULL
+2011-12-31 21:00:00 2012-01-01 12:12:12 2012-01-01 00:02:00
-- !query
diff --git a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
index c51ce87..6c02a5a 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
@@ -515,134 +515,146 @@ struct<to_timestamp(2019 10:10:10, yyyy hh:mm:ss):timestamp_ntz>
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' day
+select timestamp'2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp_ntz>
+struct<(TIMESTAMP_NTZ '2011-11-11 11:11:11' - TIMESTAMP_NTZ '2011-11-11 11:11:10'):interval day to second>
-- !query output
-2011-11-13 11:11:11
+0 00:00:01.000000000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' day
+select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '2' DAY:timestamp_ntz>
+struct<(DATE '2020-01-01' - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
-- !query output
-2011-11-09 11:11:11
+86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' + interval '2' second
+select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '02' SECOND:timestamp_ntz>
+struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
-- !query output
-2011-11-11 11:11:13
+-86 13:48:47.654322000
-- !query
-select timestamp'2011-11-11 11:11:11' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - '2011-11-11 11:11:10'
-- !query schema
-struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '02' SECOND:timestamp_ntz>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' - '2011-11-11 11:11:10')' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:10'' is of string type.; line 1 pos 7
-- !query
-select '2011-11-11 11:11:11' - interval '2' second
+select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
-- !query schema
-struct<2011-11-11 11:11:11 - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP_NTZ '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
-- !query
-select '1' - interval '2' second
+select timestamp'2011-11-11 11:11:11' - null
-- !query schema
-struct<1 - INTERVAL '02' SECOND:string>
+struct<(TIMESTAMP_NTZ '2011-11-11 11:11:11' - NULL):interval day to second>
-- !query output
NULL
-- !query
-select 1 - interval '2' second
+select null - timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<>
+struct<(NULL - TIMESTAMP_NTZ '2011-11-11 11:11:11'):interval day to second>
-- !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
+NULL
-- !query
-select '2011-11-11 11:11:11' - timestamp'2011-11-11 11:11:10'
+create temporary view ts_view as select '2011-11-11 11:11:11' str
-- !query schema
struct<>
-- !query output
-org.apache.spark.sql.AnalysisException
-cannot resolve '('2011-11-11 11:11:11' - TIMESTAMP_NTZ '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, ''2011-11-11 11:11:11'' is of string type.; line 1 pos 7
+
-- !query
-create temporary view v3 as select '2011-11-11 11:11:11' str
+select str - timestamp'2011-11-11 11:11:11' from ts_view
-- !query schema
struct<>
-- !query output
-
+org.apache.spark.sql.AnalysisException
+cannot resolve '(ts_view.str - TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - interval '2' second from v3
+select timestamp'2011-11-11 11:11:11' - str from ts_view
-- !query schema
-struct<str - INTERVAL '02' SECOND:string>
+struct<>
-- !query output
-2011-11-11 11:11:09
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' - ts_view.str)' due to data type mismatch: argument 2 requires (timestamp or timestamp without time zone) type, however, 'ts_view.str' is of string type.; line 1 pos 7
-- !query
-select str - date'2011-11-11' from v3
+select timestamp'2011-11-11 11:11:11' + '1'
-- !query schema
-struct<(str - DATE '2011-11-11'):interval day>
+struct<>
-- !query output
-0 00:00:00.000000000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' due to data type mismatch: differing types in '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + CAST('1' AS DOUBLE))' (timestamp_ntz and double).; line 1 pos 7
-- !query
-select str - timestamp'2011-11-11 11:11:10' from v3
+select '1' + timestamp'2011-11-11 11:11:11'
-- !query schema
struct<>
-- !query output
org.apache.spark.sql.AnalysisException
-cannot resolve '(v3.str - TIMESTAMP_NTZ '2011-11-11 11:11:10')' due to data type mismatch: argument 1 requires (timestamp or timestamp without time zone) type, however, 'v3.str' is of string type.; line 1 pos 7
+cannot resolve '(CAST('1' AS DOUBLE) + TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(CAST('1' AS DOUBLE) + TIMESTAMP_NTZ '2011-11-11 11:11:11')' (double and timestamp_ntz).; line 1 pos 7
-- !query
-select date'2020-01-01' - timestamp'2019-10-06 10:11:12.345678'
+select timestamp'2011-11-11 11:11:11' + null
-- !query schema
-struct<(DATE '2020-01-01' - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
+struct<>
-- !query output
-86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + NULL)' due to data type mismatch: differing types in '(TIMESTAMP_NTZ '2011-11-11 11:11:11' + NULL)' (timestamp_ntz and void).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - date'2020-01-01'
+select null + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - DATE '2020-01-01'):interval day to second>
+struct<>
-- !query output
--86 13:48:47.654322000
+org.apache.spark.sql.AnalysisException
+cannot resolve '(NULL + TIMESTAMP_NTZ '2011-11-11 11:11:11')' due to data type mismatch: differing types in '(NULL + TIMESTAMP_NTZ '2011-11-11 11:11:11')' (void and timestamp_ntz).; line 1 pos 7
-- !query
-select timestamp'2019-10-06 10:11:12.345678' - null
+select timestamp'2011-11-11 11:11:11' + interval '2' day,
+ timestamp'2011-11-11 11:11:11' - interval '2-2' year to month,
+ timestamp'2011-11-11 11:11:11' + interval '-2' second,
+ timestamp'2011-11-11 11:11:11' - interval '12:12:12.123456789' hour to second,
+ - interval 2 years + timestamp'2011-11-11 11:11:11',
+ interval '1 12' day to hour + timestamp'2011-11-11 11:11:11'
-- !query schema
-struct<(TIMESTAMP_NTZ '2019-10-06 10:11:12.345678' - NULL):interval day to second>
+struct<TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '2' DAY:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '2-2' YEAR TO MONTH:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '-02' SECOND:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' - INTERVAL '12:12:12.123456' HOUR TO SECOND:timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + (- INTERVAL '2' YEAR):timestamp_ntz,TIMESTAMP_NTZ '2011-11-11 11:11:11' + INTERVAL '1 12' DAY TO HOUR:timestamp_ntz>
-- !query output
-NULL
+2011-11-13 11:11:11 2009-09-11 11:11:11 2011-11-11 11:11:09 2011-11-10 22:58:58.876544 2009-11-11 11:11:11 2011-11-12 23:11:11
-- !query
-select null - timestamp'2019-10-06 10:11:12.345678'
+select date '2012-01-01' - interval 3 hours,
+ date '2012-01-01' + interval '12:12:12' hour to second,
+ interval '2' minute + date '2012-01-01'
-- !query schema
-struct<(NULL - TIMESTAMP_NTZ '2019-10-06 10:11:12.345678'):interval day to second>
+struct<DATE '2012-01-01' - INTERVAL '03' HOUR:timestamp,DATE '2012-01-01' + INTERVAL '12:12:12' HOUR TO SECOND:timestamp,DATE '2012-01-01' + INTERVAL '02' MINUTE:timestamp>
-- !query output
-NULL
+2011-12-31 21:00:00 2012-01-01 12:12:12 2012-01-01 00:02:00
-- !query
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org