This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 9241f82 [SPARK-31586][SQL][FOLLOWUP] Restore SQL string for datetime
- interval operations
9241f82 is described below
commit 9241f8282ff894a6a1f1cf24125fa817e8e919f1
Author: Kent Yao <[email protected]>
AuthorDate: Thu Apr 30 03:31:29 2020 +0000
[SPARK-31586][SQL][FOLLOWUP] Restore SQL string for datetime - interval
operations
### What changes were proposed in this pull request?
Because of
https://github.com/apache/spark/commit/ebc8fa50d0422f3db47b2c45025c7f2efe6ee39a
and
https://github.com/apache/spark/commit/beec8d535f093f1867678fe5afeb02453464f90d,
the SQL output strings for date/timestamp - interval operation will have a
malformed format, such as `struct<dateval:date,dateval + (- INTERVAL '2 years 2
months').....`
This PR restore this behavior by adding one `RuntimeReplaceable
`implementation for both of the operations to have their pretty SQL strings
back.
### Why are the changes needed?
restore the SQL string for datetime operations
### Does this PR introduce any user-facing change?
NO, we are restoring here
### How was this patch tested?
added unit tests
Closes #28402 from yaooqinn/SPARK-31586-F.
Authored-by: Kent Yao <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/catalyst/analysis/Analyzer.scala | 7 ++++--
.../catalyst/analysis/StreamingJoinHelper.scala | 1 +
.../catalyst/expressions/datetimeExpressions.scala | 12 ++++++++++
.../expressions/ExpressionSQLBuilderSuite.scala | 5 ++++
.../test/resources/sql-tests/inputs/interval.sql | 13 +++++++++-
.../sql-tests/results/ansi/datetime.sql.out | 8 +++----
.../sql-tests/results/ansi/interval.sql.out | 26 ++++++++++++++++----
.../resources/sql-tests/results/datetime.sql.out | 10 ++++----
.../resources/sql-tests/results/interval.sql.out | 28 +++++++++++++++++-----
.../typeCoercion/native/dateTimeOperations.sql.out | 8 +++----
10 files changed, 91 insertions(+), 27 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
index 2b889c6..3505dcc 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/Analyzer.scala
@@ -307,8 +307,10 @@ class Analyzer(
}
case s @ Subtract(l, r) if s.childrenResolved => (l.dataType,
r.dataType) match {
case (CalendarIntervalType, CalendarIntervalType) => s
- case (DateType, CalendarIntervalType) => DateAddInterval(l,
UnaryMinus(r))
- case (_, CalendarIntervalType) => Cast(TimeAdd(l, UnaryMinus(r)),
l.dataType)
+ case (DateType, CalendarIntervalType) =>
+ DatetimeSub(l, r, DateAddInterval(l, UnaryMinus(r)))
+ case (_, CalendarIntervalType) =>
+ Cast(DatetimeSub(l, r, TimeAdd(l, UnaryMinus(r))), l.dataType)
case (TimestampType, _) => SubtractTimestamps(l, r)
case (_, TimestampType) => SubtractTimestamps(l, r)
case (_, DateType) => SubtractDates(l, r)
@@ -327,6 +329,7 @@ class Analyzer(
}
}
}
+
/**
* Substitute child plan with WindowSpecDefinitions.
*/
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StreamingJoinHelper.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StreamingJoinHelper.scala
index 21f2985..6a2ff4b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StreamingJoinHelper.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/StreamingJoinHelper.scala
@@ -232,6 +232,7 @@ object StreamingJoinHelper extends PredicateHelper with
Logging {
collect(left, negate) ++ collect(right, !negate)
case TimeAdd(left, right, _) =>
collect(left, negate) ++ collect(right, negate)
+ case DatetimeSub(_, _, child) => collect(child, negate)
case UnaryMinus(child) =>
collect(child, !negate)
case CheckOverflow(child, _, _) =>
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
index 26458a6..7dfa5fa 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/datetimeExpressions.scala
@@ -1198,6 +1198,18 @@ case class TimeAdd(start: Expression, interval:
Expression, timeZoneId: Option[S
}
/**
+ * Subtract an interval from timestamp or date, which is only used to give a
pretty sql string
+ * for `datetime - interval` operations
+ */
+case class DatetimeSub(
+ start: Expression,
+ interval: Expression,
+ child: Expression) extends RuntimeReplaceable {
+ override def toString: String = s"$start - $interval"
+ override def sql: String = s"${start.sql} - ${interval.sql}"
+}
+
+/**
* Adds date and an interval.
*
* When ansi mode is on, the microseconds part of interval needs to be 0,
otherwise a runtime
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ExpressionSQLBuilderSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ExpressionSQLBuilderSuite.scala
index 7ffbae8..c12dd30 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ExpressionSQLBuilderSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ExpressionSQLBuilderSuite.scala
@@ -173,6 +173,11 @@ class ExpressionSQLBuilderSuite extends SparkFunSuite {
)
checkSQL(
+ DatetimeSub('a, interval, Literal.default(TimestampType)),
+ "`a` - INTERVAL '1 hours'"
+ )
+
+ checkSQL(
DateAddInterval('a, interval),
"`a` + INTERVAL '1 hours'"
)
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 fec11b4..451e261 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/interval.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/interval.sql
@@ -91,7 +91,7 @@ select interval 30 day day day;
-- Interval year-month arithmetic
create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp) from values
+ 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);
@@ -142,6 +142,17 @@ select
interval '99 11:22:33.123456789' day to second + tsval
from interval_arithmetic;
+-- datetimes(in string representation) + intervals
+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
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
index b95891f..05c335b 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
@@ -152,7 +152,7 @@ struct<CAST(TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2
days' AS TIMESTAMP):ti
-- !query
select timestamp'2011-11-11 11:11:11' - interval '2' day
-- !query schema
-struct<CAST(TIMESTAMP '2011-11-11 11:11:11' + (- INTERVAL '2 days') AS
TIMESTAMP):timestamp>
+struct<CAST(TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2 days' AS
TIMESTAMP):timestamp>
-- !query output
2011-11-09 11:11:11
@@ -178,7 +178,7 @@ requirement failed: Cannot add hours, minutes or seconds,
milliseconds, microsec
-- !query
select '2011-11-11' - interval '2' day
-- !query schema
-struct<CAST(CAST(2011-11-11 AS TIMESTAMP) + (- INTERVAL '2 days') AS
STRING):string>
+struct<CAST('2011-11-11' - INTERVAL '2 days' AS STRING):string>
-- !query output
2011-11-09 00:00:00
@@ -186,7 +186,7 @@ struct<CAST(CAST(2011-11-11 AS TIMESTAMP) + (- INTERVAL '2
days') AS STRING):str
-- !query
select '2011-11-11 11:11:11' - interval '2' second
-- !query schema
-struct<CAST(CAST(2011-11-11 11:11:11 AS TIMESTAMP) + (- INTERVAL '2 seconds')
AS STRING):string>
+struct<CAST('2011-11-11 11:11:11' - INTERVAL '2 seconds' AS STRING):string>
-- !query output
2011-11-11 11:11:09
@@ -194,7 +194,7 @@ struct<CAST(CAST(2011-11-11 11:11:11 AS TIMESTAMP) + (-
INTERVAL '2 seconds') AS
-- !query
select '1' - interval '2' second
-- !query schema
-struct<CAST(CAST(1 AS TIMESTAMP) + (- INTERVAL '2 seconds') AS STRING):string>
+struct<CAST('1' - INTERVAL '2 seconds' AS STRING):string>
-- !query output
NULL
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 d4a3f0a..89768d3 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: 89
+-- Number of queries: 90
-- !query
@@ -669,7 +669,7 @@ select interval 30 day day day
-- !query
create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp) from values
+ 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)
-- !query schema
@@ -689,7 +689,7 @@ select
interval '2-2' year to month + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,dateval + (- INTERVAL '2 years 2 months'):date,dateval +
(- INTERVAL '-2 years -2 months'):date,dateval + INTERVAL '2 years 2
months':date,dateval + INTERVAL '-2 years -2 months':date,dateval + (- INTERVAL
'2 years 2 months'):date,dateval + INTERVAL '2 years 2 months':date>
+struct<dateval:date,interval_arithmetic.`dateval` - INTERVAL '2 years 2
months':date,interval_arithmetic.`dateval` - INTERVAL '-2 years -2
months':date,dateval + INTERVAL '2 years 2 months':date,dateval + INTERVAL '-2
years -2 months':date,dateval + (- INTERVAL '2 years 2 months'):date,dateval +
INTERVAL '2 years 2 months':date>
-- !query output
2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
@@ -705,7 +705,7 @@ select
interval '2-2' year to month + tsval
from interval_arithmetic
-- !query schema
-struct<tsval:timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '-2 years -2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-2 years -2 months' AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp>
+struct<tsval:timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '2 years 2
months' AS TIMESTAMP):timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '-2
years -2 months' AS TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2
months' AS TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-2 years -2 months' AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp>
-- !query 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
@@ -749,13 +749,29 @@ select
interval '99 11:22:33.123456789' day to second + tsval
from interval_arithmetic
-- !query schema
-struct<tsval:timestamp,CAST(tsval + (- INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds') AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '-99 days
-11 hours -22 minutes -33.123456 seconds') AS TIMESTAMP):timestamp,CAST(tsval +
INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '99 days
11 hours 22 minutes 33.123456 seconds') AS [...]
+struct<tsval:timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '-99 days -11
hours -22 minutes -33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval +
INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '99 days
11 hour [...]
-- !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
-- !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
+-- !query schema
+struct<strval:string,CAST(interval_arithmetic.`strval` - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS
STRING):string,CAST(interval_arithmetic.`strval` - INTERVAL '-99 days -11 hours
-22 minutes -33.123456 seconds' AS STRING):string,CAST(CAST(strval AS
TIMESTAMP) + INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
STRING):string,CAST(CAST(strval AS TIMESTAMP) + INTERVAL '-99 days -11 hours
-22 minutes -33.123456 seconds' AS STRING):string,CAST(CAST(strval AS TIMES
[...]
+-- !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
diff --git a/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
b/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
index 20f66ee..e599153 100755
--- a/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
@@ -126,7 +126,7 @@ struct<CAST(TIMESTAMP '2011-11-11 11:11:11' + INTERVAL '2
days' AS TIMESTAMP):ti
-- !query
select timestamp'2011-11-11 11:11:11' - interval '2' day
-- !query schema
-struct<CAST(TIMESTAMP '2011-11-11 11:11:11' + (- INTERVAL '2 days') AS
TIMESTAMP):timestamp>
+struct<CAST(TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2 days' AS
TIMESTAMP):timestamp>
-- !query output
2011-11-09 11:11:11
@@ -142,7 +142,7 @@ struct<DATE '2011-11-11' + INTERVAL '2 seconds':date>
-- !query
select date'2011-11-11 11:11:11' - interval '2' second
-- !query schema
-struct<DATE '2011-11-11' + (- INTERVAL '2 seconds'):date>
+struct<DATE '2011-11-11' - INTERVAL '2 seconds':date>
-- !query output
2011-11-10
@@ -150,7 +150,7 @@ struct<DATE '2011-11-11' + (- INTERVAL '2 seconds'):date>
-- !query
select '2011-11-11' - interval '2' day
-- !query schema
-struct<CAST(CAST(2011-11-11 AS TIMESTAMP) + (- INTERVAL '2 days') AS
STRING):string>
+struct<CAST('2011-11-11' - INTERVAL '2 days' AS STRING):string>
-- !query output
2011-11-09 00:00:00
@@ -158,7 +158,7 @@ struct<CAST(CAST(2011-11-11 AS TIMESTAMP) + (- INTERVAL '2
days') AS STRING):str
-- !query
select '2011-11-11 11:11:11' - interval '2' second
-- !query schema
-struct<CAST(CAST(2011-11-11 11:11:11 AS TIMESTAMP) + (- INTERVAL '2 seconds')
AS STRING):string>
+struct<CAST('2011-11-11 11:11:11' - INTERVAL '2 seconds' AS STRING):string>
-- !query output
2011-11-11 11:11:09
@@ -166,7 +166,7 @@ struct<CAST(CAST(2011-11-11 11:11:11 AS TIMESTAMP) + (-
INTERVAL '2 seconds') AS
-- !query
select '1' - interval '2' second
-- !query schema
-struct<CAST(CAST(1 AS TIMESTAMP) + (- INTERVAL '2 seconds') AS STRING):string>
+struct<CAST('1' - INTERVAL '2 seconds' AS STRING):string>
-- !query output
NULL
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 7a9f08b..1dbe76f 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: 89
+-- Number of queries: 90
-- !query
@@ -648,7 +648,7 @@ select interval 30 day day day
-- !query
create temporary view interval_arithmetic as
- select CAST(dateval AS date), CAST(tsval AS timestamp) from values
+ 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)
-- !query schema
@@ -668,7 +668,7 @@ select
interval '2-2' year to month + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,dateval + (- INTERVAL '2 years 2 months'):date,dateval +
(- INTERVAL '-2 years -2 months'):date,dateval + INTERVAL '2 years 2
months':date,dateval + INTERVAL '-2 years -2 months':date,dateval + (- INTERVAL
'2 years 2 months'):date,dateval + INTERVAL '2 years 2 months':date>
+struct<dateval:date,interval_arithmetic.`dateval` - INTERVAL '2 years 2
months':date,interval_arithmetic.`dateval` - INTERVAL '-2 years -2
months':date,dateval + INTERVAL '2 years 2 months':date,dateval + INTERVAL '-2
years -2 months':date,dateval + (- INTERVAL '2 years 2 months'):date,dateval +
INTERVAL '2 years 2 months':date>
-- !query output
2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
@@ -684,7 +684,7 @@ select
interval '2-2' year to month + tsval
from interval_arithmetic
-- !query schema
-struct<tsval:timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '-2 years -2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-2 years -2 months' AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp>
+struct<tsval:timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '2 years 2
months' AS TIMESTAMP):timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '-2
years -2 months' AS TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2
months' AS TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-2 years -2 months' AS
TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '2 years 2 months') AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '2 years 2 months' AS
TIMESTAMP):timestamp>
-- !query 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
@@ -711,7 +711,7 @@ select
interval '99 11:22:33.123456789' day to second + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,dateval + (- INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds'):date,dateval + (- INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds'):date,dateval + INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds':date,dateval + INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds':date,dateval + (- INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds'):date,dateval + INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds':date>
+struct<dateval:date,interval_arithmetic.`dateval` - INTERVAL '99 days 11 hours
22 minutes 33.123456 seconds':date,interval_arithmetic.`dateval` - INTERVAL
'-99 days -11 hours -22 minutes -33.123456 seconds':date,dateval + INTERVAL '99
days 11 hours 22 minutes 33.123456 seconds':date,dateval + INTERVAL '-99 days
-11 hours -22 minutes -33.123456 seconds':date,dateval + (- INTERVAL '99 days
11 hours 22 minutes 33.123456 seconds'):date,dateval + INTERVAL '99 days 11
hours 22 minutes 33.12345 [...]
-- !query output
2012-01-01 2011-09-23 2012-04-09 2012-04-09 2011-09-23
2011-09-23 2012-04-09
@@ -727,13 +727,29 @@ select
interval '99 11:22:33.123456789' day to second + tsval
from interval_arithmetic
-- !query schema
-struct<tsval:timestamp,CAST(tsval + (- INTERVAL '99 days 11 hours 22 minutes
33.123456 seconds') AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '-99 days
-11 hours -22 minutes -33.123456 seconds') AS TIMESTAMP):timestamp,CAST(tsval +
INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '99 days
11 hours 22 minutes 33.123456 seconds') AS [...]
+struct<tsval:timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(interval_arithmetic.`tsval` - INTERVAL '-99 days -11
hours -22 minutes -33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval +
INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
TIMESTAMP):timestamp,CAST(tsval + INTERVAL '-99 days -11 hours -22 minutes
-33.123456 seconds' AS TIMESTAMP):timestamp,CAST(tsval + (- INTERVAL '99 days
11 hour [...]
-- !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
-- !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
+-- !query schema
+struct<strval:string,CAST(interval_arithmetic.`strval` - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS
STRING):string,CAST(interval_arithmetic.`strval` - INTERVAL '-99 days -11 hours
-22 minutes -33.123456 seconds' AS STRING):string,CAST(CAST(strval AS
TIMESTAMP) + INTERVAL '99 days 11 hours 22 minutes 33.123456 seconds' AS
STRING):string,CAST(CAST(strval AS TIMESTAMP) + INTERVAL '-99 days -11 hours
-22 minutes -33.123456 seconds' AS STRING):string,CAST(CAST(strval AS TIMES
[...]
+-- !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
diff --git
a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/dateTimeOperations.sql.out
b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/dateTimeOperations.sql.out
index db6ffd5..f81e9f6 100644
---
a/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/dateTimeOperations.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/typeCoercion/native/dateTimeOperations.sql.out
@@ -302,7 +302,7 @@ cannot resolve 'CAST(1 AS DECIMAL(10,0)) + (- INTERVAL '2
days')' due to data ty
-- !query
select cast('2017-12-11' as string) - interval 2 day
-- !query schema
-struct<CAST(CAST(CAST(2017-12-11 AS STRING) AS TIMESTAMP) + (- INTERVAL '2
days') AS STRING):string>
+struct<CAST(CAST('2017-12-11' AS STRING) - INTERVAL '2 days' AS STRING):string>
-- !query output
2017-12-09 00:00:00
@@ -310,7 +310,7 @@ struct<CAST(CAST(CAST(2017-12-11 AS STRING) AS TIMESTAMP) +
(- INTERVAL '2 days'
-- !query
select cast('2017-12-11 09:30:00' as string) - interval 2 day
-- !query schema
-struct<CAST(CAST(CAST(2017-12-11 09:30:00 AS STRING) AS TIMESTAMP) + (-
INTERVAL '2 days') AS STRING):string>
+struct<CAST(CAST('2017-12-11 09:30:00' AS STRING) - INTERVAL '2 days' AS
STRING):string>
-- !query output
2017-12-09 09:30:00
@@ -336,7 +336,7 @@ cannot resolve 'CAST(1 AS BOOLEAN) + (- INTERVAL '2 days')'
due to data type mis
-- !query
select cast('2017-12-11 09:30:00.0' as timestamp) - interval 2 day
-- !query schema
-struct<CAST(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) + (- INTERVAL '2 days')
AS TIMESTAMP):timestamp>
+struct<CAST(CAST('2017-12-11 09:30:00.0' AS TIMESTAMP) - INTERVAL '2 days' AS
TIMESTAMP):timestamp>
-- !query output
2017-12-09 09:30:00
@@ -344,6 +344,6 @@ struct<CAST(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) + (-
INTERVAL '2 days') AS
-- !query
select cast('2017-12-11 09:30:00' as date) - interval 2 day
-- !query schema
-struct<CAST(2017-12-11 09:30:00 AS DATE) + (- INTERVAL '2 days'):date>
+struct<CAST('2017-12-11 09:30:00' AS DATE) - INTERVAL '2 days':date>
-- !query output
2017-12-09
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
