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]

Reply via email to