You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ma...@apache.org on 2021/10/13 12:12:37 UTC
[spark] branch master updated: [SPARK-36921][SQL] Support ANSI
intervals by `DIV`
This is an automated email from the ASF dual-hosted git repository.
maxgekk 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 1ccc4dc [SPARK-36921][SQL] Support ANSI intervals by `DIV`
1ccc4dc is described below
commit 1ccc4dc5a82cecadc8ccc2a1f8aefa6845efd4be
Author: PengLei <pe...@gmail.com>
AuthorDate: Wed Oct 13 15:11:42 2021 +0300
[SPARK-36921][SQL] Support ANSI intervals by `DIV`
### What changes were proposed in this pull request?
1. support div(YearMonthIntervalType, YearMonthIntervalType), return long result
2. support div(DayTimeIntervalType, DayTimeIntervalType), return long result
3. if input is NULL or input2 is 0, then return null
### Why are the changes needed?
Extended the div function to support ANSI intervals. The operation should produce quotient of division.
[SPARK-36921](https://issues.apache.org/jira/browse/SPARK-36921)
### Does this PR introduce _any_ user-facing change?
Yes, user can use user can use YearMonthIntervalType and DayTimeIntervalType as input for div function.
### How was this patch tested?
Add ut testcase
Closes #34257 from Peng-Lei/SPARK-36921.
Authored-by: PengLei <pe...@gmail.com>
Signed-off-by: Max Gekk <ma...@gmail.com>
---
.../sql/catalyst/expressions/arithmetic.scala | 9 ++-
.../expressions/ArithmeticExpressionSuite.scala | 81 ++++++++++++++++++++++
.../test/resources/sql-tests/inputs/interval.sql | 5 ++
.../sql-tests/results/ansi/interval.sql.out | 43 +++++++++++-
.../resources/sql-tests/results/interval.sql.out | 43 +++++++++++-
5 files changed, 178 insertions(+), 3 deletions(-)
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala
index b77d558..06e8982 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/arithmetic.scala
@@ -568,6 +568,8 @@ case class Divide(
Examples:
> SELECT 3 _FUNC_ 2;
1
+ > SELECT INTERVAL '1-1' YEAR TO MONTH _FUNC_ INTERVAL '-1' MONTH;
+ -13
""",
since = "3.0.0",
group = "math_funcs")
@@ -584,7 +586,8 @@ case class IntegralDivide(
case _ => false
}
- override def inputType: AbstractDataType = TypeCollection(LongType, DecimalType)
+ override def inputType: AbstractDataType = TypeCollection(
+ LongType, DecimalType, YearMonthIntervalType, DayTimeIntervalType)
override def dataType: DataType = LongType
@@ -599,6 +602,10 @@ case class IntegralDivide(
i.integral.asInstanceOf[Integral[Any]]
case d: DecimalType =>
d.asIntegral.asInstanceOf[Integral[Any]]
+ case _: YearMonthIntervalType =>
+ IntegerType.integral.asInstanceOf[Integral[Any]]
+ case _: DayTimeIntervalType =>
+ LongType.integral.asInstanceOf[Integral[Any]]
}
(x, y) => {
val res = integral.quot(x, y)
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ArithmeticExpressionSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ArithmeticExpressionSuite.scala
index af1bc72..31d7a4b 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ArithmeticExpressionSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/ArithmeticExpressionSuite.scala
@@ -699,4 +699,85 @@ class ArithmeticExpressionSuite extends SparkFunSuite with ExpressionEvalHelper
checkConsistencyBetweenInterpretedAndCodegen((e: Expression) => Abs(e, false), tpe)
}
}
+
+ test("SPARK-36921: Support YearMonthIntervalType by div") {
+ checkEvaluation(IntegralDivide(Literal(Period.ZERO), Literal(Period.ZERO)), null)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal(Period.ZERO)), null)
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MinValue),
+ Literal(Period.ZERO)), null)
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MaxValue),
+ Literal(Period.ZERO)), null)
+
+ checkEvaluation(IntegralDivide(Literal.create(null, YearMonthIntervalType()),
+ Literal.create(null, YearMonthIntervalType())), null)
+ checkEvaluation(IntegralDivide(Literal.create(null, YearMonthIntervalType()),
+ Literal(Period.ofYears(1))), null)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal.create(null, YearMonthIntervalType())), null)
+
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MaxValue),
+ Period.ofMonths(Int.MaxValue)), 1L)
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MaxValue),
+ Period.ofMonths(Int.MinValue)), 0L)
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MinValue),
+ Period.ofMonths(Int.MinValue)), 1L)
+ checkEvaluation(IntegralDivide(Period.ofMonths(Int.MinValue),
+ Period.ofMonths(Int.MaxValue)), -1L)
+
+ checkEvaluation(IntegralDivide(Literal(Period.ZERO),
+ Literal(Period.ofYears(-1))), 0L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(2)),
+ Literal(Period.ofYears(1))), 2L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(2)),
+ Literal(Period.ofYears(-1))), -2L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal(Period.ofMonths(3))), 4L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal(Period.ofMonths(-3))), -4L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal(Period.ofMonths(5))), 2L)
+ checkEvaluation(IntegralDivide(Literal(Period.ofYears(1)),
+ Literal(Period.ofMonths(-5))), -2L)
+ }
+ test("SPARK-36921: Support DayTimeIntervalType by div") {
+ checkEvaluation(IntegralDivide(Literal(Duration.ZERO), Literal(Duration.ZERO)), null)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal(Duration.ZERO)), null)
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MaxValue, ChronoUnit.MICROS)),
+ Literal(Duration.ZERO)), null)
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MinValue, ChronoUnit.MICROS)),
+ Literal(Duration.ZERO)), null)
+
+ checkEvaluation(IntegralDivide(Literal.create(null, DayTimeIntervalType()),
+ Literal.create(null, DayTimeIntervalType())), null)
+ checkEvaluation(IntegralDivide(Literal.create(null, DayTimeIntervalType()),
+ Literal(Duration.ofDays(1))), null)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal.create(null, DayTimeIntervalType())), null)
+
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MaxValue, ChronoUnit.MICROS)),
+ Literal(Duration.of(Long.MaxValue, ChronoUnit.MICROS))), 1L)
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MinValue, ChronoUnit.MICROS)),
+ Literal(Duration.of(Long.MinValue, ChronoUnit.MICROS))), 1L)
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MaxValue, ChronoUnit.MICROS)),
+ Literal(Duration.of(Long.MinValue, ChronoUnit.MICROS))), 0L)
+ checkEvaluation(IntegralDivide(Literal(Duration.of(Long.MinValue, ChronoUnit.MICROS)),
+ Literal(Duration.of(Long.MaxValue, ChronoUnit.MICROS))), -1L)
+
+ checkEvaluation(IntegralDivide(Literal(Duration.ZERO),
+ Literal(Duration.ofDays(-1))), 0L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(2)),
+ Literal(Duration.ofDays(1))), 2L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(2)),
+ Literal(Duration.ofDays(-1))), -2L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal(Duration.ofHours(4))), 6L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal(Duration.ofHours(-4))), -6L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal(Duration.ofHours(5))), 4L)
+ checkEvaluation(IntegralDivide(Literal(Duration.ofDays(1)),
+ Literal(Duration.ofHours(-5))), -4L)
+ }
}
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 032bfca..4ff1ff9 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/interval.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/interval.sql
@@ -371,3 +371,8 @@ SELECT coalesce(INTERVAL '1' DAY, INTERVAL '01:01' HOUR TO MINUTE);
SELECT coalesce(INTERVAL 1 MONTH, INTERVAL 20 DAYS);
SELECT abs(INTERVAL '-10' YEAR);
SELECT abs(INTERVAL -'1 02:03:04.123' DAY TO SECOND);
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '1' YEAR);
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '-1' MONTH);
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '1' DAY);
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '-1' HOUR);
+SELECT div(INTERVAL '1' MONTH, INTERVAL '-1' DAY);
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 4ebf313..4775f4b 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: 271
+-- Number of queries: 276
-- !query
@@ -2568,3 +2568,44 @@ SELECT abs(INTERVAL -'1 02:03:04.123' DAY TO SECOND)
struct<abs(INTERVAL '-1 02:03:04.123' DAY TO SECOND):interval day to second>
-- !query output
1 02:03:04.123000000
+
+
+-- !query
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '1' YEAR)
+-- !query schema
+struct<(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '1' YEAR):bigint>
+-- !query output
+1
+
+
+-- !query
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '-1' MONTH)
+-- !query schema
+struct<(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH):bigint>
+-- !query output
+-13
+
+
+-- !query
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '1' DAY)
+-- !query schema
+struct<(INTERVAL '1 06' DAY TO HOUR div INTERVAL '1' DAY):bigint>
+-- !query output
+1
+
+
+-- !query
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '-1' HOUR)
+-- !query schema
+struct<(INTERVAL '1 06' DAY TO HOUR div INTERVAL '-01' HOUR):bigint>
+-- !query output
+-30
+
+
+-- !query
+SELECT div(INTERVAL '1' MONTH, INTERVAL '-1' DAY)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '1' MONTH div INTERVAL '-1' DAY)' due to data type mismatch: differing types in '(INTERVAL '1' MONTH div INTERVAL '-1' DAY)' (interval month and interval day).; line 1 pos 7
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 3835c5f..59e36b3 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: 271
+-- Number of queries: 276
-- !query
@@ -2557,3 +2557,44 @@ SELECT abs(INTERVAL -'1 02:03:04.123' DAY TO SECOND)
struct<abs(INTERVAL '-1 02:03:04.123' DAY TO SECOND):interval day to second>
-- !query output
1 02:03:04.123000000
+
+
+-- !query
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '1' YEAR)
+-- !query schema
+struct<(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '1' YEAR):bigint>
+-- !query output
+1
+
+
+-- !query
+SELECT div(INTERVAL '1-1' YEAR TO MONTH, INTERVAL '-1' MONTH)
+-- !query schema
+struct<(INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH):bigint>
+-- !query output
+-13
+
+
+-- !query
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '1' DAY)
+-- !query schema
+struct<(INTERVAL '1 06' DAY TO HOUR div INTERVAL '1' DAY):bigint>
+-- !query output
+1
+
+
+-- !query
+SELECT div(INTERVAL '1 06' DAY TO HOUR, INTERVAL '-1' HOUR)
+-- !query schema
+struct<(INTERVAL '1 06' DAY TO HOUR div INTERVAL '-01' HOUR):bigint>
+-- !query output
+-30
+
+
+-- !query
+SELECT div(INTERVAL '1' MONTH, INTERVAL '-1' DAY)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+cannot resolve '(INTERVAL '1' MONTH div INTERVAL '-1' DAY)' due to data type mismatch: differing types in '(INTERVAL '1' MONTH div INTERVAL '-1' DAY)' (interval month and interval day).; line 1 pos 7
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org