This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-4.x by this push:
new bb8caec31005 [SPARK-40945][SQL][PYTHON] Add truncate function
bb8caec31005 is described below
commit bb8caec31005751399947aa99d393520098c76a2
Author: SreeramaYeshwanthGowd <[email protected]>
AuthorDate: Mon Aug 10 16:50:41 2026 +0800
[SPARK-40945][SQL][PYTHON] Add truncate function
### What changes were proposed in this pull request?
Add a built in `truncate(expr[, scale])` scalar function that truncates a
numeric value toward zero to `scale` decimal places. `scale` defaults to 0, and
a negative `scale` truncates digits to the left of the decimal point.
API surface added:
- SQL: `truncate(expr)` and `truncate(expr, scale)`
- Scala DataFrame: `functions.truncate(col)`, `functions.truncate(col,
scale)`
- PySpark, classic and Spark Connect: `pyspark.sql.functions.truncate(col,
scale=None)`
Implementation notes:
- `Truncate` is a small `RoundBase` subclass using `RoundingMode.DOWN`,
mirroring the existing `Round` (HALF_UP) and `BRound` (HALF_EVEN). It reuses
the same input types, result type derivation, and constant scale handling.
- `RoundBase` routes `DecimalType` through `Decimal.changePrecision`, which
previously supported only the FLOOR, CEILING, HALF_UP, and HALF_EVEN modes.
This adds `Decimal.ROUND_DOWN` and the corresponding branches so the DOWN mode
is supported. The branches are trivial: on the compact (long backed) path,
integer division already truncates toward zero, so no adjustment is needed; the
BigDecimal path already uses `setScale(scale, roundMode)`, which supports DOWN
natively.
On naming: this adds a new function named `truncate` rather than
overloading the existing `trunc`. Spark's `trunc` is date only (`trunc(date,
fmt)`), and both the date form and a numeric `trunc(numeric, scale)` take two
arguments, so they cannot be distinguished by arity the way `ceil`/`floor`
overload their scale argument. Introducing a numeric overload of `trunc` would
require type based dispatch at parse time, which is a larger and more error
prone change. The name `truncate` match [...]
### Why are the changes needed?
Truncation toward zero to a given number of decimal places is a common
numeric operation that Spark cannot express directly today. `floor` and `ceil`
with a scale round toward negative and positive infinity, which is wrong for
negative values, and `round`/`bround` round to nearest. The function is
requested in SPARK-40945 and is provided by PostgreSQL, BigQuery, MySQL,
Oracle, Snowflake, and Trino, so it also improves parity with the engines Spark
users migrate from.
### Does this PR introduce _any_ user-facing change?
Yes. It adds a new built in SQL function `truncate` and the corresponding
Scala and PySpark DataFrame API entries. No existing behavior changes; the
`Decimal` change only adds support for a new rounding mode and does not alter
the existing modes.
Example:
```
spark-sql> SELECT truncate(1234.5678, 2);
1234.56
spark-sql> SELECT truncate(-1234.5678, 2);
-1234.56
spark-sql> SELECT truncate(1234.5678, -2);
1200
```
### How was this patch tested?
Added catalyst unit tests in `MathExpressionsSuite` covering decimal (both
long backed and BigDecimal backed), double, and integral inputs, positive and
negative values, positive and negative scale, the default scale, and null
propagation. Extended `DecimalSuite` so its existing "respect rounding mode"
test also exercises `ROUND_DOWN` by comparing `Decimal.changePrecision` against
`BigDecimal.setScale` for the DOWN mode. Added a DataFrame API test in
`MathFunctionsSuite`, PySpark doct [...]
### Was this patch authored or co-authored using generative AI tooling? No
Closes #57476 from SreeramaYeshwanthGowd/add-truncate-function.
Authored-by: SreeramaYeshwanthGowd <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit ae51635b2faad20ba1addd6a6ec8a507546a4862)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../source/reference/pyspark.sql/functions.rst | 1 +
python/pyspark/sql/connect/functions/builtin.py | 12 +
python/pyspark/sql/functions/__init__.py | 1 +
python/pyspark/sql/functions/builtin.py | 61 ++++
.../scala/org/apache/spark/sql/functions.scala | 47 +++
.../scala/org/apache/spark/sql/types/Decimal.scala | 5 +-
.../sql/catalyst/analysis/FunctionRegistry.scala | 1 +
.../sql/catalyst/expressions/mathExpressions.scala | 45 +++
.../expressions/MathExpressionsSuite.scala | 21 ++
.../org/apache/spark/sql/types/DecimalSuite.scala | 3 +-
.../apache/spark/sql/PlanGenerationTestSuite.scala | 4 +
.../explain-results/function_truncate.explain | 2 +
.../query-tests/queries/function_truncate.json | 81 +++++
.../queries/function_truncate.proto.bin | Bin 0 -> 730 bytes
.../sql-functions/sql-expression-schema.md | 1 +
.../sql-tests/analyzer-results/math.sql.out | 301 ++++++++++++++++++
.../analyzer-results/nonansi/math.sql.out | 301 ++++++++++++++++++
.../src/test/resources/sql-tests/inputs/math.sql | 58 ++++
.../test/resources/sql-tests/results/math.sql.out | 344 +++++++++++++++++++++
.../sql-tests/results/nonansi/math.sql.out | 344 +++++++++++++++++++++
.../org/apache/spark/sql/MathFunctionsSuite.scala | 40 +++
21 files changed, 1671 insertions(+), 2 deletions(-)
diff --git a/python/docs/source/reference/pyspark.sql/functions.rst
b/python/docs/source/reference/pyspark.sql/functions.rst
index 7cc05cae3e21..f3d973b1e735 100644
--- a/python/docs/source/reference/pyspark.sql/functions.rst
+++ b/python/docs/source/reference/pyspark.sql/functions.rst
@@ -143,6 +143,7 @@ Mathematical Functions
sqrt
tan
tanh
+ truncate
try_add
try_divide
try_mod
diff --git a/python/pyspark/sql/connect/functions/builtin.py
b/python/pyspark/sql/connect/functions/builtin.py
index 3353e6c4333c..b2f1149db00d 100644
--- a/python/pyspark/sql/connect/functions/builtin.py
+++ b/python/pyspark/sql/connect/functions/builtin.py
@@ -847,6 +847,18 @@ def round(col: "ColumnOrName", scale:
Optional[Union[Column, int]] = None) -> Co
round.__doc__ = pysparkfuncs.round.__doc__
+def truncate(col: "ColumnOrName", scale: Optional[Union[Column, int]] = None)
-> Column:
+ if scale is None:
+ return _invoke_function_over_columns("truncate", col)
+ else:
+ scale = _enum_to_value(scale)
+ scale = lit(scale) if isinstance(scale, int) else scale
+ return _invoke_function_over_columns("truncate", col, scale)
+
+
+truncate.__doc__ = pysparkfuncs.truncate.__doc__
+
+
def sec(col: "ColumnOrName") -> Column:
return _invoke_function_over_columns("sec", col)
diff --git a/python/pyspark/sql/functions/__init__.py
b/python/pyspark/sql/functions/__init__.py
index 93e80822f2ad..696691884f69 100644
--- a/python/pyspark/sql/functions/__init__.py
+++ b/python/pyspark/sql/functions/__init__.py
@@ -110,6 +110,7 @@ __all__ = [ # noqa: F405
"sqrt",
"tan",
"tanh",
+ "truncate",
"try_add",
"try_divide",
"try_mod",
diff --git a/python/pyspark/sql/functions/builtin.py
b/python/pyspark/sql/functions/builtin.py
index 736f4884a533..6e67fec8f3c8 100644
--- a/python/pyspark/sql/functions/builtin.py
+++ b/python/pyspark/sql/functions/builtin.py
@@ -8311,6 +8311,67 @@ def round(col: "ColumnOrName", scale:
Optional[Union[Column, int]] = None) -> Co
return _invoke_function_over_columns("round", col, scale)
+@_try_remote_functions
+def truncate(col: "ColumnOrName", scale: Optional[Union[Column, int]] = None)
-> Column:
+ """
+ Truncate the given value toward zero to `scale` decimal places when
`scale` >= 0,
+ or to the left of the decimal point when `scale` < 0. `scale` defaults to
0.
+
+ Unlike :func:`round`, the result is always rounded toward zero, and unlike
:func:`floor`
+ negative values are not rounded toward negative infinity.
+
+ .. versionadded:: 4.4.0
+
+ Parameters
+ ----------
+ col : :class:`~pyspark.sql.Column` or column name
+ The target column or column name to truncate.
+ A column that evaluates to a numeric.
+ scale : :class:`~pyspark.sql.Column` or int, optional
+ An optional parameter to control the number of decimal places to keep.
+ A column that evaluates to an integer. Must be a constant. Defaults to
0.
+
+ Returns
+ -------
+ :class:`~pyspark.sql.Column`
+ A column for the truncated value, of the same type as the input,
except that a decimal
+ input may return a decimal of different precision and scale.
+
+ See Also
+ --------
+ :meth:`pyspark.sql.functions.round`
+ :meth:`pyspark.sql.functions.trunc`
+ :meth:`pyspark.sql.functions.floor`
+ :meth:`pyspark.sql.functions.ceil`
+
+ Examples
+ --------
+ Example 1: Truncate toward zero to a given number of decimal places
+
+ >>> import pyspark.sql.functions as sf
+ >>> spark.range(1).select(sf.truncate(sf.lit(15.79),
sf.lit(1)).alias("r")).collect()
+ [Row(r=15.7)]
+
+ Example 2: Truncation rounds toward zero for negative values
+
+ >>> import pyspark.sql.functions as sf
+ >>> spark.range(1).select(sf.truncate(sf.lit(-2.99),
sf.lit(0)).alias("r")).collect()
+ [Row(r=-2.0)]
+
+ Example 3: The scale argument defaults to 0 when omitted
+
+ >>> import pyspark.sql.functions as sf
+ >>>
spark.range(1).select(sf.truncate(sf.lit(1234.5678)).alias("r")).collect()
+ [Row(r=1234.0)]
+ """
+ if scale is None:
+ return _invoke_function_over_columns("truncate", col)
+ else:
+ scale = _enum_to_value(scale)
+ scale = lit(scale) if isinstance(scale, int) else scale
+ return _invoke_function_over_columns("truncate", col, scale)
+
+
@_try_remote_functions
def bround(col: "ColumnOrName", scale: Optional[Union[Column, int]] = None) ->
Column:
"""
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
index 98f83528a272..a0599b42d425 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
@@ -6069,6 +6069,53 @@ object functions {
*/
def round(e: Column, scale: Column): Column = Column.fn("round", e, scale)
+ /**
+ * Truncates the value of `e` toward zero to 0 decimal places.
+ *
+ * @param e
+ * the value to truncate. A column that evaluates to a numeric.
+ * @return
+ * Returns a column of the same type as the input, except that a decimal
input may return a
+ * decimal of different precision and scale.
+ * @group math_funcs
+ * @since 4.4.0
+ */
+ def truncate(e: Column): Column = truncate(e, 0)
+
+ /**
+ * Truncates the value of `e` toward zero to `scale` decimal places when
`scale` is greater than
+ * or equal to 0, or to the left of the decimal point when `scale` is less
than 0.
+ *
+ * @param e
+ * the value to truncate. A column that evaluates to a numeric.
+ * @param scale
+ * the number of decimal places to keep. A column that evaluates to an
integral. Must be a
+ * constant.
+ * @return
+ * Returns a column of the same type as the input, except that a decimal
input may return a
+ * decimal of different precision and scale.
+ * @group math_funcs
+ * @since 4.4.0
+ */
+ def truncate(e: Column, scale: Int): Column = Column.fn("truncate", e,
lit(scale))
+
+ /**
+ * Truncates the value of `e` toward zero to `scale` decimal places when
`scale` is greater than
+ * or equal to 0, or to the left of the decimal point when `scale` is less
than 0.
+ *
+ * @param e
+ * the value to truncate. A column that evaluates to a numeric.
+ * @param scale
+ * the number of decimal places to keep. A column that evaluates to an
integral. Must be a
+ * constant.
+ * @return
+ * Returns a column of the same type as the input, except that a decimal
input may return a
+ * decimal of different precision and scale.
+ * @group math_funcs
+ * @since 4.4.0
+ */
+ def truncate(e: Column, scale: Column): Column = Column.fn("truncate", e,
scale)
+
/**
* Returns the value of the column `e` rounded to 0 decimal places with
HALF_EVEN round mode.
*
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/types/Decimal.scala
b/sql/api/src/main/scala/org/apache/spark/sql/types/Decimal.scala
index a41847f02c2a..a8de8e9d908a 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/types/Decimal.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/types/Decimal.scala
@@ -407,7 +407,7 @@ final class Decimal extends Ordered[Decimal] with
Serializable {
lv = roundMode match {
case ROUND_FLOOR => if (lv < 0) -1L else 0L
case ROUND_CEILING => if (lv > 0) 1L else 0L
- case ROUND_HALF_UP | ROUND_HALF_EVEN => 0L
+ case ROUND_HALF_UP | ROUND_HALF_EVEN | ROUND_DOWN => 0L
case _ => throw DataTypeErrors.unsupportedRoundingMode(roundMode)
}
} else {
@@ -433,6 +433,8 @@ final class Decimal extends Ordered[Decimal] with
Serializable {
if (doubled > pow10diff || doubled == pow10diff && lv % 2 != 0) {
lv += (if (droppedDigits < 0) -1L else 1L)
}
+ case ROUND_DOWN =>
+ // Truncation toward zero: `lv /= pow10diff` already dropped the
fractional part.
case _ =>
throw DataTypeErrors.unsupportedRoundingMode(roundMode)
}
@@ -578,6 +580,7 @@ object Decimal {
val ROUND_HALF_EVEN = BigDecimal.RoundingMode.HALF_EVEN
val ROUND_CEILING = BigDecimal.RoundingMode.CEILING
val ROUND_FLOOR = BigDecimal.RoundingMode.FLOOR
+ val ROUND_DOWN = BigDecimal.RoundingMode.DOWN
/** Maximum number of decimal digits an Int can represent */
val MAX_INT_DIGITS = 9
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
index 08bf81b19c0c..7950f90016ad 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
@@ -473,6 +473,7 @@ object FunctionRegistry {
expression[ToRadians]("radians"),
expression[Rint]("rint"),
expression[Round]("round"),
+ expression[Truncate]("truncate"),
expression[ShiftLeft]("shiftleft"),
expression[ShiftRight]("shiftright"),
expression[ShiftRightUnsigned]("shiftrightunsigned"),
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala
index aed6b34716a7..0cf7b7db157a 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala
@@ -1971,6 +1971,51 @@ case class BRound(
newLeft: Expression, newRight: Expression): BRound = copy(child = newLeft,
scale = newRight)
}
+/**
+ * Truncate an expression toward zero to `scale` decimal places.
+ * A negative `scale` truncates digits to the left of the decimal point.
+ * truncate(1234.5678, 2) = 1234.56, truncate(-1234.5678, 2) = -1234.56.
+ */
+// scalastyle:off line.size.limit
+@ExpressionDescription(
+ usage = "_FUNC_(expr[, scale]) - Returns `expr` truncated toward zero to
`scale` decimal places. `scale` defaults to 0. A negative `scale` truncates
digits to the left of the decimal point.",
+ arguments = """
+ Arguments:
+ * expr - The expression to truncate. An expression that evaluates to a
numeric.
+ * scale - The number of decimal places to keep. It must be a constant
integer expression and defaults to 0. A negative value truncates digits to the
left of the decimal point.
+ """,
+ examples = """
+ Examples:
+ > SELECT _FUNC_(1234.5678, 2);
+ 1234.56
+ > SELECT _FUNC_(1234.5678, -2);
+ 1200
+ > SELECT _FUNC_(-1234.5678, 2);
+ -1234.56
+ """,
+ since = "4.4.0",
+ group = "math_funcs")
+// scalastyle:on line.size.limit
+case class Truncate(
+ child: Expression,
+ scale: Expression,
+ // Kept for symmetry with Round/BRound, which do need it. Truncation
toward zero can never
+ // increase magnitude, so the ANSI overflow checks inherited from
RoundBase never trigger.
+ override val ansiEnabled: Boolean = SQLConf.get.ansiEnabled)
+ // Also inherits RoundBase's one-digit decimal precision widening, needed
for rounding modes
+ // that can carry (e.g. ceil(9.9, 0) = 10) but never exercised here since
truncation cannot.
+ extends RoundBase(child, scale, BigDecimal.RoundingMode.DOWN, "ROUND_DOWN") {
+ def this(child: Expression) = this(child, Literal(0),
SQLConf.get.ansiEnabled)
+
+ def this(child: Expression, scale: Expression) = this(child, scale,
SQLConf.get.ansiEnabled)
+
+ override def flatArguments: Iterator[Any] = Iterator(child, scale)
+
+ override protected def withNewChildrenInternal(
+ newLeft: Expression, newRight: Expression): Truncate =
+ copy(child = newLeft, scale = newRight)
+}
+
object WidthBucket {
/** Shared by interpreted eval and generated Java code; must stay public for
codegen. */
def computeBucketNumber(value: Double, min: Double, max: Double, numBucket:
Long): jl.Long = {
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/MathExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/MathExpressionsSuite.scala
index 0ea3bc77ec1c..90148932237c 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/MathExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/MathExpressionsSuite.scala
@@ -727,6 +727,27 @@ class MathExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
checkConsistencyBetweenInterpretedAndCodegen(Logarithm, DoubleType,
DoubleType)
}
+ test("truncate") {
+ // Truncation toward zero, distinct from floor/ceil (toward -inf/+inf) and
round (to nearest).
+ checkEvaluation(Truncate(Literal(Decimal(BigDecimal("1234.5678"))),
Literal(2)),
+ Decimal(BigDecimal("1234.56")))
+ checkEvaluation(Truncate(Literal(Decimal(BigDecimal("-1234.5678"))),
Literal(2)),
+ Decimal(BigDecimal("-1234.56")))
+ checkEvaluation(Truncate(Literal(Decimal(BigDecimal("1234.5678"))),
Literal(-2)),
+ Decimal(BigDecimal("1200")))
+ checkEvaluation(Truncate(Literal(Decimal(BigDecimal("-3.99"))),
Literal(0)),
+ Decimal(BigDecimal("-3")))
+ // Default scale is 0.
+ checkEvaluation(new Truncate(Literal(Decimal(BigDecimal("3.99")))),
Decimal(BigDecimal("3")))
+ // Integral input with negative scale.
+ checkEvaluation(Truncate(Literal(125), Literal(-1)), 120)
+ // Double input.
+ checkEvaluation(Truncate(Literal(3.1415926), Literal(3)), 3.141)
+ // Null propagation.
+ checkEvaluation(Truncate(Literal.create(null, DoubleType), Literal(2)),
null)
+ checkEvaluation(Truncate(Literal(1.23), Literal.create(null,
IntegerType)), null)
+ }
+
test("round/bround/floor/ceil") {
val scales = -6 to 6
val doublePi: Double = math.Pi
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/types/DecimalSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/types/DecimalSuite.scala
index 080c6693280a..a987aa52e101 100644
--- a/sql/catalyst/src/test/scala/org/apache/spark/sql/types/DecimalSuite.scala
+++ b/sql/catalyst/src/test/scala/org/apache/spark/sql/types/DecimalSuite.scala
@@ -28,7 +28,8 @@ import org.apache.spark.unsafe.types.UTF8String
class DecimalSuite extends SparkFunSuite with PrivateMethodTester with
SQLHelper {
- val allSupportedRoundModes = Seq(ROUND_HALF_UP, ROUND_HALF_EVEN,
ROUND_CEILING, ROUND_FLOOR)
+ val allSupportedRoundModes =
+ Seq(ROUND_HALF_UP, ROUND_HALF_EVEN, ROUND_CEILING, ROUND_FLOOR, ROUND_DOWN)
/** Check that a Decimal has the given string representation, precision and
scale */
private def checkDecimal(d: Decimal, string: String, precision: Int, scale:
Int): Unit = {
diff --git
a/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/PlanGenerationTestSuite.scala
b/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/PlanGenerationTestSuite.scala
index b3d8c160266a..04074accf5c8 100644
---
a/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/PlanGenerationTestSuite.scala
+++
b/sql/connect/client/jvm/src/test/scala/org/apache/spark/sql/PlanGenerationTestSuite.scala
@@ -1623,6 +1623,10 @@ class PlanGenerationTestSuite extends ConnectFunSuite
with Logging {
fn.round(fn.col("b"), 2)
}
+ functionTest("truncate") {
+ fn.truncate(fn.col("b"), 2)
+ }
+
functionTest("sec") {
fn.sec(fn.col("b"))
}
diff --git
a/sql/connect/common/src/test/resources/query-tests/explain-results/function_truncate.explain
b/sql/connect/common/src/test/resources/query-tests/explain-results/function_truncate.explain
new file mode 100644
index 000000000000..6f07efb1efd8
--- /dev/null
+++
b/sql/connect/common/src/test/resources/query-tests/explain-results/function_truncate.explain
@@ -0,0 +1,2 @@
+Project [truncate(b#0, 2) AS truncate(b, 2)#0]
++- LocalRelation <empty>, [id#0L, a#0, b#0, d#0, e#0, f#0, g#0]
diff --git
a/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.json
b/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.json
new file mode 100644
index 000000000000..48f46452c670
--- /dev/null
+++
b/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.json
@@ -0,0 +1,81 @@
+{
+ "common": {
+ "planId": "1"
+ },
+ "project": {
+ "input": {
+ "common": {
+ "planId": "0"
+ },
+ "localRelation": {
+ "schema":
"struct\u003cid:bigint,a:int,b:double,d:struct\u003cid:bigint,a:int,b:double\u003e,e:array\u003cint\u003e,f:map\u003cstring,struct\u003cid:bigint,a:int,b:double\u003e\u003e,g:string\u003e"
+ }
+ },
+ "expressions": [{
+ "unresolvedFunction": {
+ "functionName": "truncate",
+ "arguments": [{
+ "unresolvedAttribute": {
+ "unparsedIdentifier": "b"
+ },
+ "common": {
+ "origin": {
+ "jvmOrigin": {
+ "stackTrace": [{
+ "classLoaderName": "app",
+ "declaringClass": "org.apache.spark.sql.functions$",
+ "methodName": "col",
+ "fileName": "functions.scala"
+ }, {
+ "classLoaderName": "app",
+ "declaringClass":
"org.apache.spark.sql.PlanGenerationTestSuite",
+ "methodName": "~~trimmed~anonfun~~",
+ "fileName": "PlanGenerationTestSuite.scala"
+ }]
+ }
+ }
+ }
+ }, {
+ "literal": {
+ "integer": 2
+ },
+ "common": {
+ "origin": {
+ "jvmOrigin": {
+ "stackTrace": [{
+ "classLoaderName": "app",
+ "declaringClass": "org.apache.spark.sql.functions$",
+ "methodName": "truncate",
+ "fileName": "functions.scala"
+ }, {
+ "classLoaderName": "app",
+ "declaringClass":
"org.apache.spark.sql.PlanGenerationTestSuite",
+ "methodName": "~~trimmed~anonfun~~",
+ "fileName": "PlanGenerationTestSuite.scala"
+ }]
+ }
+ }
+ }
+ }],
+ "isInternal": false
+ },
+ "common": {
+ "origin": {
+ "jvmOrigin": {
+ "stackTrace": [{
+ "classLoaderName": "app",
+ "declaringClass": "org.apache.spark.sql.functions$",
+ "methodName": "truncate",
+ "fileName": "functions.scala"
+ }, {
+ "classLoaderName": "app",
+ "declaringClass": "org.apache.spark.sql.PlanGenerationTestSuite",
+ "methodName": "~~trimmed~anonfun~~",
+ "fileName": "PlanGenerationTestSuite.scala"
+ }]
+ }
+ }
+ }
+ }]
+ }
+}
\ No newline at end of file
diff --git
a/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.proto.bin
b/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.proto.bin
new file mode 100644
index 000000000000..f9f634226752
Binary files /dev/null and
b/sql/connect/common/src/test/resources/query-tests/queries/function_truncate.proto.bin
differ
diff --git a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
index 76a28ee68ca9..3923f966ab23 100644
--- a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
+++ b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
@@ -393,6 +393,7 @@
| org.apache.spark.sql.catalyst.expressions.TransformValues | transform_values
| SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k,
v) -> v + 1) | struct<transform_values(map_from_arrays(array(1, 2, 3), array(1,
2, 3)), lambdafunction((namedlambdavariable() + 1), namedlambdavariable(),
namedlambdavariable())):map<int,int>> |
| org.apache.spark.sql.catalyst.expressions.TruncDate | trunc | SELECT
trunc('2019-08-04', 'week') | struct<trunc(2019-08-04, week):date> |
| org.apache.spark.sql.catalyst.expressions.TruncTimestamp | date_trunc |
SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359') | struct<date_trunc(YEAR,
2015-03-05T09:32:05.359):timestamp> |
+| org.apache.spark.sql.catalyst.expressions.Truncate | truncate | SELECT
truncate(1234.5678, 2) | struct<truncate(1234.5678, 2):decimal(7,2)> |
| org.apache.spark.sql.catalyst.expressions.TryAdd | try_add | SELECT
try_add(1, 2) | struct<try_add(1, 2):int> |
| org.apache.spark.sql.catalyst.expressions.TryAesDecrypt | try_aes_decrypt |
SELECT
try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'),
'0000111122223333', 'GCM') |
struct<try_aes_decrypt(unhex(6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210),
0000111122223333, GCM, DEFAULT, ):binary> |
| org.apache.spark.sql.catalyst.expressions.TryDivide | try_divide | SELECT
try_divide(3, 2) | struct<try_divide(3, 2):double> |
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/math.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/math.sql.out
index 1fa7b7513993..053740acf191 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/math.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/math.sql.out
@@ -391,6 +391,307 @@ Project [bround(-9223372036854775808, -1) AS
bround(-9223372036854775808, -1)#xL
+- OneRowRelation
+-- !query
+SELECT truncate(25y, 1)
+-- !query analysis
+Project [truncate(25, 1) AS truncate(25, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, 0)
+-- !query analysis
+Project [truncate(25, 0) AS truncate(25, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -1)
+-- !query analysis
+Project [truncate(25, -1) AS truncate(25, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -2)
+-- !query analysis
+Project [truncate(25, -2) AS truncate(25, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -3)
+-- !query analysis
+Project [truncate(25, -3) AS truncate(25, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, 1)
+-- !query analysis
+Project [truncate(-25, 1) AS truncate(-25, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, 0)
+-- !query analysis
+Project [truncate(-25, 0) AS truncate(-25, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -1)
+-- !query analysis
+Project [truncate(-25, -1) AS truncate(-25, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -2)
+-- !query analysis
+Project [truncate(-25, -2) AS truncate(-25, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -3)
+-- !query analysis
+Project [truncate(-25, -3) AS truncate(-25, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(127y, -1)
+-- !query analysis
+Project [truncate(127, -1) AS truncate(127, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-128y, -1)
+-- !query analysis
+Project [truncate(-128, -1) AS truncate(-128, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(1234.5678)
+-- !query analysis
+Project [truncate(1234.5678, 0) AS truncate(1234.5678, 0)#x]
++- OneRowRelation
+
+
-- !query
SELECT conv('100', 2, 10)
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/math.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/math.sql.out
index 5fe1b69352f5..161477a44b5f 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/math.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/math.sql.out
@@ -391,6 +391,307 @@ Project [bround(-9223372036854775808, -1) AS
bround(-9223372036854775808, -1)#xL
+- OneRowRelation
+-- !query
+SELECT truncate(25y, 1)
+-- !query analysis
+Project [truncate(25, 1) AS truncate(25, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, 0)
+-- !query analysis
+Project [truncate(25, 0) AS truncate(25, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -1)
+-- !query analysis
+Project [truncate(25, -1) AS truncate(25, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -2)
+-- !query analysis
+Project [truncate(25, -2) AS truncate(25, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(25y, -3)
+-- !query analysis
+Project [truncate(25, -3) AS truncate(25, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, 1)
+-- !query analysis
+Project [truncate(-25, 1) AS truncate(-25, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, 0)
+-- !query analysis
+Project [truncate(-25, 0) AS truncate(-25, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -1)
+-- !query analysis
+Project [truncate(-25, -1) AS truncate(-25, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -2)
+-- !query analysis
+Project [truncate(-25, -2) AS truncate(-25, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-25y, -3)
+-- !query analysis
+Project [truncate(-25, -3) AS truncate(-25, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(127y, -1)
+-- !query analysis
+Project [truncate(127, -1) AS truncate(127, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-128y, -1)
+-- !query analysis
+Project [truncate(-128, -1) AS truncate(-128, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525s, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525s, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#x]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, 1)
+-- !query analysis
+Project [truncate(525, 1) AS truncate(525, 1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, 0)
+-- !query analysis
+Project [truncate(525, 0) AS truncate(525, 0)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -1)
+-- !query analysis
+Project [truncate(525, -1) AS truncate(525, -1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -2)
+-- !query analysis
+Project [truncate(525, -2) AS truncate(525, -2)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(525L, -3)
+-- !query analysis
+Project [truncate(525, -3) AS truncate(525, -3)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, 1)
+-- !query analysis
+Project [truncate(-525, 1) AS truncate(-525, 1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, 0)
+-- !query analysis
+Project [truncate(-525, 0) AS truncate(-525, 0)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -1)
+-- !query analysis
+Project [truncate(-525, -1) AS truncate(-525, -1)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -2)
+-- !query analysis
+Project [truncate(-525, -2) AS truncate(-525, -2)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(-525L, -3)
+-- !query analysis
+Project [truncate(-525, -3) AS truncate(-525, -3)#xL]
++- OneRowRelation
+
+
+-- !query
+SELECT truncate(1234.5678)
+-- !query analysis
+Project [truncate(1234.5678, 0) AS truncate(1234.5678, 0)#x]
++- OneRowRelation
+
+
-- !query
SELECT conv('100', 2, 10)
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/math.sql
b/sql/core/src/test/resources/sql-tests/inputs/math.sql
index 14a647a610cc..bd9a6b2c3de3 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/math.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/math.sql
@@ -70,6 +70,64 @@ SELECT bround(525L, -3);
SELECT bround(9223372036854775807L, -1);
SELECT bround(-9223372036854775808L, -1);
+-- Truncate with Byte input
+SELECT truncate(25y, 1);
+SELECT truncate(25y, 0);
+SELECT truncate(25y, -1);
+SELECT truncate(25y, -2);
+SELECT truncate(25y, -3);
+-- Truncate with negative Byte input: truncation is toward zero, unlike floor.
+SELECT truncate(-25y, 1);
+SELECT truncate(-25y, 0);
+SELECT truncate(-25y, -1);
+SELECT truncate(-25y, -2);
+SELECT truncate(-25y, -3);
+-- Truncate never overflows, unlike round: truncate(-128y, -1) is -120, not an
overflow.
+SELECT truncate(127y, -1);
+SELECT truncate(-128y, -1);
+
+-- Truncate with short integer input
+SELECT truncate(525s, 1);
+SELECT truncate(525s, 0);
+SELECT truncate(525s, -1);
+SELECT truncate(525s, -2);
+SELECT truncate(525s, -3);
+-- Truncate with negative short integer input: truncation is toward zero,
unlike floor.
+SELECT truncate(-525s, 1);
+SELECT truncate(-525s, 0);
+SELECT truncate(-525s, -1);
+SELECT truncate(-525s, -2);
+SELECT truncate(-525s, -3);
+
+-- Truncate with integer input
+SELECT truncate(525, 1);
+SELECT truncate(525, 0);
+SELECT truncate(525, -1);
+SELECT truncate(525, -2);
+SELECT truncate(525, -3);
+-- Truncate with negative integer input: truncation is toward zero, unlike
floor.
+SELECT truncate(-525, 1);
+SELECT truncate(-525, 0);
+SELECT truncate(-525, -1);
+SELECT truncate(-525, -2);
+SELECT truncate(-525, -3);
+
+-- Truncate with big integer input
+SELECT truncate(525L, 1);
+SELECT truncate(525L, 0);
+SELECT truncate(525L, -1);
+SELECT truncate(525L, -2);
+SELECT truncate(525L, -3);
+-- Truncate with negative big integer input: truncation is toward zero, unlike
floor.
+SELECT truncate(-525L, 1);
+SELECT truncate(-525L, 0);
+SELECT truncate(-525L, -1);
+SELECT truncate(-525L, -2);
+SELECT truncate(-525L, -3);
+
+-- Truncate with the scale argument omitted; it defaults to 0.
+SELECT truncate(1234.5678);
+
-- Conv
SELECT conv('100', 2, 10);
SELECT conv(-10, 16, -10);
diff --git a/sql/core/src/test/resources/sql-tests/results/math.sql.out
b/sql/core/src/test/resources/sql-tests/results/math.sql.out
index e2abcb099130..1f027d362cdf 100644
--- a/sql/core/src/test/resources/sql-tests/results/math.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/math.sql.out
@@ -703,6 +703,350 @@ org.apache.spark.SparkArithmeticException
}
+-- !query
+SELECT truncate(25y, 1)
+-- !query schema
+struct<truncate(25, 1):tinyint>
+-- !query output
+25
+
+
+-- !query
+SELECT truncate(25y, 0)
+-- !query schema
+struct<truncate(25, 0):tinyint>
+-- !query output
+25
+
+
+-- !query
+SELECT truncate(25y, -1)
+-- !query schema
+struct<truncate(25, -1):tinyint>
+-- !query output
+20
+
+
+-- !query
+SELECT truncate(25y, -2)
+-- !query schema
+struct<truncate(25, -2):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(25y, -3)
+-- !query schema
+struct<truncate(25, -3):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-25y, 1)
+-- !query schema
+struct<truncate(-25, 1):tinyint>
+-- !query output
+-25
+
+
+-- !query
+SELECT truncate(-25y, 0)
+-- !query schema
+struct<truncate(-25, 0):tinyint>
+-- !query output
+-25
+
+
+-- !query
+SELECT truncate(-25y, -1)
+-- !query schema
+struct<truncate(-25, -1):tinyint>
+-- !query output
+-20
+
+
+-- !query
+SELECT truncate(-25y, -2)
+-- !query schema
+struct<truncate(-25, -2):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-25y, -3)
+-- !query schema
+struct<truncate(-25, -3):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(127y, -1)
+-- !query schema
+struct<truncate(127, -1):tinyint>
+-- !query output
+120
+
+
+-- !query
+SELECT truncate(-128y, -1)
+-- !query schema
+struct<truncate(-128, -1):tinyint>
+-- !query output
+-120
+
+
+-- !query
+SELECT truncate(525s, 1)
+-- !query schema
+struct<truncate(525, 1):smallint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525s, 0)
+-- !query schema
+struct<truncate(525, 0):smallint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525s, -1)
+-- !query schema
+struct<truncate(525, -1):smallint>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525s, -2)
+-- !query schema
+struct<truncate(525, -2):smallint>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525s, -3)
+-- !query schema
+struct<truncate(525, -3):smallint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525s, 1)
+-- !query schema
+struct<truncate(-525, 1):smallint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525s, 0)
+-- !query schema
+struct<truncate(-525, 0):smallint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525s, -1)
+-- !query schema
+struct<truncate(-525, -1):smallint>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525s, -2)
+-- !query schema
+struct<truncate(-525, -2):smallint>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525s, -3)
+-- !query schema
+struct<truncate(-525, -3):smallint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(525, 1)
+-- !query schema
+struct<truncate(525, 1):int>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525, 0)
+-- !query schema
+struct<truncate(525, 0):int>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525, -1)
+-- !query schema
+struct<truncate(525, -1):int>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525, -2)
+-- !query schema
+struct<truncate(525, -2):int>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525, -3)
+-- !query schema
+struct<truncate(525, -3):int>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525, 1)
+-- !query schema
+struct<truncate(-525, 1):int>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525, 0)
+-- !query schema
+struct<truncate(-525, 0):int>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525, -1)
+-- !query schema
+struct<truncate(-525, -1):int>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525, -2)
+-- !query schema
+struct<truncate(-525, -2):int>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525, -3)
+-- !query schema
+struct<truncate(-525, -3):int>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(525L, 1)
+-- !query schema
+struct<truncate(525, 1):bigint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525L, 0)
+-- !query schema
+struct<truncate(525, 0):bigint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525L, -1)
+-- !query schema
+struct<truncate(525, -1):bigint>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525L, -2)
+-- !query schema
+struct<truncate(525, -2):bigint>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525L, -3)
+-- !query schema
+struct<truncate(525, -3):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525L, 1)
+-- !query schema
+struct<truncate(-525, 1):bigint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525L, 0)
+-- !query schema
+struct<truncate(-525, 0):bigint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525L, -1)
+-- !query schema
+struct<truncate(-525, -1):bigint>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525L, -2)
+-- !query schema
+struct<truncate(-525, -2):bigint>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525L, -3)
+-- !query schema
+struct<truncate(-525, -3):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(1234.5678)
+-- !query schema
+struct<truncate(1234.5678, 0):decimal(5,0)>
+-- !query output
+1234
+
+
-- !query
SELECT conv('100', 2, 10)
-- !query schema
diff --git a/sql/core/src/test/resources/sql-tests/results/nonansi/math.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/math.sql.out
index 09f438393328..5ab0677bfd7c 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/math.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/math.sql.out
@@ -447,6 +447,350 @@ struct<bround(-9223372036854775808, -1):bigint>
9223372036854775806
+-- !query
+SELECT truncate(25y, 1)
+-- !query schema
+struct<truncate(25, 1):tinyint>
+-- !query output
+25
+
+
+-- !query
+SELECT truncate(25y, 0)
+-- !query schema
+struct<truncate(25, 0):tinyint>
+-- !query output
+25
+
+
+-- !query
+SELECT truncate(25y, -1)
+-- !query schema
+struct<truncate(25, -1):tinyint>
+-- !query output
+20
+
+
+-- !query
+SELECT truncate(25y, -2)
+-- !query schema
+struct<truncate(25, -2):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(25y, -3)
+-- !query schema
+struct<truncate(25, -3):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-25y, 1)
+-- !query schema
+struct<truncate(-25, 1):tinyint>
+-- !query output
+-25
+
+
+-- !query
+SELECT truncate(-25y, 0)
+-- !query schema
+struct<truncate(-25, 0):tinyint>
+-- !query output
+-25
+
+
+-- !query
+SELECT truncate(-25y, -1)
+-- !query schema
+struct<truncate(-25, -1):tinyint>
+-- !query output
+-20
+
+
+-- !query
+SELECT truncate(-25y, -2)
+-- !query schema
+struct<truncate(-25, -2):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-25y, -3)
+-- !query schema
+struct<truncate(-25, -3):tinyint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(127y, -1)
+-- !query schema
+struct<truncate(127, -1):tinyint>
+-- !query output
+120
+
+
+-- !query
+SELECT truncate(-128y, -1)
+-- !query schema
+struct<truncate(-128, -1):tinyint>
+-- !query output
+-120
+
+
+-- !query
+SELECT truncate(525s, 1)
+-- !query schema
+struct<truncate(525, 1):smallint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525s, 0)
+-- !query schema
+struct<truncate(525, 0):smallint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525s, -1)
+-- !query schema
+struct<truncate(525, -1):smallint>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525s, -2)
+-- !query schema
+struct<truncate(525, -2):smallint>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525s, -3)
+-- !query schema
+struct<truncate(525, -3):smallint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525s, 1)
+-- !query schema
+struct<truncate(-525, 1):smallint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525s, 0)
+-- !query schema
+struct<truncate(-525, 0):smallint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525s, -1)
+-- !query schema
+struct<truncate(-525, -1):smallint>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525s, -2)
+-- !query schema
+struct<truncate(-525, -2):smallint>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525s, -3)
+-- !query schema
+struct<truncate(-525, -3):smallint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(525, 1)
+-- !query schema
+struct<truncate(525, 1):int>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525, 0)
+-- !query schema
+struct<truncate(525, 0):int>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525, -1)
+-- !query schema
+struct<truncate(525, -1):int>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525, -2)
+-- !query schema
+struct<truncate(525, -2):int>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525, -3)
+-- !query schema
+struct<truncate(525, -3):int>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525, 1)
+-- !query schema
+struct<truncate(-525, 1):int>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525, 0)
+-- !query schema
+struct<truncate(-525, 0):int>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525, -1)
+-- !query schema
+struct<truncate(-525, -1):int>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525, -2)
+-- !query schema
+struct<truncate(-525, -2):int>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525, -3)
+-- !query schema
+struct<truncate(-525, -3):int>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(525L, 1)
+-- !query schema
+struct<truncate(525, 1):bigint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525L, 0)
+-- !query schema
+struct<truncate(525, 0):bigint>
+-- !query output
+525
+
+
+-- !query
+SELECT truncate(525L, -1)
+-- !query schema
+struct<truncate(525, -1):bigint>
+-- !query output
+520
+
+
+-- !query
+SELECT truncate(525L, -2)
+-- !query schema
+struct<truncate(525, -2):bigint>
+-- !query output
+500
+
+
+-- !query
+SELECT truncate(525L, -3)
+-- !query schema
+struct<truncate(525, -3):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(-525L, 1)
+-- !query schema
+struct<truncate(-525, 1):bigint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525L, 0)
+-- !query schema
+struct<truncate(-525, 0):bigint>
+-- !query output
+-525
+
+
+-- !query
+SELECT truncate(-525L, -1)
+-- !query schema
+struct<truncate(-525, -1):bigint>
+-- !query output
+-520
+
+
+-- !query
+SELECT truncate(-525L, -2)
+-- !query schema
+struct<truncate(-525, -2):bigint>
+-- !query output
+-500
+
+
+-- !query
+SELECT truncate(-525L, -3)
+-- !query schema
+struct<truncate(-525, -3):bigint>
+-- !query output
+0
+
+
+-- !query
+SELECT truncate(1234.5678)
+-- !query schema
+struct<truncate(1234.5678, 0):decimal(5,0)>
+-- !query output
+1234
+
+
-- !query
SELECT conv('100', 2, 10)
-- !query schema
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/MathFunctionsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/MathFunctionsSuite.scala
index f7b27c205293..3ce1940e10e7 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/MathFunctionsSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/MathFunctionsSuite.scala
@@ -324,6 +324,46 @@ class MathFunctionsSuite extends SharedSparkSession {
testOneToOneMathFunction(rint, math.rint)
}
+ test("truncate") {
+ val df = Seq(5, 55, 555).map(Tuple1(_)).toDF("a")
+ checkAnswer(
+ df.select(truncate($"a", lit(-1)), truncate($"a", lit(-2))),
+ Seq(Row(0, 0), Row(50, 0), Row(550, 500))
+ )
+ // Truncation rounds toward zero, unlike floor for negative values.
+ val df2 = Seq(1234.5678, -1234.5678).map(Tuple1(_)).toDF("a")
+ checkAnswer(
+ df2.select(truncate($"a", lit(2))),
+ Seq(Row(1234.56), Row(-1234.56))
+ )
+ checkAnswer(
+ df2.selectExpr("truncate(a, 2)"),
+ Seq(Row(1234.56), Row(-1234.56))
+ )
+ // The truncate(Column, Int) and truncate(Column) overloads.
+ checkAnswer(
+ df2.select(truncate($"a", 2)),
+ Seq(Row(1234.56), Row(-1234.56))
+ )
+ checkAnswer(
+ df2.select(truncate($"a")),
+ Seq(Row(1234.0), Row(-1234.0))
+ )
+ // Decimal input, the type that motivated the new Decimal.changePrecision
rounding mode.
+ val df3 = Seq(BigDecimal("1234.5678"),
BigDecimal("-1234.5678")).map(Tuple1(_)).toDF("a")
+ checkAnswer(
+ df3.select(truncate($"a", lit(2))),
+ Seq(Row(BigDecimal("1234.56")), Row(BigDecimal("-1234.56")))
+ )
+ // NaN and Infinity are returned unchanged, matching RoundBase's other
rounding modes.
+ val df4 = Seq(Double.NaN, Double.PositiveInfinity, Double.NegativeInfinity)
+ .map(Tuple1(_)).toDF("a")
+ checkAnswer(
+ df4.select(truncate($"a", lit(2))),
+ Seq(Row(Double.NaN), Row(Double.PositiveInfinity),
Row(Double.NegativeInfinity))
+ )
+ }
+
test("round/bround/ceil/floor") {
val df = Seq(5, 55, 555).map(Tuple1(_)).toDF("a")
checkAnswer(
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
