You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/10/20 16:01:27 UTC
(spark) branch branch-4.x updated: [SPARK-60337][SQL] Reject nanosecond timestamps in percentile_approx and histogram_numeric
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 e2671665d075 [SPARK-60337][SQL] Reject nanosecond timestamps in percentile_approx and histogram_numeric
e2671665d075 is described below
commit e2671665d0754244241ba64b1c191369bfcec118
Author: Stevo Mitric <st...@gmail.com>
AuthorDate: Tue Oct 20 16:01:02 2026 +0000
[SPARK-60337][SQL] Reject nanosecond timestamps in percentile_approx and histogram_numeric
### What changes were proposed in this pull request?
`percentile_approx` / `approx_percentile` and `histogram_numeric` now reject `TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)` (p in [7, 9]) with `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE` in both ANSI modes, instead of implicitly casting them to `DATE`.
Neither function lists a nanosecond type in its input `TypeCollection`, so the implicit-cast fallback picks the first store-assignable member, which is `DateType`. For a nanosecond child, `inputTypes` now expects exactly that type, so the analyzer inserts no cast, and `checkInputDataTypes` reports the standard mismatch against the supported types. This is the same error the generic type coercion would give if it didn't pick `DATE` (see #59575, which makes that change for every functio [...]
`ANALYZE ... COMPUTE STATISTICS FOR COLUMNS` with `spark.sql.statistics.histogram.enabled` builds an internal `percentile_approx` for every datetime column. `CommandUtils.supportsHistogram` now skips nanosecond timestamps, so these columns get basic stats without a histogram instead of failing the command. For persistent tables, ANALYZE on a non-null nanosecond column still fails afterwards, because the catalog can't serialize nanosecond column stats yet; that limitation exists on mas [...]
Why reject instead of supporting nanos natively: both aggregates compute on doubles. `QuantileSummaries` keeps doubles as samples, and `NumericHistogram` averages double bin centers. A double has a 53-bit mantissa, so around 2020 any numeric encoding has a 256 ns resolution. That covers epoch nanos, epoch micros plus a fraction, and epoch seconds plus a fraction. Only 5 distinct doubles fall within one microsecond, and a single input `2020-01-01 12:00:00.123456789` would come back as [...]
### Why are the changes needed?
Part of SPARK-56822. Today `SELECT percentile_approx(c, 0.5) FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') t(c)` returns `2020-01-01` typed `DATE` in both ANSI modes, and `histogram_numeric` returns `DATE` bin centers. This is a silent wrong result, which the SPIP's parity criterion rules out.
### Does this PR introduce _any_ user-facing change?
Yes, only for the nanosecond timestamp types, which are behind the preview flag `spark.sql.timestampNanosTypes.enabled`. These calls now fail at analysis instead of returning a `DATE`.
### How was this patch tested?
- New tests in `ApproximatePercentileSuite` and `HistogramNumericSuite`: NTZ/LTZ at p=7..9, both ANSI modes.
- New test in `ApproximatePercentileQuerySuite`: `percentile_approx` and `approx_percentile`, scalar and array percentages, both ANSI modes.
- New golden cases in `timestamp-ntz-nanos.sql` and `timestamp-ltz-nanos.sql`: both ANSI modes, plus the explicit-cast workaround.
- New `StatisticsCollectionSuite` test: ANALYZE with histograms on a cached view with populated and all-null nanos columns.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Isaac
Closes #59578 from stevomitric/stevomitric/nanos-approx-percentile.
Authored-by: Stevo Mitric <st...@gmail.com>
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
(cherry picked from commit 65680270d6be641823422e3c250cac19ea77ee36)
Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
.../aggregate/ApproximatePercentileSuite.scala | 32 ++++++++++++++++++++--
.../aggregate/HistogramNumericSuite.scala | 26 ++++++++++++++++++
.../spark/sql/execution/command/CommandUtils.scala | 2 ++
.../sql/ApproximatePercentileQuerySuite.scala | 30 ++++++++++++++++++++
.../spark/sql/StatisticsCollectionSuite.scala | 22 +++++++++++++++
5 files changed, 110 insertions(+), 2 deletions(-)
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/ApproximatePercentileSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/ApproximatePercentileSuite.scala
index 1710a1b897a6..60fb56ec799e 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/ApproximatePercentileSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/ApproximatePercentileSuite.scala
@@ -20,6 +20,7 @@ package org.apache.spark.sql.catalyst.expressions.aggregate
import java.sql.Date
import org.apache.spark.SparkFunSuite
+import org.apache.spark.sql.AnalysisException
import org.apache.spark.sql.catalyst.InternalRow
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
import org.apache.spark.sql.catalyst.analysis.UnresolvedAttribute
@@ -28,13 +29,15 @@ import org.apache.spark.sql.catalyst.dsl.plans._
import org.apache.spark.sql.catalyst.expressions.{Alias, AttributeReference, BoundReference, Cast, CreateArray, DecimalLiteral, GenericInternalRow, Literal}
import org.apache.spark.sql.catalyst.expressions.Cast._
import org.apache.spark.sql.catalyst.expressions.aggregate.ApproximatePercentile.{PercentileDigest, PercentileDigestSerializer}
+import org.apache.spark.sql.catalyst.plans.SQLHelper
import org.apache.spark.sql.catalyst.plans.logical.LocalRelation
import org.apache.spark.sql.catalyst.util.{ArrayData, QuantileSummaries}
import org.apache.spark.sql.catalyst.util.QuantileSummaries.Stats
-import org.apache.spark.sql.types.{ArrayType, Decimal, DecimalType, DoubleType, FloatType, IntegerType, IntegralType, LongType, TimeType}
+import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.types.{ArrayType, Decimal, DecimalType, DoubleType, FloatType, IntegerType, IntegralType, LongType, TimestampLTZNanosType, TimestampNTZNanosType, TimeType}
import org.apache.spark.util.SizeEstimator
-class ApproximatePercentileSuite extends SparkFunSuite {
+class ApproximatePercentileSuite extends SparkFunSuite with SQLHelper {
private val random = new java.util.Random()
@@ -152,6 +155,31 @@ class ApproximatePercentileSuite extends SparkFunSuite {
assert(Math.abs(result.asInstanceOf[Long] - 500L) < 5L)
}
+ test("SPARK-60337: ApproximatePercentile rejects nanosecond timestamps") {
+ // The sketch holds doubles, so a nanosecond timestamp is rejected rather than implicitly
+ // cast to DATE.
+ for {
+ ansiEnabled <- Seq("true", "false")
+ dataType <- (7 to 9).flatMap(p => Seq(TimestampNTZNanosType(p), TimestampLTZNanosType(p)))
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled) {
+ val agg = new ApproximatePercentile(UnresolvedAttribute("a"), Literal(0.5D))
+ checkError(
+ exception = intercept[AnalysisException] {
+ LocalRelation(AttributeReference("a", dataType)()).select(agg).analyze
+ },
+ condition = "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ parameters = Map(
+ "sqlExpr" -> "\"percentile_approx(a, 0.5, 10000)\"",
+ "paramIndex" -> "first",
+ "requiredType" -> ("(\"NUMERIC\" or \"DATE\" or \"TIMESTAMP\" or \"TIMESTAMP_NTZ\" " +
+ "or \"INTERVAL YEAR TO MONTH\" or \"INTERVAL DAY TO SECOND\" or \"TIME\")"),
+ "inputSql" -> "\"a\"",
+ "inputType" -> s"\"${dataType.sql}\""))
+ }
+ }
+ }
+
test("class ApproximatePercentile, low level interface, update, merge, eval...") {
val childExpression = Cast(BoundReference(0, IntegerType, nullable = true), DoubleType)
val inputAggregationBufferOffset = 1
diff --git a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/HistogramNumericSuite.scala b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/HistogramNumericSuite.scala
index 98bf07ff76a3..48e5441fab8c 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/HistogramNumericSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/HistogramNumericSuite.scala
@@ -21,6 +21,7 @@ import java.sql.Timestamp
import java.time.{Duration, Period}
import org.apache.spark.SparkFunSuite
+import org.apache.spark.sql.AnalysisException
import org.apache.spark.sql.catalyst.InternalRow
import org.apache.spark.sql.catalyst.analysis.TypeCheckResult.DataTypeMismatch
import org.apache.spark.sql.catalyst.analysis.UnresolvedAttribute
@@ -217,6 +218,31 @@ class HistogramNumericSuite extends SparkFunSuite with SQLHelper {
}
}
+ test("SPARK-60337: HistogramNumeric rejects nanosecond timestamps") {
+ // The histogram holds doubles, so a nanosecond timestamp is rejected rather than implicitly
+ // cast to DATE.
+ for {
+ ansiEnabled <- Seq("true", "false")
+ dataType <- (7 to 9).flatMap(p => Seq(TimestampNTZNanosType(p), TimestampLTZNanosType(p)))
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled) {
+ val agg = new HistogramNumeric(UnresolvedAttribute("a"), Literal(3))
+ checkError(
+ exception = intercept[AnalysisException] {
+ LocalRelation(AttributeReference("a", dataType)()).select(agg).analyze
+ },
+ condition = "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ parameters = Map(
+ "sqlExpr" -> "\"histogram_numeric(a, 3)\"",
+ "paramIndex" -> "first",
+ "requiredType" -> ("(\"NUMERIC\" or \"DATE\" or \"TIMESTAMP\" or \"TIMESTAMP_NTZ\" " +
+ "or \"INTERVAL YEAR TO MONTH\" or \"INTERVAL DAY TO SECOND\" or \"TIME\")"),
+ "inputSql" -> "\"a\"",
+ "inputType" -> s"\"${dataType.sql}\""))
+ }
+ }
+ }
+
test("class HistogramNumeric, exercise many different numeric input types") {
val inputs = Seq(
(Literal(null),
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CommandUtils.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CommandUtils.scala
index 3eb2b14539ff..b11db861fd75 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CommandUtils.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/command/CommandUtils.scala
@@ -371,6 +371,8 @@ object CommandUtils extends Logging {
case _: IntegralType => true
case _: DecimalType => true
case DoubleType | FloatType => true
+ // percentile_approx rejects nanosecond timestamps, so they get no histogram.
+ case _: AnyTimestampNanoType => false
case _: DatetimeType => true
case _ => false
}
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/ApproximatePercentileQuerySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/ApproximatePercentileQuerySuite.scala
index a7705de0b4b7..1b5b4a59e795 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/ApproximatePercentileQuerySuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/ApproximatePercentileQuerySuite.scala
@@ -596,6 +596,36 @@ class ApproximatePercentileQuerySuite extends SharedSparkSession {
}
}
+ test("SPARK-60337: percentile_approx rejects nanosecond timestamps") {
+ // The sketch holds doubles, which can't represent nanosecond timestamps exactly, so they are
+ // rejected rather than implicitly cast to DATE.
+ for {
+ ansiEnabled <- Seq("true", "false")
+ family <- Seq("TIMESTAMP_NTZ", "TIMESTAMP_LTZ")
+ precision <- 7 to 9
+ (func, percentage) <- Seq(
+ "percentile_approx" -> "0.5", "approx_percentile" -> "array(0.25, 0.75)")
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled) {
+ val call = s"$func(c, $percentage)"
+ val query = s"SELECT $call FROM VALUES " +
+ s"(CAST('2020-01-01 12:00:00.123456789' AS $family($precision))) AS t(c)"
+ checkError(
+ exception = intercept[AnalysisException](sql(query)),
+ condition = "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ parameters = Map(
+ "sqlExpr" -> s"\"$func(c, $percentage, 10000)\"",
+ "paramIndex" -> "first",
+ "requiredType" -> ("(\"NUMERIC\" or \"DATE\" or \"TIMESTAMP\" or \"TIMESTAMP_NTZ\" " +
+ "or \"INTERVAL YEAR TO MONTH\" or \"INTERVAL DAY TO SECOND\" or \"TIME\")"),
+ "inputSql" -> "\"c\"",
+ "inputType" -> s"\"$family($precision)\""),
+ context = ExpectedContext(
+ call, query.indexOf(call), query.indexOf(call) + call.length - 1))
+ }
+ }
+ }
+
test("percentile_approx, multiple records with the minimum value in a partition") {
withTempView(table) {
spark.sparkContext.makeRDD(Seq(1, 1, 2, 1, 1, 3, 1, 1, 4, 1, 1, 5), 4).toDF("col")
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/StatisticsCollectionSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/StatisticsCollectionSuite.scala
index 45b1f7931ea1..42bff6f6c606 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/StatisticsCollectionSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/StatisticsCollectionSuite.scala
@@ -666,6 +666,28 @@ class StatisticsCollectionSuite extends StatisticsCollectionTestBase with Shared
}
}
+ test("SPARK-60337: no histogram for nanosecond timestamp columns") {
+ withSQLConf(SQLConf.HISTOGRAM_ENABLED.key -> "true") {
+ withTempView("nanos") {
+ sql(
+ """CACHE TABLE nanos AS SELECT * FROM VALUES
+ | (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789', CAST(NULL AS TIMESTAMP_LTZ(7)), 1),
+ | (TIMESTAMP_NTZ '2021-06-01 00:00:00.000000001', CAST(NULL AS TIMESTAMP_LTZ(7)), 2)
+ | AS t(ntz, ltz, i)
+ """.stripMargin)
+ sql("ANALYZE TABLE nanos COMPUTE STATISTICS FOR COLUMNS ntz, ltz, i")
+ val stats = spark.table("nanos").queryExecution.optimizedPlan.stats.attributeStats
+ .map { case (attr, stat) => attr.name -> stat }
+ assert(stats.keySet === Set("ntz", "ltz", "i"))
+ assert(stats("ntz").histogram.isEmpty)
+ assert(stats("ntz").nullCount.contains(0))
+ assert(stats("ltz").histogram.isEmpty)
+ assert(stats("ltz").nullCount.contains(2))
+ assert(stats("i").histogram.isDefined)
+ }
+ }
+ }
+
test("analyzes column statistics in cached local temporary view") {
withTempView("tempView") {
// Analyzes in a temporary view
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org