andygrove opened a new issue, #268:
URL: https://github.com/apache/datafusion-spark/issues/268
Part of #174 and apache/datafusion-comet#7300.
### What the function does
`nanvl(expr1, expr2)` returns `expr1` if it is not NaN, otherwise `expr2`.
If `expr1` is NULL the result is NULL (even when `expr2` is not). `expr2` is
only evaluated when `expr1` is NaN. Available since 1.5.0.
Both arguments accept DOUBLE or FLOAT (`ImplicitCastInputTypes` with
`TypeCollection(DoubleType, FloatType)`), so integer, DECIMAL and STRING
arguments are implicitly cast to DOUBLE. Type coercion then makes the two sides
agree: FLOAT with DOUBLE casts the FLOAT side to DOUBLE, and an untyped NULL
`expr2` is cast to `expr1`'s type. The result type is `expr1`'s type after
coercion (FLOAT only when both are FLOAT).
### Examples
```sql
SELECT nanvl(cast('NaN' as double), 123);
-- 123.0
SELECT nanvl(5.0D, CAST(NULL AS DOUBLE));
-- 5.0
SELECT nanvl(CAST(NULL AS DOUBLE), 5.0D);
-- NULL
SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(NULL AS DOUBLE));
-- NULL
SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
-- NaN
-- conditional_t has an INT column c2 (values 0, 1, 1, 0)
SELECT nanvl(c2, 1/0) FROM conditional_t;
-- 0.0, 0.0, 1.0, 1.0 (sorted; double; no DIVIDE_BY_ZERO even with ANSI on)
SELECT nanvl(1-0, 1/0) FROM conditional_t;
-- 1.0 for every row
```
### Edge cases to test
- NULL `expr1` gives NULL regardless of `expr2`; NULL `expr2` matters only
when `expr1` is NaN.
- `expr1` = NaN for FLOAT and DOUBLE, including NaNs with different bit
patterns; `+/-Infinity` and `-0.0` are not NaN and are returned unchanged (sign
of zero preserved).
- Mixed types: `nanvl(float, double)` and `nanvl(double, float)` return
DOUBLE; `nanvl(float, float)` returns FLOAT; integer and DECIMAL arguments
become DOUBLE.
- Lazy evaluation of `expr2`: the `nanvl(c2, 1/0)` golden result
(`conditional-functions.sql.out`) shows that an error in `expr2` is not raised
when `expr1` is not NaN. A scalar UDF receives both arguments already
evaluated, so with `datafusion.execution.enable_ansi_mode` on `1/0` would fail
first. Either plan `nanvl` as `CASE WHEN isnan(x) THEN y ELSE x END`
(DataFusion's `CASE` evaluates branches lazily) or document the difference.
- Scalar and array combinations for both arguments.
### Differences across Spark versions
None found (3.4 through 4.2).
### Implementation notes
- Spark:
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/nullExpressions.scala`,
`NaNvl`; type coercion in
`sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionHelper.scala`.
- DataFusion core has `nanvl` (`datafusion/functions/src/math/nanvl.rs`)
with the same per-row semantics (NULL `x` returns NULL, NaN `x` returns `y`),
coercing non-float arguments to Float64 and returning Float32 only for two
Float32 inputs. It evaluates `y` eagerly. It is likely enough to verify it
against the cases above and re-export it (or wrap it with Spark's coercion)
from `datafusion-spark`, rather than writing a new kernel.
- Belongs with the conditional functions
(`crates/spark/src/function/conditional/`). Add
`crates/sqllogictest/test_files/conditional/nanvl.slt` and 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]