You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2019/07/05 17:02:22 UTC
[spark] branch master updated: [SPARK-27898][SQL] Support 4 date
operators(date + integer, integer + date, date - integer and date - date)
This is an automated email from the ASF dual-hosted git repository.
dongjoon 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 d493a1f [SPARK-27898][SQL] Support 4 date operators(date + integer, integer + date, date - integer and date - date)
d493a1f is described below
commit d493a1f6bf23938b566ee72f1c8c8f384ebddfe6
Author: Yuming Wang <yu...@ebay.com>
AuthorDate: Fri Jul 5 10:01:43 2019 -0700
[SPARK-27898][SQL] Support 4 date operators(date + integer, integer + date, date - integer and date - date)
## What changes were proposed in this pull request?
This pr add support 4 PostgreSQL's date operators(date + integer, integer + date, date - integer and date - date):
Operator | Example | Result
-- | -- | --
\+ | date '2001-09-28' + 7 | date '2001-10-05'
\+ | 7 + date '2001-09-28' | date '2001-10-05'
\- | date '2001-10-01' - 7 | date '2001-09-24'
\- | date '2001-10-01' - date '2001-09-28' | integer '3' (days)
Most databases support `date - date` operation, where PostgreSQL, Vertica, Teradata, Oracle and DB2 returns `Integer` type, Hive and Presto returns `Interval` type, MySQL returns unexpected value, and SQL Server does not support `date - date` operation.
**PostgreSQL**:
```sql
postgres=# select substr(version(), 0, 16), date '2001-09-28' + 7, 7 + date '2001-09-28', date '2001-10-01' - 7, date '2001-10-01' - date '2001-09-28';
substr | ?column? | ?column? | ?column? | ?column?
-----------------+------------+------------+------------+----------
PostgreSQL 11.3 | 2001-10-05 | 2001-10-05 | 2001-09-24 | 3
(1 row)
```
**Vertica**:
```sql
dbadmin=> select version(), date '2001-09-28' + 7, 7 + date '2001-09-28', date '2001-10-01' - 7, date '2001-10-01' - date '2001-09-28';
version | ?column? | ?column? | ?column? | ?column?
------------------------------------+------------+------------+------------+----------
Vertica Analytic Database v9.1.1-0 | 2001-10-05 | 2001-10-05 | 2001-09-24 | 3
(1 row)
```
**Teradata**:
**Oracle**:
**DB2**:
**Hive**:
```sql
hive> select version(), date '2001-10-01' - date '2001-09-28';
OK
3.1.1 rf4e0529634b6231a0072295da48af466cf2f10b7 3 00:00:00.000000000
Time taken: 2.038 seconds, Fetched: 1 row(s)
```
**Presto**:
```sql
presto> select date '2001-10-01' - date '2001-09-28';
_col0
----------------
3 00:00:00.000
(1 row)
```
**MySQL**:
```SQL
mysql> SELECT version(), date '2001-10-01' - date '2001-09-28';
+-----------+---------------------------------------+
| version() | date '2001-10-01' - date '2001-09-28' |
+-----------+---------------------------------------+
| 5.7.26 | 73 |
+-----------+---------------------------------------+
1 row in set (0.00 sec)
```
More details:
https://www.postgresql.org/docs/12/functions-datetime.html
## How was this patch tested?
unit tests
Closes #24755 from wangyum/Add4DateOperators.
Authored-by: Yuming Wang <yu...@ebay.com>
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
.../spark/sql/catalyst/analysis/TypeCoercion.scala | 11 +++++--
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 6 ++++
.../test/resources/sql-tests/inputs/datetime.sql | 7 ++++-
.../resources/sql-tests/results/datetime.sql.out | 34 +++++++++++++++++++++-
4 files changed, 54 insertions(+), 4 deletions(-)
diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
index 1ff1e7f..1fdec89 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
@@ -820,8 +820,10 @@ object TypeCoercion {
}
/**
- * Turns Add/Subtract of DateType/TimestampType/StringType and CalendarIntervalType
- * to TimeAdd/TimeSub
+ * 1. Turns Add/Subtract of DateType/TimestampType/StringType and CalendarIntervalType
+ * to TimeAdd/TimeSub.
+ * 2. Turns Add/Subtract of DateType/IntegerType and IntegerType/DateType
+ * to DateAdd/DateSub/DateDiff.
*/
object DateTimeOperations extends Rule[LogicalPlan] {
@@ -837,6 +839,11 @@ object TypeCoercion {
Cast(TimeAdd(l, r), l.dataType)
case Subtract(l, r @ CalendarIntervalType()) if acceptedTypes.contains(l.dataType) =>
Cast(TimeSub(l, r), l.dataType)
+
+ case Add(l @ DateType(), r @ IntegerType()) => DateAdd(l, r)
+ case Add(l @ IntegerType(), r @ DateType()) => DateAdd(r, l)
+ case Subtract(l @ DateType(), r @ IntegerType()) => DateSub(l, r)
+ case Subtract(l @ DateType(), r @ DateType()) => DateDiff(l, r)
}
}
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
index 0b168d0..2c3ba1b 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
@@ -1406,6 +1406,7 @@ class TypeCoercionSuite extends AnalysisTest {
val timestamp = Literal(new Timestamp(0L))
val interval = Literal(new CalendarInterval(0, 0))
val str = Literal("2015-01-01")
+ val intValue = Literal(0, IntegerType)
ruleTest(dateTimeOperations, Add(date, interval), Cast(TimeAdd(date, interval), DateType))
ruleTest(dateTimeOperations, Add(interval, date), Cast(TimeAdd(date, interval), DateType))
@@ -1424,6 +1425,11 @@ class TypeCoercionSuite extends AnalysisTest {
// interval operations should not be effected
ruleTest(dateTimeOperations, Add(interval, interval), Add(interval, interval))
ruleTest(dateTimeOperations, Subtract(interval, interval), Subtract(interval, interval))
+
+ ruleTest(dateTimeOperations, Add(date, intValue), DateAdd(date, intValue))
+ ruleTest(dateTimeOperations, Add(intValue, date), DateAdd(date, intValue))
+ ruleTest(dateTimeOperations, Subtract(date, intValue), DateSub(date, intValue))
+ ruleTest(dateTimeOperations, Subtract(date, date), DateDiff(date, date))
}
/**
diff --git a/sql/core/src/test/resources/sql-tests/inputs/datetime.sql b/sql/core/src/test/resources/sql-tests/inputs/datetime.sql
index 8bd8bc2..2f7ffb7 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/datetime.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/datetime.sql
@@ -28,4 +28,9 @@ select a, b from ttf2 order by a, current_date;
select weekday('2007-02-03'), weekday('2009-07-30'), weekday('2017-05-27'), weekday(null), weekday('1582-10-15 13:10:15');
-select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01');
\ No newline at end of file
+select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01');
+
+select date '2001-09-28' + 7;
+select 7 + date '2001-09-28';
+select date '2001-10-01' - 7;
+select date '2001-10-01' - date '2001-09-28';
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 2090633..178400e 100644
--- a/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/datetime.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 11
+-- Number of queries: 15
-- !query 0
@@ -97,3 +97,35 @@ select year('1500-01-01'), month('1500-01-01'), dayOfYear('1500-01-01')
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>
-- !query 10 output
1500 1 1
+
+
+-- !query 11
+select date '2001-09-28' + 7
+-- !query 11 schema
+struct<date_add(DATE '2001-09-28', 7):date>
+-- !query 11 output
+2001-10-05
+
+
+-- !query 12
+select 7 + date '2001-09-28'
+-- !query 12 schema
+struct<date_add(DATE '2001-09-28', 7):date>
+-- !query 12 output
+2001-10-05
+
+
+-- !query 13
+select date '2001-10-01' - 7
+-- !query 13 schema
+struct<date_sub(DATE '2001-10-01', 7):date>
+-- !query 13 output
+2001-09-24
+
+
+-- !query 14
+select date '2001-10-01' - date '2001-09-28'
+-- !query 14 schema
+struct<datediff(DATE '2001-10-01', DATE '2001-09-28'):int>
+-- !query 14 output
+3
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org