NULL Handling in Functions

This page explains how NULL inputs reach the scalar and aggregate functions of a DuckDB extension written in Rust, and how to give them SQL NULL semantics.

The one thing to take away: for a scalar function, DefaultNullHandling does not make DuckDB return NULL for you. Your callback is invoked for NULL rows too, and if it writes a value there, that value is the answer. Call DataChunk::propagate_nulls, or use ScalarFunctionBuilder::map1 / map2, which do it for you. An aggregate's update likewise receives NULL rows under either setting.

What DuckDB actually does

DuckDB's FunctionNullHandling has two settings, and quack-rs mirrors them as NullHandling. The names suggest that the default makes the engine handle NULL propagation. For functions registered through the C API it does not: a scalar function's output for a NULL row is kept as written, and an aggregate's update is handed NULL rows (see Aggregate functions). For scalars the result is silent wrong answers rather than an error.

For scalar functions, two pieces of DuckDB source settle it (quoted from v1.5.4):

src/main/capi/scalar_function-c.cpp — the C API bridge calls your callback for the whole flattened chunk and never looks at the result's validity:

void CAPIScalarFunction(DataChunk &input, ExpressionState &state, Vector &result) {
    ...
    input.Flatten();
    ...
    c_bind_info.info.function(c_function_info, c_input, c_result);
    if (!function_info.success) {
        throw InvalidInputException(function_info.error);
    }
    ...
}

src/execution/expression_executor/execute_function.cpp — the only NULL check is a debug-only assertion that your function already did the right thing:

static void VerifyNullHandling(const BoundFunctionExpression &expr, DataChunk &args, Vector &result) {
#ifdef DEBUG
    if (args.data.empty() || expr.function.GetNullHandling() != FunctionNullHandling::DEFAULT_NULL_HANDLING) {
        return;
    }
    // ... D_ASSERT(!result_data.validity.RowIsValid(idx));
#endif
}

Every DuckDB a user installs is a release build, so that assertion is compiled out.

Why the obvious test passes anyway

SELECT my_func(NULL);   -- NULL, even for a broken function

A literal NULL is constant-folded during binding: DuckDB evaluates the expression once, sees a NULL argument to a DEFAULT_NULL_HANDLING function, and substitutes NULL without calling anything. The wrong answers only appear when the argument comes from a column:

CREATE TABLE t(i BIGINT);
INSERT INTO t VALUES (1), (NULL), (3);
SELECT i, my_func(i) FROM t;

Against DuckDB 1.5.4, a function that unconditionally writes 999 returns:

quack-rs pins this behaviour in tests/ffi_roundtrip.rs, so a future DuckDB that starts propagating will show up as a test failure rather than as a surprise.

Doing it right

The safe route: typed scalar functions

ScalarFunctionBuilder::map1 / map2 take an ordinary Rust closure and handle validity for you — a NULL argument short-circuits to a NULL result without ever calling your code:

#![allow(unused)]
fn main() {
use libduckdb_sys::{duckdb_aggregate_state, duckdb_bind_info, duckdb_connection,
    duckdb_data_chunk, duckdb_function_info, duckdb_init_info, duckdb_vector, idx_t};
use quack_rs::prelude::*;
fn live_connection() -> libduckdb_sys::duckdb_connection {
    std::mem::forget(quack_rs::testing::InMemoryDb::open().unwrap());
    let (mut db, mut con) = (std::ptr::null_mut(), std::ptr::null_mut());
    unsafe {
        assert_eq!(libduckdb_sys::duckdb_open(std::ptr::null(), &mut db), libduckdb_sys::DuckDBSuccess);
        assert_eq!(libduckdb_sys::duckdb_connect(db, &mut con), libduckdb_sys::DuckDBSuccess);
    }
    con
}
/// First column of the first row, as BIGINT; `None` for NULL.
fn query_i64(con: libduckdb_sys::duckdb_connection, sql: &str) -> Option<i64> {
    let mut result = unsafe { quack_rs::query::query(con, sql) }.unwrap();
    let chunk = result.next_chunk().unwrap().unwrap();
    let reader = unsafe { chunk.reader(0) };
    unsafe { reader.is_valid(0).then(|| reader.read_i64(0)) }
}
let con = live_connection();
let run = || -> Result<(), ExtensionError> { unsafe {
ScalarFunctionBuilder::map1("double_it", |x: i64| x * 2)?
    .register(con)?;
} Ok(()) };
run().unwrap();
assert_eq!(query_i64(con, "SELECT double_it(21)"), Some(42));
// From a column, not a literal: `double_it(NULL::BIGINT)` is constant-folded
// to NULL whatever the function does, so it cannot tell a broken one apart.
assert_eq!(query_i64(con, "SELECT double_it(i) FROM (VALUES (NULL::BIGINT)) t(i)"), None);
}

