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]

Reply via email to