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/07/11 17:49:04 UTC
[spark] branch branch-3.2 updated: [SPARK-36083][SQL]
make_timestamp: return different result based on the default timestamp type
This is an automated email from the ASF dual-hosted git repository.
maxgekk pushed a commit to branch branch-3.2
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.2 by this push:
new 09e5bbd [SPARK-36083][SQL] make_timestamp: return different result based on the default timestamp type
09e5bbd is described below
commit 09e5bbdfbe53ccb4efd85f5774b7bee9e731a14f
Author: Gengliang Wang <ge...@apache.org>
AuthorDate: Sun Jul 11 20:47:49 2021 +0300
[SPARK-36083][SQL] make_timestamp: return different result based on the default timestamp type
### What changes were proposed in this pull request?
The SQL function MAKE_TIMESTAMP should return different results based on the default timestamp type:
* when "spark.sql.timestampType" is TIMESTAMP_NTZ, return TimestampNTZType literal
* when "spark.sql.timestampType" is TIMESTAMP_LTZ, return TimestampType literal
### Why are the changes needed?
As "spark.sql.timestampType" sets the default timestamp type, the make_timestamp function should behave consistently with it.
### Does this PR introduce _any_ user-facing change?
Yes, when the value of "spark.sql.timestampType" is TIMESTAMP_NTZ, the result type of `MAKE_TIMESTAMP` is of TIMESTAMP_NTZ type.
### How was this patch tested?
Unit test
Closes #33290 from gengliangwang/mkTS.
Authored-by: Gengliang Wang <ge...@apache.org>
Signed-off-by: Max Gekk <ma...@gmail.com>
(cherry picked from commit 17ddcc9e8273a098b63984b950bfa6cd36b58b99)
Signed-off-by: Max Gekk <ma...@gmail.com>
---
.../catalyst/expressions/datetimeExpressions.scala | 30 ++++--
.../expressions/DateExpressionsSuite.scala | 120 +++++++++++----------
.../test/resources/sql-tests/inputs/datetime.sql | 4 +
.../sql-tests/results/ansi/datetime.sql.out | 19 +++-
.../sql-tests/results/datetime-legacy.sql.out | 18 +++-
.../resources/sql-tests/results/datetime.sql.out | 18 +++-
.../results/timestampNTZ/datetime.sql.out | 18 +++-
7 files changed, 161 insertions(+), 66 deletions(-)
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 be527ce..979eeba 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
@@ -2286,7 +2286,8 @@ case class MakeDate(
// scalastyle:off line.size.limit
@ExpressionDescription(
- usage = "_FUNC_(year, month, day, hour, min, sec[, timezone]) - Create timestamp from year, month, day, hour, min, sec and timezone fields.",
+ usage = "_FUNC_(year, month, day, hour, min, sec[, timezone]) - Create timestamp from year, month, day, hour, min, sec and timezone fields. " +
+ "The result data type is consistent with the value of configuration `spark.sql.timestampType`",
arguments = """
Arguments:
* year - the year to represent, from 1 to 9999
@@ -2324,7 +2325,8 @@ case class MakeTimestamp(
sec: Expression,
timezone: Option[Expression] = None,
timeZoneId: Option[String] = None,
- failOnError: Boolean = SQLConf.get.ansiEnabled)
+ failOnError: Boolean = SQLConf.get.ansiEnabled,
+ override val dataType: DataType = SQLConf.get.timestampType)
extends SeptenaryExpression with TimeZoneAwareExpression with ImplicitCastInputTypes
with NullIntolerant {
@@ -2335,7 +2337,8 @@ case class MakeTimestamp(
hour: Expression,
min: Expression,
sec: Expression) = {
- this(year, month, day, hour, min, sec, None, None, SQLConf.get.ansiEnabled)
+ this(year, month, day, hour, min, sec, None, None, SQLConf.get.ansiEnabled,
+ SQLConf.get.timestampType)
}
def this(
@@ -2346,7 +2349,8 @@ case class MakeTimestamp(
min: Expression,
sec: Expression,
timezone: Expression) = {
- this(year, month, day, hour, min, sec, Some(timezone), None, SQLConf.get.ansiEnabled)
+ this(year, month, day, hour, min, sec, Some(timezone), None, SQLConf.get.ansiEnabled,
+ SQLConf.get.timestampType)
}
override def children: Seq[Expression] = Seq(year, month, day, hour, min, sec) ++ timezone
@@ -2355,7 +2359,6 @@ case class MakeTimestamp(
override def inputTypes: Seq[AbstractDataType] =
Seq(IntegerType, IntegerType, IntegerType, IntegerType, IntegerType, DecimalType(8, 6)) ++
timezone.map(_ => StringType)
- override def dataType: DataType = TimestampType
override def nullable: Boolean = if (failOnError) children.exists(_.nullable) else true
override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
@@ -2388,7 +2391,11 @@ case class MakeTimestamp(
} else {
LocalDateTime.of(year, month, day, hour, min, seconds, nanos)
}
- instantToMicros(ldt.atZone(zoneId).toInstant)
+ if (dataType == TimestampType) {
+ instantToMicros(ldt.atZone(zoneId).toInstant)
+ } else {
+ localDateTimeToMicros(ldt)
+ }
} catch {
case _: DateTimeException if !failOnError => null
}
@@ -2422,6 +2429,14 @@ case class MakeTimestamp(
val failOnErrorBranch = if (failOnError) "throw e;" else s"${ev.isNull} = true;"
nullSafeCodeGen(ctx, ev, (year, month, day, hour, min, secAndNanos, timezone) => {
val zoneId = timezone.map(tz => s"$dtu.getZoneId(${tz}.toString())").getOrElse(zid)
+ val toMicrosCode = if (dataType == TimestampType) {
+ s"""
+ |java.time.Instant instant = ldt.atZone($zoneId).toInstant();
+ |${ev.value} = $dtu.instantToMicros(instant);
+ |""".stripMargin
+ } else {
+ s"${ev.value} = $dtu.localDateTimeToMicros(ldt);"
+ }
s"""
try {
org.apache.spark.sql.types.Decimal secFloor = $secAndNanos.floor();
@@ -2439,8 +2454,7 @@ case class MakeTimestamp(
} else {
ldt = java.time.LocalDateTime.of($year, $month, $day, $hour, $min, seconds, nanos);
}
- java.time.Instant instant = ldt.atZone($zoneId).toInstant();
- ${ev.value} = $dtu.instantToMicros(instant);
+ $toMicrosCode
} catch (java.time.DateTimeException e) {
$failOnErrorBranch
}"""
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 afcc729..5f071c3 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
@@ -37,6 +37,7 @@ import org.apache.spark.sql.catalyst.util.DateTimeConstants._
import org.apache.spark.sql.catalyst.util.DateTimeTestUtils._
import org.apache.spark.sql.catalyst.util.DateTimeUtils.{getZoneId, TimeZoneUTC}
import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.internal.SQLConf.TimestampTypes
import org.apache.spark.sql.types._
import org.apache.spark.sql.types.DataTypeTestUtils.{dayTimeIntervalTypes, yearMonthIntervalTypes}
import org.apache.spark.unsafe.types.{CalendarInterval, UTF8String}
@@ -1131,66 +1132,77 @@ class DateExpressionsSuite extends SparkFunSuite with ExpressionEvalHelper {
}
}
- test("creating values of TimestampType via make_timestamp") {
- val expected = Timestamp.valueOf("2013-7-15 8:15:23.5")
+ test("creating values of Timestamp/TimestampNTZ via make_timestamp") {
+ Seq(TimestampTypes.TIMESTAMP_NTZ, TimestampTypes.TIMESTAMP_LTZ).foreach { tsType =>
+ def expectedAnswer(s: String): Any = tsType match {
+ case TimestampTypes.TIMESTAMP_NTZ => LocalDateTime.parse(s.replace(" ", "T"))
+ case TimestampTypes.TIMESTAMP_LTZ => Timestamp.valueOf(s)
+ }
- Seq(true, false).foreach { ansi =>
- withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi.toString) {
- var makeTimestampExpr = MakeTimestamp(
- Literal(2013), Literal(7), Literal(15), Literal(8), Literal(15),
- Literal(Decimal(BigDecimal(23.5), 8, 6)), Some(Literal(ZoneId.systemDefault().getId)))
- checkEvaluation(makeTimestampExpr, expected)
- checkEvaluation(makeTimestampExpr.copy(year = Literal.create(null, IntegerType)), null)
- checkEvaluation(makeTimestampExpr.copy(month = Literal.create(null, IntegerType)), null)
- checkEvaluation(makeTimestampExpr.copy(day = Literal.create(null, IntegerType)), null)
- checkEvaluation(makeTimestampExpr.copy(hour = Literal.create(null, IntegerType)), null)
- checkEvaluation(makeTimestampExpr.copy(min = Literal.create(null, IntegerType)), null)
- checkEvaluation(makeTimestampExpr.copy(sec = Literal.create(null, DecimalType(8, 6))), null)
- checkEvaluation(makeTimestampExpr.copy(timezone = None), expected)
-
- Seq(
- (makeTimestampExpr.copy(year = Literal(Int.MaxValue)), "Invalid value for Year"),
- (makeTimestampExpr.copy(month = Literal(13)), "Invalid value for Month"),
- (makeTimestampExpr.copy(day = Literal(32)), "Invalid value for Day"),
- (makeTimestampExpr.copy(hour = Literal(25)), "Invalid value for Hour"),
- (makeTimestampExpr.copy(min = Literal(65)), "Invalid value for Min"),
- (makeTimestampExpr.copy(sec = Literal(Decimal(
- BigDecimal(70.0), 8, 6))), "Invalid value for Second")
- ).foreach { entry =>
- if (ansi) {
- checkExceptionInExpression[DateTimeException](entry._1, EmptyRow, entry._2)
- } else {
- checkEvaluation(entry._1, null)
+ withSQLConf(SQLConf.TIMESTAMP_TYPE.key -> tsType.toString) {
+ val expected = expectedAnswer("2013-07-15 08:15:23.5")
+
+ Seq(true, false).foreach { ansi =>
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi.toString) {
+ var makeTimestampExpr = MakeTimestamp(
+ Literal(2013), Literal(7), Literal(15), Literal(8), Literal(15),
+ Literal(Decimal(BigDecimal(23.5), 8, 6)), Some(Literal(ZoneId.systemDefault().getId)))
+ checkEvaluation(makeTimestampExpr, expected)
+ checkEvaluation(makeTimestampExpr.copy(year = Literal.create(null, IntegerType)), null)
+ checkEvaluation(makeTimestampExpr.copy(month = Literal.create(null, IntegerType)), null)
+ checkEvaluation(makeTimestampExpr.copy(day = Literal.create(null, IntegerType)), null)
+ checkEvaluation(makeTimestampExpr.copy(hour = Literal.create(null, IntegerType)), null)
+ checkEvaluation(makeTimestampExpr.copy(min = Literal.create(null, IntegerType)), null)
+ checkEvaluation(makeTimestampExpr.copy(sec = Literal.create(null, DecimalType(8, 6))),
+ null)
+ checkEvaluation(makeTimestampExpr.copy(timezone = None), expected)
+
+ Seq(
+ (makeTimestampExpr.copy(year = Literal(Int.MaxValue)), "Invalid value for Year"),
+ (makeTimestampExpr.copy(month = Literal(13)), "Invalid value for Month"),
+ (makeTimestampExpr.copy(day = Literal(32)), "Invalid value for Day"),
+ (makeTimestampExpr.copy(hour = Literal(25)), "Invalid value for Hour"),
+ (makeTimestampExpr.copy(min = Literal(65)), "Invalid value for Min"),
+ (makeTimestampExpr.copy(sec = Literal(Decimal(
+ BigDecimal(70.0), 8, 6))), "Invalid value for Second")
+ ).foreach { entry =>
+ if (ansi) {
+ checkExceptionInExpression[DateTimeException](entry._1, EmptyRow, entry._2)
+ } else {
+ checkEvaluation(entry._1, null)
+ }
+ }
+
+ makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(6), Literal(30),
+ Literal(23), Literal(59), Literal(Decimal(BigDecimal(60.0), 8, 6)))
+ if (ansi) {
+ checkExceptionInExpression[DateTimeException](makeTimestampExpr.copy(sec = Literal(
+ Decimal(BigDecimal(60.5), 8, 6))), EmptyRow, "The fraction of sec must be zero")
+ } else {
+ checkEvaluation(makeTimestampExpr, expectedAnswer("2019-07-01 00:00:00"))
+ }
+
+ makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(8), Literal(12), Literal(0),
+ Literal(0), Literal(Decimal(BigDecimal(58.000001), 8, 6)))
+ checkEvaluation(makeTimestampExpr, expectedAnswer("2019-08-12 00:00:58.000001"))
}
}
- makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(6), Literal(30),
- Literal(23), Literal(59), Literal(Decimal(BigDecimal(60.0), 8, 6)))
- if (ansi) {
- checkExceptionInExpression[DateTimeException](makeTimestampExpr.copy(sec = Literal(
- Decimal(BigDecimal(60.5), 8, 6))), EmptyRow, "The fraction of sec must be zero")
- } else {
- checkEvaluation(makeTimestampExpr, Timestamp.valueOf("2019-07-01 00:00:00"))
+ // non-ansi test
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> "false") {
+ val makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(6), Literal(30),
+ Literal(23), Literal(59), Literal(Decimal(BigDecimal(60.0), 8, 6)))
+ checkEvaluation(makeTimestampExpr.copy(sec = Literal(Decimal(BigDecimal(60.5), 8, 6))),
+ null)
}
- makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(8), Literal(12), Literal(0),
- Literal(0), Literal(Decimal(BigDecimal(58.000001), 8, 6)))
- checkEvaluation(makeTimestampExpr, Timestamp.valueOf("2019-08-12 00:00:58.000001"))
- }
- }
-
- // non-ansi test
- withSQLConf(SQLConf.ANSI_ENABLED.key -> "false") {
- val makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(6), Literal(30),
- Literal(23), Literal(59), Literal(Decimal(BigDecimal(60.0), 8, 6)))
- checkEvaluation(makeTimestampExpr.copy(sec = Literal(Decimal(BigDecimal(60.5), 8, 6))), null)
- }
-
- Seq(true, false).foreach { ansi =>
- withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi.toString) {
- val makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(8), Literal(12),
- Literal(0), Literal(0), Literal(Decimal(BigDecimal(58.000001), 8, 6)))
- checkEvaluation(makeTimestampExpr, Timestamp.valueOf("2019-08-12 00:00:58.000001"))
+ Seq(true, false).foreach { ansi =>
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansi.toString) {
+ val makeTimestampExpr = MakeTimestamp(Literal(2019), Literal(8), Literal(12),
+ Literal(0), Literal(0), Literal(Decimal(BigDecimal(58.000001), 8, 6)))
+ checkEvaluation(makeTimestampExpr, expectedAnswer("2019-08-12 00:00:58.000001"))
+ }
+ }
}
}
}
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 178174f..8127804 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/datetime.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/datetime.sql
@@ -257,3 +257,7 @@ select to_timestamp_ntz('2021-06-25 10:11:12') - interval '10-9' year to month;
select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15' day to hour;
select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40' day to minute;
select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40:32.99899999' day to second;
+
+-- timestamp numeric fields constructor
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678);
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007);
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 d42d3f4..678932e 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
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 193
+-- Number of queries: 195
-- !query
@@ -1642,3 +1642,20 @@ select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40:32.99899999'
struct<to_timestamp_ntz(2021-06-25 10:11:12) - INTERVAL '20 15:40:32.998999' DAY TO SECOND:timestamp_ntz>
-- !query output
2021-06-04 18:30:39.001001
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678):timestamp>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+The fraction of sec must be zero. Valid range is [0, 60].
diff --git a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
index 2bf5c9f..4b8687c 100644
--- a/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/datetime-legacy.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 193
+-- Number of queries: 195
-- !query
@@ -1584,3 +1584,19 @@ select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40:32.99899999'
struct<to_timestamp_ntz(2021-06-25 10:11:12) - INTERVAL '20 15:40:32.998999' DAY TO SECOND:timestamp_ntz>
-- !query output
2021-06-04 18:30:39.001001
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678):timestamp>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 60.007):timestamp>
+-- !query output
+NULL
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 22dc56d..a2383b5 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
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 193
+-- Number of queries: 195
-- !query
@@ -1592,3 +1592,19 @@ select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40:32.99899999'
struct<to_timestamp_ntz(2021-06-25 10:11:12) - INTERVAL '20 15:40:32.998999' DAY TO SECOND:timestamp_ntz>
-- !query output
2021-06-04 18:30:39.001001
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678):timestamp>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 60.007):timestamp>
+-- !query output
+NULL
diff --git a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/datetime.sql.out b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/datetime.sql.out
index 19e1a7d..3905acd 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/datetime.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/datetime.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 193
+-- Number of queries: 195
-- !query
@@ -1593,3 +1593,19 @@ select to_timestamp_ntz('2021-06-25 10:11:12') - interval '20 15:40:32.99899999'
struct<to_timestamp_ntz(2021-06-25 10:11:12) - INTERVAL '20 15:40:32.998999' DAY TO SECOND:timestamp_ntz>
-- !query output
2021-06-04 18:30:39.001001
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 45.678):timestamp_ntz>
+-- !query output
+2021-07-11 06:30:45.678
+
+
+-- !query
+SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007)
+-- !query schema
+struct<make_timestamp(2021, 7, 11, 6, 30, 60.007):timestamp_ntz>
+-- !query output
+NULL
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org