Use map1_opt / map2_opt when the function needs to see NULLs; those register SpecialNullHandling for you and hand the closure Option<T>.

The raw route: propagate_nulls

When you write the extern "C" callback yourself, restore SQL semantics with one call at the end:

#![allow(unused)]
fn main() {
use libduckdb_sys::{duckdb_aggregate_state, duckdb_bind_info, duckdb_connection,
    duckdb_data_chunk, duckdb_function_info, duckdb_init_info, duckdb_vector, idx_t};
use quack_rs::prelude::*;
fn live_connection() -> libduckdb_sys::duckdb_connection {
    std::mem::forget(quack_rs::testing::InMemoryDb::open().unwrap());
    let (mut db, mut con) = (std::ptr::null_mut(), std::ptr::null_mut());
    unsafe {
        assert_eq!(libduckdb_sys::duckdb_open(std::ptr::null(), &mut db), libduckdb_sys::DuckDBSuccess);
        assert_eq!(libduckdb_sys::duckdb_connect(db, &mut con), libduckdb_sys::DuckDBSuccess);
    }
    con
}
/// First column of the first row, as BIGINT; `None` for NULL.
fn query_i64(con: libduckdb_sys::duckdb_connection, sql: &str) -> Option<i64> {
    let mut result = unsafe { quack_rs::query::query(con, sql) }.unwrap();
    let chunk = result.next_chunk().unwrap().unwrap();
    let reader = unsafe { chunk.reader(0) };
    unsafe { reader.is_valid(0).then(|| reader.read_i64(0)) }
}
quack_rs::scalar_callback!(double_it, |_info, input, output| {
    let chunk = unsafe { DataChunk::from_raw(input) };
    let reader = unsafe { chunk.reader(0) };
    let mut writer = unsafe { VectorWriter::from_vector(output) };
    for row in 0..chunk.size() {
        unsafe { writer.write_i64(row, reader.read_i64(row) * 2) };
    }
    // Without this, double_it(i) for a NULL `i` from a column is 0, not NULL.
    unsafe { chunk.propagate_nulls(&mut writer) };
});
let con = live_connection();
unsafe { ScalarFunctionBuilder::new("double_it").param(TypeId::BigInt).returns(TypeId::BigInt)
    .function(double_it).register(con).unwrap(); }
assert_eq!(query_i64(con, "SELECT double_it(21)"), Some(42));
// From a column, not a literal: `double_it(NULL::BIGINT)` is constant-folded
// to NULL whatever the function does, so it cannot tell a broken one apart.
assert_eq!(query_i64(con, "SELECT double_it(i) FROM (VALUES (NULL::BIGINT)) t(i)"), None);
}

propagate_nulls resolves each column's validity pointer once and marks the output NULL wherever any input column is NULL. A column with no validity mask has no NULLs and costs nothing. DataChunk::any_null(row) is the per-row form when you need the decision inline.

NullHandling enum

