andygrove commented on code in PR #301:
URL: https://github.com/apache/datafusion-spark/pull/301#discussion_r4585381023
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
+ && is_float32_or_null(t2)
+ && !matches!((t1, t2), (DataType::Null, DataType::Null))
+ {
+ DataType::Float32
+ } else {
+ DataType::Float64
+ };
+
+ Ok(vec![target.clone(), target])
+ }
+
+ fn return_type(&self, arg_types: &[DataType]) -> Result<DataType> {
+ let [t1, _t2] = take_function_args("nanvl", arg_types)?;
+ Ok(t1.clone())
+ }
+
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+
+ fn simplify(
+ &self,
+ args: Vec<Expr>,
+ _info: &SimplifyContext,
+ ) -> Result<ExprSimplifyResult> {
+ let [expr1, expr2] = take_function_args("nanvl", args)?;
+ // `expr1 != expr1` is true exactly when expr1 is NaN (standard IEEE
754
+ // trick), and is NULL when expr1 is NULL. That NULL naturally takes
the
+ // ELSE branch below (a CASE WHEN only takes the THEN branch when the
+ // condition is true), returning expr1 (NULL) without ever evaluating
+ // expr2 — which reproduces Spark's NULL-propagation rule for free.
+ // CASE WHEN's branches are evaluated lazily, so expr2 is only
+ // evaluated on rows where expr1 actually is NaN.
+ let is_nan = expr1.clone().not_eq(expr1.clone());
Review Comment:
**`expr1 != expr1` is never true for NaN in DataFusion: arrow comparison
kernels use IEEE 754 totalOrder, where NaN equals itself. So the rewritten CASE
never takes the THEN branch for column input.**
Checked locally: `SELECT nanvl(c, 9) FROM (VALUES (CAST('NaN' AS DOUBLE)),
(1.0::double)) t(c)` returns `NaN, 1.0` instead of `9, 1.0`, and `SELECT c != c
FROM (VALUES (CAST('NaN' AS DOUBLE))) t(c)` returns false. The same happens for
FLOAT columns and for -NaN. Only constant-folded literal NaN inputs happen to
work. Fix: use `datafusion_functions::math::expr_fn::isnan(expr1.clone())` as
the condition.
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
+ && is_float32_or_null(t2)
+ && !matches!((t1, t2), (DataType::Null, DataType::Null))
+ {
+ DataType::Float32
+ } else {
+ DataType::Float64
+ };
+
+ Ok(vec![target.clone(), target])
+ }
+
+ fn return_type(&self, arg_types: &[DataType]) -> Result<DataType> {
+ let [t1, _t2] = take_function_args("nanvl", arg_types)?;
+ Ok(t1.clone())
+ }
+
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+
+ fn simplify(
+ &self,
+ args: Vec<Expr>,
+ _info: &SimplifyContext,
+ ) -> Result<ExprSimplifyResult> {
+ let [expr1, expr2] = take_function_args("nanvl", args)?;
+ // `expr1 != expr1` is true exactly when expr1 is NaN (standard IEEE
754
+ // trick), and is NULL when expr1 is NULL. That NULL naturally takes
the
+ // ELSE branch below (a CASE WHEN only takes the THEN branch when the
+ // condition is true), returning expr1 (NULL) without ever evaluating
+ // expr2 — which reproduces Spark's NULL-propagation rule for free.
+ // CASE WHEN's branches are evaluated lazily, so expr2 is only
+ // evaluated on rows where expr1 actually is NaN.
+ let is_nan = expr1.clone().not_eq(expr1.clone());
+ let case_expr = when(is_nan, expr2).otherwise(expr1)?;
Review Comment:
**simplify() copies expr1 into three places without checking volatility, so
a volatile expr1 gets evaluated separately in each place and the NaN test
compares two different values.**
Checked locally: `SELECT nanvl(random(), 7) FROM (VALUES (1),(2),(3)) t(x)`
returns 7.0 on every row. `random() != random()` is true, so the THEN branch is
taken. Spark returns the random value. Fix: return
`ExprSimplifyResult::Original(args)` when `expr1.is_volatile()`, and use the
eager kernel in that case.
##########
crates/sqllogictest/test_files/conditional/nanvl.slt:
##########
@@ -0,0 +1,137 @@
+# SPDX-License-Identifier: Apache-2.0
+# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under
one or more contributor license agreements
+
+## Spark-compatible nanvl() tests
+## See https://github.com/apache/datafusion-spark/issues/268
+
+## Basic NaN replacement
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), 123);
+----
+123
+
+## Non-NaN expr1 is returned unchanged; a NULL expr2 doesn't matter
+query R
+SELECT nanvl(5.0::double, CAST(NULL AS DOUBLE));
+----
+5
+
+## NULL expr1 always gives NULL, regardless of expr2
+query R
+SELECT nanvl(CAST(NULL AS DOUBLE), 5.0::double);
+----
+NULL
+
+## NaN expr1 with NULL expr2 gives NULL
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(NULL AS DOUBLE));
+----
+NULL
+
+## NaN expr1 with NaN expr2 gives NaN
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+----
+NaN
+
+## +/-Infinity and -0.0 are not NaN and pass through unchanged, sign preserved
+query R
+SELECT nanvl(CAST('Infinity' AS DOUBLE), 0.0::double);
+----
+Infinity
+
+query R
+SELECT nanvl(CAST('-Infinity' AS DOUBLE), 0.0::double);
+----
+-Infinity
+
+query R
+SELECT nanvl(-0.0::double, 1.0::double);
+----
+0
+
+## FLOAT/FLOAT stays FLOAT; any FLOAT/DOUBLE mix promotes to DOUBLE
+query R
+SELECT nanvl(CAST('NaN' AS FLOAT), CAST('NaN' AS FLOAT));
+----
+NaN
+
+query R
+SELECT nanvl(CAST('NaN' AS FLOAT), 1.0::double);
+----
+1
+
+query R
+SELECT nanvl(1.0::double, CAST('NaN' AS FLOAT));
+----
+1
+
+## Integer and DECIMAL arguments are implicitly cast to DOUBLE
+query R
+SELECT nanvl(1, 2);
+----
+1
+
+query R
+SELECT nanvl(CAST(1 AS DECIMAL(10,2)), 2);
+----
+1
+
+## Wider precision/scale DECIMAL still coerces to DOUBLE
+query R
+SELECT nanvl(CAST(123.456 AS DECIMAL(20,5)), 999::double);
+----
+123.456
+
+## DECIMAL on expr2's side also coerces, and only matters when expr1 is NaN
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(42.5 AS DECIMAL(10,2)));
+----
+42.5
+
+## DECIMAL with negative scale / small precision
+query R
+SELECT nanvl(CAST(7 AS DECIMAL(3,0)), 1::double);
+----
+7
+
+## Both arguments DECIMAL: still promotes to DOUBLE (never FLOAT), since
DECIMAL
+## is never the FLOAT/FLOAT special case
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(3.14 AS DECIMAL(5,2)));
+----
+3.14
+
+## Scalar and array combinations
Review Comment:
**No test feeds a NaN value from a column or array. Every NaN case uses a
literal, which the simplifier constant-folds, so the broken NaN check on the
normal execution path is never tested.**
The 'Scalar and array combinations' and ANSI lazy-evaluation tests only use
0.0/1.0 or 0/1 rows, so the THEN branch never runs for array input. The suite
passes even though `nanvl(col, x)` never replaces NaN. Add a VALUES row with
CAST('NaN' AS DOUBLE), and one with FLOAT, and expect the replacement value.
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
Review Comment:
**A NULL expr1 with a FLOAT expr2 coerces to FLOAT. Spark coerces a NullType
left argument to DOUBLE (the TypeCollection default), and
FunctionArgumentConversion then widens the FLOAT right side to DOUBLE.**
`SELECT arrow_typeof(nanvl(NULL, CAST(1 AS FLOAT)))` returns Float32
(checked locally). Spark's `nanvl(NULL, CAST(1 AS FLOAT))` has type DOUBLE. The
schema differs from Spark. `nanvl(FLOAT, NULL)` -> FLOAT is correct, because
Spark casts a null right argument to the left type, so the rule is not
symmetric.
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
+ && is_float32_or_null(t2)
+ && !matches!((t1, t2), (DataType::Null, DataType::Null))
+ {
+ DataType::Float32
+ } else {
+ DataType::Float64
+ };
Review Comment:
**coerce_types sends every non-FLOAT type to Float64 without checking it.
Types Spark rejects (BOOLEAN, for example) are accepted whenever arrow can cast
them.**
`SELECT nanvl(true, 1)` returns 1.0 (checked locally). Spark rejects it with
DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE, because BOOLEAN cannot be implicitly
cast to DOUBLE or FLOAT. Restrict the accepted types to numeric, decimal,
string and null, and return plan_err for the rest.
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
+ && is_float32_or_null(t2)
+ && !matches!((t1, t2), (DataType::Null, DataType::Null))
+ {
+ DataType::Float32
+ } else {
+ DataType::Float64
+ };
Review Comment:
**STRING arguments go through a strict DataFusion cast to DOUBLE. With ANSI
off, an invalid numeric string makes the whole query fail instead of returning
NULL as Spark's legacy cast does.**
With enable_ansi_mode=false, `SELECT nanvl('abc', 1)` fails at planning with
"Optimizer rule 'simplify_expressions' failed ... Cannot cast string 'abc' to
value of Float64 type" (checked locally). Spark non-ANSI gives NULL for the
cast, so nanvl returns NULL. The doc says STRING is supported, but there is no
test with a string argument.
##########
crates/sqllogictest/test_files/conditional/nanvl.slt:
##########
@@ -0,0 +1,137 @@
+# SPDX-License-Identifier: Apache-2.0
+# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under
one or more contributor license agreements
+
+## Spark-compatible nanvl() tests
+## See https://github.com/apache/datafusion-spark/issues/268
+
+## Basic NaN replacement
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), 123);
+----
+123
+
+## Non-NaN expr1 is returned unchanged; a NULL expr2 doesn't matter
+query R
+SELECT nanvl(5.0::double, CAST(NULL AS DOUBLE));
+----
+5
+
+## NULL expr1 always gives NULL, regardless of expr2
+query R
+SELECT nanvl(CAST(NULL AS DOUBLE), 5.0::double);
+----
+NULL
+
+## NaN expr1 with NULL expr2 gives NULL
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(NULL AS DOUBLE));
+----
+NULL
+
+## NaN expr1 with NaN expr2 gives NaN
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+----
+NaN
+
+## +/-Infinity and -0.0 are not NaN and pass through unchanged, sign preserved
+query R
+SELECT nanvl(CAST('Infinity' AS DOUBLE), 0.0::double);
+----
+Infinity
+
+query R
+SELECT nanvl(CAST('-Infinity' AS DOUBLE), 0.0::double);
+----
+-Infinity
+
+query R
+SELECT nanvl(-0.0::double, 1.0::double);
Review Comment:
**The -0.0 test expects `0`, so it cannot tell -0.0 from 0.0. The 'sign
preserved' claim in the comment and in the user_doc is not actually tested.**
If -0.0 were normalised to 0.0, the result would still print `0` and the
test would still pass. Assert on something sign-sensitive, such as `1 /
nanvl(-0.0::double, 1.0::double)` = -Infinity, or a CAST to VARCHAR.
##########
crates/spark/src/function/conditional/nanvl.rs:
##########
@@ -0,0 +1,200 @@
+// SPDX-License-Identifier: Apache-2.0
+// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF)
under one or more contributor license agreements
+
+use std::sync::Arc;
+
+use arrow::array::{Array, ArrayRef, AsArray, Float32Array, Float64Array};
+use arrow::datatypes::{DataType, Float32Type, Float64Type};
+use datafusion_common::Result;
+use datafusion_common::exec_err;
+use datafusion_common::utils::take_function_args;
+use datafusion_expr::expr_fn::when;
+use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
+use datafusion_expr::{
+ ColumnarValue, Documentation, Expr, ScalarFunctionArgs, ScalarUDFImpl,
Signature,
+ Volatility,
+};
+use datafusion_functions::utils::make_scalar_function;
+use datafusion_macros::user_doc;
+
+/// Spark-compatible `nanvl` expression.
+///
+/// <https://spark.apache.org/docs/latest/api/sql/index.html#nanvl>
+#[user_doc(
+ doc_section(label = "Conditional Functions"),
+ description = "Returns `expr1` if it is not NaN, or `expr2` if `expr1` is
NaN. Unlike \
+ DataFusion's built-in `nanvl`, which evaluates both arguments eagerly,
this function \
+ only evaluates `expr2` when `expr1` is NaN, matching Spark's
short-circuiting behavior. \
+ If `expr1` is NULL the result is NULL, even when `expr2` is not NULL;
a NULL `expr2` \
+ only matters when `expr1` is NaN. Both arguments are implicitly cast
to DOUBLE (integer, \
+ DECIMAL and STRING arguments included); if both arguments are FLOAT
the result is FLOAT, \
+ otherwise the result is DOUBLE. +/-Infinity and -0.0 are not NaN and
are returned \
+ unchanged, with the sign of zero preserved.",
+ syntax_example = "nanvl(expr1, expr2)",
+ sql_example = r#"```sql
+> SELECT nanvl(cast('NaN' as double), 123);
+123
+> SELECT nanvl(5.0, CAST(NULL AS DOUBLE));
+5
+> SELECT nanvl(CAST(NULL AS DOUBLE), 5.0);
+NULL
+> SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+NaN
+```"#,
+ argument(
+ name = "expr1",
+ description = "Numeric expression, cast to DOUBLE (or FLOAT if both
arguments are \
+ FLOAT). NULL returns NULL regardless of expr2."
+ ),
+ argument(
+ name = "expr2",
+ description = "Numeric expression, cast to match expr1. Only
evaluated, and only \
+ matters, when expr1 is NaN."
+ )
+)]
+#[derive(Debug, PartialEq, Eq, Hash)]
+pub struct SparkNanvl {
+ signature: Signature,
+}
+
+impl Default for SparkNanvl {
+ fn default() -> Self {
+ Self::new()
+ }
+}
+
+impl SparkNanvl {
+ pub fn new() -> Self {
+ Self {
+ signature: Signature::user_defined(Volatility::Immutable),
+ }
+ }
+}
+
+impl ScalarUDFImpl for SparkNanvl {
+ fn name(&self) -> &str {
+ "nanvl"
+ }
+
+ fn signature(&self) -> &Signature {
+ &self.signature
+ }
+
+ fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
+ let [t1, t2] = take_function_args("nanvl", arg_types)?;
+
+ // Spark coerces non-FLOAT/DOUBLE arguments (ints, decimals, strings)
to DOUBLE.
+ // FLOAT is only kept when *both* sides are FLOAT (or NULL, which
adapts to the
+ // other side); any other combination, including FLOAT mixed with
DOUBLE,
+ // promotes to DOUBLE.
+ let is_float32_or_null =
+ |dt: &DataType| matches!(dt, DataType::Float32 | DataType::Null);
+
+ let target = if is_float32_or_null(t1)
+ && is_float32_or_null(t2)
+ && !matches!((t1, t2), (DataType::Null, DataType::Null))
+ {
+ DataType::Float32
+ } else {
+ DataType::Float64
+ };
+
+ Ok(vec![target.clone(), target])
+ }
+
+ fn return_type(&self, arg_types: &[DataType]) -> Result<DataType> {
+ let [t1, _t2] = take_function_args("nanvl", arg_types)?;
+ Ok(t1.clone())
+ }
+
+ fn documentation(&self) -> Option<&Documentation> {
+ self.doc()
+ }
+
+ fn simplify(
+ &self,
+ args: Vec<Expr>,
+ _info: &SimplifyContext,
+ ) -> Result<ExprSimplifyResult> {
+ let [expr1, expr2] = take_function_args("nanvl", args)?;
+ // `expr1 != expr1` is true exactly when expr1 is NaN (standard IEEE
754
+ // trick), and is NULL when expr1 is NULL. That NULL naturally takes
the
+ // ELSE branch below (a CASE WHEN only takes the THEN branch when the
+ // condition is true), returning expr1 (NULL) without ever evaluating
+ // expr2 — which reproduces Spark's NULL-propagation rule for free.
+ // CASE WHEN's branches are evaluated lazily, so expr2 is only
+ // evaluated on rows where expr1 actually is NaN.
+ let is_nan = expr1.clone().not_eq(expr1.clone());
+ let case_expr = when(is_nan, expr2).otherwise(expr1)?;
+ Ok(ExprSimplifyResult::Simplified(case_expr))
+ }
+
+ // This eager path is used only when `simplify()` doesn't run (e.g. Comet's
+ // physical execution, which bypasses DataFusion's logical optimizer).
+ // DataFusion's query engine instead takes the lazy CASE-expression rewrite
+ // from `simplify()` above.
+ fn invoke_with_args(&self, args: ScalarFunctionArgs) ->
Result<ColumnarValue> {
+ match args.args[0].data_type() {
+ DataType::Float32 => {
+ make_scalar_function(spark_nanvl_f32, vec![])(&args.args)
+ }
+ DataType::Float64 => {
+ make_scalar_function(spark_nanvl_f64, vec![])(&args.args)
+ }
+ other => exec_err!("Unsupported data type {other} for function
nanvl"),
+ }
+ }
+}
+
+fn spark_nanvl_f64(args: &[ArrayRef]) -> Result<ArrayRef> {
+ let [expr1, expr2] = take_function_args("nanvl", args)?;
+ let expr1 = expr1.as_primitive::<Float64Type>();
+ let expr2 = expr2.as_primitive::<Float64Type>();
+
+ let result: Float64Array = (0..expr1.len())
Review Comment:
**spark_nanvl_f64 and spark_nanvl_f32 are the same function apart from the
type parameter, and both build the result with a per-row index loop and
`.collect()`.**
Two copies of the same logic must be kept in sync, and the per-row Option
collect is slower than a vectorised kernel. Use one generic `fn spark_nanvl<T:
ArrowPrimitiveType>() where T::Native: num_traits::Float`, or build an is-NaN
BooleanArray and call `arrow::compute::kernels::zip::zip(&mask, expr2, expr1)`.
##########
crates/sqllogictest/test_files/conditional/nanvl.slt:
##########
@@ -0,0 +1,137 @@
+# SPDX-License-Identifier: Apache-2.0
+# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for
additional information regarding copyright ownership
+# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under
one or more contributor license agreements
+
+## Spark-compatible nanvl() tests
+## See https://github.com/apache/datafusion-spark/issues/268
+
+## Basic NaN replacement
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), 123);
+----
+123
+
+## Non-NaN expr1 is returned unchanged; a NULL expr2 doesn't matter
+query R
+SELECT nanvl(5.0::double, CAST(NULL AS DOUBLE));
+----
+5
+
+## NULL expr1 always gives NULL, regardless of expr2
+query R
+SELECT nanvl(CAST(NULL AS DOUBLE), 5.0::double);
+----
+NULL
+
+## NaN expr1 with NULL expr2 gives NULL
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(NULL AS DOUBLE));
+----
+NULL
+
+## NaN expr1 with NaN expr2 gives NaN
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST('NaN' AS DOUBLE));
+----
+NaN
+
+## +/-Infinity and -0.0 are not NaN and pass through unchanged, sign preserved
+query R
+SELECT nanvl(CAST('Infinity' AS DOUBLE), 0.0::double);
+----
+Infinity
+
+query R
+SELECT nanvl(CAST('-Infinity' AS DOUBLE), 0.0::double);
+----
+-Infinity
+
+query R
+SELECT nanvl(-0.0::double, 1.0::double);
+----
+0
+
+## FLOAT/FLOAT stays FLOAT; any FLOAT/DOUBLE mix promotes to DOUBLE
+query R
+SELECT nanvl(CAST('NaN' AS FLOAT), CAST('NaN' AS FLOAT));
+----
+NaN
+
+query R
+SELECT nanvl(CAST('NaN' AS FLOAT), 1.0::double);
+----
+1
+
+query R
+SELECT nanvl(1.0::double, CAST('NaN' AS FLOAT));
+----
+1
+
+## Integer and DECIMAL arguments are implicitly cast to DOUBLE
+query R
+SELECT nanvl(1, 2);
+----
+1
+
+query R
+SELECT nanvl(CAST(1 AS DECIMAL(10,2)), 2);
+----
+1
+
+## Wider precision/scale DECIMAL still coerces to DOUBLE
+query R
+SELECT nanvl(CAST(123.456 AS DECIMAL(20,5)), 999::double);
+----
+123.456
+
+## DECIMAL on expr2's side also coerces, and only matters when expr1 is NaN
+query R
+SELECT nanvl(CAST('NaN' AS DOUBLE), CAST(42.5 AS DECIMAL(10,2)));
+----
+42.5
+
+## DECIMAL with negative scale / small precision
+query R
+SELECT nanvl(CAST(7 AS DECIMAL(3,0)), 1::double);
+----
+7
+
+## Both arguments DECIMAL: still promotes to DOUBLE (never FLOAT), since
DECIMAL
Review Comment:
**Two test comments do not match the queries. 'DECIMAL with negative scale'
uses DECIMAL(3,0), and 'Both arguments DECIMAL' passes a DOUBLE as expr1.**
The negative-scale and DECIMAL/DECIMAL cases are documented but never run. A
DECIMAL/DECIMAL regression, such as a wrong coercion target, would go
unnoticed. Fix the queries to match the comments (for example `nanvl(CAST(1 AS
DECIMAL(5,2)), CAST(2 AS DECIMAL(5,2)))`), or reword the comments.
--
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]