NANVL function design

1. Background

1.1. Oracle semantics

Oracle provides the NANVL(n, m) function for handling NaN (Not a Number) floating-point values:

  • If n is NaN, the function returns the substitute m.

  • If n is not NaN, the function returns n.

  • The function supports the BINARY_FLOAT and BINARY_DOUBLE types.

1.2. Purpose

NaN is a special value defined by the IEEE 754 floating-point standard. It commonly occurs during data import, scientific computing, and ETL processing. Leaving NaN values untreated can cause:

  • Aggregate results to become NaN

  • Unexpected comparison behavior (NaN ≠ NaN)

  • Index and constraint checks to fail

NANVL provides a declarative way to replace NaN with a valid numeric value, such as zero, -1, or an average. It is needed for migrating Oracle applications to IvorySQL.

2. Architecture

2.1. Design choice

NANVL is implemented as a C function. It requires no special syntax and can be parsed as an ordinary function call. After it is registered as a regular SQL function with CREATE FUNCTION, PostgreSQL’s standard function lookup resolves its overloads automatically.

2.2. Code organization

contrib/ivorysql_ora/
├── src/builtin_functions/
│   ├── builtin_functions--1.0.sql    -- SQL registration
│   └── numeric_datatype_functions.c  -- C function implementation
├── sql/
│   └── ora_nanvl.sql                 -- Regression test SQL
├── expected/
│   └── ora_nanvl.out                 -- Expected output
└── Makefile                          -- Add ora_nanvl to the ORA_REGRESS list

2.3. Function attributes

Attribute Value Reason

IMMUTABLE

Yes

A pure calculation always returns the same result for the same inputs

PARALLEL SAFE

Yes

The function has no side effects and is safe for parallel query plans

3. Implementation details

3.1. binary_float and binary_double implementation

Datum
binary_float_nanvl(PG_FUNCTION_ARGS)
{
    float4      arg1;

    if (PG_ARGISNULL(0))
        PG_RETURN_NULL();

    arg1 = PG_GETARG_FLOAT4(0);

    if (!isnan(arg1))
        PG_RETURN_FLOAT4(arg1);

    if (PG_ARGISNULL(1))
        PG_RETURN_NULL();

    PG_RETURN_FLOAT4(PG_GETARG_FLOAT4(1));
}

  • BINARY_FLOAT is represented by float4 (4-byte IEEE 754 single precision), while BINARY_DOUBLE is represented by float8 (8-byte double precision). binary_double_nanvl has the same structure, with float4 and FLOAT4 replaced by float8 and FLOAT8.

  • NaN is detected with isnan() from the standard C library header <math.h>.

3.2. number implementation

Datum
number_nanvl(PG_FUNCTION_ARGS)
{
    Numeric     arg1;

    if (PG_ARGISNULL(0))
        PG_RETURN_NULL();

    arg1 = PG_GETARG_NUMERIC(0);

    if (!numeric_is_nan(arg1))
        PG_RETURN_NUMERIC(arg1);

    if (PG_ARGISNULL(1))
        PG_RETURN_NULL();

    PG_RETURN_NUMERIC(PG_GETARG_NUMERIC(1));
}

  • Oracle’s NUMBER type maps to PostgreSQL’s Numeric type.

  • PostgreSQL Numeric can represent NaN, while Oracle NUMBER does not support NaN.

  • The implementation reuses the core numeric_is_nan() function, declared in utils/numeric.h and defined in utils/adt/numeric.c.

  • numeric_is_nan() uses the NUMERIC_IS_NAN() macro internally.

3.3. NULL handling

The semantics of NANVL depend initially on the first argument, n:

  1. If n is NULL, return NULL without reading m.

  2. If n is neither NULL nor NaN, return n. In this case, m does not participate in the operation, and a NULL m does not affect the result.

  3. Only when n is NaN is m used as the return value. The result is NULL only if m is NULL in this case.

The three overloads therefore omit STRICT and use PG_ARGISNULL() in the function body to handle these cases explicitly. Each function first checks whether n is NULL, then whether it is NaN, and checks whether m is NULL only when returning m. The numeric, float4, and float8 overloads use the same NULL-handling logic and differ only in how they detect NaN.