ifnull

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Replacing NULLs. Returns its first argument if it isn't NULL, and the second otherwise. It's coalesce with exactly two arguments.

Syntax

ifnull(a, b)

Returns: the combined type of a and b.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_conditional_and_cast_ifnull. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of four sensor readings with some values missing:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_ifnull"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, sensor VARCHAR, errors INTEGER, temp DOUBLE, calibrated DECIMAL(6, 2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'north', 2, 21.5, 21.25), (2, '', 0, NULL, 19.75), "
    "(3, 'east', NULL, 18.0, NULL), (4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

ifnull(a, b) is a when a isn't NULL, and b when it is; it's NULL only when both are. b is computed only when a is NULL, so ifnull(1, 1 / 0) returns 1.0 without dividing, while ifnull(NULL, error('x')) reaches the error and stops the query with the message x.

a and b are combined into one type as in coalesce: ifnull(1, 2.5) is a DECIMAL(11,1), a DECIMAL column with a DOUBLE column gives a DOUBLE, and a DATE with a TIMESTAMP gives a TIMESTAMP.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT ifnull(NULL, NULL), ifnull(1, NULL), ifnull(NULL, 2), ifnull('', 'x'), "
    "ifnull(NULL::INTEGER, '5'), ifnull(DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00')")
run("SELECT ifnull(1, 2.5), typeof(ifnull(1, 2.5)), ifnull(1, 1 / 0)")
run("SELECT ifnull('a', 1)")
run("SELECT ifnull(1, 2, 3)")
run("SELECT ifnull(NULL, error('x'))")

[(None, 1, 2, '', 5, datetime.datetime(2026, 10, 16, 0, 0))]
[(Decimal('1.0'), 'DECIMAL(11,1)', 1.0)]
OSError: Could not convert string 'a' to INT32
InternalError: Parse query: 'SELECT ifnull(1, 2, 3)' failed with: Parser Error: Wrong number of arguments to IFNULL.
OSError: x

Examples

Defaults for a number, a text and a measurement. Sensor 2's empty name isn't NULL, so it stays empty. The DECIMAL calibration falls back to the DOUBLE temperature, so the result is a DOUBLE:

run(f"SELECT id, ifnull(errors, 0), ifnull(sensor, 'unnamed'), ifnull(calibrated, temp), "
    f"typeof(ifnull(calibrated, temp)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2, 'north', 21.25, 'DOUBLE'), (2, 0, '', 19.75, 'DOUBLE'), (3, 0, 'east', 18.0, 'DOUBLE'), (4, 0, 'unnamed', None, 'DOUBLE')]

The readings with no errors, counting a missing count as none:

run(f"SELECT id FROM {TABLE} WHERE ifnull(errors, 0) = 0 ORDER BY id LIMIT 10")

[(2,), (3,), (4,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not ifnull() of a column, so ifnull() on a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes ifnull() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes ifnull() and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE ifnull(errors, 0) = 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (COALESCE(errors, 0) = 0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           errors          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test the NULL case separately. ifnull(errors, 0) = 0 keeps the same rows as errors = 0 OR errors IS NULL. The second form tests one column against constants, which VAST DataBase can evaluate:

plan(f"SELECT id FROM {TABLE} WHERE errors = 0 OR errors IS NULL")
run(f"SELECT id FROM {TABLE} WHERE errors = 0 OR errors IS NULL ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(2,), (3,), (4,)]

• Store the default. If a column is always read with the same default, write the default when you load the rows.

Limitations and errors

• Exactly two arguments. One or three fail with Parser Error: Wrong number of arguments to IFNULL. For more fallbacks, use coalesce.
• Text and numbers don't combine. ifnull('a', 1) converts 'a' to the number's type and fails with Could not convert string 'a' to INT32. Cast one of them.
• Other SQL dialects' names aren't available. nvl, nvl2, zeroifnull and isnull fail with Catalog Error: Scalar Function with name ... does not exist!. Use ifnull(x, d) for nvl(x, d), CASE WHEN x IS NOT NULL THEN a ELSE b END for nvl2(x, a, b), ifnull(x, 0) for zeroifnull(x), and x IS NULL for isnull(x).

run("SELECT nvl(NULL, 1)")
run("SELECT nvl2(NULL, 1, 2)")
run("SELECT zeroifnull(NULL)")
run("SELECT isnull(NULL)")

InternalError: Catalog Error: Scalar Function with name nvl does not exist!
InternalError: Catalog Error: Scalar Function with name nvl2 does not exist!
InternalError: Catalog Error: Scalar Function with name zeroifnull does not exist!
InternalError: Catalog Error: Scalar Function with name isnull does not exist!

Similar functions

run("SELECT ifnull(NULL, 'b'), coalesce(NULL, NULL, 3), nullif(0, 0), if(NULL IS NULL, 'b', 'a'), "
    "CASE WHEN NULL IS NULL THEN 'b' END")

[('b', 3, None, 'b', 'b')]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Conditional and conversion functions: all the conditional and conversion functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.