You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/19 19:07:07 UTC

(datafusion) 01/01: fix: nvl2 result type should not depend on its first argument (#26498)

This is an automated email from the ASF dual-hosted git repository.

github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-26498-7e102d74cd3b48aba54e958aa64977f912860862
in repository https://gitbox.apache.org/repos/asf/datafusion.git

commit a9129e758dea2c6a26953b36020c06c156689244
Author: Yu-Chuan Hung <86...@users.noreply.github.com>
AuthorDate: Mon Oct 19 19:06:56 2026 +0000

    fix: nvl2 result type should not depend on its first argument (#26498)
    
    ## Which issue does this PR close?
    
    - Closes #26024.
    
    ## Rationale for this change
    
    The result type of `nvl2(x, a, b)` depended on the type of `x`, even
    though `x` is only checked for NULL. This caused decimal results to be
    widened (e.g. `Decimal128(22, 2)`) or turned into strings, and made
    timestamp results fail during planning or optimization. The equivalent
    `CASE WHEN x IS NOT NULL THEN a ELSE b END` worked in all of these
    cases.
    
    ## What changes are included in this PR?
    
    - `NVL2Func::coerce_types` now finds the common type of `a` and `b`
    only, using `type_union_coercion` (the same rule `CASE` uses for its
    THEN/ELSE branches). `x` keeps its original type.
    - If `a` and `b` have no common type, it now returns a plan error
    instead of an internal error.
    
    ## What is the testing strategy for this PR?
    
    Added sqllogictest cases to `nvl2.slt` that cover the reproducers from
    the issue (decimal with int/float/string tests, timestamp with
    int/string-literal tests). Each case checks both the value and
    `arrow_typeof`.
    
    ## Are there any user-facing changes?
    
    Yes. `nvl2` now returns the same type as the equivalent `CASE`
    expression. Queries that used to get a widened decimal or `Utf8` result
    now get the type of `a`/`b`, and the timestamp cases that used to error
    now work. No public API changes.
---
 datafusion/functions/src/core/nvl2.rs       | 25 +++++------
 datafusion/sqllogictest/test_files/nvl2.slt | 68 +++++++++++++++++++++++++++++
 2 files changed, 78 insertions(+), 15 deletions(-)

diff --git a/datafusion/functions/src/core/nvl2.rs b/datafusion/functions/src/core/nvl2.rs
index d68296d9b8..671e1d74f5 100644
--- a/datafusion/functions/src/core/nvl2.rs
+++ b/datafusion/functions/src/core/nvl2.rs
@@ -16,7 +16,7 @@
 // under the License.
 
 use arrow::datatypes::{DataType, Field, FieldRef};
-use datafusion_common::{Result, internal_err, utils::take_function_args};
+use datafusion_common::{Result, internal_err, plan_err, utils::take_function_args};
 use datafusion_expr::{
     ColumnarValue, Documentation, Expr, ReturnFieldArgs, ScalarFunctionArgs,
     ScalarUDFImpl, Signature, Volatility,
@@ -125,20 +125,15 @@ impl ScalarUDFImpl for NVL2Func {
 
     fn coerce_types(&self, arg_types: &[DataType]) -> Result<Vec<DataType>> {
         let [tested, if_non_null, if_null] = take_function_args(self.name(), arg_types)?;
-        let new_type =
-            [if_non_null, if_null]
-                .iter()
-                .try_fold(tested.clone(), |acc, x| {
-                    // `type_union_coercion` finds a loose common type; the actual
-                    // coercion is done by `maybe_data_types`.
-                    let coerced_type = type_union_coercion(&acc, x);
-                    if let Some(coerced_type) = coerced_type {
-                        Ok(coerced_type)
-                    } else {
-                        internal_err!("Coercion from {acc} to {x} failed.")
-                    }
-                })?;
-        Ok(vec![new_type; arg_types.len()])
+        // `type_union_coercion` is order-sensitive (e.g. for timezones); start from
+        // `if_null` to match `CASE`, which coerces starting from the ELSE type.
+        let Some(new_type) = type_union_coercion(if_null, if_non_null) else {
+            return plan_err!(
+                "nvl2 cannot coerce {if_non_null} and {if_null} to a common type"
+            );
+        };
+
+        Ok(vec![tested.clone(), new_type.clone(), new_type])
     }
 
     fn documentation(&self) -> Option<&Documentation> {
diff --git a/datafusion/sqllogictest/test_files/nvl2.slt b/datafusion/sqllogictest/test_files/nvl2.slt
index 0dcb82bdf2..75c007b801 100644
--- a/datafusion/sqllogictest/test_files/nvl2.slt
+++ b/datafusion/sqllogictest/test_files/nvl2.slt
@@ -118,3 +118,71 @@ query ?
 SELECT NVL2(NULL, NULL, NULL);
 ----
 NULL
+
+
+# issue 26024 - nvl2 result type should not depend on its first argument
+statement ok
+CREATE TABLE t AS SELECT
+  1 AS i, 1.5 AS f, 'a' AS s,
+  CAST(1.23 AS DECIMAL(5,2)) AS d1, CAST(4.56 AS DECIMAL(5,2)) AS d2,
+  TIMESTAMP '2024-01-01 10:00:00' AS ts1, TIMESTAMP '2024-01-02 10:00:00' AS ts2;
+
+query T
+SELECT arrow_typeof(CASE WHEN i IS NOT NULL THEN d1 ELSE d2 END) AS case_type FROM t;
+----
+Decimal128(5, 2)
+
+query RT
+SELECT nvl2(i, d1, d2) AS v, arrow_typeof(nvl2(i, d1, d2)) AS v_type FROM t;
+----
+1.23 Decimal128(5, 2)
+
+query RT
+SELECT nvl2(f, d1, d2) AS v, arrow_typeof(nvl2(f, d1, d2)) AS v_type FROM t;
+----
+1.23 Decimal128(5, 2)
+
+query RT
+SELECT nvl2(s, d1, d2) AS v, arrow_typeof(nvl2(s, d1, d2)) AS v_type FROM t;
+----
+1.23 Decimal128(5, 2)
+
+query PT
+SELECT nvl2(i, ts1, ts2) AS v, arrow_typeof(nvl2(i, ts1, ts2)) AS v_type FROM t;
+----
+2024-01-01T10:00:00 Timestamp(ns)
+
+query PT
+SELECT nvl2('a', ts1, ts2) AS v, arrow_typeof(nvl2('a', ts1, ts2)) AS v_type FROM t;
+----
+2024-01-01T10:00:00 Timestamp(ns)
+
+statement ok
+DROP TABLE t;
+
+# nvl2 should pick the same timezone as the equivalent CASE expression
+# when its branches have different timezones
+statement ok
+CREATE TABLE t_tz AS
+SELECT i, a, arrow_cast(a, 'Timestamp(Nanosecond, Some("+05:00"))') AS b
+FROM (SELECT 1 AS i,
+             arrow_cast(TIMESTAMP '2024-01-01 22:00:00', 'Timestamp(Nanosecond, Some("+00:00"))') AS a);
+
+query PTD
+SELECT CASE WHEN i IS NOT NULL THEN a ELSE b END AS v,
+       arrow_typeof(CASE WHEN i IS NOT NULL THEN a ELSE b END) AS v_type,
+       CAST(CASE WHEN i IS NOT NULL THEN a ELSE b END AS DATE) AS v_date
+FROM t_tz;
+----
+2024-01-02T03:00:00+05:00 Timestamp(ns, "+05:00") 2024-01-02
+
+query PTD
+SELECT nvl2(i, a, b) AS v,
+       arrow_typeof(nvl2(i, a, b)) AS v_type,
+       CAST(nvl2(i, a, b) AS DATE) AS v_date
+FROM t_tz;
+----
+2024-01-02T03:00:00+05:00 Timestamp(ns, "+05:00") 2024-01-02
+
+statement ok
+DROP TABLE t_tz;


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org