Implement lazy evaluation for nvl

@chenkovsky

Is your feature request related to a problem or challenge?

DataFusion's CASE handling is quite optimized and handles many cases with constants, lazy evaluation, and more

Recently, @chenkovsky fixed lazy evaluation for coalesce by rewriting it to use CASE in PR:

fix: lazy evaluation for coalesce #17357

This both made the code faster and more correct

Describe the solution you'd like

We can do the same thing for the nvl function, as suggested by @pepijnve in #17813 (comment)

https://github.com/apache/datafusion/blob/main/datafusion/functions/src/core/nvl.rs

Describe alternatives you've considered

No response

Additional context

No response

take

@alamb

@alamb I started on this, but then had second doubts. A quick google search led met to https://dincosman.com/2025/05/17/nvl-eager-evaluates/#:~:text=In%20the%20NVL%20example%2C%20lazy,first%20condition%20isn't%20met. Perhaps it would be better to keep eager evaluation for nvl?

I've never been able to convince myself to pony up the money for the SQL specification, but perhaps I should. This feels like one of those cases where it would be useful to have. As a poor man's proxy I asked Claude. Take this with a grain of salt as with anything AI produced.

Does the SQL specification mandate eager evaluation of function arguments?

The SQL specification does not mandate eager evaluation of function arguments. In fact, SQL's evaluation model is generally left implementation-defined, giving database systems considerable flexibility.

Key points:
No explicit evaluation order mandate: The SQL standard doesn't prescribe whether arguments must be evaluated before, during, or conditionally within function execution. This is intentionally left to implementers.
Short-circuit evaluation is common: Many database systems use lazy/short-circuit evaluation for certain operations:

CASE expressions typically don't evaluate branches that won't be selected

Boolean operators like AND and OR often short-circuit (e.g., false AND (1/0 = 1) won't cause a division by zero error in most systems)

COALESCE and NULLIF typically evaluate arguments only as needed

Implementation variance: Different database systems handle this differently:

Some may optimize away function calls with constant arguments

Some may defer evaluation until necessary

Evaluation order can vary between different functions in the same system
Why this matters: This flexibility allows for optimizations but can lead to portability issues. For example, relying on evaluation order for side effects or avoiding errors (like division by zero) may work in one database but not another.

If you need guaranteed evaluation order or conditional execution, using explicit CASE expressions or implementation-specific features is more reliable than depending on function argument evaluation semantics.

Is this flexibility described in the ISO standard?

Yes, the ISO SQL standard does address this, though it's somewhat scattered across different sections. The key concept is that SQL uses "effectively evaluated" semantics rather than mandating specific evaluation timing or order.

Key standard provisions:

General Rules flexibility: The SQL standard's "General Rules" sections describe what must be true logically, not how or when to compute it. Implementations can evaluate expressions in any order that produces correct results.

Conditional evaluation: The standard explicitly allows (and expects) conditional evaluation for constructs like CASE. From ISO/IEC 9075-2 (Foundation), the CASE expression rules indicate that only the selected branch needs to be evaluated.

No guaranteed ordering: Unlike some programming languages, SQL doesn't guarantee left-to-right argument evaluation. The standard states that the evaluation order of operands and function arguments is implementation-dependent unless specifically required by the operation.

"Without actually evaluating": The standard uses language like "without necessarily evaluating" in various places, indicating that implementations can defer or skip evaluation when the result is determinable without it.

Error handling ambiguity: The standard doesn't mandate when errors must be raised. An expression that could raise an error (like 1/0) might not if the optimizer can prove the value isn't needed.

Practical implication: This means you cannot reliably depend on side effects or evaluation order in SQL expressions. The standard intentionally provides this flexibility to allow for query optimization.

Some more empirical testing of a few query engines shows DataFusion is the odd one out. I'll proceed with the implementation for now.

DataFusion

> select ifnull('ok', 1/0);
Arrow error: Divide by zero error

SQLite

sqlite> select ifnull('ok', 1/0);
ok

duckdb

D select ifnull('ok', 1/0);
Conversion Error:
Could not convert string 'ok' to DOUBLE                                                                                                                                                                                                                                                                                                                                    

LINE 1: select ifnull('ok', 1/0);
                      ^                                                                                                                                                                                                                                                                                                                                                    
D select ifnull(1.0, 1/0);
┌────────────────────────┐
│ COALESCE(1.0, (1 / 0)) │                                                                                                                                                                                                                                                                                                                                                 
│         double         │                                                                                                                                                                                                                                                                                                                                                 
├────────────────────────┤                                                                                                                                                                                                                                                                                                                                                 
│          1.0           │  
└────────────────────────┘

databricks
https://docs.databricks.com/aws/en/sql/language-manual/functions/nvl literally says nvl(x, y) is a synonym for coalesce(x, y) so I assume they do lazy evaluation as well.

apache#17982 Make `nvl` a thin wrapper for `coalesce`

apache#17982 Documentation fix

I've never been able to convince myself to pony up the money for the SQL specification, but perhaps I should. This feels like one of those cases where it would be useful to have. As a poor man's proxy I asked Claude. Take this with a grain of salt as with anything AI produced.

For the record, I have ponied up money for the SQL specification (well, had an employer do so) and my personal conclusion is it does not offer the level of detail you would hope for functions like nvl (at least my copy of ISO 9075-1:2016(E) aka SQL-96 doesn't)

Let's go for the empirical method then which tells us:

It differs across implementations
There's prior art for making it lazy

#17972 is related to this. If we implement nvl as case when x is not null x else y end, then we need to be sure there's no performance regression due to this change.

#17982 Make `nvl` a thin wrapper for `coalesce` (#17991)

Update fork (#12)

apache#17982 Make `nvl` a thin wrapper for `coalesce` (apache#17991)

Revert "apache#17982 Make `nvl` a thin wrapper for `coalesce` (apache…

We merged a fix for this in the 51 release, but we have found as subtle problem so I am planning to revert it for 51 (the fix should appear in 52):

[branch-51] Revert rewrite for coalesce, nvl and nvl2 simplification #18567

apache#17982 Make `nvl` a thin wrapper for `coalesce` (apache#17991)

Navigation Menu

Repository navigation

Implement lazy evaluation for `nvl` #17982

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Implement lazy evaluation for nvl #17982

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

pepijnve commented on Oct 9, 2025

pepijnve commented on Oct 9, 2025

pepijnve commented on Oct 9, 2025

pepijnve commented on Oct 9, 2025

alamb commented on Oct 9, 2025

pepijnve commented on Oct 9, 2025

pepijnve commented on Oct 11, 2025

alamb commented on Nov 9, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Implement lazy evaluation for `nvl` #17982