Skip to content

Commit d75fd7b

Browse files
DeviousCardiclaude
andauthored
fix: do not evaluate a volatile coalesce or nvl argument twice (apache#25561)
## Which issue does this PR close? - Closes apache#25477. ## Rationale for this change `coalesce` is rewritten by `simplify` into `CASE WHEN a IS NOT NULL THEN a ELSE b END`, which names every argument but the last one twice. For a volatile argument those two mentions are two independent draws, so the null test and the returned value disagree: ```sql -- both draws are independent, so COALESCE can return NULL, -- which a single evaluation can never produce SELECT count(*), count(c) FROM (SELECT coalesce(nullif(floor(random() * 2), 0), neg) AS c FROM w); +--------+----------+ | 100000 | 75117 | +--------+----------+ ``` 24,883 NULLs, which is exactly `P(draw1 != 0) * P(draw2 == 0) = 0.5 * 0.5`. With a non-null literal as the last argument the planner marks the output non-nullable and it fails outright: ``` Arrow error: Invalid argument error: Column 'c' is declared as non-nullable but contains null values ``` `nvl` delegates to `CoalesceFunc`, so it has the same bug. ## What changes are included in this PR? `simplify` still rewrites `coalesce` into the fast `CASE`, but it stops at the first volatile argument that is not the last one and leaves the remaining arguments as a `coalesce` call, so each argument is named once: ```sql coalesce(a, v, c) --> CASE WHEN a IS NOT NULL THEN a ELSE coalesce(v, c) END coalesce(v, b) --> unchanged ``` That leftover call is evaluated by `invoke_with_args`, which gets back the runtime kernel removed in e5dcc8c (apache#17357) and evaluates each argument exactly once. Calls with no volatile argument before the last plan exactly as before and never reach the kernel. A volatile *last* argument is named once by the `ELSE`, so it needs no guard. `nvl` delegates to `CoalesceFunc` and is fixed by the same change. `nvl2` names each argument once and is unaffected. As discussed in review, a scalar binding function (`apply(v, x -> CASE WHEN x IS NOT NULL THEN x ELSE coalesce(b, c) END)`) built on the new lambda support would be a cleaner long-term replacement for the kernel. It needs a new always-registered higher-order function and has Substrait implications, so I have kept this PR to the narrow fix. ## What is the testing strategy for this PR? `sqllogictest` cases in `coalesce.slt`. To get exact expected rows from a volatile argument they use `case when random() >= 0 then x end`, which is volatile but always equals `x`. They cover array arguments, a scalar fallback, all-null input, strings, and a volatile middle argument. The 100k-row `random()` reproducer above is kept as one query covering both `coalesce` and `nvl`, asserting `count(c) = count(*)`. ## Are there any user-facing changes? The bug fix itself (no more NULLs or non-nullable errors from a volatile `coalesce`/`nvl`). One behaviour change: in a call with a volatile non-final argument, that argument and the ones after it are evaluated eagerly by the kernel rather than lazily by `CASE`. For example, `coalesce(random(), y / x)` now evaluates `y / x`. Arguments before the first volatile one, and all non-volatile calls, keep the lazy `CASE` behaviour. ## AI disclosure Following the [DataFusion AI policy](https://datafusion.apache.org/contributor-guide/ai-policy.html) and the [ASF Generative Tooling Guidance](https://www.apache.org/legal/generative-tooling.html): I used Claude Code to help write the code, tests and this description, and I reviewed all of it. The restored kernel is DataFusion's own code from before apache#17357; no third-party code is included. Things I would like a reviewer to check: - Whether the eager evaluation of the arguments after a volatile one is acceptable, or whether it should be documented on `coalesce`/`nvl`. - The `case when random() >= 0 then x end` trick in the tests relies on `CASE` with a volatile condition staying volatile through optimization. --------- Co-authored-by: Claude Opus 5 (1M context) <[email protected]>
1 parent 676c003 commit d75fd7b

2 files changed

Lines changed: 168 additions & 11 deletions

File tree

‎datafusion/functions/src/core/coalesce.rs‎

Lines changed: 81 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -2,10 +2,14 @@
22
// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership
33
// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements
44

5+
use arrow::array::{BooleanArray, new_null_array};
6+
use arrow::compute::kernels::zip::zip;
7+
use arrow::compute::{and, is_not_null, is_null};
58
use arrow::datatypes::{DataType, Field, FieldRef};
69
use datafusion_common::{Result, exec_err, internal_err, plan_err};
710
use datafusion_expr::binary::try_type_union_resolution;
811
use datafusion_expr::conditional_expressions::CaseBuilder;
12+
use datafusion_expr::expr::ScalarFunction;
913
use datafusion_expr::simplify::{ExprSimplifyResult, SimplifyContext};
1014
use datafusion_expr::{
1115
ColumnarValue, Documentation, Expr, ReturnFieldArgs, ScalarFunctionArgs,
@@ -90,22 +94,88 @@ impl ScalarUDFImpl for CoalesceFunc {
9094
));
9195
}
9296

