andygrove opened a new pull request, #332:
URL: https://github.com/apache/datafusion-spark/pull/332

   Closes #125.
   
   ## Summary
   
   Adds Spark-compatible `greatest` and `least` 
(`crates/spark/src/function/math/greatest_least.rs`, registered in the `math` 
module, which matches Spark's `group = "math_funcs"`). They replace 
DataFusion's built-ins when the Spark function library is registered.
   
   Spark semantics implemented:
   
   - NULL arguments are skipped. The result is NULL only if every argument is 
NULL.
   - Spark folds left and replaces the running result only with a value that is 
strictly greater (or smaller), so the first of equal arguments wins: 
`greatest(-0.0, 0.0)` is `-0.0` and `least(0.0, -0.0)` is `0.0`.
   - Floats follow `SQLOrderingUtil.compareDoubles` at any depth of arrays and 
structs: NaN (including NaNs with the sign bit set) is greater than every other 
value, and `-0.0` equals `0.0`. The comparison runs on a normalized copy (via 
the existing `array::float_normalize`), and the result keeps the original bits.
   - A NULL inside an array or struct orders before every other value, for both 
`greatest` and `least`.
   - At least two arguments are required.
   - The result is nullable only if every argument is nullable.
   
   Why DataFusion's built-ins aren't enough:
   
   - They order floats by IEEE 754 total order, so `-0.0 < 0.0` and a 
negative-sign NaN is smaller than `-Infinity`.
   - They let a later argument win a tie, and they reduce the constants before 
the columns, so the argument order isn't kept.
   - `least` orders a NULL inside a nested value after every other value.
   - They accept a single argument.
   - They coerce with DataFusion's `comparison_coercion`, which compares a 
number with a string. Spark rejects that.
   
   `invoke_with_args` is fully implemented and doesn't use `simplify`. It 
handles any mix of scalars and arrays. Non-nested types use the vectorized 
`gt`/`lt` kernels, with a constant candidate passed as an Arrow `Scalar`. 
Nested types use `make_comparator`. A NULL constant is skipped, and a call with 
only scalars returns a scalar.
   
   ## Differences from Comet's version
   
   - Comet's UDF covers only floats and nested types 
(`SparkGreatestLeast::handles`) and leaves everything else to DataFusion's 
built-in. This version handles every orderable type.
   - Comet uses `variadic_any` and relies on Spark's planner to coerce the 
arguments. This version does Spark's coercion itself in `coerce_types` (see 
below), so it is correct when DataFusion plans the query.
   - Comet's errors are plain `exec_err!`s. This version raises Spark's error 
classes at planning time: `WRONG_NUM_ARGS.WITHOUT_SUGGESTION`, 
`DATATYPE_MISMATCH.DATA_DIFF_TYPES` and 
`DATATYPE_MISMATCH.INVALID_ORDERING_TYPE`.
   - Comet carries its own `float_semantics` comparator module (about 2000 
lines). This version normalizes floats with the crate's existing 
`float_normalize` and then uses Arrow's comparators. Once floats are 
normalized, Arrow's total order matches Spark's SQL ordering.
   - Comet merges constants ahead of time. This version keeps the argument 
order and skips NULL constants. It's simpler and has the same results.
   - The unit tests are ported: the exhaustive edge-value float test with 
constants in the middle, float32 ties, scalar-only calls, nested lists with 
mismatched field names, and the argument-count check. New tests cover coercion, 
error classes, integers and strings with NULLs, a constant struct, and the NULL 
type.
   
   ## Type rules
   
   The rules follow Spark's `findWiderTypeWithoutStringPromotion` 
(`TypeCoercionHelper`): the tightest common type first, then a wider decimal, 
then arrays and structs element by element.
   
   - Integers and floats widen by `numericPrecedence`. An integer and a FLOAT 
widen to DOUBLE with `datafusion.execution.enable_ansi_mode` on 
(`AnsiTypeCoercion`, Spark 4.x default) and to FLOAT with it off. The mode is 
read through `with_updated_config`, so `SET` takes effect.
   - An integer and a decimal: the decimal if it holds every value of the 
integer type, otherwise `widerDecimalType(DecimalType.forType(int), d)`.
   - Two decimals: `widerDecimalType` bounded with 
`boundedPreferIntegralDigits`, so `DECIMAL(38,10)` and `DECIMAL(38,0)` give 
`DECIMAL(38,0)`.
   - A decimal and a float give DOUBLE.
   - Strings with strings and binary with binary (any Arrow layout).
   - A date and a timestamp give the timestamp. TIMESTAMP_NTZ and TIMESTAMP 
give TIMESTAMP.
   - Arrays widen their elements. Structs widen their fields when they have the 
same field names in the same order.
   - NULL with anything gives the other type. `greatest(NULL, NULL)` is 
NULL-typed.
   - These are a `DATA_DIFF_TYPES` error: a string with a number or date, a 
boolean with a number, and an array with a scalar.
   - Maps (at any depth) are an `INVALID_ORDERING_TYPE` error.
   
   Known divergences:
   
   - Spark matches struct field names ignoring case (`named_struct('a', 1)` and 
`named_struct('A', 2L)` give `struct<a: bigint>`). This version needs an exact 
match, like `array_position` in this crate, because DataFusion can't cast 
between structs whose field names differ only in case.
   - Arrow-only types with no Spark counterpart (unsigned integers, Float16, 
mixed interval units) don't widen to each other. Only identical types are 
accepted.
   - The `DATA_DIFF_TYPES` message leaves out Spark's `Cannot resolve "<sql>" 
due to data type mismatch:` prefix, because the argument SQL isn't available in 
`coerce_types`.
   
   ## Testing
   
   - `crates/sqllogictest/test_files/math/greatest.slt` and `least.slt` cover 
scalars and columns, NULLs, NaN/Infinity, the sign of `-0.0` ties, integer, 
decimal and float widening, strings, booleans, binary, dates and timestamps, 
arrays (NULL elements, length, NaN), structs, all three error classes, and the 
ANSI on/off FLOAT coercion.
   - PySpark 4.2.0 (`crates/sqllogictest/scripts/pyspark_slt_runner.py` on both 
files): 68 pass, 0 differ. The 12 records that use `arrow_typeof` are skipped. 
Their result types were checked separately with `typeof` in a PySpark 4.2.0 
session (for example `decimal(11,1)`, `decimal(22,2)`, `decimal(38,0)`, 
`smallint`, and `double`/`float` for INT and FLOAT under ANSI on/off). The 
exact error messages were checked in the same session.
   - `cargo fmt --all`: clean
   - `cargo clippy --all-targets --all-features -- -D warnings`: clean
   - `cargo test -p datafusion-spark`: 343 passed
   - `cargo test -p datafusion-spark-sqllogictest`: 249 of 249 files passed
   
   While testing I found that this crate's `atan2` doesn't normalize `-0.0` the 
way Spark does (`math.atan2(y + 0.0, x + 0.0)`). That is out of scope here.
   


-- 
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