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 <gengli...@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 <gengli...@apache.org> Signed-off-by: Max Gekk <max.g...@gmail.com> (cherry picked from commit 17ddcc9e8273a098b63984b950bfa6cd36b58b99) Signed-off-by: Max Gekk <max.g...@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-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org