93-
let n = args.len();
94-
let (init, last_elem) = args.split_at(n - 1);
95-
let whens = init
96-
.iter()
97-
.map(|x| x.clone().is_not_null())
98-
.collect::<Vec<_>>();
99-
let cases = init.to_vec();
97+
// `coalesce(a, b)` is rewritten to the (lazy, fast)
98+
// `CASE WHEN a IS NOT NULL THEN a ELSE b END`, which names every argument
99+
// but the last one twice. A volatile argument named twice is evaluated
100+
// twice, so the rewrite stops at the first volatile non-final argument
101+
// and the rest is left to the `invoke_with_args` kernel, which evaluates
102+
// each argument once. See https://github.com/apache/datafusion/issues/25477
103+
let (init, last) = args.split_at(args.len() - 1);
104+
let (prefix, rest) = match init.iter().position(|arg| arg.is_volatile()) {
105+
None => (init, last[0].clone()),
106+
Some(0) => return Ok(ExprSimplifyResult::Original(args)),
107+
Some(i) => (
108+
&init[..i],
109+
Expr::ScalarFunction(ScalarFunction::new_udf(
110+
super::coalesce(),
111+
args[i..].to_vec(),
112+
)),
113+
),
114+
};
115+
116+
let whens = prefix.iter().map(|x| x.clone().is_not_null()).collect();
100117
Ok(ExprSimplifyResult::Simplified(
101-
CaseBuilder::new(None, whens, cases, Some(Box::new(last_elem[0].clone())))
102-
.end()?,
118+
CaseBuilder::new(None, whens, prefix.to_vec(), Some(Box::new(rest))).end()?,
103119
))
104120
}
105121

