This is an automated email from the ASF dual-hosted git repository.
gengliang 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 11880d1e830 [SPARK-38984][SQL] Allow comparison between TimestampNTZ
and Timestamp
11880d1e830 is described below
commit 11880d1e83061733325a448d91f41c565eb5f038
Author: Gengliang Wang <gengli...@apache.org>
AuthorDate: Fri Apr 22 01:46:38 2022 +0800
[SPARK-38984][SQL] Allow comparison between TimestampNTZ and Timestamp
### What changes were proposed in this pull request?
Currently, comparisons between TimestampNTZ and Timestamp is not allowed.
Furthermore, comparing TimestampNTZ and Date type is not allow(handled) in ANSI
type coercion.
This PR is to allow it. When comparing TimestampNTZ and Timestamp, the
TimestampNTZ value will be cast as TimestampLTZ.
### Why are the changes needed?
* As per the ANSI standard, a Timestamp type are allowed implicitly derived
from another type.
* Oracle and Snowflake allow the comparison between TimestampNTZ and
TimestampLTZ as well.
* In function calls, we already allow such implicit casting.
### Does this PR introduce _any_ user-facing change?
No, TimestampNTZ is not released yet.
### How was this patch tested?
UT
Closes #36300 from gengliangwang/compareNTZLTZ.
Authored-by: Gengliang Wang <gengli...@apache.org>
Signed-off-by: Gengliang Wang <gengli...@apache.org>
---
.../sql/catalyst/analysis/AnsiTypeCoercion.scala | 3 +-
.../spark/sql/catalyst/analysis/TypeCoercion.scala | 18 +++++---
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 32 ++++++++++++++
.../resources/sql-tests/inputs/timestamp-ntz.sql | 7 +++
.../sql-tests/results/timestamp-ntz.sql.out | 50 +++++++++++++++++++++-
5 files changed, 102 insertions(+), 8 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
index 036efba34fa..c784e0f7487 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
@@ -116,8 +116,7 @@ object AnsiTypeCoercion extends TypeCoercionBase {
Some(widerType)
}
- case (_: TimestampType, _: DateType) | (_: DateType, _: TimestampType) =>
- Some(TimestampType)
+ case (d1: DatetimeType, d2: DatetimeType) =>
Some(findWiderDateTimeType(d1, d2))
case (t1: DayTimeIntervalType, t2: DayTimeIntervalType) =>
Some(DayTimeIntervalType(t1.startField.min(t2.startField),
t1.endField.max(t2.endField)))
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 e348d1134a0..cc84fbebcb8 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
@@ -166,6 +166,18 @@ abstract class TypeCoercionBase {
}
}
+ protected def findWiderDateTimeType(d1: DatetimeType, d2: DatetimeType):
DatetimeType =
+ (d1, d2) match {
+ case (_: TimestampType, _: DateType) | (_: DateType, _: TimestampType) =>
+ TimestampType
+
+ case (_: TimestampType, _: TimestampNTZType) | (_: TimestampNTZType, _:
TimestampType) =>
+ TimestampType
+
+ case (_: TimestampNTZType, _: DateType) | (_: DateType, _:
TimestampNTZType) =>
+ TimestampNTZType
+ }
+
/**
* Type coercion rule that combines multiple type coercion rules and applies
them in a single tree
* traversal.
@@ -843,17 +855,13 @@ object TypeCoercion extends TypeCoercionBase {
val index = numericPrecedence.lastIndexWhere(t => t == t1 || t == t2)
Some(numericPrecedence(index))
- case (_: TimestampType, _: DateType) | (_: DateType, _: TimestampType) =>
- Some(TimestampType)
+ case (d1: DatetimeType, d2: DatetimeType) =>
Some(findWiderDateTimeType(d1, d2))
case (t1: DayTimeIntervalType, t2: DayTimeIntervalType) =>
Some(DayTimeIntervalType(t1.startField.min(t2.startField),
t1.endField.max(t2.endField)))
case (t1: YearMonthIntervalType, t2: YearMonthIntervalType) =>
Some(YearMonthIntervalType(t1.startField.min(t2.startField),
t1.endField.max(t2.endField)))
- case (_: TimestampNTZType, _: DateType) | (_: DateType, _:
TimestampNTZType) =>
- Some(TimestampNTZType)
-
case (t1, t2) => findTypeForComplex(t1, t2, findTightestCommonType)
}
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 782f3e41f42..d7489aad7fc 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
@@ -426,6 +426,38 @@ abstract class TypeCoercionSuiteBase extends AnalysisTest {
SubtractTimestamps(timestampNTZLiteral, Cast(timestampLiteral,
TimestampNTZType)))
}
+ test("datetime comparison") {
+ val rule = ImplicitTypeCasts
+ val dateLiteral = Literal(java.sql.Date.valueOf("2021-01-01"))
+ val timestampNTZLiteral =
Literal(LocalDateTime.parse("2021-01-01T00:00:00"))
+ val timestampLiteral = Literal(Timestamp.valueOf("2021-01-01 00:00:00"))
+ Seq(
+ EqualTo,
+ EqualNullSafe,
+ GreaterThan,
+ GreaterThanOrEqual,
+ LessThan,
+ LessThanOrEqual).foreach { op =>
+ ruleTest(rule,
+ op(dateLiteral, timestampNTZLiteral),
+ op(Cast(dateLiteral, TimestampNTZType), timestampNTZLiteral))
+ ruleTest(rule,
+ op(timestampNTZLiteral, dateLiteral),
+ op(timestampNTZLiteral, Cast(dateLiteral, TimestampNTZType)))
+ ruleTest(rule,
+ op(dateLiteral, timestampLiteral),
+ op(Cast(dateLiteral, TimestampType), timestampLiteral))
+ ruleTest(rule,
+ op(timestampLiteral, dateLiteral),
+ op(timestampLiteral, Cast(dateLiteral, TimestampType)))
+ ruleTest(rule,
+ op(timestampNTZLiteral, timestampLiteral),
+ op(Cast(timestampNTZLiteral, TimestampType), timestampLiteral))
+ ruleTest(rule,
+ op(timestampLiteral, timestampNTZLiteral),
+ op(timestampLiteral, Cast(timestampNTZLiteral, TimestampType)))
+ }
+ }
}
class TypeCoercionSuite extends TypeCoercionSuiteBase {
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
index bec31d324e4..b75604485cd 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz.sql
@@ -24,3 +24,10 @@ select timestampdiff(QUARTER, timestamp_ntz'2022-01-01
01:02:03', timestamp_ntz'
select timestampdiff(HOUR, timestamp_ntz'2022-02-14 01:02:03',
timestamp_ltz'2022-02-14 02:03:04');
select timestampdiff(YEAR, date'2022-02-15', timestamp_ntz'2023-02-15
10:11:12');
select timestampdiff(MILLISECOND, timestamp_ntz'2022-02-14 23:59:59.123',
date'2022-02-15');
+
+select timestamp_ntz'2022-01-01 00:00:00' = date'2022-01-01';
+select timestamp_ntz'2022-01-01 00:00:00' > date'2022-01-01';
+select timestamp_ntz'2022-01-01 00:00:00' < date'2022-01-01';
+select timestamp_ntz'2022-01-01 00:00:00' = timestamp_ltz'2022-01-01 00:00:00';
+select timestamp_ntz'2022-01-01 00:00:00' > timestamp_ltz'2022-01-01 00:00:00';
+select timestamp_ntz'2022-01-01 00:00:00' < timestamp_ltz'2022-01-01 00:00:00';
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
index 146c403b878..1388f640d5b 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 13
+-- Number of queries: 19
-- !query
@@ -105,3 +105,51 @@ select timestampdiff(MILLISECOND, timestamp_ntz'2022-02-14
23:59:59.123', date'2
struct<timestampdiff(MILLISECOND, TIMESTAMP_NTZ '2022-02-14 23:59:59.123',
DATE '2022-02-15'):bigint>
-- !query output
877
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' = date'2022-01-01'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' = DATE '2022-01-01'):boolean>
+-- !query output
+true
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' > date'2022-01-01'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' > DATE '2022-01-01'):boolean>
+-- !query output
+false
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' < date'2022-01-01'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' < DATE '2022-01-01'):boolean>
+-- !query output
+false
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' = timestamp_ltz'2022-01-01 00:00:00'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' = TIMESTAMP '2022-01-01
00:00:00'):boolean>
+-- !query output
+true
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' > timestamp_ltz'2022-01-01 00:00:00'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' > TIMESTAMP '2022-01-01
00:00:00'):boolean>
+-- !query output
+false
+
+
+-- !query
+select timestamp_ntz'2022-01-01 00:00:00' < timestamp_ltz'2022-01-01 00:00:00'
+-- !query schema
+struct<(TIMESTAMP_NTZ '2022-01-01 00:00:00' < TIMESTAMP '2022-01-01
00:00:00'):boolean>
+-- !query output
+false
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org
For additional commands, e-mail: commits-h...@spark.apache.org
