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]

Reply via email to