106122
/// coalesce evaluates to the first value which is not NULL
107-
fn invoke_with_args(&self, _args: ScalarFunctionArgs) -> Result<ColumnarValue> {
108-
internal_err!("coalesce should have been simplified to case")
123+
///
124+
/// Only reached when [`Self::simplify`] keeps the call because an argument
125+
/// is volatile; evaluates each argument exactly once.
126+
fn invoke_with_args(&self, args: ScalarFunctionArgs) -> Result<ColumnarValue> {
127+
let args = args.args;
128+
// do not accept 0 arguments.
129+
if args.is_empty() {
130+
return exec_err!(
131+
"coalesce was called with {} arguments. It requires at least 1.",
132+
args.len()
133+
);
134+
}
135+
136+
let return_type = args[0].data_type();
137+
let mut return_array = args.iter().filter_map(|x| match x {
138+
ColumnarValue::Array(array) => Some(array.len()),
139+
_ => None,
140+
});
141+
142+
if let Some(size) = return_array.next() {
143+
// start with nulls as default output
144+
let mut current_value = new_null_array(&return_type, size);
145+
let mut remainder = BooleanArray::from(vec![true; size]);
146+
147+
for arg in args {
148+
match arg {
149+
ColumnarValue::Array(ref array) => {
150+
let to_apply = and(&remainder, &is_not_null(array.as_ref())?)?;
151+
current_value = zip(&to_apply, array, &current_value)?;
152+
remainder = and(&remainder, &is_null(array)?)?;
153+
}
154+
ColumnarValue::Scalar(value) => {
155+
if value.is_null() {
156+
continue;
157+
} else {
158+
let last_value = value.to_scalar()?;
159+
current_value = zip(&remainder, &last_value, &current_value)?;
160+
break;
161+
}
162+
}
163+
}
164+
if remainder.true_count() == 0 {
165+
break;
166+
}
167+
}
168+
Ok(ColumnarValue::Array(current_value))
169+
} else {
170+
let result = args
171+
.iter()
172+
.find_map(|x| match x {
173+
ColumnarValue::Scalar(s) if !s.is_null() => Some(x.clone()),
174+
_ => None,
175+
})
176+
.unwrap_or_else(|| args[0].clone());
177+
Ok(result)
178+
}
109179
}
110180

111181
fn conditional_arguments<'a>(

‎datafusion/sqllogictest/test_files/coalesce.slt‎

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -445,3 +445,90 @@ world
445445

446446
statement ok
447447
drop table ree_t;
448+
449+
##########
450+
# Volatile arguments must be evaluated exactly once
451+
# https://github.com/apache/datafusion/issues/25477
452+
##########
453+
454+
# The CASE rewrite would name a volatile non-final argument twice (two draws),
455+
# so a volatile first argument keeps the `coalesce` call.
456+
query TT
457+
explain select coalesce(nullif(floor(random() * 2), 0), -1) as c;
458+
----
459+
logical_plan
460+
01)Projection: coalesce(nullif(floor(random() * Float64(2)), Float64(0)), Float64(-1)) AS c
461+
02)--EmptyRelation: rows=1
462+
physical_plan
463+
01)ProjectionExec: expr=[coalesce(nullif(floor(random() * 2), 0), -1) as c]
464+
02)--PlaceholderRowExec
465+
466+
# With two draws, about 25% of rows were NULL (and Arrow rejected them because
467+
# the `-1` fallback makes the result non-nullable). With one draw, every row is
468+
# either 1 or -1.
469+
statement ok
470+
create table volatile_t as select * from generate_series(1, 100000) as t(i);
471+
472+
query III
473+
select
474+
count(coalesce(nullif(floor(random() * 2), 0), -1)),
475+
count(nvl(nullif(floor(random() * 2), 0), -1)),
476+
count(*) filter (where coalesce(nullif(floor(random() * 2), 0), -1) not in (1, -1))
477+
from volatile_t;
478+
----
479+
100000 100000 0
480+
481+
statement ok
482+
drop table volatile_t;
483+
484+
# `case when random() >= 0 then x end` is volatile but always equals `x`, which
485+
# makes the results of the volatile code path deterministic.
486+
statement ok
487+
create table volatile_args(a int, b int, c int, s varchar, x int, y int) as values
488+
(1, 9, 8, null, 0, 1),
489+
(null, 2, 8, 'b', 0, 1),
490+
(null, null, 3, null, 0, 1),
491+
(null, null, null, null, 0, 1);
492+
493+
query IIIIT
494+
select
495+
coalesce(case when random() >= 0 then a end, b, c),
496+
coalesce(case when random() >= 0 then a end, -1),
497+
coalesce(case when random() >= 0 then a end, null::int),
498+
coalesce(a, case when random() >= 0 then b end, c),
499+
coalesce(case when random() >= 0 then s end, 'none')
500+
from volatile_args;
501+
----
502+
1 1 1 1 none
503+
2 -1 NULL 2 b
504+
3 -1 NULL 3 none
505+
NULL -1 NULL NULL none
506+
507+
query TI
508+
select
509+
coalesce(case when random() >= 0 then null::varchar end, 'datafusion', 'unused'),
510+
coalesce(case when random() >= 0 then null::int end, null::int);
511+
----
512+
datafusion NULL
513+
514+
# Arguments before the first volatile one keep the CASE rewrite; only the rest
515+
# is left to `coalesce`.
516+
query TT
517+
explain select coalesce(a, case when random() >= 0 then b end, c) from volatile_args;
518+
----
519+
logical_plan
520+
01)Projection: CASE WHEN volatile_args.a IS NOT NULL THEN volatile_args.a ELSE coalesce(CASE WHEN random() >= Float64(0) THEN volatile_args.b END, volatile_args.c) END AS coalesce(volatile_args.a,CASE WHEN random() >= Int64(0) THEN volatile_args.b END,volatile_args.c)
521+
02)--TableScan: volatile_args projection=[a, b, c]
522+
physical_plan
523+
01)ProjectionExec: expr=[CASE WHEN a@0 IS NOT NULL THEN a@0 ELSE coalesce(CASE WHEN random() >= 0 THEN b@1 END, c@2) END as coalesce(volatile_args.a,CASE WHEN random() >= Int64(0) THEN volatile_args.b END,volatile_args.c)]
524+
02)--DataSourceExec: partitions=1, partition_sizes=[1]
525+
526+
# A volatile last argument is named once, so it keeps the lazy CASE rewrite:
527+
# `y / x` must not divide by zero when `a` is not null.
528+
query R
529+
select coalesce(a, y / x + random()) from volatile_args where a is not null;
530+
----
531+
1
532+
533+
statement ok
534+
drop table volatile_args;

0 commit comments

Comments
 (0)