This is an automated email from the ASF dual-hosted git repository.
wenchen 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 eb84057 [SPARK-36428][SQL] the seconds parameter of make_timestamp
should accept integer type
eb84057 is described below
commit eb840578f7a0153721bc85ff2c59115772b79dd8
Author: gengjiaan <[email protected]>
AuthorDate: Fri Aug 13 13:13:02 2021 +0800
[SPARK-36428][SQL] the seconds parameter of make_timestamp should accept
integer type
### What changes were proposed in this pull request?
With ANSI mode, `SELECT make_timestamp(1, 1, 1, 1, 1, 1)` fails, because
the 'seconds' parameter needs to be of type DECIMAL(8,6), and INT can't be
implicitly casted to DECIMAL(8,6) under ANSI mode.
```
org.apache.spark.sql.AnalysisException
cannot resolve 'make_timestamp(1, 1, 1, 1, 1, 1)' due to data type
mismatch: argument 6 requires decimal(8,6) type, however, '1' is of int type.;
line 1 pos 7
```
We should update the function `make_timestamp` to allow integer type
'seconds' parameter.
### Why are the changes needed?
Make `make_timestamp` could accepts integer as 'seconds' parameter.
### Does this PR introduce _any_ user-facing change?
'Yes'.
`make_timestamp` could accepts integer as 'seconds' parameter.
### How was this patch tested?
New tests.
Closes #33665 from beliefer/SPARK-36428.
Lead-authored-by: gengjiaan <[email protected]>
Co-authored-by: Jiaan Geng <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit 7d823367348c0235ffb212b09afcf053d070068d)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../catalyst/expressions/datetimeExpressions.scala | 35 +++++++++++++++++-----
.../expressions/DateExpressionsSuite.scala | 8 +++++
.../test/resources/sql-tests/inputs/timestamp.sql | 4 +++
.../sql-tests/results/ansi/timestamp.sql.out | 35 +++++++++++++++++++++-
.../sql-tests/results/datetime-legacy.sql.out | 34 ++++++++++++++++++++-
.../resources/sql-tests/results/timestamp.sql.out | 34 ++++++++++++++++++++-
.../results/timestampNTZ/timestamp-ansi.sql.out | 35 +++++++++++++++++++++-
.../results/timestampNTZ/timestamp.sql.out | 34 ++++++++++++++++++++-
8 files changed, 207 insertions(+), 12 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 5d42b31..5d69bbc 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
@@ -2494,8 +2494,9 @@ case class MakeTimestampLTZ(
* day - the day-of-month to represent, from 1 to 31
* hour - the hour-of-day to represent, from 0 to 23
* min - the minute-of-hour to represent, from 0 to 59
- * sec - the second-of-minute and its micro-fraction to represent, from
- 0 to 60. If the sec argument equals to 60, the seconds field is
set
+ * sec - the second-of-minute and its micro-fraction to represent, from 0
to 60.
+ The value can be either an integer like 13 , or a fraction like
13.123.
+ If the sec argument equals to 60, the seconds field is set
to 0 and 1 minute is added to the final timestamp.
* timezone - the time zone identifier. For example, CET, UTC and etc.
""",
@@ -2507,6 +2508,8 @@ case class MakeTimestampLTZ(
2014-12-27 21:30:45.887
> SELECT _FUNC_(2019, 6, 30, 23, 59, 60);
2019-07-01 00:00:00
+ > SELECT _FUNC_(2019, 6, 30, 23, 59, 1);
+ 2019-06-30 23:59:01
> SELECT _FUNC_(2019, 13, 1, 10, 11, 12, 'PST');
NULL
> SELECT _FUNC_(null, 7, 22, 15, 30, 0);
@@ -2556,13 +2559,20 @@ case class MakeTimestamp(
// Accept `sec` as DecimalType to avoid loosing precision of microseconds
while converting
// them to the fractional part of `sec`.
override def inputTypes: Seq[AbstractDataType] =
- Seq(IntegerType, IntegerType, IntegerType, IntegerType, IntegerType,
DecimalType(8, 6)) ++
- timezone.map(_ => StringType)
+ Seq(IntegerType, IntegerType, IntegerType, IntegerType, IntegerType,
+ TypeCollection(DecimalType(8, 6), IntegerType, NullType)) ++
timezone.map(_ => StringType)
override def nullable: Boolean = if (failOnError)
children.exists(_.nullable) else true
override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
copy(timeZoneId = Option(timeZoneId))
+ private lazy val toDecimal = sec.dataType match {
+ case DecimalType() =>
+ (secEval: Any) => secEval.asInstanceOf[Decimal]
+ case IntegerType =>
+ (secEval: Any) => Decimal(BigDecimal(secEval.asInstanceOf[Int]), 8, 6)
+ }
+
private def toMicros(
year: Int,
month: Int,
@@ -2617,7 +2627,7 @@ case class MakeTimestamp(
day.asInstanceOf[Int],
hour.asInstanceOf[Int],
min.asInstanceOf[Int],
- sec.asInstanceOf[Decimal],
+ toDecimal(sec),
zid)
}
@@ -2625,6 +2635,7 @@ case class MakeTimestamp(
val dtu = DateTimeUtils.getClass.getName.stripSuffix("$")
val zid = ctx.addReferenceObj("zoneId", zoneId, classOf[ZoneId].getName)
val d = Decimal.getClass.getName.stripSuffix("$")
+ val decimalValue = ctx.freshName("decimalValue")
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)
@@ -2636,11 +2647,21 @@ case class MakeTimestamp(
} else {
s"${ev.value} = $dtu.localDateTimeToMicros(ldt);"
}
+ val toDecimalCode = sec.dataType match {
+ case DecimalType() =>
+ s"org.apache.spark.sql.types.Decimal $decimalValue = $secAndNanos;"
+ case IntegerType =>
+ s"""
+ |org.apache.spark.sql.types.Decimal $decimalValue =
+ |$d$$.MODULE$$.apply(new java.math.BigDecimal($secAndNanos), 8,
6);
+ """.stripMargin
+ }
s"""
try {
- org.apache.spark.sql.types.Decimal secFloor = $secAndNanos.floor();
+ $toDecimalCode
+ org.apache.spark.sql.types.Decimal secFloor = $decimalValue.floor();
org.apache.spark.sql.types.Decimal nanosPerSec =
$d$$.MODULE$$.apply(1000000000L, 10, 0);
- int nanos =
(($secAndNanos.$$minus(secFloor)).$$times(nanosPerSec)).toInt();
+ int nanos =
(($decimalValue.$$minus(secFloor)).$$times(nanosPerSec)).toInt();
int seconds = secFloor.toInt();
java.time.LocalDateTime ldt;
if (seconds == 60) {
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 eecaa93..0026f9a 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
@@ -1222,6 +1222,14 @@ class DateExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
checkEvaluation(makeTimestampExpr, expectedAnswer("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(1))
+ checkEvaluation(makeTimestampExpr, expectedAnswer("2019-08-12
00:00:01"))
+ }
+ }
}
}
}
diff --git a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
index f8a3b3c..a55adb0 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp.sql
@@ -18,6 +18,10 @@ select localtimestamp() = localtimestamp();
SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678);
SELECT make_timestamp(2021, 07, 11, 6, 30, 45.678, 'CET');
SELECT make_timestamp(2021, 07, 11, 6, 30, 60.007);
+SELECT make_timestamp(1, 1, 1, 1, 1, 1);
+SELECT make_timestamp(1, 1, 1, 1, 1, 60);
+SELECT make_timestamp(1, 1, 1, 1, 1, 61);
+SELECT make_timestamp(1, 1, 1, 1, 1, null);
-- [SPARK-31710] TIMESTAMP_SECONDS, TIMESTAMP_MILLISECONDS and
TIMESTAMP_MICROSECONDS that always create timestamp_ltz
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null);
diff --git
a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
index 948a790..02138a6 100644
--- a/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/ansi/timestamp.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 82
+-- Number of queries: 86
-- !query
@@ -108,6 +108,39 @@ The fraction of sec must be zero. Valid range is [0, 60].
-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 1)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 1):timestamp>
+-- !query output
+0001-01-01 01:01:01
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 60)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 60):timestamp>
+-- !query output
+0001-01-01 01:02:00
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 61)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Invalid value for SecondOfMinute (valid values 0 - 59): 61
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, null)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, NULL):timestamp>
+-- !query output
+NULL
+
+
+-- !query
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null)
-- !query schema
struct<timestamp_seconds(1230219000):timestamp,timestamp_seconds(-1230219000):timestamp,timestamp_seconds(NULL):timestamp>
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 59797bc..a820bf6 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: 159
+-- Number of queries: 163
-- !query
@@ -759,6 +759,38 @@ NULL
-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 1)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 1):timestamp>
+-- !query output
+0001-01-01 01:01:01
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 60)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 60):timestamp>
+-- !query output
+0001-01-01 01:02:00
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 61)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 61):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, null)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, NULL):timestamp>
+-- !query output
+NULL
+
+
+-- !query
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null)
-- !query schema
struct<timestamp_seconds(1230219000):timestamp,timestamp_seconds(-1230219000):timestamp,timestamp_seconds(NULL):timestamp>
diff --git a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
index b3b7306..cf8fb9e 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 82
+-- Number of queries: 86
-- !query
@@ -101,6 +101,38 @@ NULL
-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 1)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 1):timestamp>
+-- !query output
+0001-01-01 01:01:01
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 60)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 60):timestamp>
+-- !query output
+0001-01-01 01:02:00
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 61)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 61):timestamp>
+-- !query output
+NULL
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, null)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, NULL):timestamp>
+-- !query output
+NULL
+
+
+-- !query
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null)
-- !query schema
struct<timestamp_seconds(1230219000):timestamp,timestamp_seconds(-1230219000):timestamp,timestamp_seconds(NULL):timestamp>
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
index a355c2d..5d0f9aa 100644
---
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp-ansi.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 82
+-- Number of queries: 86
-- !query
@@ -108,6 +108,39 @@ The fraction of sec must be zero. Valid range is [0, 60].
-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 1)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 1):timestamp_ntz>
+-- !query output
+0001-01-01 01:01:01
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 60)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 60):timestamp_ntz>
+-- !query output
+0001-01-01 01:02:00
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 61)
+-- !query schema
+struct<>
+-- !query output
+java.time.DateTimeException
+Invalid value for SecondOfMinute (valid values 0 - 59): 61
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, null)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, NULL):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null)
-- !query schema
struct<timestamp_seconds(1230219000):timestamp,timestamp_seconds(-1230219000):timestamp,timestamp_seconds(NULL):timestamp>
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
index 6c02a5a..0b24c1e 100644
---
a/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/timestampNTZ/timestamp.sql.out
@@ -1,5 +1,5 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 82
+-- Number of queries: 86
-- !query
@@ -101,6 +101,38 @@ NULL
-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 1)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 1):timestamp_ntz>
+-- !query output
+0001-01-01 01:01:01
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 60)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 60):timestamp_ntz>
+-- !query output
+0001-01-01 01:02:00
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, 61)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, 61):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
+SELECT make_timestamp(1, 1, 1, 1, 1, null)
+-- !query schema
+struct<make_timestamp(1, 1, 1, 1, 1, NULL):timestamp_ntz>
+-- !query output
+NULL
+
+
+-- !query
select
TIMESTAMP_SECONDS(1230219000),TIMESTAMP_SECONDS(-1230219000),TIMESTAMP_SECONDS(null)
-- !query schema
struct<timestamp_seconds(1230219000):timestamp,timestamp_seconds(-1230219000):timestamp,timestamp_seconds(NULL):timestamp>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
