This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new fcfac8f [SPARK-31527][SQL] date add/subtract interval only allow
those day precision in ansi mode
fcfac8f is described below
commit fcfac8f672ba6c8e49350f2c5fdd957584255327
Author: Kent Yao <[email protected]>
AuthorDate: Mon Apr 27 05:28:46 2020 +0000
[SPARK-31527][SQL] date add/subtract interval only allow those day
precision in ansi mode
To follow ANSIļ¼the expressions - `date + interval`, `interval + date` and
`date - interval` should only accept intervals which the `microseconds` part is
0.
Better ANSI compliance
No, this PR should target 3.0.0 in which this feature is newly added.
add more unit tests
Closes #28310 from yaooqinn/SPARK-31527.
Authored-by: Kent Yao <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit ebc8fa50d0422f3db47b2c45025c7f2efe6ee39a)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/catalyst/analysis/Analyzer.scala | 27 ++++++----
.../catalyst/expressions/datetimeExpressions.scala | 63 ++++++++++++++++++++++
.../spark/sql/catalyst/util/DateTimeUtils.scala | 16 ++++++
.../expressions/DateExpressionsSuite.scala | 36 ++++++++++++-
.../expressions/ExpressionSQLBuilderSuite.scala | 4 ++
.../sql/catalyst/util/DateTimeUtilsSuite.scala | 10 +++-
.../resources/sql-tests/inputs/ansi/datetime.sql | 1 +
.../sql-tests/results/{ => ansi}/datetime.sql.out | 56 ++++++++++++++-----
.../sql-tests/results/ansi/interval.sql.out | 7 +--
.../resources/sql-tests/results/datetime.sql.out | 4 +-
.../resources/sql-tests/results/interval.sql.out | 4 +-
.../typeCoercion/native/dateTimeOperations.sql.out | 6 +--
12 files changed, 198 insertions(+), 36 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 e4be634..6de3189 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
@@ -246,7 +246,7 @@ class Analyzer(
ResolveLambdaVariables(conf) ::
ResolveTimeZone(conf) ::
ResolveRandomSeed ::
- ResolveBinaryArithmetic(conf) ::
+ ResolveBinaryArithmetic ::
TypeCoercion.typeCoercionRules(conf) ++
extendedResolutionRules : _*),
Batch("Post-Hoc Resolution", Once, postHocResolutionRules: _*),
@@ -268,17 +268,21 @@ class Analyzer(
/**
* For [[Add]]:
* 1. if both side are interval, stays the same;
- * 2. else if one side is interval, turns it to [[TimeAdd]];
- * 3. else if one side is date, turns it to [[DateAdd]] ;
- * 4. else stays the same.
+ * 2. else if one side is date and the other is interval,
+ * turns it to [[DateAddInterval]];
+ * 3. else if one side is interval, turns it to [[TimeAdd]];
+ * 4. else if one side is date, turns it to [[DateAdd]] ;
+ * 5. else stays the same.
*
* For [[Subtract]]:
* 1. if both side are interval, stays the same;
- * 2. else if the right side is an interval, turns it to [[TimeSub]];
- * 3. else if one side is timestamp, turns it to [[SubtractTimestamps]];
- * 4. else if the right side is date, turns it to
[[DateDiff]]/[[SubtractDates]];
- * 5. else if the left side is date, turns it to [[DateSub]];
- * 6. else turns it to stays the same.
+ * 2. else if the left side is date and the right side is interval,
+ * turns it to [[DateAddInterval(l, -r)]];
+ * 3. else if the right side is an interval, turns it to [[TimeSub]];
+ * 4. else if one side is timestamp, turns it to [[SubtractTimestamps]];
+ * 5. else if the right side is date, turns it to
[[DateDiff]]/[[SubtractDates]];
+ * 6. else if the left side is date, turns it to [[DateSub]];
+ * 7. else turns it to stays the same.
*
* For [[Multiply]]:
* 1. If one side is interval, turns it to [[MultiplyInterval]];
@@ -288,12 +292,14 @@ class Analyzer(
* 1. If the left side is interval, turns it to [[DivideInterval]];
* 2. otherwise, stays the same.
*/
- case class ResolveBinaryArithmetic(conf: SQLConf) extends Rule[LogicalPlan] {
+ object ResolveBinaryArithmetic extends Rule[LogicalPlan] {
override def apply(plan: LogicalPlan): LogicalPlan =
plan.resolveOperatorsUp {
case p: LogicalPlan => p.transformExpressionsUp {
case a @ Add(l, r) if a.childrenResolved => (l.dataType, r.dataType)
match {
case (CalendarIntervalType, CalendarIntervalType) => a
+ case (DateType, CalendarIntervalType) => DateAddInterval(l, r)
case (_, CalendarIntervalType) => Cast(TimeAdd(l, r), l.dataType)
+ case (CalendarIntervalType, DateType) => DateAddInterval(r, l)
case (CalendarIntervalType, _) => Cast(TimeAdd(r, l), r.dataType)
case (DateType, dt) if dt != StringType => DateAdd(l, r)
case (dt, DateType) if dt != StringType => DateAdd(r, l)
@@ -301,6 +307,7 @@ 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(TimeSub(l, r), l.dataType)
case (TimestampType, _) => SubtractTimestamps(l, r)
case (_, TimestampType) => SubtractTimestamps(l, r)
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 8f57421..6e95f64 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
@@ -35,6 +35,7 @@ import org.apache.spark.sql.catalyst.util.{DateTimeUtils,
LegacyDateFormats, Tim
import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeUtils._
import org.apache.spark.sql.catalyst.util.LegacyDateFormats.SIMPLE_DATE_FORMAT
+import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.types._
import org.apache.spark.unsafe.types.{CalendarInterval, UTF8String}
@@ -1196,6 +1197,68 @@ case class TimeAdd(start: Expression, interval:
Expression, timeZoneId: Option[S
}
/**
+ * Adds date and an interval.
+ *
+ * When ansi mode is on, the microseconds part of interval needs to be 0,
otherwise a runtime
+ * [[IllegalArgumentException]] will be raised.
+ * When ansi mode is off, if the microseconds part of interval is 0, we
perform date + interval
+ * for better performance. if the microseconds part is not 0, then the date
will be converted to a
+ * timestamp to add with the whole interval parts.
+ */
+case class DateAddInterval(
+ start: Expression,
+ interval: Expression,
+ timeZoneId: Option[String] = None,
+ ansiEnabled: Boolean = SQLConf.get.ansiEnabled)
+ extends BinaryExpression with ExpectsInputTypes with TimeZoneAwareExpression
{
+
+ override def left: Expression = start
+ override def right: Expression = interval
+
+ override def toString: String = s"$left + $right"
+ override def sql: String = s"${left.sql} + ${right.sql}"
+ override def inputTypes: Seq[AbstractDataType] = Seq(DateType,
CalendarIntervalType)
+
+ override def dataType: DataType = DateType
+
+ override def nullSafeEval(start: Any, interval: Any): Any = {
+ val itvl = interval.asInstanceOf[CalendarInterval]
+ if (ansiEnabled || itvl.microseconds == 0) {
+ DateTimeUtils.dateAddInterval(start.asInstanceOf[Int], itvl)
+ } else {
+ val startTs = DateTimeUtils.epochDaysToMicros(start.asInstanceOf[Int],
zoneId)
+ val resultTs = DateTimeUtils.timestampAddInterval(
+ startTs, itvl.months, itvl.days, itvl.microseconds, zoneId)
+ DateTimeUtils.microsToEpochDays(resultTs, zoneId)
+ }
+ }
+
+ override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
+ val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
+ nullSafeCodeGen(ctx, ev, (sd, i) => if (ansiEnabled) {
+ s"""${ev.value} = $dtu.dateAddInterval($sd, $i);"""
+ } else {
+ val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
+ val startTs = ctx.freshName("startTs")
+ val resultTs = ctx.freshName("resultTs")
+ s"""
+ |if ($i.microseconds == 0) {
+ | ${ev.value} = $dtu.dateAddInterval($sd, $i);
+ |} else {
+ | long $startTs = $dtu.epochDaysToMicros($sd, $zid);
+ | long $resultTs =
+ | $dtu.timestampAddInterval($startTs, $i.months, $i.days,
$i.microseconds, $zid);
+ | ${ev.value} = $dtu.microsToEpochDays($resultTs, $zid);
+ |}
+ |""".stripMargin
+ })
+ }
+
+ override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
+ copy(timeZoneId = Option(timeZoneId))
+}
+
+/**
* This is a common function for databases supporting TIMESTAMP WITHOUT
TIMEZONE. This function
* takes a timestamp which is timezone-agnostic, and interprets it as a
timestamp in UTC, and
* renders that timestamp as a timestamp in the given time zone.
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
index a2fa87a..fb7875d 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
@@ -619,6 +619,22 @@ object DateTimeUtils {
}
/**
+ * Add the date and the interval's months and days.
+ * Returns a date value, expressed in days since 1.1.1970.
+ *
+ * @throws DateTimeException if the result exceeds the supported date range
+ * @throws IllegalArgumentException if the interval has `microseconds` part
+ */
+ def dateAddInterval(
+ start: SQLDate,
+ interval: CalendarInterval): SQLDate = {
+ require(interval.microseconds == 0,
+ "Cannot add hours, minutes or seconds, milliseconds, microseconds to a
date")
+ val ld =
LocalDate.ofEpochDay(start).plusMonths(interval.months).plusDays(interval.days)
+ localDateToDays(ld)
+ }
+
+ /**
* Returns number of months between time1 and time2. time1 and time2 are
expressed in
* microseconds since 1.1.1970. If time1 is later than time2, the result is
positive.
*
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
index 1d01f4b..dd5a532 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/DateExpressionsSuite.scala
@@ -27,7 +27,7 @@ import org.apache.spark.{SparkFunSuite, SparkUpgradeException}
import org.apache.spark.sql.catalyst.InternalRow
import
org.apache.spark.sql.catalyst.expressions.codegen.GenerateUnsafeProjection
import org.apache.spark.sql.catalyst.util.{DateTimeUtils, IntervalUtils,
TimestampFormatter}
-import org.apache.spark.sql.catalyst.util.DateTimeConstants.NANOS_PER_SECOND
+import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.types._
@@ -359,6 +359,40 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
checkConsistencyBetweenInterpretedAndCodegen(DateAdd, DateType,
IntegerType)
}
+ test("date add interval") {
+ val d = Date.valueOf("2016-02-28")
+ Seq("true", "false") foreach { flag =>
+ withSQLConf((SQLConf.ANSI_ENABLED.key, flag)) {
+ checkEvaluation(
+ DateAddInterval(Literal(d), Literal(new CalendarInterval(0, 1, 0))),
+ DateTimeUtils.fromJavaDate(Date.valueOf("2016-02-29")))
+ checkEvaluation(
+ DateAddInterval(Literal(d), Literal(new CalendarInterval(1, 1, 0))),
+ DateTimeUtils.fromJavaDate(Date.valueOf("2016-03-29")))
+ checkEvaluation(DateAddInterval(Literal(d), Literal.create(null,
CalendarIntervalType)),
+ null)
+ checkEvaluation(DateAddInterval(Literal.create(null, DateType),
+ Literal(new CalendarInterval(1, 1, 0))),
+ null)
+ }
+ }
+
+ withSQLConf((SQLConf.ANSI_ENABLED.key, "true")) {
+ checkExceptionInExpression[IllegalArgumentException](
+ DateAddInterval(Literal(d), Literal(new CalendarInterval(1, 1, 25 *
MICROS_PER_HOUR))),
+ "Cannot add hours, minutes or seconds, milliseconds, microseconds to a
date")
+ }
+
+ withSQLConf((SQLConf.ANSI_ENABLED.key, "false")) {
+ checkEvaluation(
+ DateAddInterval(Literal(d), Literal(new CalendarInterval(1, 1, 25))),
+ DateTimeUtils.fromJavaDate(Date.valueOf("2016-03-29")))
+ checkEvaluation(
+ DateAddInterval(Literal(d), Literal(new CalendarInterval(1, 1, 25 *
MICROS_PER_HOUR))),
+ DateTimeUtils.fromJavaDate(Date.valueOf("2016-03-30")))
+ }
+ }
+
test("date_sub") {
checkEvaluation(
DateSub(Literal(Date.valueOf("2015-01-01")), Literal(1.toByte)),
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 492d97b..ab0ef22 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
@@ -176,5 +176,9 @@ class ExpressionSQLBuilderSuite extends SparkFunSuite {
TimeSub('a, interval),
"`a` - INTERVAL '1 hours'"
)
+ checkSQL(
+ DateAddInterval('a, interval),
+ "`a` + INTERVAL '1 hours'"
+ )
}
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
index 3981792..b547c44 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/util/DateTimeUtilsSuite.scala
@@ -30,7 +30,7 @@ import org.apache.spark.sql.catalyst.plans.SQLHelper
import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
import org.apache.spark.sql.catalyst.util.DateTimeUtils._
-import org.apache.spark.unsafe.types.UTF8String
+import org.apache.spark.unsafe.types.{CalendarInterval, UTF8String}
class DateTimeUtilsSuite extends SparkFunSuite with Matchers with SQLHelper {
@@ -391,6 +391,14 @@ class DateTimeUtilsSuite extends SparkFunSuite with
Matchers with SQLHelper {
assert(dateAddMonths(input, -13) === days(1996, 1, 28))
}
+ test("date add interval with day precision") {
+ val input = days(1997, 2, 28, 10, 30)
+ assert(dateAddInterval(input, new CalendarInterval(36, 0, 0)) ===
days(2000, 2, 28))
+ assert(dateAddInterval(input, new CalendarInterval(36, 47, 0)) ===
days(2000, 4, 15))
+ assert(dateAddInterval(input, new CalendarInterval(-13, 0, 0)) ===
days(1996, 1, 28))
+ intercept[IllegalArgumentException](dateAddInterval(input, new
CalendarInterval(36, 47, 1)))
+ }
+
test("timestamp add months") {
val ts1 = date(1997, 2, 28, 10, 30, 0)
val ts2 = date(2000, 2, 28, 10, 30, 0, 123000)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/ansi/datetime.sql
b/sql/core/src/test/resources/sql-tests/inputs/ansi/datetime.sql
new file mode 100644
index 0000000..58ecf80
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/ansi/datetime.sql
@@ -0,0 +1 @@
+--IMPORT datetime.sql
diff --git a/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
old mode 100755
new mode 100644
similarity index 93%
copy from sql/core/src/test/resources/sql-tests/results/datetime.sql.out
copy to sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
index aec04da..81a73a6
--- a/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/datetime.sql.out
@@ -5,9 +5,15 @@
-- !query
select current_date = current_date(), current_timestamp = current_timestamp()
-- !query schema
-struct<(current_date() = current_date()):boolean,(current_timestamp() =
current_timestamp()):boolean>
+struct<>
-- !query output
-true true
+org.apache.spark.sql.catalyst.parser.ParseException
+
+no viable alternative at input 'current_date'(line 1, pos 22)
+
+== SQL ==
+select current_date = current_date(), current_timestamp = current_timestamp()
+----------------------^^^
-- !query
@@ -42,16 +48,25 @@ create temporary view ttf1 as select * from values
-- !query schema
struct<>
-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+no viable alternative at input 'current_date'(line 4, pos 10)
+
+== SQL ==
+create temporary view ttf1 as select * from values
+ (1, 2),
+ (2, 3)
+ as ttf1(current_date, current_timestamp)
+----------^^^
-- !query
select current_date, current_timestamp from ttf1
-- !query schema
-struct<current_date:int,current_timestamp:int>
+struct<>
-- !query output
-1 2
-2 3
+org.apache.spark.sql.AnalysisException
+Table or view not found: ttf1; line 1 pos 44
-- !query
@@ -68,10 +83,15 @@ struct<>
-- !query
select current_date = current_date(), current_timestamp = current_timestamp(),
a, b from ttf2
-- !query schema
-struct<(current_date() = current_date()):boolean,(current_timestamp() =
current_timestamp()):boolean,a:int,b:int>
+struct<>
-- !query output
-true true 1 2
-true true 2 3
+org.apache.spark.sql.catalyst.parser.ParseException
+
+no viable alternative at input 'current_date'(line 1, pos 22)
+
+== SQL ==
+select current_date = current_date(), current_timestamp = current_timestamp(),
a, b from ttf2
+----------------------^^^
-- !query
@@ -94,9 +114,15 @@ struct<weekday(CAST(2007-02-03 AS
DATE)):int,weekday(CAST(2009-07-30 AS DATE)):i
-- !query
select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01')
-- !query schema
-struct<year(CAST(1500-01-01 AS DATE)):int,month(CAST(1500-01-01 AS
DATE)):int,dayofyear(CAST(1500-01-01 AS DATE)):int>
+struct<>
-- !query output
-1500 1 1
+org.apache.spark.sql.catalyst.parser.ParseException
+
+no viable alternative at input 'year'(line 1, pos 7)
+
+== SQL ==
+select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01')
+-------^^^
-- !query
@@ -134,17 +160,19 @@ struct<CAST(TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2
days' AS TIMESTAMP):ti
-- !query
select date'2011-11-11 11:11:11' + interval '2' second
-- !query schema
-struct<CAST(CAST(DATE '2011-11-11' AS TIMESTAMP) + INTERVAL '2 seconds' AS
DATE):date>
+struct<>
-- !query output
-2011-11-11
+java.lang.IllegalArgumentException
+requirement failed: Cannot add hours, minutes or seconds, milliseconds,
microseconds to a date
-- !query
select date'2011-11-11 11:11:11' - interval '2' second
-- !query schema
-struct<CAST(CAST(DATE '2011-11-11' AS TIMESTAMP) - INTERVAL '2 seconds' AS
DATE):date>
+struct<>
-- !query output
-2011-11-10
+java.lang.IllegalArgumentException
+requirement failed: Cannot add hours, minutes or seconds, milliseconds,
microseconds to a date
-- !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 d4238c7..e8201d4 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
@@ -689,7 +689,7 @@ select
interval '2-2' year to month + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '2 years 2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '-2 years -2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '2 years 2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '-2 years -2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- INTERVAL '2 years 2
months') AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '2 years 2
months' AS DATE):date>
+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>
-- !query output
2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
@@ -732,9 +732,10 @@ select
interval '99 11:22:33.123456789' day to second + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS DATE):date,CAST(CAST(dateval AS
TIMESTAMP) - INTERVAL '-99 days -11 hours -22 minutes -33.123456 seconds' AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '99 days 11 hours 22
minutes 33.123456 seconds' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) +
INTERVAL '-99 days -11 hours -22 minutes -33.123456 seconds' AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- INTERVA [...]
+struct<>
-- !query output
-2012-01-01 2011-09-23 2012-04-09 2012-04-09 2011-09-23
2011-09-23 2012-04-09
+java.lang.IllegalArgumentException
+requirement failed: Cannot add hours, minutes or seconds, milliseconds,
microseconds to a date
-- !query
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 aec04da..2e60085 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
@@ -134,7 +134,7 @@ struct<CAST(TIMESTAMP '2011-11-11 11:11:11' - INTERVAL '2
days' AS TIMESTAMP):ti
-- !query
select date'2011-11-11 11:11:11' + interval '2' second
-- !query schema
-struct<CAST(CAST(DATE '2011-11-11' AS TIMESTAMP) + INTERVAL '2 seconds' AS
DATE):date>
+struct<DATE '2011-11-11' + INTERVAL '2 seconds':date>
-- !query output
2011-11-11
@@ -142,7 +142,7 @@ struct<CAST(CAST(DATE '2011-11-11' AS TIMESTAMP) + INTERVAL
'2 seconds' AS DATE)
-- !query
select date'2011-11-11 11:11:11' - interval '2' second
-- !query schema
-struct<CAST(CAST(DATE '2011-11-11' AS TIMESTAMP) - INTERVAL '2 seconds' AS
DATE):date>
+struct<DATE '2011-11-11' + (- INTERVAL '2 seconds'):date>
-- !query output
2011-11-10
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 0d758ca..84e3fb3 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
@@ -668,7 +668,7 @@ select
interval '2-2' year to month + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '2 years 2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '-2 years -2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '2 years 2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '-2 years -2
months' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- INTERVAL '2 years 2
months') AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '2 years 2
months' AS DATE):date>
+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>
-- !query output
2012-01-01 2009-11-01 2014-03-01 2014-03-01 2009-11-01
2009-11-01 2014-03-01
@@ -711,7 +711,7 @@ select
interval '99 11:22:33.123456789' day to second + dateval
from interval_arithmetic
-- !query schema
-struct<dateval:date,CAST(CAST(dateval AS TIMESTAMP) - INTERVAL '99 days 11
hours 22 minutes 33.123456 seconds' AS DATE):date,CAST(CAST(dateval AS
TIMESTAMP) - INTERVAL '-99 days -11 hours -22 minutes -33.123456 seconds' AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + INTERVAL '99 days 11 hours 22
minutes 33.123456 seconds' AS DATE):date,CAST(CAST(dateval AS TIMESTAMP) +
INTERVAL '-99 days -11 hours -22 minutes -33.123456 seconds' AS
DATE):date,CAST(CAST(dateval AS TIMESTAMP) + (- INTERVA [...]
+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>
-- !query output
2012-01-01 2011-09-23 2012-04-09 2012-04-09 2011-09-23
2011-09-23 2012-04-09
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 d5c27ad..7b1fcdd 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
@@ -118,7 +118,7 @@ struct<CAST(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) +
INTERVAL '2 days' AS TIME
-- !query
select cast('2017-12-11 09:30:00' as date) + interval 2 day
-- !query schema
-struct<CAST(CAST(CAST(2017-12-11 09:30:00 AS DATE) AS TIMESTAMP) + INTERVAL '2
days' AS DATE):date>
+struct<CAST(2017-12-11 09:30:00 AS DATE) + INTERVAL '2 days':date>
-- !query output
2017-12-13
@@ -231,7 +231,7 @@ struct<CAST(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) +
INTERVAL '2 days' AS TIME
-- !query
select interval 2 day + cast('2017-12-11 09:30:00' as date)
-- !query schema
-struct<CAST(CAST(CAST(2017-12-11 09:30:00 AS DATE) AS TIMESTAMP) + INTERVAL '2
days' AS DATE):date>
+struct<CAST(2017-12-11 09:30:00 AS DATE) + INTERVAL '2 days':date>
-- !query output
2017-12-13
@@ -344,6 +344,6 @@ struct<CAST(CAST(2017-12-11 09:30:00.0 AS TIMESTAMP) -
INTERVAL '2 days' AS TIME
-- !query
select cast('2017-12-11 09:30:00' as date) - interval 2 day
-- !query schema
-struct<CAST(CAST(CAST(2017-12-11 09:30:00 AS DATE) AS TIMESTAMP) - INTERVAL '2
days' AS DATE):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]
