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