andygrove opened a new issue, #264:
URL: https://github.com/apache/datafusion-spark/issues/264
Part of #174 and apache/datafusion-comet#7300.
### What the function does
`bround(expr[, d])` rounds `expr` to `d` decimal places (default 0) using
HALF_EVEN ("banker's") rounding. A negative `d` rounds to the left of the
decimal point. It is `BRound`, a `RoundBase` with `RoundingMode.HALF_EVEN`, the
same code as `round` except for the rounding mode. Available since 2.0.0.
`expr` accepts any numeric type and `d` is implicitly cast to INT and must
be foldable (`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT` otherwise). The result type
equals the input type for TINYINT, SMALLINT, INT, BIGINT, FLOAT and DOUBLE. For
`DECIMAL(p, s)` it is `DECIMAL(min(p - s + 1 + min(s, d), 38), min(s, d))` when
`d >= 0` and `DECIMAL(min(max(p - s + 1, -d + 1), 38), 0)` when `d < 0`. The
result is always nullable; a NULL `d` returns NULL without evaluating `expr`.
### Examples
```sql
SELECT bround(2.5, 0);
-- 2 decimal(2,0)
SELECT bround(25, -1);
-- 20
SELECT bround(525s, -1);
-- 520 smallint
SELECT bround(525L, -3);
-- 1000 bigint
SELECT bround(25y, -2);
-- 0 tinyint
SELECT bround(2147483647, -1);
-- error ARITHMETIC_OVERFLOW (ANSI on)
-- -2147483646 (ANSI off)
SELECT bround(-128y, -1);
-- error ARITHMETIC_OVERFLOW (ANSI on)
-- 126 (ANSI off)
```
`MathExpressionsSuite` asserts `bround(2.5D, 0) = 2.0`, `bround(3.5D, 0) =
4.0`, `bround(-2.5D, 0) = -2.0`, `bround(-0.35D, 1) = -0.4`, `bround(-35, -1) =
-40` and `bround(45.00BD, -1) = 40` (where `round` gives 50).
### Edge cases to test
- NULL `expr`, NULL `d` (literal), non-foldable `d` (analysis error
`DATATYPE_MISMATCH.NON_FOLDABLE_INPUT`).
- Ties in both directions and at several scales: 0.5, 1.5, 2.5, -2.5, 0.125
with `d = 2`, 25/35/45 with `d = -1`.
- Integer types with negative `d`: result stays the same integer type;
overflow with ANSI on fails with `ARITHMETIC_OVERFLOW` (SQLSTATE 22003, message
`Overflow`, config `spark.sql.ansi.enabled`), with ANSI off it wraps (golden
`nonansi/math.sql.out`: `bround(32767s, -1)` is -32766,
`bround(-9223372036854775808L, -1)` is 9223372036854775806). Very negative `d`
returns 0 (`bround(25y, -3)` is 0).
- Integer types with `d >= 0` return the input unchanged.
- FLOAT / DOUBLE: Spark converts with `BigDecimal.valueOf(double)` (the
decimal string from `Double.toString`), rounds HALF_EVEN, then converts back.
So `bround(2.675D, 2)` rounds the decimal 2.675 and gives 2.68, while `(x *
100).round_ties_even() / 100` gives 2.67 because 2.675 * 100 is
267.49999999999997 in binary. FLOAT is widened to DOUBLE first
(`bround(3.1415f, 4)` is `3.1415f`).
- NaN and +/-Infinity are returned unchanged. -0.0 and negative values that
round to zero (`bround(-0.4D, 0)`) return `0.0`, not `-0.0`, because
`BigDecimal` has no negative zero.
- Very large `d` (e.g. 400) on DOUBLE leaves the value unchanged; very
negative `d` gives 0.0.
- DECIMAL: result precision/scale per the formula above, including the cap
at 38. `d` larger than the scale returns the value unchanged with the input
scale. On the negative-`d` path, a result that needs 39 digits throws
`NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION` from `Decimal.set` (derived
from code).
- Column `expr` with scalar `d`, and a scalar `expr`.
### Differences across Spark versions
- 4.0: ANSI on by default, so integer overflow now errors by default
(`ARITHMETIC_OVERFLOW`); it wraps in 3.x defaults.
- 3.4: the decimal positive-scale path used the value's precision in
`toPrecision(decimal.precision, s, mode)`; 3.5+ uses the result type precision.
- `NON_FOLDABLE_INPUT` `inputName` is `scale` in 3.x and `` `scale` `` in
4.0+.
- `Double.toString` changed in JDK 19 to always produce the shortest repr;
on older JDKs a few doubles have a longer digit string, which can change a
HALF_EVEN tie. This applies whenever Spark runs on JDK 17 or older.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala`,
`BRound` and `RoundBase` (`dataType`, `nullSafeEval`, `doGenCode`).
- DataFusion core has no `bround`; its `round` rounds half away from zero
and keeps floating-point arithmetic.
- `datafusion-spark` has `crates/spark/src/function/math/round.rs`
(HALF_UP). Generalise its integer and decimal helpers over the rounding mode
and reuse them, but note two known gaps there that `bround` should not inherit:
decimal results keep the input scale instead of Spark's reduced scale
(apache/datafusion-spark#37), and floating-point rounding multiplies by `10^d`
instead of rounding the shortest decimal representation (Rust's `{}` formatting
of `f64` gives the shortest round-trip digits, which can be fed to a decimal
rounding routine). Use `datafusion.execution.enable_ansi_mode` for the overflow
behaviour and raise `ARITHMETIC_OVERFLOW`.
- Tests: the placeholder `crates/sqllogictest/test_files/math/bround.slt`
exists with commented-out queries; enable and extend it. Document with
`#[user_doc]`.
---
_This issue was generated by an LLM (Claude) from the Apache Spark source
code and tests. Examples come from Spark's `ExpressionDescription` examples,
test suites and `.sql.out` golden files, but please verify them against Spark
before relying on them._
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]