andygrove opened a new issue, #262:
URL: https://github.com/apache/datafusion-spark/issues/262
Part of #174 and apache/datafusion-comet#7300.
### What the function does
`floor(expr, scale)` rounds `expr` down (toward negative infinity) to
`scale` decimal places; a negative `scale` rounds to the left of the decimal
point. Spark builds it through `FloorExpressionBuilder`: one argument gives
`Floor` (already implemented as `crates/spark/src/function/math/floor.rs`), two
arguments give `RoundFloor`, which uses `RoundBase` with `RoundingMode.FLOOR`.
Available since 3.3.0. The matching `ceil(expr, scale)` form is already tracked
in apache/datafusion-spark#15 and apache/datafusion-spark#22 and should share
the implementation.
`scale` must be a foldable, non-NULL expression whose type is exactly INT
(checked before type coercion, so `2L` or `CAST(2 AS SMALLINT)` is rejected).
`expr` is implicitly cast to DECIMAL (`inputTypes = Seq(DecimalType,
IntegerType)`): TINYINT to `DECIMAL(3,0)`, SMALLINT to `DECIMAL(5,0)`, INT to
`DECIMAL(10,0)`, BIGINT to `DECIMAL(20,0)`, FLOAT to `DECIMAL(14,7)`, DOUBLE to
`DECIMAL(30,15)`. For an input `DECIMAL(p, s)` the result is `DECIMAL(min(p - s
+ 1 + min(s, scale), 38), min(s, scale))` when `scale >= 0`, and
`DECIMAL(min(max(p - s + 1, -scale + 1), 38), 0)` when `scale < 0`. The result
is always nullable.
### Examples
```sql
SELECT floor(3.1411, 3);
-- 3.141
SELECT floor(3.1411, -3);
-- 0
SELECT floor(-0.35, 1);
-- -0.4 decimal(2,1)
SELECT floor(-35, -1);
-- -40 decimal(11,0)
SELECT floor(5, 0);
-- 5 decimal(11,0)
SELECT floor(-9.9, 0);
-- -10 decimal(2,0)
SELECT floor(CAST(-99 AS DECIMAL(2, 0)), -1);
-- -100 decimal(3,0)
SELECT floor(3.14115, -3);
-- 0 decimal(4,0)
SELECT floor(2.5, null);
-- error NON_FOLDABLE_ARGUMENT
```
### Edge cases to test
- NULL `expr` returns NULL. NULL or non-literal `scale` fails analysis:
`NON_FOLDABLE_ARGUMENT` (SQLSTATE 42K08) ``The function `floor` requires the
parameter `scale` to be a foldable expression of the type "INT", but the actual
argument is a non-foldable.`` (also for `floor(2.5, 'a')`). Three arguments:
`WRONG_NUM_ARGS.WITHOUT_SUGGESTION`.
- Result types for every input type, including the `p - s + 1` growth and
the cap at 38 (`floor(CAST(-99 AS DECIMAL(2,0)), -1)` is `DECIMAL(3,0)`).
- `scale` larger than the input scale leaves the value unchanged
(`RoundFloor(bdPi, 8..10)` returns `bdPi` in `MathExpressionsSuite`).
- Negative numbers round away from zero: `floor(-0.1, 0)` is -1,
`floor(-2.5, 0)` is -3, `floor(135.135, -2)` is 100.
- Negative scale beyond the integral digits: `floor(3.1411, -3)` is 0,
`floor(-3.1411, -3)` is -1000.
- Overflow on the negative-scale path when the precision is already 38:
`floor(CAST(-99999999999999999999999999999999999999 AS DECIMAL(38,0)), -1)`
needs 39 digits and throws `NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION` from
`Decimal.set` (derived from code, regardless of ANSI mode).
- FLOAT / DOUBLE inputs go through the implicit cast first:
- Values that do not fit (`|x| >= 10^7` for FLOAT, `|x| >= 10^15` for
DOUBLE) are NULL with ANSI off and fail with
`NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION` with `spark.sql.ansi.enabled=true`
(the 4.0+ default).
- NaN and +/-Infinity become NULL in both modes (the cast catches
`NumberFormatException`).
- The cast uses `BigDecimal.valueOf(double)` (shortest decimal string),
then HALF_UP to the cast scale, so FLOAT `0.1f` becomes `0.1000000`.
- BIGINT inputs near `Long.MaxValue` fit `DECIMAL(20,0)`.
- Decimal32/64/256 Arrow inputs if accepted (Spark only has 128-bit-range
decimals up to precision 38).
### Differences across Spark versions
- 3.4 and 3.5: a NULL or non-foldable `scale` fails with
`_LEGACY_ERROR_TEMP_1100` (``The 'scale' parameter of function 'floor' needs to
be a int literal.``); 4.0+ uses `NON_FOLDABLE_ARGUMENT`.
- 3.4: the positive-scale path called
`decimal.toPrecision(decimal.precision, s, mode)` (the value's precision); 3.5+
uses the result type precision `p`.
- 4.0: ANSI on by default, which changes the FLOAT/DOUBLE overflow case from
NULL to an error.
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/mathExpressions.scala`,
`FloorExpressionBuilder`, `CeilFloorExpressionBuilderBase`, `RoundFloor` and
`RoundBase` (`dataType`, `nullSafeEval`).
- DataFusion core `floor` takes one argument and keeps the input type.
DataFusion's `trunc` / `round` with a precision argument use different rounding
modes and keep floating-point types.
- In `datafusion-spark`, extend `crates/spark/src/function/math/floor.rs` to
accept the optional `scale` (decided in `return_field_from_args` from the
literal), and share a `RoundBase`-style decimal rounding helper with `ceil` and
`round` (`round.rs` already has HALF_UP decimal rounding on unscaled integers;
add FLOOR/CEILING modes). Implement the implicit casts in the signature (or
require the caller to cast), with DOUBLE/FLOAT conversion matching Spark's cast.
- Tests: extend `crates/sqllogictest/test_files/math/floor.slt` with the
two-argument cases, and update the `#[user_doc]` on `SparkFloor` (then run
`dev/update_function_docs.sh`).
---
_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]