#![allow(unused)]
fn main() {
use quack_rs::types::NullHandling;

// Default: the function promises NULL in -> NULL out.
// Scalar: you must keep that promise (see above).
// Aggregate: `update` still receives NULL rows; skip them yourself.
NullHandling::DefaultNullHandling;

// The function means to see NULLs and may return non-NULL for them.
NullHandling::SpecialNullHandling;
}

Aggregate functions

Aggregates behave like scalar functions here: under either setting, update receives every row of the chunk, NULL rows included. CAPIAggregateUpdate in DuckDB's aggregate_function-c.cpp flattens the inputs and passes the whole chunk through; nothing on the way filters by validity. An aggregate that ignores NULLs skips them itself:

#![allow(unused)]
fn main() {
use libduckdb_sys::{duckdb_aggregate_state, duckdb_bind_info, duckdb_connection,
    duckdb_data_chunk, duckdb_function_info, duckdb_init_info, duckdb_vector, idx_t};
use quack_rs::prelude::*;
unsafe fn demo(chunk: &DataChunk, reader: &VectorReader) {
for row in 0..chunk.size() {
    if !unsafe { reader.is_valid(row) } {
        continue; // a NULL row: its data slot holds no meaningful value
    }
    // ... accumulate reader.read_i64(row) into *states.add(row) ...
}
}
}

SpecialNullHandling declares that the aggregate may return non-NULL for NULL input (a count_with_nulls, say). For an aggregate DuckDB reads the setting in one place only — the correlated-subquery decorrelator, to pick an INNER or LEFT join — and no query we tried (correlated scalar subqueries, with and without arithmetic or coalesce around the aggregate, LATERAL, a correlated subquery in WHERE) answered differently under the two settings on DuckDB 1.5.5. Set it anyway when it is true; it is what DuckDB expects.

#![allow(unused)]
fn main() {
use libduckdb_sys::{duckdb_aggregate_state, duckdb_bind_info, duckdb_connection,
    duckdb_data_chunk, duckdb_function_info, duckdb_init_info, duckdb_vector, idx_t};
use quack_rs::prelude::*;
#[derive(Default)] struct CountState { count: i64 }
impl AggregateState for CountState {}
unsafe extern "C" fn my_update(_: duckdb_function_info, _: duckdb_data_chunk, _: *mut duckdb_aggregate_state) {}
unsafe extern "C" fn my_combine(_: duckdb_function_info, _: *mut duckdb_aggregate_state, _: *mut duckdb_aggregate_state, _: idx_t) {}
unsafe extern "C" fn my_finalize(_: duckdb_function_info, _: *mut duckdb_aggregate_state, _: duckdb_vector, _: idx_t, _: idx_t) {}
unsafe fn demo(con: duckdb_connection) -> Result<(), ExtensionError> {
use quack_rs::aggregate::AggregateFunctionBuilder;
use quack_rs::types::{TypeId, NullHandling};

unsafe {
    AggregateFunctionBuilder::new("count_with_nulls")
        .param(TypeId::BigInt)
        .returns(TypeId::BigInt)
        .null_handling(NullHandling::SpecialNullHandling)
        .ffi_state::<CountState>()
        .update(my_update)   // counts rows whose value is NULL, too
        .combine(my_combine)
        .finalize(my_finalize)
        .register(con)?;
}
Ok(())
}
}

Empty groups in a correlated subquery

One difference from an uncorrelated query holds under both settings. In SELECT (SELECT my_count(x) FROM t2 WHERE t2.k = t1.k) FROM t1, an outer row with no matching t2 rows gets NULL: the decorrelated plan joins the aggregate's groups back to the outer rows, and an outer row with no group never has an empty state finalized. DuckDB rewrites that NULL to 0 for its own count and count(*) only. A count-like aggregate of yours that returns 0 for empty input therefore returns NULL here; write coalesce((SELECT ...), 0) if the query needs 0. (SELECT my_count(x) FROM t2 WHERE false, uncorrelated, does finalize an empty state and returns 0.)

When to use special NULL handling

If you don't call .null_handling(), DefaultNullHandling is used.