This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new c7ca131ab1df [SPARK-60546][SQL] Reject subtraction of
nanosecond-precision timestamps
c7ca131ab1df is described below
commit c7ca131ab1df14fe7b0619e8644ff9f8eb0741d9
Author: Stevo Mitric <[email protected]>
AuthorDate: Tue Oct 27 21:17:05 2026 +0000
[SPARK-60546][SQL] Reject subtraction of nanosecond-precision timestamps
### What changes were proposed in this pull request?
Subtraction with a nanosecond-precision timestamp operand
(`TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)`, p in [7, 9]) now fails analysis with
the new `UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION` error, instead of
returning a microsecond-grid interval (SPARK-57832). This covers nanos - nanos,
mixed micro/nanos, nanos/DATE in either order, and legacy interval mode. It
also covers values that analysis evaluates early: constant `VALUES` rows,
`EXECUTE IMMEDIATE` / parameterized-query argu [...]
The check is `NanosTimestampSubtractionCheck`. It runs from `CheckAnalysis`
(after the other per-operator checks), from the single-pass resolver's
resolution checks, and before those early evaluations. A new
`SUBTRACT_TIMESTAMPS` tree pattern prunes it. The as-of join's internal
ordering distance is exempt: for DataFrame `joinAsOf`, only the subtraction
nodes `AsOfJoin` builds, so a nanosecond subtraction in a user key or the join
condition is still rejected; for the SQL `ASOF JOIN` p [...]
### Why are the changes needed?
The result silently drops sub-microsecond digits (`'…05.123456789' -
'…05.000000111'` gives `0.123456`). Day-time intervals may later gain
fractional-seconds precision (`INTERVAL DAY TO SECOND(p)`, as in Oracle,
Snowflake and Trino), and then `ts(p1) - ts(p2)` should return `INTERVAL DAY TO
SECOND(max(p1, p2))`. Changing a result type after release is hard, so it's
better to fail now (agreed with cloud-fan).
### Does this PR introduce _any_ user-facing change?
Yes, for nanosecond timestamps, which are behind
`spark.sql.timestampNanosTypes.enabled`. Subtraction with a nanosecond operand
now fails analysis instead of returning a microsecond-grid interval.
Microsecond timestamp subtraction is unchanged.
### How was this patch tested?
- Golden cases in `timestamp-ntz-nanos.sql` / `timestamp-ltz-nanos.sql`:
each operand path, a filter, a subquery, a `VALUES` row, `EXECUTE IMMEDIATE`,
`PIVOT`, SQL `ASOF JOIN` over mixed micro/nanos keys (scalar and tuple), and
the workarounds (both analyzers agree).
- New tests in `TimestampNanosFunctionsSuiteBase` (SQL and Column API, NTZ
and LTZ, legacy interval mode) and `TimestampNanosJoinSuiteBase` (`joinAsOf`
over NTZ/LTZ nanos keys with both analyzers and sort-merge on/off,
sub-microsecond neighbours, and rejection in a key and in the join condition),
ANSI on and off.
- `SortMergeAsOfJoinSuite`, `DataFrameAsOfJoinSuite`,
`AsOfJoinSortMergeSQLSuite`, `TypeCoercionSuite`, `AnsiTypeCoercionSuite`,
`DateExpressionsSuite`, `SparkThrowableSuite` and the timestamp/time/asof
golden files pass.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Isaac
Closes #59785 from
stevomitric/stevomitric/nanos-reject-timestamp-subtraction.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
(cherry picked from commit 653db2957ac7d1b18d8f0b070fe8b3b01ba34c9d)
Signed-off-by: Uros Bojanic <[email protected]>
---
.../src/main/resources/error/error-conditions.json | 5 +
docs/sql-ref-datatypes.md | 1 +
.../sql/catalyst/analysis/CheckAnalysis.scala | 2 +
.../analysis/NanosTimestampSubtractionCheck.scala | 65 +++++++
.../sql/catalyst/analysis/PivotTransformer.scala | 2 +
.../sql/catalyst/analysis/TypeCoercionHelper.scala | 4 +-
.../analysis/resolver/ResolverRunner.scala | 9 +-
.../catalyst/expressions/datetimeExpressions.scala | 13 +-
.../sql/catalyst/optimizer/RewriteAsOfJoin.scala | 7 +
.../spark/sql/catalyst/trees/TreePatterns.scala | 1 +
.../util/EvaluateUnresolvedInlineTable.scala | 2 +
.../command/v2/ParameterBindingUtils.scala | 3 +
.../analyzer-results/timestamp-ltz-nanos.sql.out | 78 +++++---
.../analyzer-results/timestamp-ntz-nanos.sql.out | 201 +++++++++++++++++--
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 25 +--
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 38 ++--
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 92 +++++----
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 216 ++++++++++++++++++---
.../sql/TimestampNanosFunctionsSuiteBase.scala | 32 +++
.../spark/sql/TimestampNanosJoinSuiteBase.scala | 62 ++++++
20 files changed, 713 insertions(+), 145 deletions(-)
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index a0ee5facb3be..a350016cbcaf 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -9487,6 +9487,11 @@
"Temporary views cannot be created with the WITH SCHEMA clause.
Recreate the temporary view when the underlying schema changes, or use a
persisted view."
]
},
+ "TIMESTAMP_NANOS_SUBTRACTION" : {
+ "message" : [
+ "Subtracting nanosecond-precision timestamps
(TIMESTAMP_LTZ/TIMESTAMP_NTZ with precision in [7, 9]) in <sqlExpr>. The
interval result has microsecond precision. Cast both operands to TIMESTAMP_LTZ
or TIMESTAMP_NTZ to subtract at microsecond precision, or cast them to
TIMESTAMP_LTZ(9) or TIMESTAMP_NTZ(9) and use `unix_nanos(end) -
unix_nanos(start)` for the elapsed time in nanoseconds."
+ ]
+ },
"TIMESTAMP_NANOS_WITH_LEGACY_TIME_PARSER" : {
"message" : [
"Parsing or formatting nanosecond-precision timestamps
(TIMESTAMP_LTZ/TIMESTAMP_NTZ with precision in [7, 9]) under the LEGACY time
parser policy. Set <config> to CORRECTED."
diff --git a/docs/sql-ref-datatypes.md b/docs/sql-ref-datatypes.md
index 5664a555e80c..5c2ccb31dc23 100644
--- a/docs/sql-ref-datatypes.md
+++ b/docs/sql-ref-datatypes.md
@@ -72,6 +72,7 @@ Spark SQL and DataFrames support the following data types:
- `TimestampNTZNanosType(precision)` / `TimestampLTZNanosType(precision)`:
Preview nanosecond-capable variants of `TIMESTAMP_NTZ` and `TIMESTAMP_LTZ` with
fractional seconds precision `precision` in `[7, 9]`. Unparameterized
`TIMESTAMP`, `TIMESTAMP_NTZ`, and `TIMESTAMP_LTZ` remain microsecond types. In
schema-driven Dataset/DataFrame conversion, Spark maps `TimestampNTZNanosType`
to `java.time.LocalDateTime` and `TimestampLTZNanosType` to
`java.time.Instant`; values with more sub-micro [...]
- Note: Nanosecond precision is only available through the schema-driven
APIs above. Typed encoders derived from
`java.time.Instant`/`java.time.LocalDateTime` (for example `ds.as[T]` for a
case class and `spark.createDataFrame(Seq(...))` without an explicit schema)
bind these types to the microsecond `TIMESTAMP_LTZ`/`TIMESTAMP_NTZ` types, and
typed Scala/Java UDFs such as `udf((i: java.time.Instant) => ...)` take
microsecond inputs, so a nanosecond argument needs an explicit `CAST` t [...]
- Note: The supported range is `0001-01-01` to `9999-12-31`, as for the
microsecond types. Adding an interval (`+ INTERVAL`, `timestampadd`) past the
internal limit of about years -290308 to +294247 (epoch microseconds in a
64-bit long) raises `DATETIME_OVERFLOW`, and casting a string past it returns
`NULL`, or raises `CAST_INVALID_INPUT` under ANSI mode. Parquet
`TIMESTAMP(NANOS)` and Avro `timestamp-nanos`/`local-timestamp-nanos` store a
64-bit count of epoch nanoseconds, so they o [...]
+ - Note: Subtracting timestamps when either operand has nanosecond
precision is not supported yet, because the interval result has microsecond
precision. Cast both operands to `TIMESTAMP_LTZ` or `TIMESTAMP_NTZ` to subtract
at microsecond precision, or cast them to `TIMESTAMP_LTZ(9)` or
`TIMESTAMP_NTZ(9)` and use `unix_nanos(end) - unix_nanos(start)` for the
elapsed time in nanoseconds.
* Interval types
- `YearMonthIntervalType(startField, endField)`: Represents a year-month
interval which is made up of a contiguous subset of the following fields:
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
index 99440b42a508..95100dabc402 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
@@ -1235,6 +1235,8 @@ trait CheckAnalysis extends LookupCatalog with
QueryErrorsBase with PlanToString
case _ => // Analysis successful!
}
+
+ NanosTimestampSubtractionCheck.checkOperator(operator)
}
checkCollectedMetrics(plan)
extendedCheckRules.foreach(_(plan))
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/NanosTimestampSubtractionCheck.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/NanosTimestampSubtractionCheck.scala
new file mode 100644
index 000000000000..8c2186598904
--- /dev/null
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/NanosTimestampSubtractionCheck.scala
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.spark.sql.catalyst.analysis
+
+import org.apache.spark.sql.catalyst.expressions.{Expression, ExprUtils, If,
SubtractTimestamps}
+import org.apache.spark.sql.catalyst.plans.logical.{AsOfJoin, LogicalPlan}
+import org.apache.spark.sql.catalyst.trees.TreePattern.SUBTRACT_TIMESTAMPS
+import org.apache.spark.sql.types.AnyTimestampNanoType
+
+/**
+ * Rejects subtraction with a nanosecond-precision timestamp operand. The
interval result only has
+ * microsecond precision, and adding fractional-seconds precision to it later
would change the
+ * result type. The as-of join ordering distance is internal, so its type
never reaches users, and
+ * `RewriteAsOfJoin` ranks nanosecond keys by exact epoch nanoseconds instead.
+ */
+object NanosTimestampSubtractionCheck extends (LogicalPlan => Unit) {
+ def apply(plan: LogicalPlan): Unit = plan.foreachUp(checkOperator)
+
+ def checkOperator(operator: LogicalPlan): Unit = operator match {
+ case j: AsOfJoin =>
+ j.expressions.filterNot(_ eq j.orderExpression).foreach(checkExpression)
+ // The SQL path never evaluates the distance, and its operands are also
in `asOfCondition`.
+ if (!j.requiresSortMergeAsOfJoin) checkAsOfDistance(j.orderExpression)
+ case o => o.expressions.foreach(checkExpression)
+ }
+
+ def checkExpression(expression: Expression): Unit = {
+ if (expression.containsPattern(SUBTRACT_TIMESTAMPS)) {
+ expression.foreach {
+ case s: SubtractTimestamps if s.childrenResolved &&
+ s.children.exists(c =>
AnyTimestampNanoType.acceptsType(c.dataType)) =>
+ s.failAnalysis(
+ errorClass = "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ messageParameters = Map("sqlExpr" -> ExprUtils.toSQLExpr(s)))
+ case _ =>
+ }
+ }
+ }
+
+ // `AsOfJoin.makeOrderingExpr` builds the distance as a subtraction of the
keys, or as an `If`
+ // over two such subtractions for nearest. Skip only those nodes; the keys
are user expressions.
+ private def checkAsOfDistance(expression: Expression): Unit = expression
match {
+ case s: SubtractTimestamps => s.children.foreach(checkExpression)
+ case If(predicate, trueValue, falseValue) =>
+ checkExpression(predicate)
+ checkAsOfDistance(trueValue)
+ checkAsOfDistance(falseValue)
+ case other => checkExpression(other)
+ }
+}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/PivotTransformer.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/PivotTransformer.scala
index 7f22dab71e3b..ac7bc5844cfd 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/PivotTransformer.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/PivotTransformer.scala
@@ -97,6 +97,8 @@ object PivotTransformer extends AliasHelper with
SQLConfHelper {
aggregates: Seq[Expression],
childOutput: Seq[Attribute],
newAlias: (Expression, Option[String]) => Alias): LogicalPlan = {
+ // The values are evaluated here, before the analyzed plan is checked.
+ pivotValues.foreach(NanosTimestampSubtractionCheck.checkExpression)
val evalPivotValues = pivotValues.map { value =>
val foldable = trimAliases(value).foldable
if (!foldable) {
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
index 1939e191c92e..bd897d7561d4 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala
@@ -430,7 +430,9 @@ abstract class TypeCoercionHelper {
* microsecond grid (a DayTimeIntervalType in the default mode, a
CalendarIntervalType when
* spark.sql.legacy.interval.enabled is set), so widening the precision
never changes the numeric
* result -- it only keeps the two operands the same concrete type. For a
pure-micro cross-family
- * pair this returns TimestampNTZType, identical to the pre-nanos behavior.
+ * pair this returns TimestampNTZType, identical to the pre-nanos behavior.
A nanos operand only
+ * survives analysis in the as-of join distance;
[[NanosTimestampSubtractionCheck]] rejects it
+ * anywhere else.
*/
private def subtractTimestampsCommonType(dt1: DataType, dt2: DataType):
DataType = {
val p = math.max(
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverRunner.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverRunner.scala
index 8bfd37de2f7b..1e535e90280b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverRunner.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverRunner.scala
@@ -18,7 +18,11 @@
package org.apache.spark.sql.catalyst.analysis.resolver
import org.apache.spark.sql.catalyst.{QueryPlanningTracker, SQLConfHelper}
-import org.apache.spark.sql.catalyst.analysis.{AnalysisContext,
MaterializedCTECheck}
+import org.apache.spark.sql.catalyst.analysis.{
+ AnalysisContext,
+ MaterializedCTECheck,
+ NanosTimestampSubtractionCheck
+}
import org.apache.spark.sql.catalyst.plans.logical.LogicalPlan
import org.apache.spark.sql.catalyst.trees.TreeNodeTag
import org.apache.spark.sql.internal.SQLConf
@@ -39,7 +43,8 @@ class ResolverRunner(
*/
private val singlePassOnlyResolutionChecks: Seq[LogicalPlan => Unit] = Seq(
NonDeterministicExpressionCheck,
- MaterializedCTECheck
+ MaterializedCTECheck,
+ NanosTimestampSubtractionCheck
)
/**
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 cc0b279c0ecf..88e1f53e3708 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
@@ -4946,9 +4946,9 @@ object Extract {
* - Otherwise the expression returns `DayTimeIntervalType` with the
difference in microseconds
* between given timestamps.
*
- * Both microsecond and nanosecond-precision timestamp types are accepted as
operands. Because the
- * difference is reported on the microsecond grid in either mode, a nanosecond
operand contributes
- * only its `epochMicros`; the sub-microsecond remainder is truncated.
+ * Nanosecond-precision operands are only used by the as-of join ordering
distance;
+ * `NanosTimestampSubtractionCheck` rejects them anywhere else, since the
interval result has
+ * microsecond precision. A nanosecond operand contributes only its
`epochMicros`.
*/
case class SubtractTimestamps(
left: Expression,
@@ -4963,10 +4963,7 @@ case class SubtractTimestamps(
def this(endTimestamp: Expression, startTimestamp: Expression) =
this(endTimestamp, startTimestamp, SQLConf.get.legacyIntervalEnabled)
- // Nanosecond-precision timestamps are accepted alongside the microsecond
types. The difference is
- // always reported on the microsecond grid (both result types --
DayTimeIntervalType and, in
- // legacy mode, CalendarIntervalType -- carry microsecond resolution), so
each operand contributes
- // only its epochMicros; the sub-microsecond remainder is truncated.
+ // Nanosecond operands are accepted for the as-of join distance (see the
class doc).
override def inputTypes: Seq[AbstractDataType] =
Seq(
TypeCollection(AnyTimestampType, AnyTimestampNanoType),
@@ -4974,6 +4971,8 @@ case class SubtractTimestamps(
override def dataType: DataType =
if (legacyInterval) CalendarIntervalType else DayTimeIntervalType()
+ override def nodePatternsInternal(): Seq[TreePattern] =
Seq(SUBTRACT_TIMESTAMPS)
+
override def withTimeZone(timeZoneId: String): TimeZoneAwareExpression =
copy(timeZoneId = Option(timeZoneId))
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteAsOfJoin.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteAsOfJoin.scala
index 3fb9d02dd024..e9fed820e85b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteAsOfJoin.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteAsOfJoin.scala
@@ -23,6 +23,7 @@ import org.apache.spark.sql.catalyst.expressions.aggregate._
import org.apache.spark.sql.catalyst.plans._
import org.apache.spark.sql.catalyst.plans.logical._
import org.apache.spark.sql.catalyst.rules._
+import org.apache.spark.sql.types.AnyTimestampNanoType
/**
* Replaces logical [[AsOfJoin]] operator using a combination of Join and
Aggregate operator.
@@ -78,6 +79,12 @@ object RewriteAsOfJoin extends Rule[LogicalPlan] {
val filtered = Filter(conditionWithOuterReference, right)
val orderExpressionWithOuterReference = orderExpression.transformUp {
+ // A nanosecond timestamp subtraction compares only epochMicros;
rank by the exact
+ // epoch-nanosecond difference instead.
+ case s: SubtractTimestamps
+ if s.children.forall(c =>
AnyTimestampNanoType.acceptsType(c.dataType)) =>
+ Subtract(UnixNanos(s.left), UnixNanos(s.right))
+ }.transformUp {
case a: AttributeReference if left.outputSet.contains(a) =>
OuterReference(a)
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/trees/TreePatterns.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/trees/TreePatterns.scala
index 35f66347e068..52dbfdeb3216 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/trees/TreePatterns.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/trees/TreePatterns.scala
@@ -96,6 +96,7 @@ object TreePattern extends Enumeration {
val SESSION_WINDOW: Value = Value
val SQL_FUNCTION_EXPRESSION: Value = Value
val SQL_SCALAR_FUNCTION: Value = Value
+ val SUBTRACT_TIMESTAMPS: Value = Value
val SUM: Value = Value
val TEMP_RESOLVED_COLUMN: Value = Value
val TIME_WINDOW: Value = Value
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/EvaluateUnresolvedInlineTable.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/EvaluateUnresolvedInlineTable.scala
index e8d9e9b569ae..68a0c301adda 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/EvaluateUnresolvedInlineTable.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/EvaluateUnresolvedInlineTable.scala
@@ -52,6 +52,8 @@ object EvaluateUnresolvedInlineTable extends SQLConfHelper
ignoreCollation: Boolean = false): LogicalPlan = {
validateInputDimension(table)
validateInputEvaluable(table)
+ // Rows may be evaluated here, before the analyzed plan is checked.
+ table.rows.flatten.foreach(NanosTimestampSubtractionCheck.checkExpression)
val resolvedTable = findCommonTypesAndCast(table, ignoreCollation)
if (canEvalAllDuringAnalysis(table)) {
earlyEvalIfPossible(resolvedTable)
diff --git
a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/v2/ParameterBindingUtils.scala
b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/v2/ParameterBindingUtils.scala
index 581525d9b0a2..6c4392555647 100644
---
a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/v2/ParameterBindingUtils.scala
+++
b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/v2/ParameterBindingUtils.scala
@@ -18,6 +18,7 @@
package org.apache.spark.sql.execution.command.v2
import org.apache.spark.sql.catalyst.InternalRow
+import org.apache.spark.sql.catalyst.analysis.NanosTimestampSubtractionCheck
import org.apache.spark.sql.catalyst.expressions.{Alias, Expression, Literal,
VariableReference}
import org.apache.spark.sql.errors.QueryCompilationErrors
@@ -60,6 +61,8 @@ object ParameterBindingUtils {
preExtractedNames: Seq[String] = Seq.empty): (Array[Any], Array[String])
= {
val values = scala.collection.mutable.ListBuffer[Any]()
val names = scala.collection.mutable.ListBuffer[String]()
+ // The args are evaluated here, before the analyzed plan is checked.
+ args.foreach(NanosTimestampSubtractionCheck.checkExpression)
args.zipWithIndex.foreach { case (expr, idx) =>
val (paramExpr, paramName) = if (preExtractedNames.nonEmpty) {
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
index 686bba8e71ed..0a02eadcc7fe 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ltz-nanos.sql.out
@@ -787,54 +787,80 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC'
-- !query analysis
-Project [(2020-01-01 19:04:05.123456789 - 2019-12-31 19:04:05.000000111) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ '2019-12-31
19:04:05.000000111')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' -
TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 108,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' -
TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC'
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
-- !query analysis
-Project [(2020-01-01 19:04:05.123456789 - 2020-01-01 19:04:05.123456001) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ '2020-01-01
19:04:05.123456001')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP
'2020-01-01 19:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' -
TIMESTAMP_LTZ '2020-01-02 03:04:05 UTC'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC'
+SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
-- !query analysis
-Project [(2019-12-31 19:04:05.000000111 - 2020-01-01 19:04:05.123456789) AS
(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111' - TIMESTAMP_LTZ '2020-01-01
19:04:05.123456789')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 72,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'"
+ } ]
+}
-- !query
-SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9))
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC')
-- !query analysis
-Project [(cast(cast(2020-01-02 03:04:05.1234567 UTC as timestamp_ltz(7)) as
timestamp_ltz(9)) - cast(2020-01-01 03:04:05.000000009 UTC as
timestamp_ltz(9))) AS (CAST(2020-01-02 03:04:05.1234567 UTC AS
TIMESTAMP_LTZ(7)) - CAST(2020-01-01 03:04:05.000000009 UTC AS
TIMESTAMP_LTZ(9)))#x]
+Project [(unix_nanos(2020-01-01 19:04:05.123456789) - unix_nanos(2019-12-31
19:04:05.000000111)) AS (unix_nanos(TIMESTAMP_LTZ '2020-01-01
19:04:05.123456789') - unix_nanos(TIMESTAMP_LTZ '2019-12-31
19:04:05.000000111'))#x]
+- OneRowRelation
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05 UTC' AS TIMESTAMP_LTZ(9)))
-- !query analysis
[Analyzer test output redacted due to nondeterminism]
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
--- !query analysis
-[Analyzer test output redacted due to nondeterminism]
-
-
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC'
--- !query analysis
-Project [(2019-12-31 16:00:00.123456789 - 1959-12-31 16:00:00.000000999) AS
(TIMESTAMP_LTZ '2019-12-31 16:00:00.123456789' - TIMESTAMP_LTZ '1959-12-31
16:00:00.000000999')#x]
-+- OneRowRelation
-
-
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9))
+SELECT CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' AS
TIMESTAMP_LTZ) - CAST(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' AS
TIMESTAMP_LTZ)
-- !query analysis
-Project [(2020-01-01 19:04:05.123456789 - cast(null as timestamp_ltz(9))) AS
(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - CAST(NULL AS
TIMESTAMP_LTZ(9)))#x]
+Project [(cast(2020-01-01 19:04:05.123456789 as timestamp) - cast(2019-12-31
19:04:05.000000111 as timestamp)) AS (CAST(TIMESTAMP_LTZ '2020-01-01
19:04:05.123456789' AS TIMESTAMP) - CAST(TIMESTAMP_LTZ '2019-12-31
19:04:05.000000111' AS TIMESTAMP))#x]
+- OneRowRelation
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
index 325ab70f3414..deb4b44486be 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/timestamp-ntz-nanos.sql.out
@@ -676,56 +676,219 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'
-- !query analysis
-Project [(2020-01-02 03:04:05.123456789 - 2020-01-01 03:04:05.000000111) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01
03:04:05.000000111')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 100,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'
+SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
-- !query analysis
-Project [(2020-01-02 03:04:05.123456789 - 2020-01-02 03:04:05.123456001) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-02
03:04:05.123456001')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 114,
+ "fragment" : "('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) -
('2020-01-01 03:04:05.000000009' :: timestamp_ntz(9))"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'
+SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
-- !query analysis
-Project [(2020-01-01 03:04:05.000000111 - 2020-01-02 03:04:05.123456789) AS
(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789')#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 72,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'"
+ } ]
+}
-- !query
-SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
+SELECT 1 WHERE TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05' > INTERVAL '0' SECOND
-- !query analysis
-Project [(cast(cast(2020-01-02 03:04:05.1234567 as timestamp_ntz(7)) as
timestamp_ntz(9)) - cast(2020-01-01 03:04:05.000000009 as timestamp_ntz(9))) AS
(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) - CAST(2020-01-01
03:04:05.000000009 AS TIMESTAMP_NTZ(9)))#x]
-+- OneRowRelation
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 16,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05')
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 16,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT d FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05') AS t(d)
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 23,
+ "stopIndex" : 105,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT ?' USING TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01 03:04:05'
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 36,
+ "stopIndex" : 118,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM VALUES (1, INTERVAL '1' DAY) AS t(a, iv) PIVOT (count(a) FOR iv
IN (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01
03:04:05'))
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 83,
+ "stopIndex" : 165,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') -
unix_nanos(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111')
-- !query analysis
-Project [(2020-01-02 03:04:05.123456789 - cast(2020-01-02 03:04:05 as
timestamp_ntz(9))) AS (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-02 03:04:05')#x]
+Project [(unix_nanos(2020-01-02 03:04:05.123456789) - unix_nanos(2020-01-01
03:04:05.000000111)) AS (unix_nanos(TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789') - unix_nanos(TIMESTAMP_NTZ '2020-01-01
03:04:05.000000111'))#x]
+- OneRowRelation
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789') -
unix_nanos(CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)))
-- !query analysis
[Analyzer test output redacted due to nondeterminism]
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'
+SELECT CAST(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' AS TIMESTAMP_NTZ) -
CAST(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' AS TIMESTAMP_NTZ)
-- !query analysis
-Project [(2020-01-01 00:00:00.123456789 - 1960-01-01 00:00:00.000000999) AS
(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ '1960-01-01
00:00:00.000000999')#x]
+Project [(cast(2020-01-02 03:04:05.123456789 as timestamp_ntz) -
cast(2020-01-01 03:04:05.000000111 as timestamp_ntz)) AS (CAST(TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789' AS TIMESTAMP_NTZ) - CAST(TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111' AS TIMESTAMP_NTZ))#x]
+- OneRowRelation
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9))
+SET spark.sql.join.asofJoin.enabled=true
-- !query analysis
-Project [(2020-01-02 03:04:05.123456789 - cast(null as timestamp_ntz(9))) AS
(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
TIMESTAMP_NTZ(9)))#x]
-+- OneRowRelation
+SetCommand (spark.sql.join.asofJoin.enabled,Some(true))
+
+
+-- !query
+SELECT l.v, r.v FROM VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1) AS
l(ts, v)
+ ASOF JOIN VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10),
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(ts, v)
+ MATCH_CONDITION (l.ts >= r.ts)
+-- !query analysis
+Project [v#x, v#x]
++- AsOfJoin (cast(ts#x as timestamp_ntz(9)) >= ts#x), Inner
+ :- SubqueryAlias l
+ : +- LocalRelation [ts#x, v#x]
+ +- SubqueryAlias r
+ +- LocalRelation [ts#x, v#x]
+
+
+-- !query
+SELECT l.v, r.v FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1)
AS l(id, ts, v)
+ ASOF JOIN VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10), (1,
TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(id, ts, v)
+ MATCH_CONDITION ((l.id, l.ts) >= (r.id, r.ts))
+-- !query analysis
+Project [v#x, v#x]
++- AsOfJoin (cast(named_struct(id, id#x, ts, ts#x) as
struct<id:int,ts:timestamp_ntz(9)>) >= named_struct(id, id#x, ts, ts#x)), Inner
+ :- SubqueryAlias l
+ : +- LocalRelation [id#x, ts#x, v#x]
+ +- SubqueryAlias r
+ +- LocalRelation [id#x, ts#x, v#x]
+
+
+-- !query
+SET spark.sql.join.asofJoin.enabled=false
+-- !query analysis
+SetCommand (spark.sql.join.asofJoin.enabled,Some(false))
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
index 90162d505124..8a4a21ac0a13 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ltz-nanos.sql
@@ -208,28 +208,15 @@ SELECT TIMESTAMP_LTZ '1960-01-31 03:04:05.123456789 UTC'
+ INTERVAL '1' MONTH;
-- legacy calendar interval is still rejected by TimestampAddInterval's type
check.
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' + make_interval(0, 1,
0, 2, 0, 0, 0);
--- SPARK-57832: TIMESTAMP_LTZ(p) - TIMESTAMP_LTZ(p) yields a microsecond-grid
DayTimeIntervalType.
--- Only each operand's epochMicros participates, so the sub-microsecond
remainder is truncated: the
--- 789/111 sub-micro digits drop out and the difference is exactly 1 day +
0.123456 s. Like the
--- micro TIMESTAMP_LTZ case, the subtraction runs on session-zone local
date-times, so a DST
--- transition between the two instants could shift the interval; there is none
in the UTC span here.
+-- Subtraction with a TIMESTAMP_LTZ(p) operand is rejected: the interval
result only has
+-- microsecond precision.
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC';
--- Two values inside the same microsecond subtract to zero once the remainder
is truncated.
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC';
--- The subtraction is antisymmetric.
-SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC';
--- Mixed precision (7 vs 9) widens to the common nanos type before subtracting.
-SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9));
--- Mixed with a micro TIMESTAMP_LTZ operand.
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC';
--- A DATE operand is cast to the nanos LTZ type (midnight in the session time
zone); the
--- fraction below the micro grid drops. The bare LTZ literal and the DATE both
read in the session
--- zone (America/Los_Angeles), so the difference is exactly 1 day.
SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01';
--- Pre-epoch operand exercises the negative-epoch path.
-SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC';
--- NULL operand propagates.
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9));
+-- The elapsed time in nanoseconds, and subtraction at microsecond precision
after a cast.
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC');
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05 UTC' AS TIMESTAMP_LTZ(9)));
+SELECT CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' AS
TIMESTAMP_LTZ) - CAST(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' AS
TIMESTAMP_LTZ);
-- SPARK-57818: convert_timezone is NTZ-only, so a nanosecond LTZ(p) source is
rejected rather
-- than silently reinterpreted as NTZ (the positive TIMESTAMP_NTZ(p) path is
covered in
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
index 6aa4ec03a020..6744c8f09f41 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/timestamp-ntz-nanos.sql
@@ -177,25 +177,31 @@ SELECT TIMESTAMP_NTZ '1960-01-31 03:04:05.123456789' +
INTERVAL '1' MONTH;
-- legacy calendar interval is still rejected by TimestampAddInterval's type
check.
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' + make_interval(0, 1, 0,
2, 0, 0, 0);
--- SPARK-57832: TIMESTAMP_NTZ(p) - TIMESTAMP_NTZ(p) yields a microsecond-grid
DayTimeIntervalType.
--- Only each operand's epochMicros participates, so the sub-microsecond
remainder is truncated: the
--- 789/111 sub-micro digits drop out and the difference is exactly 1 day +
0.123456 s.
+-- Subtraction with a TIMESTAMP_NTZ(p) operand is rejected: the interval
result only has
+-- microsecond precision. This holds in a filter, a subquery, and the folded
VALUES rows,
+-- EXECUTE IMMEDIATE arguments and PIVOT values too.
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111';
--- Two values inside the same microsecond subtract to zero once the remainder
is truncated.
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001';
--- The subtraction is antisymmetric.
-SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789';
--- Mixed precision (7 vs 9) widens to the common nanos type before
subtracting; the result stays on
--- the micros grid.
SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9));
--- Mixed with a micro TIMESTAMP_NTZ operand.
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05';
--- A DATE operand is cast to the nanos type (midnight); the fraction below the
micro grid drops.
SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01';
--- Pre-epoch operand exercises the negative-epoch path.
-SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999';
--- NULL operand propagates.
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9));
+SELECT 1 WHERE TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05' > INTERVAL '0' SECOND;
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05');
+SELECT d FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05') AS t(d);
+EXECUTE IMMEDIATE 'SELECT ?' USING TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01 03:04:05';
+SELECT * FROM VALUES (1, INTERVAL '1' DAY) AS t(a, iv) PIVOT (count(a) FOR iv
IN (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01
03:04:05'));
+-- The elapsed time in nanoseconds, and subtraction at microsecond precision
after a cast.
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') -
unix_nanos(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111');
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789') -
unix_nanos(CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)));
+SELECT CAST(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' AS TIMESTAMP_NTZ) -
CAST(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' AS TIMESTAMP_NTZ);
+-- A SQL as-of join over mixed micro/nanos keys is accepted, with a scalar or
a tuple condition:
+-- its ordering distance is never evaluated.
+SET spark.sql.join.asofJoin.enabled=true;
+SELECT l.v, r.v FROM VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1) AS
l(ts, v)
+ ASOF JOIN VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10),
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(ts, v)
+ MATCH_CONDITION (l.ts >= r.ts);
+SELECT l.v, r.v FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1)
AS l(id, ts, v)
+ ASOF JOIN VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10), (1,
TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(id, ts, v)
+ MATCH_CONDITION ((l.id, l.ts) >= (r.id, r.ts));
+SET spark.sql.join.asofJoin.enabled=false;
-- SPARK-57818: convert_timezone over nanosecond-precision TIMESTAMP_NTZ. The
sub-microsecond
-- remainder is carried through unchanged; only the whole-microsecond part
shifts with the zone
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
index 45cc4763847f..5d1d0cb3a1dd 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ltz-nanos.sql.out
@@ -893,65 +893,91 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-01 03:04:05.000000111 UTC'
-- !query schema
-struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ
'2019-12-31 19:04:05.000000111'):interval day to second>
--- !query output
-1 00:00:00.123456000
-
-
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456001 UTC'
--- !query schema
-struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456001'):interval day to second>
--- !query output
-0 00:00:00.000000000
-
-
--- !query
-SELECT TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05.123456789 UTC'
--- !query schema
-struct<(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111' - TIMESTAMP_LTZ
'2020-01-01 19:04:05.123456789'):interval day to second>
+struct<>
-- !query output
--1 00:00:00.123456000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' -
TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 108,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' -
TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC'"
+ } ]
+}
-- !query
-SELECT ('2020-01-02 03:04:05.1234567 UTC' :: timestamp_ltz(7)) - ('2020-01-01
03:04:05.000000009 UTC' :: timestamp_ltz(9))
+SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
-- !query schema
-struct<(CAST(2020-01-02 03:04:05.1234567 UTC AS TIMESTAMP_LTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 UTC AS TIMESTAMP_LTZ(9))):interval day to
second>
+struct<>
-- !query output
-1 00:00:00.123456000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP
'2020-01-01 19:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' -
TIMESTAMP_LTZ '2020-01-02 03:04:05 UTC'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - TIMESTAMP_LTZ
'2020-01-02 03:04:05 UTC'
+SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
-- !query schema
-struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - TIMESTAMP '2020-01-01
19:04:05'):interval day to second>
+struct<>
-- !query output
-0 00:00:00.123456000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 72,
+ "fragment" : "TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC')
-- !query schema
-struct<(TIMESTAMP_LTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'):interval day to second>
+struct<(unix_nanos(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789') -
unix_nanos(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111')):decimal(22,0)>
-- !query output
-1 00:00:00.000000000
+86400123456678
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-01 00:00:00.123456789 UTC' - TIMESTAMP_LTZ
'1960-01-01 00:00:00.000000999 UTC'
+SELECT unix_nanos(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC') -
unix_nanos(CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05 UTC' AS TIMESTAMP_LTZ(9)))
-- !query schema
-struct<(TIMESTAMP_LTZ '2019-12-31 16:00:00.123456789' - TIMESTAMP_LTZ
'1959-12-31 16:00:00.000000999'):interval day to second>
+struct<(unix_nanos(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789') -
unix_nanos(CAST(TIMESTAMP '2020-01-01 19:04:05' AS
TIMESTAMP_LTZ(9)))):decimal(22,0)>
-- !query output
-21915 00:00:00.123456000
+123456789
-- !query
-SELECT TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' - CAST(NULL AS
timestamp_ltz(9))
+SELECT CAST(TIMESTAMP_LTZ '2020-01-02 03:04:05.123456789 UTC' AS
TIMESTAMP_LTZ) - CAST(TIMESTAMP_LTZ '2020-01-01 03:04:05.000000111 UTC' AS
TIMESTAMP_LTZ)
-- !query schema
-struct<(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' - CAST(NULL AS
TIMESTAMP_LTZ(9))):interval day to second>
+struct<(CAST(TIMESTAMP_LTZ '2020-01-01 19:04:05.123456789' AS TIMESTAMP) -
CAST(TIMESTAMP_LTZ '2019-12-31 19:04:05.000000111' AS TIMESTAMP)):interval day
to second>
-- !query output
-NULL
+1 00:00:00.123456000
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
index a36f137d2b7f..e897b32e13f4 100644
--- a/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/timestamp-ntz-nanos.sql.out
@@ -763,65 +763,237 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05.000000111'):interval day to second>
+struct<>
-- !query output
-1 00:00:00.123456000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 100,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111'"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'
+SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456001'):interval day to second>
+struct<>
-- !query output
-0 00:00:00.000000000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 AS TIMESTAMP_NTZ(9)))\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 114,
+ "fragment" : "('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) -
('2020-01-01 03:04:05.000000009' :: timestamp_ntz(9))"
+ } ]
+}
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'
+SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' - TIMESTAMP_NTZ
'2020-01-02 03:04:05.123456789'):interval day to second>
+struct<>
-- !query output
--1 00:00:00.123456000
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 72,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'"
+ } ]
+}
-- !query
-SELECT ('2020-01-02 03:04:05.1234567' :: timestamp_ntz(7)) - ('2020-01-01
03:04:05.000000009' :: timestamp_ntz(9))
+SELECT 1 WHERE TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05' > INTERVAL '0' SECOND
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 16,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT (SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-01 03:04:05')
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 16,
+ "stopIndex" : 98,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT d FROM VALUES (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05') AS t(d)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 23,
+ "stopIndex" : 105,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+EXECUTE IMMEDIATE 'SELECT ?' USING TIMESTAMP_NTZ '2020-01-02
03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01 03:04:05'
-- !query schema
-struct<(CAST(2020-01-02 03:04:05.1234567 AS TIMESTAMP_NTZ(7)) -
CAST(2020-01-01 03:04:05.000000009 AS TIMESTAMP_NTZ(9))):interval day to second>
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 36,
+ "stopIndex" : 118,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT * FROM VALUES (1, INTERVAL '1' DAY) AS t(a, iv) PIVOT (count(a) FOR iv
IN (TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ '2020-01-01
03:04:05'))
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "sqlExpr" : "\"(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05')\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 83,
+ "stopIndex" : 165,
+ "fragment" : "TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' -
TIMESTAMP_NTZ '2020-01-01 03:04:05'"
+ } ]
+}
+
+
+-- !query
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') -
unix_nanos(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111')
+-- !query schema
+struct<(unix_nanos(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789') -
unix_nanos(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111')):decimal(22,0)>
+-- !query output
+86400123456678
+
+
+-- !query
+SELECT unix_nanos(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789') -
unix_nanos(CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)))
+-- !query schema
+struct<(unix_nanos(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789') -
unix_nanos(CAST(DATE '2020-01-01' AS TIMESTAMP_NTZ(9)))):decimal(22,0)>
+-- !query output
+86400000000789
+
+
+-- !query
+SELECT CAST(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' AS TIMESTAMP_NTZ) -
CAST(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' AS TIMESTAMP_NTZ)
+-- !query schema
+struct<(CAST(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' AS TIMESTAMP_NTZ) -
CAST(TIMESTAMP_NTZ '2020-01-01 03:04:05.000000111' AS TIMESTAMP_NTZ)):interval
day to second>
-- !query output
1 00:00:00.123456000
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'
+SET spark.sql.join.asofJoin.enabled=true
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - TIMESTAMP_NTZ
'2020-01-02 03:04:05'):interval day to second>
+struct<key:string,value:string>
-- !query output
-0 00:00:00.123456000
+spark.sql.join.asofJoin.enabled true
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE '2020-01-01'
+SELECT l.v, r.v FROM VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1) AS
l(ts, v)
+ ASOF JOIN VALUES (TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10),
(TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(ts, v)
+ MATCH_CONDITION (l.ts >= r.ts)
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-02 00:00:00.000000789' - DATE
'2020-01-01'):interval day to second>
+struct<v:int,v:int>
-- !query output
-1 00:00:00.000000000
+1 20
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'
+SELECT l.v, r.v FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000002', 1)
AS l(id, ts, v)
+ ASOF JOIN VALUES (1, TIMESTAMP_NTZ '2020-01-01 00:00:00.000001100', 10), (1,
TIMESTAMP_NTZ '2020-01-01 00:00:00.000001800', 20) AS r(id, ts, v)
+ MATCH_CONDITION ((l.id, l.ts) >= (r.id, r.ts))
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-01 00:00:00.123456789' - TIMESTAMP_NTZ
'1960-01-01 00:00:00.000000999'):interval day to second>
+struct<v:int,v:int>
-- !query output
-21915 00:00:00.123456000
+1 20
-- !query
-SELECT TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
timestamp_ntz(9))
+SET spark.sql.join.asofJoin.enabled=false
-- !query schema
-struct<(TIMESTAMP_NTZ '2020-01-02 03:04:05.123456789' - CAST(NULL AS
TIMESTAMP_NTZ(9))):interval day to second>
+struct<key:string,value:string>
-- !query output
-NULL
+spark.sql.join.asofJoin.enabled false
-- !query
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
index 565bdeb5e2ac..b9916957725d 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosFunctionsSuiteBase.scala
@@ -1358,6 +1358,38 @@ abstract class TimestampNanosFunctionsSuiteBase extends
SharedSparkSession {
checkAnswer(
df.select(toStr(col("c").cast(TimestampNTZType))),
Row("2020-01-01T12:00:00.123456"))
}
+
+ test("subtraction with a nanosecond-precision timestamp operand is
rejected") {
+ Seq[(DataType, DataType, Any, Any)](
+ (TimestampNTZNanosType(9), TimestampNTZType,
+ LocalDateTime.parse("2020-01-02T03:04:05.123456789"),
+ LocalDateTime.parse("2020-01-01T03:04:05")),
+ (TimestampLTZNanosType(9), TimestampType,
+ Instant.parse("2020-01-02T03:04:05.123456789Z"),
+ Instant.parse("2020-01-01T03:04:05Z"))).foreach { case (nanosType,
microType, n, m) =>
+ val df = spark.createDataFrame(
+ spark.sparkContext.parallelize(Seq(Row(n, m,
java.sql.Date.valueOf("2020-01-01")))),
+ new StructType().add("n", nanosType).add("m", microType).add("d",
DateType))
+ Seq("false", "true").foreach { legacyInterval =>
+ withSQLConf(SQLConf.LEGACY_INTERVAL_ENABLED.key -> legacyInterval) {
+ checkError(
+ exception = intercept[AnalysisException](df.selectExpr("n - n")),
+ condition = "UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION",
+ parameters = Map("sqlExpr" -> "\"(n - n)\""),
+ context = ExpectedContext(fragment = "n - n", start = 0, stop = 4))
+ Seq(col("n") - col("m"), col("m") - col("n"), col("n") - col("d"),
col("d") - col("n"))
+ .foreach { c =>
+ val e = intercept[AnalysisException](df.select(c))
+ assert(e.getCondition ==
"UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION")
+ }
+ }
+ }
+ // Casting to the micro type still subtracts, at microsecond precision.
+ checkAnswer(
+ df.select(col("n").cast(microType) - col("m")),
+ Row(java.time.Duration.ofDays(1).plusNanos(123456000)))
+ }
+ }
}
// Runs the nanosecond timestamp function tests with ANSI mode enabled
explicitly.
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
index 79762085c727..2157c2ecf929 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/TimestampNanosJoinSuiteBase.scala
@@ -19,6 +19,8 @@ package org.apache.spark.sql
import java.time.{Instant, LocalDateTime}
+import scala.jdk.CollectionConverters._
+
import org.apache.spark.SparkConf
import org.apache.spark.sql.catalyst.expressions.EqualTo
import org.apache.spark.sql.catalyst.plans.logical.Join
@@ -397,6 +399,66 @@ abstract class TimestampNanosJoinSuiteBase extends
SharedSparkSession with Adapt
}
}
}
+
+ // The as-of join orders candidates by an internal timestamp subtraction,
which stays allowed for
+ // nanosecond keys even though user-written nanosecond subtraction is
rejected.
+ test("DataFrame as-of join over nanosecond keys") {
+ Seq[(DataType, String => Any)](
+ TimestampNTZNanosType(9) -> (s => LocalDateTime.parse(s)),
+ TimestampLTZNanosType(9) -> (s => Instant.parse(s + "Z"))).foreach {
case (keyType, key) =>
+ val schema = new StructType().add("k", keyType).add("k2",
keyType).add("v", IntegerType)
+ def df(rows: (String, Int)*): classic.DataFrame = spark.createDataFrame(
+ rows.map { case (k, v) => Row(key(k), key(k), v) }.asJava, schema)
+ val left = df("2020-01-01T00:00:01.000000500" -> 1)
+ val right = df("2020-01-01T00:00:00.000000100" -> 10,
"2020-01-01T00:00:03.000000200" -> 20)
+ def asOf(l: Column, r: Column, joinExprs: Column, direction: String):
classic.DataFrame =
+ left.joinAsOf(right, l, r, joinExprs, joinType = "inner", tolerance =
null,
+ allowExactMatches = true, direction = direction)
+ // (left key, right rows, expected right value for backward / forward /
nearest). The last two
+ // need the exact distance: their candidates differ from the left key
below a microsecond.
+ val cases = Seq(
+ ("00:00:01.000000500", Seq("00:00:00.000000100" -> 10,
"00:00:03.000000200" -> 20),
+ (10, 20, 10)),
+ ("00:00:00.000001900", Seq("00:00:00.000001100" -> 1,
"00:00:00.000001800" -> 2,
+ "00:00:00.000002300" -> 3, "00:00:00.000002900" -> 4), (2, 3, 2)),
+ ("00:00:00.000000999", Seq("00:00:00.000000000" -> 1,
"00:00:00.000001001" -> 2),
+ (1, 2, 2)))
+ Seq("false", "true").foreach { singlePass =>
+ withSQLConf(
+ SQLConf.ANALYZER_SINGLE_PASS_RESOLVER_ENABLED.key -> singlePass,
+ SQLConf.ANALYZER_SINGLE_PASS_RESOLVER_ENABLED_TENTATIVELY.key ->
"false",
+ SQLConf.ANALYZER_DUAL_RUN_LEGACY_AND_SINGLE_PASS_RESOLVER.key ->
"false",
+
SQLConf.ANALYZER_SINGLE_PASS_RESOLVER_ENABLE_ASOF_JOIN_RESOLUTION.key ->
"true") {
+ for {
+ sortMerge <- Seq("false", "true")
+ (leftKey, rightRows, (backward, forward, nearest)) <- cases
+ (direction, expected) <-
+ Seq("backward" -> backward, "forward" -> forward, "nearest" ->
nearest)
+ } withSQLConf(SQLConf.SORT_MERGE_AS_OF_JOIN_ENABLED.key ->
sortMerge) {
+ val l = df(s"2020-01-01T$leftKey" -> 0)
+ val r = df(rightRows.map { case (k, v) => s"2020-01-01T$k" -> v }:
_*)
+ // The single-pass analyzer cannot resolve the deserializer
`checkToRDD` adds.
+ QueryTest.checkAnswer(
+ df = l.joinAsOf(r, l("k"), r("k"), usingColumns = Seq.empty,
joinType = "inner",
+ tolerance = null, allowExactMatches = true, direction =
direction)
+ .select(r("v")),
+ expectedAnswer = Seq(Row(expected)),
+ checkToRDD = false)
+ }
+ // A nanosecond subtraction in an as-of key or in the join condition
is still rejected.
+ Seq(
+ () => asOf(left("k") - left("k2"), right("k") - right("k2"), null,
"nearest"),
+ () => asOf(left("k") - left("k2"), right("k") - right("k2"), null,
"backward"),
+ () => asOf(left("k"), right("k"),
+ left("k") - right("k") < functions.expr("INTERVAL 5 SECONDS"),
"backward")
+ ).foreach { join =>
+ val e = intercept[AnalysisException](join())
+ assert(e.getCondition ==
"UNSUPPORTED_FEATURE.TIMESTAMP_NANOS_SUBTRACTION")
+ }
+ }
+ }
+ }
+ }
}
// Runs the nanosecond timestamp join tests with ANSI mode enabled explicitly.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
