This is an automated email from the ASF dual-hosted git repository.
uros-b pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 8e8f37fd6ad0 [SPARK-60542][SQL] Support nanosecond-precision
timestamps in percentile, median, percentile_cont and percentile_disc
8e8f37fd6ad0 is described below
commit 8e8f37fd6ad0cf655cd101a834ce8202f23f1022
Author: Stevo Mitric <[email protected]>
AuthorDate: Wed Oct 28 08:42:45 2026 +0000
[SPARK-60542][SQL] Support nanosecond-precision timestamps in percentile,
median, percentile_cont and percentile_disc
### What changes were proposed in this pull request?
This PR makes the exact percentile aggregates `percentile`, `median`,
`percentile_cont` and `percentile_disc` accept nanosecond-precision timestamps
(`TIMESTAMP_NTZ(p)` / `TIMESTAMP_LTZ(p)`, p in [7, 9]) and return the input
type, family and precision. It's part of the nanosecond timestamp SPIP
(SPARK-56822).
- `AnyTimestampNanoType` is appended last to the input `TypeCollection`. A
nanos input matches it directly, so no cast is inserted. Because it's last, the
implicit-cast fallback for every other input type picks the same member as
before.
- `percentile_disc` returns an input value, so the result is exact.
- `percentile`, `median` and `percentile_cont` interpolate `lower +
fraction * (higher - lower)` exactly in epoch nanoseconds using
`java.math.BigDecimal`, then floor the result to the input precision (100 ns
for p=7, 10 ns for p=8). A double can't do this: epoch nanoseconds need about
61 bits, and the 0001..9999 span overflows a long. The position `percentage *
(count - 1)` is computed exactly too, because a double can land just below a
whole number (`90 * 0.7` is `62.99999999999999` [...]
- `percentile_cont` over a nanos timestamp is not pushed down to DS V2
sources, which would interpolate with their own rounding. `percentile_disc`
still is, since it returns an input value.
Design decision for reviewers: an interpolated value that falls between two
grid points is floored toward -inf. For example, the median of `.000000001` and
`.000000002` at p=9 is `.000000001`. Flooring matches how nanos values are
already stored at a lower precision, and it keeps the result between the two
neighboring values for both ascending and descending order. The TIME and
interval paths truncate toward zero with `.toLong`. That's the same thing for
TIME, which is never negative, [...]
### Why are the changes needed?
Since SPARK-60335 stopped the implicit narrowing, these functions reject a
nanos timestamp with `DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE` in both ANSI
modes; before that, ANSI silently cast it to `TIME(6)`. The SPIP asks for the
supported functions to work at nanosecond precision, and the exact percentile
family can do so without losing any digits.
### Does this PR introduce _any_ user-facing change?
Yes, for the nanosecond timestamp preview types only
(`spark.sql.timestampNanosTypes.enabled`). These functions now return a nanos
timestamp of the input type instead of a type-mismatch error. The
`UNEXPECTED_INPUT_TYPE` message for other unsupported inputs now also lists the
nanos types. Microsecond timestamps behave as before.
### How was this patch tested?
- `PercentileSuite`: result types, exact interpolation (sub-microsecond,
pre-epoch, the 0001..9999 range, p=7/8 flooring, non-dyadic percentages,
ascending vs descending), `percentile_disc` (current and legacy calculation),
and serialized buffer merge.
- `TimestampNanosFunctionsAnsiOnSuite` / `AnsiOffSuite`: SQL and Column API
results and types for NTZ/LTZ at p=7/8/9 over multiple partitions.
`TypeCoercionSuite` / `AnsiTypeCoercionSuite`: nanos input is not cast and
micro timestamps are never widened to nanos. `DataSourceV2StrategySuite`: no
pushdown of `percentile_cont` over nanos.
- `timestamp-ntz-nanos.sql` / `timestamp-ltz-nanos.sql` golden files,
including window, frequency and ANSI-off cases; the four functions move out of
the SPARK-60335 rejection block.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Isaac
Closes #59781 from stevomitric/stevomitric/nanos-percentile.
Authored-by: Stevo Mitric <[email protected]>
Signed-off-by: Uros Bojanic <[email protected]>
---
python/pyspark/sql/functions/builtin.py | 2 +-
.../expressions/aggregate/percentiles.scala | 113 +++++--
.../sql/catalyst/util/V2ExpressionBuilder.scala | 9 +-
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 21 +-
.../expressions/aggregate/PercentileSuite.scala | 140 ++++++++-
.../analyzer-results/timestamp-ltz-nanos.sql.out | 292 ++++++------------
.../analyzer-results/timestamp-ntz-nanos.sql.out | 316 ++++++++------------
.../sql-tests/inputs/timestamp-ltz-nanos.sql | 55 +++-
.../sql-tests/inputs/timestamp-ntz-nanos.sql | 63 +++-
.../sql-tests/results/timestamp-ltz-nanos.sql.out | 306 ++++++-------------
.../sql-tests/results/timestamp-ntz-nanos.sql.out | 329 ++++++++-------------
.../sql/TimestampNanosFunctionsSuiteBase.scala | 76 +++++
.../datasources/v2/DataSourceV2StrategySuite.scala | 15 +-
13 files changed, 851 insertions(+), 886 deletions(-)
diff --git a/python/pyspark/sql/functions/builtin.py
b/python/pyspark/sql/functions/builtin.py
index 0ec168874b55..d4941b890bdb 100644
--- a/python/pyspark/sql/functions/builtin.py
+++ b/python/pyspark/sql/functions/builtin.py
@@ -1871,7 +1871,7 @@ def median(col: "ColumnOrName") -> Column:
----------
col : :class:`~pyspark.sql.Column` or column name
target column to compute on.
- A column that evaluates to a numeric, interval, or time.
+ A column that evaluates to a numeric, interval, time, or
nanosecond-precision timestamp.
Returns
-------
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala
index 4427dc25097e..56ca77d64d4d 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/aggregate/percentiles.scala
@@ -17,6 +17,7 @@
package org.apache.spark.sql.catalyst.expressions.aggregate
+import java.math.{BigDecimal => JBigDecimal, RoundingMode}
import java.util
import org.apache.spark.sql.catalyst.InternalRow
@@ -31,6 +32,7 @@ import org.apache.spark.sql.errors.{QueryCompilationErrors,
QueryExecutionErrors
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.types._
import org.apache.spark.sql.types.TypeCollection.NumericAndAnsiInterval
+import org.apache.spark.unsafe.types.TimestampNanosVal
import org.apache.spark.util.ArrayImplicits._
import org.apache.spark.util.collection.OpenHashMap
@@ -58,6 +60,10 @@ abstract class PercentileBase
case arrayData: ArrayData => arrayData.toDoubleArray()
}
+ // Nanosecond timestamps are interpolated exactly, at an exact position.
+ @transient
+ private lazy val isNanos = child.dataType.isInstanceOf[AnyTimestampNanoType]
+
// Returns null for empty inputs
override def nullable: Boolean = true
@@ -66,6 +72,7 @@ abstract class PercentileBase
case _: YearMonthIntervalType => YearMonthIntervalType()
case _: DayTimeIntervalType => DayTimeIntervalType()
case t: TimeType => t
+ case t: AnyTimestampNanoType => t
case _ => DoubleType
}
if (returnPercentileArray) ArrayType(resultType, false) else resultType
@@ -76,7 +83,11 @@ abstract class PercentileBase
case _: ArrayType => ArrayType(DoubleType, false)
case _ => DoubleType
}
- Seq(TypeCollection(NumericAndAnsiInterval, AnyTimeType),
percentageExpType, IntegralType)
+ // AnyTimestampNanoType goes last, so the implicit cast fallback for other
inputs is unchanged.
+ Seq(
+ TypeCollection(NumericAndAnsiInterval, AnyTimeType,
AnyTimestampNanoType),
+ percentageExpType,
+ IntegralType)
}
// Check the inputTypes are valid, and the percentageExpression satisfies:
@@ -121,6 +132,9 @@ abstract class PercentileBase
case n: Number => n.doubleValue
}
+ // Nanosecond timestamps are kept as is, so that no sub-microsecond digit is
lost.
+ protected def toPercentileValue(key: AnyRef): Any = if (isNanos) key else
toDoubleValue(key)
+
override def update(
buffer: OpenHashMap[AnyRef, Long],
input: InternalRow): OpenHashMap[AnyRef, Long] = {
@@ -154,7 +168,7 @@ abstract class PercentileBase
generateOutput(getPercentiles(buffer))
}
- private def getPercentiles(buffer: OpenHashMap[AnyRef, Long]): Seq[Double] =
{
+ private def getPercentiles(buffer: OpenHashMap[AnyRef, Long]): Seq[Any] = {
if (buffer.isEmpty) {
return Seq.empty
}
@@ -172,11 +186,11 @@ abstract class PercentileBase
percentages.map(getPercentile(accumulatedCounts, _)).toImmutableArraySeq
}
- private def generateOutput(percentiles: Seq[Double]): Any = {
+ private def generateOutput(percentiles: Seq[Any]): Any = {
val results = child.dataType match {
- case _: YearMonthIntervalType => percentiles.map(_.toInt)
- case _: DayTimeIntervalType => percentiles.map(_.toLong)
- case _: TimeType => percentiles.map(_.toLong)
+ case _: YearMonthIntervalType =>
percentiles.map(_.asInstanceOf[Double].toInt)
+ case _: DayTimeIntervalType =>
percentiles.map(_.asInstanceOf[Double].toLong)
+ case _: TimeType => percentiles.map(_.asInstanceOf[Double].toLong)
case _ => percentiles
}
if (percentiles.isEmpty) {
@@ -195,12 +209,21 @@ abstract class PercentileBase
*/
protected def getPercentile(
accumulatedCounts: Seq[(AnyRef, Long)],
- percentile: Double): Double = {
+ percentile: Double): Any = {
val position = (accumulatedCounts.last._2 - 1) * percentile
+ // A double position can land just below a whole number (e.g. 90 * 0.7),
which flooring a
+ // nanosecond result would turn into a whole step. A NaN percentage keeps
the double position
+ // (the first value), as for other types.
+ lazy val exactPosition = JBigDecimal.valueOf(accumulatedCounts.last._2 - 1)
+ .multiply(JBigDecimal.valueOf(percentile))
// We may need to do linear interpolation to get the exact percentile
- val lower = position.floor.toLong
- val higher = position.ceil.toLong
+ val (lower, higher) = if (isNanos && !percentile.isNaN) {
+ (exactPosition.setScale(0, RoundingMode.FLOOR).longValueExact(),
+ exactPosition.setScale(0, RoundingMode.CEILING).longValueExact())
+ } else {
+ (position.floor.toLong, position.ceil.toLong)
+ }
// Use binary search to find the lower and the higher position.
val countsArray = accumulatedCounts.map(_._2).toArray[Long]
@@ -210,18 +233,22 @@ abstract class PercentileBase
val lowerKey = accumulatedCounts(lowerIndex)._1
if (higher == lower) {
// no interpolation needed because position does not have a fraction
- return toDoubleValue(lowerKey)
+ return toPercentileValue(lowerKey)
}
val higherKey = accumulatedCounts(higherIndex)._1
if (higherKey == lowerKey) {
// no interpolation needed because lower position and higher position
has the same key
- return toDoubleValue(lowerKey)
+ return toPercentileValue(lowerKey)
}
if (discrete) {
// We end up here only if spark.sql.legacy.percentileDiscCalculation=true
- toDoubleValue(lowerKey)
+ toPercentileValue(lowerKey)
+ } else if (isNanos) {
+ interpolateNanos(lowerKey.asInstanceOf[TimestampNanosVal],
+ higherKey.asInstanceOf[TimestampNanosVal],
+ exactPosition.subtract(JBigDecimal.valueOf(lower)))
} else {
// Linear interpolation to get the exact percentile. Computed as lower +
fraction *
// (higher - lower) to stay monotonic; the delta can overflow to
Infinity only for adjacent
@@ -232,6 +259,28 @@ abstract class PercentileBase
}
}
+ // Exact `lower + fraction * (higher - lower)` in epoch nanoseconds (a
double can't hold them),
+ // floored to the input precision.
+ private def interpolateNanos(
+ lower: TimestampNanosVal,
+ higher: TimestampNanosVal,
+ fraction: JBigDecimal): TimestampNanosVal = {
+ import PercentileBase.NANOS_PER_MICRO
+ def toEpochNanos(v: TimestampNanosVal): JBigDecimal =
+ JBigDecimal.valueOf(v.epochMicros).multiply(NANOS_PER_MICRO).add(
+ JBigDecimal.valueOf(v.nanosWithinMicro.toLong))
+ val lowerNanos = toEpochNanos(lower)
+ val nanos = toEpochNanos(higher).subtract(lowerNanos)
+ .multiply(fraction)
+ .add(lowerNanos)
+ .setScale(0, RoundingMode.FLOOR)
+ val micros = nanos.divide(NANOS_PER_MICRO, 0, RoundingMode.FLOOR)
+ val nanosWithinMicro =
nanos.subtract(micros.multiply(NANOS_PER_MICRO)).intValueExact()
+ DateTimeUtils.truncateTimestampNanosToPrecision(
+ TimestampNanosVal.fromParts(micros.longValueExact(),
nanosWithinMicro.toShort),
+ TimestampFamily.fractionalPrecision(child.dataType).get)
+ }
+
/**
* use a binary search to find the index of the position closest to the
current value.
*/
@@ -244,6 +293,10 @@ abstract class PercentileBase
}
}
+object PercentileBase {
+ private val NANOS_PER_MICRO =
JBigDecimal.valueOf(DateTimeConstants.NANOS_PER_MICROS)
+}
+
/**
* The Percentile aggregate function returns the exact percentile(s) of
numeric column `expr` at
* the given percentage(s) with value range in [0.0, 1.0].
@@ -262,8 +315,9 @@ abstract class PercentileBase
usage =
"""
_FUNC_(col, percentage [, frequency]) - Returns the exact percentile
value of numeric,
- ANSI interval or TIME column `col` at the given percentage. The value
of percentage must be
- between 0.0 and 1.0. The value of frequency should be positive integral
+ ANSI interval, TIME or nanosecond-precision timestamp column `col` at
the given percentage.
+ The value of percentage must be between 0.0 and 1.0. The value of
frequency should be
+ positive integral
_FUNC_(col, array(percentage1 [, percentage2]...) [, frequency]) -
Returns the exact
percentile value array of numeric column `col` at the given
percentage(s). Each value
@@ -274,7 +328,8 @@ abstract class PercentileBase
arguments = """
Arguments:
* col - The column to compute the percentile of.
- An expression that evaluates to a numeric, ANSI interval, or time.
+ An expression that evaluates to a numeric, ANSI interval, time, or
nanosecond-precision
+ timestamp (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]).
* percentage - The percentile(s) to compute, each between 0.0 and 1.0.
Either a
single numeric value or an array of numeric values. Must be foldable.
* frequency - Optional. The number of times each value should be counted.
@@ -343,11 +398,13 @@ case class Percentile(
}
@ExpressionDescription(
- usage = "_FUNC_(col) - Returns the median of numeric, ANSI interval or TIME
column `col`.",
+ usage = "_FUNC_(col) - Returns the median of numeric, ANSI interval, TIME or
" +
+ "nanosecond-precision timestamp column `col`.",
arguments = """
Arguments:
* col - The column to compute the median of.
- An expression that evaluates to a numeric, interval, or time.
+ An expression that evaluates to a numeric, interval, time, or
nanosecond-precision
+ timestamp (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]).
""",
examples = """
Examples:
@@ -372,8 +429,8 @@ case class Median(child: Expression)
}
/**
- * Return a percentile value based on a continuous distribution of
- * numeric, ANSI interval or TIME column at the given percentage (specified in
ORDER BY clause).
+ * Return a percentile value based on a continuous distribution of numeric,
ANSI interval, TIME or
+ * nanosecond-precision timestamp column at the given percentage (specified in
ORDER BY clause).
* The value of percentage must be between 0.0 and 1.0.
*/
case class PercentileCont(left: Expression, right: Expression, reverse:
Boolean = false)
@@ -473,7 +530,7 @@ case class PercentileDisc(
override protected def getPercentile(
accumulatedCounts: Seq[(AnyRef, Long)],
- percentile: Double): Double = {
+ percentile: Double): Any = {
if (legacyCalculation) {
super.getPercentile(accumulatedCounts, percentile)
} else {
@@ -488,7 +545,7 @@ case class PercentileDisc(
val higherIndex = binarySearchCount(countsArray, 0,
accumulatedCounts.size, higher)
val higherKey = accumulatedCounts(higherIndex)._1
- toDoubleValue(higherKey)
+ toPercentileValue(higherKey)
}
}
@@ -500,13 +557,14 @@ case class PercentileDisc(
// scalastyle:off line.size.limit
@ExpressionDescription(
usage = "_FUNC_(percentage) WITHIN GROUP (ORDER BY col) - Return a
percentile value based on " +
- "a continuous distribution of numeric, ANSI interval or TIME column `col`
at the given " +
- "`percentage` (specified in ORDER BY clause).",
+ "a continuous distribution of numeric, ANSI interval, TIME or
nanosecond-precision timestamp " +
+ "column `col` at the given `percentage` (specified in ORDER BY clause).",
arguments = """
Arguments:
* percentage - The percentile to compute, between 0.0 and 1.0. Must be
foldable.
* col - The column to compute the percentile of, specified in the ORDER
BY clause.
- An expression that evaluates to a numeric, ANSI interval, or time.
+ An expression that evaluates to a numeric, ANSI interval, time, or
nanosecond-precision
+ timestamp (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]).
""",
examples = """
Examples:
@@ -532,13 +590,14 @@ object PercentileContBuilder extends ExpressionBuilder {
// scalastyle:off line.size.limit
@ExpressionDescription(
usage = "_FUNC_(percentage) WITHIN GROUP (ORDER BY col) - Return a
percentile value based on " +
- "a discrete distribution of numeric, ANSI interval or TIME column `col` at
the given " +
- "`percentage` (specified in ORDER BY clause).",
+ "a discrete distribution of numeric, ANSI interval, TIME or
nanosecond-precision timestamp " +
+ "column `col` at the given `percentage` (specified in ORDER BY clause).",
arguments = """
Arguments:
* percentage - The percentile to compute, between 0.0 and 1.0. Must be
foldable.
* col - The column to compute the percentile of, specified in the ORDER
BY clause.
- An expression that evaluates to a numeric, ANSI interval, or time.
+ An expression that evaluates to a numeric, ANSI interval, time, or
nanosecond-precision
+ timestamp (TIMESTAMP_LTZ(p) / TIMESTAMP_NTZ(p), p in [7, 9]).
""",
examples = """
Examples:
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/V2ExpressionBuilder.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/V2ExpressionBuilder.scala
index 4fa4a327c792..db9f1b66c5da 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/V2ExpressionBuilder.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/V2ExpressionBuilder.scala
@@ -29,7 +29,8 @@ import org.apache.spark.sql.connector.expressions.{Cast =>
V2Cast, Expression =>
import org.apache.spark.sql.connector.expressions.aggregate.{AggregateFunc,
Avg, Count, CountStar, GeneralAggregateFunc, Max, Min, Sum,
UserDefinedAggregateFunc}
import org.apache.spark.sql.connector.expressions.filter.{AlwaysFalse,
AlwaysTrue, And => V2And, Not => V2Not, Or => V2Or, Predicate => V2Predicate}
import org.apache.spark.sql.internal.SQLConf
-import org.apache.spark.sql.types.{BooleanType, DataType, IntegerType,
StringType}
+import org.apache.spark.sql.types.{AnyTimestampNanoType, BooleanType,
DataType, IntegerType,
+ StringType}
/**
* The builder to generate V2 expressions from catalyst expressions.
@@ -431,8 +432,10 @@ class V2ExpressionBuilder(e: Expression, isPredicate:
Boolean = false) extends L
case aggregate.Mode(PushableExpression(expr), _, _, Some(reverse)) =>
Some(new GeneralAggregateFunc(
"MODE", isDistinct, Array.empty, Array(generateSortValue(expr,
!reverse))))
- case aggregate.Percentile(
- PushableExpression(left), PushableExpression(right), LongLiteral(1L), _,
_, reverse) =>
+ // A source would interpolate a nanosecond timestamp with its own
rounding, not Spark's floor.
+ case p @ aggregate.Percentile(
+ PushableExpression(left), PushableExpression(right), LongLiteral(1L), _,
_, reverse)
+ if !p.child.dataType.isInstanceOf[AnyTimestampNanoType] =>
Some(new GeneralAggregateFunc("PERCENTILE_CONT", isDistinct,
Array(right), Array(generateSortValue(left, reverse))))
case aggregate.PercentileDisc(
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
index fb9cc87257e6..d2d0823ccea8 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
@@ -25,7 +25,8 @@ import org.apache.spark.sql.catalyst.analysis.TypeCoercion._
import org.apache.spark.sql.catalyst.dsl.expressions._
import org.apache.spark.sql.catalyst.dsl.plans._
import org.apache.spark.sql.catalyst.expressions._
-import
org.apache.spark.sql.catalyst.expressions.aggregate.ApproximatePercentile
+import
org.apache.spark.sql.catalyst.expressions.aggregate.{ApproximatePercentile,
Median,
+ Percentile, PercentileCont, PercentileDisc}
import org.apache.spark.sql.catalyst.plans.ReferenceAllColumns
import org.apache.spark.sql.catalyst.plans.logical._
import org.apache.spark.sql.catalyst.rules.{Rule, RuleExecutor}
@@ -427,6 +428,24 @@ abstract class TypeCoercionSuiteBase extends AnalysisTest {
ruleTest(implicitTypeCastsRule, LastDay(nanos), LastDay(Cast(nanos,
DateType)))
}
+ test("SPARK-60542: implicit type cast - percentile family over nanos and
micro timestamps") {
+ val nanos = Literal.default(TimestampNTZNanosType(9))
+ Seq(
+ new Percentile(nanos, Literal(0.5)),
+ Median(nanos),
+ PercentileCont(nanos, Literal(0.5)),
+ PercentileDisc(nanos, Literal(0.5))).foreach { e =>
+ ruleTest(implicitTypeCastsRule, e, e)
+ }
+ // The nanos member is last in the input TypeCollection, so a micro
timestamp keeps its
+ // implicit cast and is never widened to nanos.
+ val inputType = new Percentile(nanos, Literal(0.5)).inputTypes.head
+ Seq(TimestampType, TimestampNTZType).foreach { micro =>
+ assert(!implicitCast(Literal.default(micro), inputType)
+ .exists(_.dataType.isInstanceOf[AnyTimestampNanoType]))
+ }
+ }
+
test("type coercion for Concat") {
val rule = TypeCoercion.ConcatCoercion
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/PercentileSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/PercentileSuite.scala
index 92b036cbf94d..ccf62c9484a9 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/PercentileSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/aggregate/PercentileSuite.scala
@@ -28,6 +28,7 @@ import org.apache.spark.sql.catalyst.expressions.Cast._
import org.apache.spark.sql.catalyst.plans.logical.LocalRelation
import org.apache.spark.sql.catalyst.util.ArrayData
import org.apache.spark.sql.types._
+import org.apache.spark.unsafe.types.TimestampNanosVal
import org.apache.spark.util.collection.OpenHashMap
class PercentileSuite extends SparkFunSuite {
@@ -128,6 +129,139 @@ class PercentileSuite extends SparkFunSuite {
assert(agg.eval(buffer) === 5L)
}
+ private def nanosTs(epochMicros: Long, nanosWithinMicro: Int):
TimestampNanosVal =
+ TimestampNanosVal.fromParts(epochMicros, nanosWithinMicro.toShort)
+
+ private def evalNanos(agg: PercentileBase, values: Seq[TimestampNanosVal]):
Any = {
+ val buffer = agg.createAggregationBuffer()
+ values.foreach(v => agg.update(buffer, InternalRow(v)))
+ agg.eval(buffer)
+ }
+
+ test("SPARK-60542: percentile family over nanosecond timestamps keeps the
input type") {
+ Seq(7, 8, 9).flatMap(p => Seq(TimestampNTZNanosType(p),
TimestampLTZNanosType(p))).foreach {
+ dt =>
+ val child = BoundReference(0, dt, nullable = true)
+ Seq(new Percentile(child, Literal(0.5)), PercentileDisc(child,
Literal(0.5))).foreach {
+ agg =>
+ assert(agg.checkInputDataTypes() === TypeCheckSuccess)
+ assert(agg.dataType === dt)
+ }
+ val percentages = CreateArray(Seq(Literal(0.25), Literal(0.75)))
+ assert(new Percentile(child, percentages).dataType === ArrayType(dt,
false))
+ assert(Median(child).dataType === dt)
+ assert(PercentileCont(child, Literal(0.5)).dataType === dt)
+ }
+ }
+
+ test("SPARK-60542: percentile over nanosecond timestamps interpolates
exactly") {
+ // 2020-01-01 12:00:00.123456 UTC.
+ val micros = 1577880000123456L
+ val p9 = BoundReference(0, TimestampNTZNanosType(9), nullable = true)
+ // Both inputs map to the same double, so a double interpolation can't
return .123456790.
+ assert(evalNanos(new Percentile(p9, Literal(0.5)),
+ Seq(nanosTs(micros, 789), nanosTs(micros, 791))) === nanosTs(micros,
790))
+ // The exact midpoint .1234567895 is floored to the precision.
+ assert(evalNanos(new Percentile(p9, Literal(0.5)),
+ Seq(nanosTs(micros, 789), nanosTs(micros, 790))) === nanosTs(micros,
789))
+ // The result carries into the next microsecond.
+ assert(evalNanos(new Percentile(p9, Literal(0.5)),
+ Seq(nanosTs(micros, 999), nanosTs(micros + 1, 1))) === nanosTs(micros +
1, 0))
+ // Pre-epoch: the midpoint of -1000ns and 1ns is -499.5ns, floored to
-500ns.
+ assert(evalNanos(new Percentile(p9, Literal(0.5)),
+ Seq(nanosTs(-1, 0), nanosTs(0, 1))) === nanosTs(-1, 500))
+ // 0001-01-01 00:00:00 and 9999-12-31 23:59:59.999999999: the span
overflows a long in nanos.
+ assert(evalNanos(new Percentile(p9, Literal(0.5)),
+ Seq(nanosTs(-62135596800000000L, 0), nanosTs(253402300799999999L, 999)))
===
+ nanosTs(95633351999999999L, 999))
+ // Single value, p = 0 and p = 1, and an array of percentages.
+ val values = Seq(nanosTs(micros, 100), nanosTs(micros, 200),
nanosTs(micros, 900))
+ assert(evalNanos(new Percentile(p9, Literal(0.5)), values.take(1)) ===
values.head)
+ assert(evalNanos(new Percentile(p9, Literal(0.0)), values) === values.head)
+ assert(evalNanos(new Percentile(p9, Literal(1.0)), values) === values.last)
+ val percentages = CreateArray(Seq(Literal(0.25), Literal(0.75)))
+ assert(evalNanos(new Percentile(p9, percentages),
values).asInstanceOf[ArrayData]
+ .toSeq[TimestampNanosVal](TimestampNTZNanosType(9)) ===
+ Seq(nanosTs(micros, 150), nanosTs(micros, 550)))
+ // Descending order (percentile_cont ... ORDER BY c DESC) mirrors the
ascending result.
+ val twoValues = Seq(nanosTs(0, 0), nanosTs(0, 3))
+ assert(evalNanos(new Percentile(p9, Literal(0.25), reverse = true),
twoValues) ===
+ nanosTs(0, 2))
+ assert(evalNanos(new Percentile(p9, Literal(0.75)), twoValues) ===
nanosTs(0, 2))
+
+ // Results are floored to the 100ns (p = 7) and 10ns (p = 8) grids.
+ val p7 = BoundReference(0, TimestampLTZNanosType(7), nullable = true)
+ assert(evalNanos(new Percentile(p7, Literal(0.5)),
+ Seq(nanosTs(micros, 100), nanosTs(micros, 400))) === nanosTs(micros,
200))
+ assert(evalNanos(new Percentile(p7, Literal(0.5)),
+ Seq(nanosTs(-1, 0), nanosTs(0, 100))) === nanosTs(-1, 500))
+ val p8 = BoundReference(0, TimestampNTZNanosType(8), nullable = true)
+ assert(evalNanos(new Percentile(p8, Literal(0.5)),
+ Seq(nanosTs(micros, 10), nanosTs(micros, 40))) === nanosTs(micros, 20))
+ }
+
+ test("SPARK-60542: percentile over nanosecond timestamps uses an exact
position") {
+ val p9 = BoundReference(0, TimestampNTZNanosType(9), nullable = true)
+ // 90 * 0.7 is 62.99999999999999 as a double, but the exact position is
the 63rd input value.
+ val spaced = (0 to 90).map(nanosTs(0, _))
+ assert(evalNanos(new Percentile(p9, Literal(0.7)), spaced) === spaced(63))
+ assert(evalNanos(new Percentile(p9, Literal(0.3)), Seq(nanosTs(0, 0),
nanosTs(0, 10))) ===
+ nanosTs(0, 3))
+ // 3 * 0.7 is 2.0999999999999996 as a double; ascending 0.7 and descending
0.3 agree.
+ val values = Seq(nanosTs(0, 100), nanosTs(0, 200), nanosTs(0, 300),
nanosTs(1, 300))
+ val p7 = BoundReference(0, TimestampLTZNanosType(7), nullable = true)
+ Seq(p9, p7).foreach { child =>
+ assert(evalNanos(new Percentile(child, Literal(0.7)), values) ===
nanosTs(0, 400))
+ assert(evalNanos(new Percentile(child, Literal(0.3), reverse = true),
values) ===
+ nanosTs(0, 400))
+ }
+ // A tenth of a one-year gap is a whole number of nanoseconds.
+ val year = 365L * 24 * 60 * 60 * 1000 * 1000
+ assert(evalNanos(new Percentile(p9, Literal(0.7)),
+ Seq(nanosTs(0, 0), nanosTs(0, 1), nanosTs(0, 2), nanosTs(year, 2))) ===
+ nanosTs(year / 10, 2))
+ }
+
+ test("SPARK-60542: NaN percentage over nanosecond timestamps matches other
types") {
+ // NaN passes the range check; as for other types, the first value in sort
order is returned.
+ val child = BoundReference(0, TimestampNTZNanosType(9), nullable = true)
+ val values = Seq(nanosTs(0, 100), nanosTs(0, 200), nanosTs(0, 300))
+ val nan = Literal(Double.NaN)
+ assert(evalNanos(new Percentile(child, nan), values) === values.head)
+ assert(evalNanos(new Percentile(child, nan, reverse = true), values) ===
values.last)
+ assert(evalNanos(new Percentile(child, CreateArray(Seq(Literal(0.5),
nan))), values)
+
.asInstanceOf[ArrayData].toSeq[TimestampNanosVal](TimestampNTZNanosType(9)) ===
+ Seq(values(1), values.head))
+ assert(evalNanos(PercentileDisc(child, nan), values) === values.head)
+ assert(evalNanos(PercentileDisc(child, nan, legacyCalculation = true),
values) === values.head)
+ val doubles = new Percentile(BoundReference(0, DoubleType, nullable =
true), nan)
+ val buffer = doubles.createAggregationBuffer()
+ Seq(1.0, 2.0, 3.0).foreach(v => doubles.update(buffer, InternalRow(v)))
+ assert(doubles.eval(buffer) === 1.0)
+ }
+
+ test("SPARK-60542: percentile_disc over nanosecond timestamps returns an
input value") {
+ val child = BoundReference(0, TimestampNTZNanosType(9), nullable = true)
+ val values = (1 to 4).map(nanosTs(1577880000123456L, _))
+ assert(evalNanos(PercentileDisc(child, Literal(0.6)), values) ===
values(2))
+ assert(evalNanos(PercentileDisc(child, Literal(0.6), legacyCalculation =
true), values) ===
+ values(1))
+ assert(evalNanos(PercentileDisc(child, Literal(0.6), reverse = true),
values) === values(1))
+ }
+
+ test("SPARK-60542: percentile over nanosecond timestamps merges serialized
buffers") {
+ val child = BoundReference(0, TimestampLTZNanosType(9), nullable = true)
+ val agg = new Percentile(child, Literal(0.5))
+ val buffer1 = agg.createAggregationBuffer()
+ Seq(nanosTs(-1, 999), null).foreach(v => agg.update(buffer1,
InternalRow(v)))
+ val buffer2 = agg.createAggregationBuffer()
+ agg.update(buffer2, InternalRow(nanosTs(0, 3)))
+ val merged = agg.merge(
+ agg.deserialize(agg.serialize(buffer1)),
agg.deserialize(agg.serialize(buffer2)))
+ assert(agg.eval(merged) === nanosTs(0, 1))
+ assert(agg.eval(agg.createAggregationBuffer()) === null)
+ }
+
test("class Percentile, low level interface, update, merge, eval...") {
val childExpression = Cast(BoundReference(0, IntegerType, nullable =
true), DoubleType)
val inputAggregationBufferOffset = 1
@@ -191,7 +325,8 @@ class PercentileSuite extends SparkFunSuite {
messageParameters = Map(
"paramIndex" -> ordinalNumber(0),
"requiredType" -> ("((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" " +
- "or \"INTERVAL YEAR TO MONTH\") or \"TIME\")"),
+ "or \"INTERVAL YEAR TO MONTH\") or \"TIME\" " +
+ "or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7,
9])\")"),
"inputSql" -> "\"a\"",
"inputType" -> toSQLType(dataType)
)
@@ -214,7 +349,8 @@ class PercentileSuite extends SparkFunSuite {
messageParameters = Map(
"paramIndex" -> ordinalNumber(0),
"requiredType" -> ("((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" " +
- "or \"INTERVAL YEAR TO MONTH\") or \"TIME\")"),
+ "or \"INTERVAL YEAR TO MONTH\") or \"TIME\" " +
+ "or \"(TIMESTAMP_LTZ(P) OR TIMESTAMP_NTZ(P) WITH P IN [7,
9])\")"),
"inputSql" -> "\"a\"",
"inputType" -> toSQLType(dataType)
)
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 0a02eadcc7fe..f39e0eefd15f 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
@@ -2145,106 +2145,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
- } ]
-}
-
-
--- !query
-SELECT median(c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
-- !query
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
@@ -2461,7 +2361,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
-SELECT percentile(c, 0.5)
+SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
@@ -2471,22 +2371,22 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_trunc(HOUR, c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
+ "stopIndex" : 28,
+ "fragment" : "time_trunc('HOUR', c)"
} ]
}
-- !query
-SELECT median(c)
+SELECT time_diff('SECOND', TIME '12:00:00', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
@@ -2496,125 +2396,121 @@
org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
+ "paramIndex" : "third",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
+ "stopIndex" : 46,
+ "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
} ]
}
-- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
+SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+Project [last_day(cast(c#x as date)) AS last_day(c)#x, next_day(cast(c#x as
date), MO, false) AS next_day(c, MO)#x, datediff(cast(c#x as date),
cast(2019-12-31 as date)) AS datediff(c, 2019-12-31)#x, add_months(cast(c#x as
date), 1) AS add_months(c, 1)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SET spark.sql.ansi.enabled=true
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+SetCommand (spark.sql.ansi.enabled,Some(true))
-- !query
-SELECT time_trunc('HOUR', c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "second",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_trunc(HOUR, c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 28,
- "fragment" : "time_trunc('HOUR', c)"
- } ]
-}
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(0.25 as double),
1, 0, 0, false) AS percentile(c, 0.25, 1)#x, percentile_cont(c#x, cast(0.75 as
double), false) AS percentile_cont(0.75) WITHIN GROUP (ORDER BY c)#x,
percentile_cont(c#x, cast(0.25 as double), true) AS percentile_cont(0.25)
WITHIN GROUP (ORDER BY c DESC)#x, percentile_disc(c#x, cast(0.5 as double),
false, 0, 0, false) AS percentile_disc(0.5) WITHIN GROUP (ORDER BY c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT time_diff('SECOND', TIME '12:00:00', c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "third",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 46,
- "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
- } ]
-}
+Aggregate [typeof(median(c#x)) AS typeof(median(c))#x, typeof(percentile(c#x,
cast(array(0.25, 0.75) as array<double>), 1, 0, 0, false)) AS
typeof(percentile(c, array(0.25, 0.75), 1))#x, typeof(percentile_disc(c#x,
cast(0.5 as double), false, 0, 0, false)) AS typeof(percentile_disc(0.5) WITHIN
GROUP (ORDER BY c))#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789
UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456791
UTC') AS t(c)
-- !query analysis
-Project [last_day(cast(c#x as date)) AS last_day(c)#x, next_day(cast(c#x as
date), MO, false) AS next_day(c, MO)#x, datediff(cast(c#x as date),
cast(2019-12-31 as date)) AS datediff(c, 2019-12-31)#x, add_months(cast(c#x as
date), 1) AS add_months(c, 1)#x]
+Aggregate [median(c#x) AS median(c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '1969-12-31 23:59:59.999999000
UTC'),
+ (TIMESTAMP_LTZ '1970-01-01 00:00:00.000000001
UTC') AS t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000200 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000300 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001300 UTC') AS t(c)
+-- !query analysis
+Aggregate [percentile(c#x, cast(0.7 as double), 1, 0, 0, false) AS
percentile(c, 0.7, 1)#x, percentile_cont(c#x, cast(0.3 as double), true) AS
percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))),
+ (CAST('2020-01-01 12:00:00.0000004 UTC' AS timestamp_ltz(7))) AS
t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(array(cast(0.0 as
decimal(2,1)), 1.0) as array<double>), 1, 0, 0, false) AS percentile(c,
array(0.0, 1.0), 1)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(0.25 as double),
1, 0, 0, false) AS percentile(c, 0.25, 1)#x, percentile_cont(c#x, cast(0.75 as
double), false) AS percentile_cont(0.75) WITHIN GROUP (ORDER BY c)#x,
percentile_disc(c#x, cast(0.5 as double), false, 0, 0, false) AS
percentile_disc(0.5) WITHIN GROUP (ORDER BY c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c)
+-- !query analysis
+Aggregate [typeof(median(c#x)) AS typeof(median(c))#x]
+- SubqueryAlias t
+- LocalRelation [c#x]
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 deb4b44486be..6d937615ceb0 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
@@ -2235,106 +2235,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
- } ]
-}
-
-
--- !query
-SELECT median(c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
-- !query
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
@@ -2551,7 +2451,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
-SELECT percentile(c, 0.5)
+SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
@@ -2561,22 +2461,22 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_trunc(HOUR, c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
+ "stopIndex" : 28,
+ "fragment" : "time_trunc('HOUR', c)"
} ]
}
-- !query
-SELECT median(c)
+SELECT time_diff('SECOND', TIME '12:00:00', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
@@ -2586,125 +2486,145 @@
org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
+ "paramIndex" : "third",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
+ "stopIndex" : 46,
+ "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
} ]
}
-- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
+SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+Project [last_day(cast(c#x as date)) AS last_day(c)#x, next_day(cast(c#x as
date), MO, false) AS next_day(c, MO)#x, datediff(cast(c#x as date),
cast(2019-12-31 as date)) AS datediff(c, 2019-12-31)#x, add_months(cast(c#x as
date), 1) AS add_months(c, 1)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SET spark.sql.ansi.enabled=true
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+SetCommand (spark.sql.ansi.enabled,Some(true))
-- !query
-SELECT time_trunc('HOUR', c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "second",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_trunc(HOUR, c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 28,
- "fragment" : "time_trunc('HOUR', c)"
- } ]
-}
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(0.25 as double),
1, 0, 0, false) AS percentile(c, 0.25, 1)#x, percentile_cont(c#x, cast(0.75 as
double), false) AS percentile_cont(0.75) WITHIN GROUP (ORDER BY c)#x,
percentile_cont(c#x, cast(0.25 as double), true) AS percentile_cont(0.25)
WITHIN GROUP (ORDER BY c DESC)#x, percentile_disc(c#x, cast(0.5 as double),
false, 0, 0, false) AS percentile_disc(0.5) WITHIN GROUP (ORDER BY c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT time_diff('SECOND', TIME '12:00:00', c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS t(c)
-- !query analysis
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "third",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 46,
- "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
- } ]
-}
+Aggregate [typeof(median(c#x)) AS typeof(median(c))#x, typeof(percentile(c#x,
cast(array(0.25, 0.75) as array<double>), 1, 0, 0, false)) AS
typeof(percentile(c, array(0.25, 0.75), 1))#x, typeof(percentile_disc(c#x,
cast(0.5 as double), false, 0, 0, false)) AS typeof(percentile_disc(0.5) WITHIN
GROUP (ORDER BY c))#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
-- !query
-SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456791')
AS t(c)
-- !query analysis
-Project [last_day(cast(c#x as date)) AS last_day(c)#x, next_day(cast(c#x as
date), MO, false) AS next_day(c, MO)#x, datediff(cast(c#x as date),
cast(2019-12-31 as date)) AS datediff(c, 2019-12-31)#x, add_months(cast(c#x as
date), 1) AS add_months(c, 1)#x]
+Aggregate [median(c#x) AS median(c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '1969-12-31 23:59:59.999999000'),
+ (TIMESTAMP_NTZ '1970-01-01 00:00:00.000000001')
AS t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000200'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000300'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001300') AS t(c)
+-- !query analysis
+Aggregate [percentile(c#x, cast(0.7 as double), 1, 0, 0, false) AS
percentile(c, 0.7, 1)#x, percentile_cont(c#x, cast(0.3 as double), true) AS
percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))),
+ (CAST('2020-01-01 12:00:00.0000004' AS timestamp_ntz(7))) AS t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(array(cast(0.0 as
decimal(2,1)), 1.0) as array<double>), 1, 0, 0, false) AS percentile(c,
array(0.0, 1.0), 1)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT k, median(c) OVER (PARTITION BY k)
+ FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001'),
+ (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000003'),
+ (2, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000005') AS t(k, c)
+-- !query analysis
+Project [k#x, median(c) OVER (PARTITION BY k ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)#x]
++- Project [k#x, c#x, median(c) OVER (PARTITION BY k ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING)#x, median(c) OVER (PARTITION BY k ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)#x]
+ +- Window [median(c#x) windowspecdefinition(k#x,
specifiedwindowframe(RowFrame, unboundedpreceding$(), unboundedfollowing$()))
AS median(c) OVER (PARTITION BY k ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)#x], [k#x]
+ +- Project [k#x, c#x]
+ +- SubqueryAlias t
+ +- LocalRelation [k#x, c#x]
+
+
+-- !query
+SELECT percentile(c, 0.5, f)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001', 3),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000009', 1) AS t(c, f)
+-- !query analysis
+Aggregate [percentile(c#x, cast(0.5 as double), f#x, 0, 0, false) AS
percentile(c, 0.5, f)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x, f#x]
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query analysis
+SetCommand (spark.sql.ansi.enabled,Some(false))
+
+
+-- !query
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c)
+-- !query analysis
+Aggregate [median(c#x) AS median(c)#x, percentile(c#x, cast(0.25 as double),
1, 0, 0, false) AS percentile(c, 0.25, 1)#x, percentile_cont(c#x, cast(0.75 as
double), false) AS percentile_cont(0.75) WITHIN GROUP (ORDER BY c)#x,
percentile_disc(c#x, cast(0.5 as double), false, 0, 0, false) AS
percentile_disc(0.5) WITHIN GROUP (ORDER BY c)#x]
++- SubqueryAlias t
+ +- LocalRelation [c#x]
+
+
+-- !query
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS t(c)
+-- !query analysis
+Aggregate [typeof(median(c#x)) AS typeof(median(c))#x]
+- SubqueryAlias t
+- LocalRelation [c#x]
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 8a4a21ac0a13..b805490baac7 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
@@ -656,14 +656,6 @@ SELECT approx_percentile(c, 0.5)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT histogram_numeric(c, 2)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT median(c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_diff('SECOND', TIME '12:00:00', c)
@@ -683,14 +675,6 @@ SELECT approx_percentile(c, 0.5)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT histogram_numeric(c, 2)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT median(c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_diff('SECOND', TIME '12:00:00', c)
@@ -698,3 +682,42 @@ SELECT time_diff('SECOND', TIME '12:00:00', c)
SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c);
SET spark.sql.ansi.enabled=true;
+
+-- percentile / median / percentile_cont / percentile_disc over
TIMESTAMP_LTZ(p) keep the input
+-- type. Interpolation is exact in nanoseconds and floored to p;
percentile_disc returns an input
+-- value; NULLs are ignored.
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c);
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c);
+-- A sub-microsecond midpoint that a double interpolation would lose.
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789
UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456791
UTC') AS t(c);
+-- Pre-epoch: the midpoint -499.5ns is floored to -500ns.
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '1969-12-31 23:59:59.999999000
UTC'),
+ (TIMESTAMP_LTZ '1970-01-01 00:00:00.000000001
UTC') AS t(c);
+-- A non-dyadic percentage uses an exact position: 3 * 0.7 is
2.0999999999999996 as a double.
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000200 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000300 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001300 UTC') AS t(c);
+-- At p=7 the midpoint .00000025 is floored to .0000002.
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))),
+ (CAST('2020-01-01 12:00:00.0000004 UTC' AS timestamp_ltz(7))) AS
t(c);
+-- Same results with ANSI mode off.
+SET spark.sql.ansi.enabled=false;
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c);
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c);
+SET spark.sql.ansi.enabled=true;
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 6744c8f09f41..32361ee72303 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
@@ -660,14 +660,6 @@ SELECT percentile_approx(c, 0.5)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT histogram_numeric(c, 2)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT median(c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_diff('SECOND', TIME '12:00:00', c)
@@ -687,14 +679,6 @@ SELECT percentile_approx(c, 0.5)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT histogram_numeric(c, 2)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT median(c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SELECT time_diff('SECOND', TIME '12:00:00', c)
@@ -702,3 +686,50 @@ SELECT time_diff('SECOND', TIME '12:00:00', c)
SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c);
SET spark.sql.ansi.enabled=true;
+
+-- percentile / median / percentile_cont / percentile_disc over
TIMESTAMP_NTZ(p) keep the input
+-- type. Interpolation is exact in nanoseconds and floored to p;
percentile_disc returns an input
+-- value; NULLs are ignored.
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c);
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS
t(c);
+-- A sub-microsecond midpoint that a double interpolation would lose.
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456791')
AS t(c);
+-- Pre-epoch: the midpoint -499.5ns is floored to -500ns.
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '1969-12-31 23:59:59.999999000'),
+ (TIMESTAMP_NTZ '1970-01-01 00:00:00.000000001')
AS t(c);
+-- A non-dyadic percentage uses an exact position: 3 * 0.7 is
2.0999999999999996 as a double.
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000200'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000300'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001300') AS t(c);
+-- At p=7 the midpoint .00000025 is floored to .0000002.
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))),
+ (CAST('2020-01-01 12:00:00.0000004' AS timestamp_ntz(7))) AS
t(c);
+-- As a window function, and with a frequency column.
+SELECT k, median(c) OVER (PARTITION BY k)
+ FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001'),
+ (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000003'),
+ (2, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000005') AS t(k, c);
+SELECT percentile(c, 0.5, f)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001', 3),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000009', 1) AS t(c, f);
+-- Same results with ANSI mode off.
+SET spark.sql.ansi.enabled=false;
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c);
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS
t(c);
+SET spark.sql.ansi.enabled=true;
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 5d1d0cb3a1dd..4b6900205e41 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
@@ -2345,114 +2345,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
- } ]
-}
-
-
--- !query
-SELECT median(c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
-- !query
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
@@ -2687,7 +2579,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
-SELECT percentile(c, 0.5)
+SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
struct<>
@@ -2699,22 +2591,22 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_trunc(HOUR, c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
+ "stopIndex" : 28,
+ "fragment" : "time_trunc('HOUR', c)"
} ]
}
-- !query
-SELECT median(c)
+SELECT time_diff('SECOND', TIME '12:00:00', c)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
struct<>
@@ -2726,135 +2618,127 @@
org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
+ "paramIndex" : "third",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
+ "stopIndex" : 46,
+ "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
} ]
}
-- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
+SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
-struct<>
+struct<last_day(c):date,next_day(c, MO):date,datediff(c,
2019-12-31):int,add_months(c, 1):date>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+2020-01-31 2020-01-06 1 2020-02-01
-- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SET spark.sql.ansi.enabled=true
-- !query schema
-struct<>
+struct<key:string,value:string>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+spark.sql.ansi.enabled true
-- !query
-SELECT time_trunc('HOUR', c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c)
-- !query schema
-struct<>
+struct<median(c):timestamp_ltz(9),percentile(c, 0.25,
1):timestamp_ltz(9),percentile_cont(0.75) WITHIN GROUP (ORDER BY
c):timestamp_ltz(9),percentile_cont(0.25) WITHIN GROUP (ORDER BY c
DESC):timestamp_ltz(9),percentile_disc(0.5) WITHIN GROUP (ORDER BY
c):timestamp_ltz(9)>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "second",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_trunc(HOUR, c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 28,
- "fragment" : "time_trunc('HOUR', c)"
- } ]
-}
+2020-01-01 04:00:00.0000004 2020-01-01 04:00:00.00000025 2020-01-01
04:00:00.0000007 2020-01-01 04:00:00.0000007 2020-01-01 04:00:00.0000004
-- !query
-SELECT time_diff('SECOND', TIME '12:00:00', c)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c)
-- !query schema
-struct<>
+struct<typeof(median(c)):string,typeof(percentile(c, array(0.25, 0.75),
1)):string,typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c)):string>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_LTZ(9)\"",
- "paramIndex" : "third",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 46,
- "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
- } ]
-}
+timestamp_ltz(7) array<timestamp_ltz(7)> timestamp_ltz(7)
-- !query
-SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
- FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456789
UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.123456791
UTC') AS t(c)
-- !query schema
-struct<last_day(c):date,next_day(c, MO):date,datediff(c,
2019-12-31):int,add_months(c, 1):date>
+struct<median(c):timestamp_ltz(9)>
-- !query output
-2020-01-31 2020-01-06 1 2020-02-01
+2020-01-01 04:00:00.12345679
+
+
+-- !query
+SELECT median(c) FROM VALUES (TIMESTAMP_LTZ '1969-12-31 23:59:59.999999000
UTC'),
+ (TIMESTAMP_LTZ '1970-01-01 00:00:00.000000001
UTC') AS t(c)
+-- !query schema
+struct<median(c):timestamp_ltz(9)>
+-- !query output
+1969-12-31 15:59:59.9999995
+
+
+-- !query
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000200 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000300 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001300 UTC') AS t(c)
+-- !query schema
+struct<percentile(c, 0.7, 1):timestamp_ltz(9),percentile_cont(0.3) WITHIN
GROUP (ORDER BY c DESC):timestamp_ltz(9)>
+-- !query output
+2020-01-01 04:00:00.0000004 2020-01-01 04:00:00.0000004
+
+
+-- !query
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))),
+ (CAST('2020-01-01 12:00:00.0000004 UTC' AS timestamp_ltz(7))) AS
t(c)
+-- !query schema
+struct<median(c):timestamp_ltz(7),percentile(c, array(0.0, 1.0),
1):array<timestamp_ltz(7)>>
+-- !query output
+2020-01-01 04:00:00.0000002 [2020-01-01 04:00:00.0000001,2020-01-01
04:00:00.0000004]
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000100 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000000400 UTC'),
+ (TIMESTAMP_LTZ '2020-01-01 12:00:00.000001000 UTC'), (NULL) AS
t(c)
+-- !query schema
+struct<median(c):timestamp_ltz(9),percentile(c, 0.25,
1):timestamp_ltz(9),percentile_cont(0.75) WITHIN GROUP (ORDER BY
c):timestamp_ltz(9),percentile_disc(0.5) WITHIN GROUP (ORDER BY
c):timestamp_ltz(9)>
+-- !query output
+2020-01-01 04:00:00.0000004 2020-01-01 04:00:00.00000025 2020-01-01
04:00:00.0000007 2020-01-01 04:00:00.0000004
+
+
+-- !query
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001 UTC' AS timestamp_ltz(7))) AS
t(c)
+-- !query schema
+struct<typeof(median(c)):string>
+-- !query output
+timestamp_ltz(7)
-- !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 e897b32e13f4..70e6832990dc 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
@@ -2357,114 +2357,6 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
}
--- !query
-SELECT percentile(c, 0.5)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
- } ]
-}
-
-
--- !query
-SELECT median(c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
--- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
--- !query schema
-struct<>
--- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
-
-
-- !query
SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
@@ -2699,7 +2591,7 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
-- !query
-SELECT percentile(c, 0.5)
+SELECT time_trunc('HOUR', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
struct<>
@@ -2711,22 +2603,22 @@ org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile(c, 0.5, 1)\""
+ "paramIndex" : "second",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_trunc(HOUR, c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 25,
- "fragment" : "percentile(c, 0.5)"
+ "stopIndex" : 28,
+ "fragment" : "time_trunc('HOUR', c)"
} ]
}
-- !query
-SELECT median(c)
+SELECT time_diff('SECOND', TIME '12:00:00', c)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
struct<>
@@ -2738,135 +2630,150 @@
org.apache.spark.sql.catalyst.ExtendedAnalysisException
"messageParameters" : {
"inputSql" : "\"c\"",
"inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"median(c)\""
+ "paramIndex" : "third",
+ "requiredType" : "\"TIME\"",
+ "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 8,
- "stopIndex" : 16,
- "fragment" : "median(c)"
+ "stopIndex" : 46,
+ "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
} ]
}
-- !query
-SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY c)
+SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
-- !query schema
-struct<>
+struct<last_day(c):date,next_day(c, MO):date,datediff(c,
2019-12-31):int,add_months(c, 1):date>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_cont(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_cont(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+2020-01-31 2020-01-06 1 2020-02-01
-- !query
-SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SET spark.sql.ansi.enabled=true
-- !query schema
-struct<>
+struct<key:string,value:string>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "first",
- "requiredType" : "((\"NUMERIC\" or \"INTERVAL DAY TO SECOND\" or
\"INTERVAL YEAR TO MONTH\") or \"TIME\")",
- "sqlExpr" : "\"percentile_disc(c, 0.5)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 53,
- "fragment" : "percentile_disc(0.5) WITHIN GROUP (ORDER BY c)"
- } ]
-}
+spark.sql.ansi.enabled true
-- !query
-SELECT time_trunc('HOUR', c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_cont(0.25) WITHIN GROUP (ORDER BY c DESC),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c)
-- !query schema
-struct<>
+struct<median(c):timestamp_ntz(9),percentile(c, 0.25,
1):timestamp_ntz(9),percentile_cont(0.75) WITHIN GROUP (ORDER BY
c):timestamp_ntz(9),percentile_cont(0.25) WITHIN GROUP (ORDER BY c
DESC):timestamp_ntz(9),percentile_disc(0.5) WITHIN GROUP (ORDER BY
c):timestamp_ntz(9)>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "second",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_trunc(HOUR, c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 28,
- "fragment" : "time_trunc('HOUR', c)"
- } ]
-}
+2020-01-01 12:00:00.0000004 2020-01-01 12:00:00.00000025 2020-01-01
12:00:00.0000007 2020-01-01 12:00:00.0000007 2020-01-01 12:00:00.0000004
-- !query
-SELECT time_diff('SECOND', TIME '12:00:00', c)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT typeof(median(c)), typeof(percentile(c, array(0.25, 0.75))),
+ typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS t(c)
-- !query schema
-struct<>
+struct<typeof(median(c)):string,typeof(percentile(c, array(0.25, 0.75),
1)):string,typeof(percentile_disc(0.5) WITHIN GROUP (ORDER BY c)):string>
-- !query output
-org.apache.spark.sql.catalyst.ExtendedAnalysisException
-{
- "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
- "sqlState" : "42K09",
- "messageParameters" : {
- "inputSql" : "\"c\"",
- "inputType" : "\"TIMESTAMP_NTZ(9)\"",
- "paramIndex" : "third",
- "requiredType" : "\"TIME\"",
- "sqlExpr" : "\"time_diff(SECOND, TIME '12:00:00', c)\""
- },
- "queryContext" : [ {
- "objectType" : "",
- "objectName" : "",
- "startIndex" : 8,
- "stopIndex" : 46,
- "fragment" : "time_diff('SECOND', TIME '12:00:00', c)"
- } ]
-}
+timestamp_ntz(7) array<timestamp_ntz(7)> timestamp_ntz(7)
-- !query
-SELECT last_day(c), next_day(c, 'MO'), datediff(c, '2019-12-31'),
add_months(c, 1)
- FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789') AS t(c)
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456789'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.123456791')
AS t(c)
-- !query schema
-struct<last_day(c):date,next_day(c, MO):date,datediff(c,
2019-12-31):int,add_months(c, 1):date>
+struct<median(c):timestamp_ntz(9)>
-- !query output
-2020-01-31 2020-01-06 1 2020-02-01
+2020-01-01 12:00:00.12345679
+
+
+-- !query
+SELECT median(c) FROM VALUES (TIMESTAMP_NTZ '1969-12-31 23:59:59.999999000'),
+ (TIMESTAMP_NTZ '1970-01-01 00:00:00.000000001')
AS t(c)
+-- !query schema
+struct<median(c):timestamp_ntz(9)>
+-- !query output
+1969-12-31 23:59:59.9999995
+
+
+-- !query
+SELECT percentile(c, 0.7), percentile_cont(0.3) WITHIN GROUP (ORDER BY c DESC)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000200'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000300'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001300') AS t(c)
+-- !query schema
+struct<percentile(c, 0.7, 1):timestamp_ntz(9),percentile_cont(0.3) WITHIN
GROUP (ORDER BY c DESC):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:00:00.0000004 2020-01-01 12:00:00.0000004
+
+
+-- !query
+SELECT median(c), percentile(c, array(0.0, 1.0))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))),
+ (CAST('2020-01-01 12:00:00.0000004' AS timestamp_ntz(7))) AS t(c)
+-- !query schema
+struct<median(c):timestamp_ntz(7),percentile(c, array(0.0, 1.0),
1):array<timestamp_ntz(7)>>
+-- !query output
+2020-01-01 12:00:00.0000002 [2020-01-01 12:00:00.0000001,2020-01-01
12:00:00.0000004]
+
+
+-- !query
+SELECT k, median(c) OVER (PARTITION BY k)
+ FROM VALUES (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001'),
+ (1, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000003'),
+ (2, TIMESTAMP_NTZ '2020-01-01 12:00:00.000000005') AS t(k, c)
+-- !query schema
+struct<k:int,median(c) OVER (PARTITION BY k ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING):timestamp_ntz(9)>
+-- !query output
+1 2020-01-01 12:00:00.000000002
+1 2020-01-01 12:00:00.000000002
+2 2020-01-01 12:00:00.000000005
+
+
+-- !query
+SELECT percentile(c, 0.5, f)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000001', 3),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000009', 1) AS t(c, f)
+-- !query schema
+struct<percentile(c, 0.5, f):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:00:00.000000001
+
+
+-- !query
+SET spark.sql.ansi.enabled=false
+-- !query schema
+struct<key:string,value:string>
+-- !query output
+spark.sql.ansi.enabled false
+
+
+-- !query
+SELECT median(c), percentile(c, 0.25), percentile_cont(0.75) WITHIN GROUP
(ORDER BY c),
+ percentile_disc(0.5) WITHIN GROUP (ORDER BY c)
+ FROM VALUES (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000100'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000000400'),
+ (TIMESTAMP_NTZ '2020-01-01 12:00:00.000001000'), (NULL) AS t(c)
+-- !query schema
+struct<median(c):timestamp_ntz(9),percentile(c, 0.25,
1):timestamp_ntz(9),percentile_cont(0.75) WITHIN GROUP (ORDER BY
c):timestamp_ntz(9),percentile_disc(0.5) WITHIN GROUP (ORDER BY
c):timestamp_ntz(9)>
+-- !query output
+2020-01-01 12:00:00.0000004 2020-01-01 12:00:00.00000025 2020-01-01
12:00:00.0000007 2020-01-01 12:00:00.0000004
+
+
+-- !query
+SELECT typeof(median(c))
+ FROM VALUES (CAST('2020-01-01 12:00:00.0000001' AS timestamp_ntz(7))) AS t(c)
+-- !query schema
+struct<typeof(median(c)):string>
+-- !query output
+timestamp_ntz(7)
-- !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 b9916957725d..5b99decc1d5e 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
@@ -911,6 +911,82 @@ abstract class TimestampNanosFunctionsSuiteBase extends
SharedSparkSession {
}
}
+ test("SPARK-60542: percentile family over nanosecond timestamps keeps the
input type") {
+ Seq(7, 8, 9).foreach { p =>
+ val schema = new StructType()
+ .add("ntz", TimestampNTZNanosType(p))
+ .add("ltz", TimestampLTZNanosType(p))
+ // Sub-microsecond offsets that are multiples of 100ns (exact at every
p) plus a NULL row,
+ // spread over several partitions so that partial buffers get merged.
+ val ldt = LocalDateTime.parse("2020-01-01T12:00:00")
+ val ins = Instant.parse("2020-01-01T12:00:00Z")
+ val data = Seq(100, 400, 1000).map(n => Row(ldt.plusNanos(n),
ins.plusNanos(n))) :+
+ Row(null, null)
+ val df = spark.createDataFrame(spark.sparkContext.parallelize(data, 3),
schema)
+ val exprs = Seq(
+ "median(%s)",
+ "percentile(%s, 0.25)",
+ "percentile_cont(0.75) WITHIN GROUP (ORDER BY %s)",
+ "percentile_cont(0.25) WITHIN GROUP (ORDER BY %s DESC)",
+ "percentile_disc(0.5) WITHIN GROUP (ORDER BY %s)")
+ // The interpolated 250ns is floored to 200ns at p = 7.
+ val expectedNanos = Seq(400, if (p == 7) 200 else 250, 700, 700, 400)
+ val ntzRes = df.selectExpr(exprs.map(_.format("ntz")): _*)
+ assert(ntzRes.schema.map(_.dataType) ===
Seq.fill(exprs.length)(TimestampNTZNanosType(p)))
+ checkAnswer(ntzRes, Row(expectedNanos.map(ldt.plusNanos(_)): _*))
+ val ltzRes = df.selectExpr(exprs.map(_.format("ltz")): _*)
+ assert(ltzRes.schema.map(_.dataType) ===
Seq.fill(exprs.length)(TimestampLTZNanosType(p)))
+ checkAnswer(ltzRes, Row(expectedNanos.map(ins.plusNanos(_)): _*))
+
+ // The Scala Column API agrees, also for an array of percentages.
+ val arrayRes = df.select(
+ median(col("ntz")), percentile(col("ltz"), array(lit(0.25),
lit(0.75))))
+ assert(arrayRes.schema.map(_.dataType) === Seq(
+ TimestampNTZNanosType(p), ArrayType(TimestampLTZNanosType(p),
containsNull = false)))
+ checkAnswer(arrayRes,
+ Row(ldt.plusNanos(400), Seq(expectedNanos(1),
700).map(ins.plusNanos(_))))
+
+ // A non-dyadic percentage uses an exact position: 3 * 0.7 is
2.0999999999999996 as a double.
+ val df2 = spark.createDataFrame(spark.sparkContext.parallelize(
+ Seq(100, 200, 300, 1300).map(n => Row(ldt.plusNanos(n),
ins.plusNanos(n)))), schema)
+ checkAnswer(
+ df2.selectExpr(
+ "percentile(ntz, 0.7)", "percentile_cont(0.3) WITHIN GROUP (ORDER BY
ltz DESC)"),
+ Row(ldt.plusNanos(400), ins.plusNanos(400)))
+ }
+ }
+
+ test("SPARK-60542: percentile over nanosecond timestamps interpolates
exactly") {
+ def median9(values: String*): DataFrame = spark.createDataFrame(
+ spark.sparkContext.parallelize(values.map(v =>
Row(LocalDateTime.parse(v)))),
+ new StructType().add("c",
TimestampNTZNanosType(9))).selectExpr("median(c)")
+ // Both values map to the same double, so a double interpolation can't
return .123456790.
+ checkAnswer(
+ median9("2020-01-01T12:00:00.123456789",
"2020-01-01T12:00:00.123456791"),
+ Row(LocalDateTime.parse("2020-01-01T12:00:00.123456790")))
+ // Pre-epoch: the midpoint -499.5ns is floored to -500ns.
+ checkAnswer(
+ median9("1969-12-31T23:59:59.999999", "1970-01-01T00:00:00.000000001"),
+ Row(LocalDateTime.parse("1969-12-31T23:59:59.999999500")))
+ // The full 0001..9999 range overflows a long in nanoseconds.
+ checkAnswer(
+ median9("0001-01-01T00:00:00", "9999-12-31T23:59:59.999999999"),
+ Row(LocalDateTime.parse("5000-07-02T11:59:59.999999999")))
+ checkAnswer(median9("2020-01-01T12:00:00.123456789"),
+ Row(LocalDateTime.parse("2020-01-01T12:00:00.123456789")))
+ checkAnswer(median9(), Row(null))
+ // A NaN percentage passes analysis and returns the first value, as for
other types.
+ val first = LocalDateTime.parse("2020-01-01T12:00:00.000000100")
+ val df = spark.createDataFrame(
+ spark.sparkContext.parallelize(Seq(Row(first),
Row(first.plusNanos(100)))),
+ new StructType().add("c", TimestampNTZNanosType(9)))
+ checkAnswer(
+ df.selectExpr(
+ "percentile(c, CAST('NaN' AS DOUBLE))",
+ "percentile_cont(CAST('NaN' AS DOUBLE)) WITHIN GROUP (ORDER BY c)"),
+ Row(first, first))
+ }
+
test("SPARK-57816: date_format / to_char / to_varchar over
nanosecond-precision timestamps") {
// The 9-`S` pattern is a fixed-width fraction field, so it always emits 9
digits; truncating to
// precision `p` zeros the low digits (floor); it does not drop them. The
session zone is
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2StrategySuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2StrategySuite.scala
index 5e62431b404d..533ac9b593cc 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2StrategySuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/v2/DataSourceV2StrategySuite.scala
@@ -26,7 +26,7 @@ import org.apache.spark.sql.catalyst.InternalRow
import org.apache.spark.sql.catalyst.analysis.UnresolvedAttribute
import org.apache.spark.sql.catalyst.dsl.expressions._
import org.apache.spark.sql.catalyst.expressions._
-import org.apache.spark.sql.catalyst.expressions.aggregate.Count
+import
org.apache.spark.sql.catalyst.expressions.aggregate.{AggregateExpression,
AggregateFunction, Complete, Count, Percentile, PercentileDisc}
import org.apache.spark.sql.catalyst.expressions.variant.VariantGet
import org.apache.spark.sql.catalyst.optimizer.{BooleanSimplification,
CombineFilters, ConstantFolding}
import org.apache.spark.sql.catalyst.plans.Inner
@@ -43,7 +43,7 @@ import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.internal.connector.SupportsPushDownCatalystFilters
import org.apache.spark.sql.sources.{BaseRelation, TableScan}
import org.apache.spark.sql.test.SharedSparkSession
-import org.apache.spark.sql.types.{ArrayType, BooleanType, DoubleType,
FloatType, IntegerType, LongType, StringType, StructField, StructType,
TimestampType, TimeType, VariantType}
+import org.apache.spark.sql.types.{ArrayType, BooleanType, DoubleType,
FloatType, IntegerType, LongType, StringType, StructField, StructType,
TimestampNTZNanosType, TimestampType, TimeType, VariantType}
import org.apache.spark.sql.util.CaseInsensitiveStringMap
import org.apache.spark.udf.worker.UDFWorkerSpecification
import org.apache.spark.unsafe.types.UTF8String
@@ -1010,6 +1010,17 @@ class DataSourceV2StrategySuite extends
SharedSparkSession {
assert(result.isEmpty, "null path should not translate (graceful, no NPE)")
}
+ test("SPARK-60542: percentile_cont over nanosecond timestamps is not pushed
down") {
+ def translate(agg: AggregateFunction): Option[V2Expression] =
+ new V2ExpressionBuilder(AggregateExpression(agg, Complete, isDistinct =
false)).build()
+ val d = AttributeReference("d", DoubleType)()
+ val ts = AttributeReference("ts", TimestampNTZNanosType(9))()
+ assert(translate(new Percentile(d, Literal(0.5))).isDefined)
+ // A source would interpolate with its own rounding; percentile_disc
returns an input value.
+ assert(translate(new Percentile(ts, Literal(0.5))).isEmpty)
+ assert(translate(PercentileDisc(ts, Literal(0.5))).isDefined)
+ }
+
test("VariantGet with non-column child returns None") {
val lit = Literal("v")
val path = Literal.create("$.a", StringType)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
