nullif

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Replacing NULLs. Returns NULL if its two arguments are equal, and the first argument otherwise. Use it to turn a placeholder, such as an empty string or a zero, into NULL.

Syntax

nullif(a, b)

Returns: the type of a.

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_nullif. 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 survey answers. The form stored an empty comment and a count of 0 where nothing was entered, and the last row has no values:

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_nullif"'

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}.answers"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, comment VARCHAR, first_city VARCHAR, last_city VARCHAR, visits TINYINT, total INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'great', 'Paris', 'Rome', 4, 200), (2, '', 'Oslo', 'Oslo', 0, 0), "
    "(3, 'ok', 'Lima', 'lima', 2, 50), (4, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

nullif(a, b) compares a and b with =. When they're equal it returns NULL; otherwise it returns a. It's the same as CASE WHEN a = b THEN NULL ELSE a END.

The comparison follows the rules of =:

• Values of different types are converted first. nullif(1, 1.0), nullif(1, '1') and nullif('1', 1) are all NULL, and nullif(DATE '2026-10-16', TIMESTAMP '2026-10-16') is a NULL DATE.
• Text is compared exactly, so nullif('a', 'A') returns 'a'. With COLLATE nocase on the first argument, nullif('a' COLLATE nocase, 'A') is NULL.
• NaN equals NaN, and 0.0 equals -0.0, so both pairs give NULL.
• Lists and structs are equal when all their elements are: nullif([1], [1]) and nullif({'a': 1}, {'a': 1}) are NULL.

The result keeps the type of a: nullif(1::TINYINT, 300) is the TINYINT 1.

NULLs and edge cases

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

run("SELECT nullif(NULL, 1), nullif(1, NULL), nullif(NULL, NULL), nullif('', ''), nullif(1, 2), "
    "10 / nullif(0, 0)")
run("SELECT nullif(1, 1.0), typeof(nullif(1, 1.0)), nullif(1, '1'), nullif('1', 1), "
    "nullif(DATE '2026-10-16', TIMESTAMP '2026-10-16'), nullif('a', 'A'), nullif('a' COLLATE nocase, 'A')")
run("SELECT nullif('nan'::DOUBLE, 'nan'::DOUBLE), nullif(0.0, -0.0), nullif([1], [1]), "
    "nullif({'a': 1}, {'a': 1}), nullif(1::TINYINT, 300), typeof(nullif(1::TINYINT, 300))")
run("SELECT nullif(1, 'x')")
run("SELECT nullif(1)")

[(None, 1, None, None, 1, None)]
[(None, 'INTEGER', None, None, None, 'a', None)]
[(None, None, None, None, 1, 'TINYINT')]
InternalError: Conversion Error: Could not convert string 'x' to INT32
InternalError: Parse query: 'SELECT nullif(1)' failed with: Parser Error: syntax error at or near ")"

Examples

Turn the placeholders into NULL, and divide by a count that may be zero. total / nullif(visits, 0) is NULL for answer 2 instead of a division by zero:

run(f"SELECT id, nullif(comment, ''), nullif(visits, 0), total / nullif(visits, 0) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'great', 4, 50.0), (2, None, None, None), (3, 'ok', 2, 25.0), (4, None, None, None)]

Keep the last city only where it differs from the first. Answer 3's cities differ only in case, so compare them with lower() to treat them as the same:

run(f"SELECT id, nullif(last_city, first_city), nullif(lower(last_city), lower(first_city)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Rome', 'rome'), (2, None, None), (3, 'lima', None), (4, None, None)]

With coalesce, replace both the empty comments and the NULLs with one default:

run(f"SELECT id, coalesce(nullif(comment, ''), 'no comment') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'great'), (2, 'no comment'), (3, 'ok'), (4, 'no comment')]

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 nullif() of a column, so nullif() on a column is always computed by the Query Engine:

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

The plan of a condition on nullif() shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE nullif(comment, '') IS NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CASE  WHEN ((comment = ''│
│    )) THEN (NULL) ELSE    │
│    comment END IS NULL)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          comment          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test the placeholder and the NULL separately. nullif(comment, '') IS NULL keeps the same rows as comment = '' OR comment IS NULL. The second form tests one column against constants, which VAST DataBase can evaluate:

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

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

• Store NULL instead of a placeholder. If you control how the rows are written, write NULL for a missing value, and you won't need nullif() when you read them.

Limitations and errors

• Exactly two arguments. One or three fail with Parser Error: syntax error.
• The arguments must compare. nullif(1, 'x') converts 'x' to the first argument's type and fails with Conversion Error: Could not convert string 'x' to INT32.
• nullifzero isn't available. It fails with Catalog Error: Scalar Function with name nullifzero does not exist!. Use nullif(x, 0).

run("SELECT nullifzero(0)")
run("SELECT nullif(0, 0)")

InternalError: Catalog Error: Scalar Function with name nullifzero does not exist!
[(None,)]

Similar functions

run("SELECT nullif('', ''), coalesce(nullif('', ''), 'b'), ifnull(NULL, 'b'), "
    "CASE WHEN '-' IN ('', '-') THEN NULL ELSE '-' END")

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

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.