if

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Choosing a value. Returns the second argument if the condition is true, and the third otherwise. It's a short form of a CASE with one condition: if(c, a, b) is CASE WHEN c THEN a ELSE b END.

Syntax

if(condition, 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_if. 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 orders. 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_if"'

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}.orders"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, price DOUBLE, express BOOLEAN, discount DECIMAL(5, 2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, true, 0.50), (2, 4.0, false, NULL), (3, 1.5, true, 1.25), (4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

if(condition, a, b) returns a when condition is true and b when it's false or NULL. a and b are combined into one type as in CASE: if(1 = 1, 1, 2.5) is a DECIMAL(11,1), 1.0.

Only the argument that's returned is computed, so if(false, 1 / 0, 0) returns 0.0 and if(true, 0, CAST('x' AS INTEGER)) returns 0 without failing on 'x'.

The condition can be a number or text that converts to BOOLEAN: 0 is false, other numbers such as 1 and 2 are true, and the text 'true' is true.

NULLs and edge cases

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

run("SELECT if(NULL, 'yes', 'no'), if(true, NULL, 'no'), if(false, 'yes', NULL), "
    "if(0, 'yes', 'no'), if(2, 'yes', 'no'), if('true', 'yes', 'no'), if(1 = 1, 1, 'x')")
run("SELECT if(1 = 1, 1, 2.5), typeof(if(1 = 1, 1, 2.5)), if(false, 1 / 0, 0), "
    "if(true, 0, CAST('x' AS INTEGER))")
run("SELECT if('abc', 'yes', 'no')")
run("SELECT if(DATE '2026-10-16', 'yes', 'no')")
run("SELECT if(true, 'yes')")

[('no', None, None, 'no', 'yes', 'yes', 1)]
[(Decimal('1.0'), 'DECIMAL(11,1)', 0.0, 0)]
InternalError: Conversion Error: Could not convert string 'abc' to BOOL
InternalError: Conversion Error: Unimplemented type for cast (DATE -> BOOLEAN)
InternalError: Parse query: 'SELECT if(true, 'yes')' failed with: Parser Error: Wrong number of arguments to IF.

Examples

Name each order by whether it has a discount and whether it ships express. The last row's express is NULL, so it gets the third argument:

run(f"SELECT id, if(discount IS NULL, 'full price', 'discounted'), if(express, 'express', 'standard') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'discounted', 'express'), (2, 'full price', 'standard'), (3, 'discounted', 'express'), (4, 'full price', 'standard')]

Add a fee to the express orders only:

run(f"SELECT id, price + if(express, 5, 0) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 7.5), (2, 4.0), (3, 6.5), (4, None)]

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

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

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

plan(f"SELECT id FROM {TABLE} WHERE if(express, 1, 0) = 1")

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

Make it faster

• Write the condition directly. WHERE express = true keeps the same rows as WHERE if(express, 1, 0) = 1 and is a comparison of a column with a constant, which VAST DataBase can evaluate. Compare the plans:

plan(f"SELECT id FROM {TABLE} WHERE express = true")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Use if() in the SELECT list to label or compute values, and keep WHERE to plain comparisons of columns with constants where you can.

Limitations and errors

• Exactly three arguments. For a value with no "else", use CASE WHEN c THEN a END, which returns NULL when c isn't true. More conditions need a CASE.
• The condition must convert to BOOLEAN. Text such as 'abc' fails with Conversion Error: Could not convert string 'abc' to BOOL; a DATE, BLOB or list fails with Conversion Error: Unimplemented type for cast. Write a comparison instead, such as d > DATE '2026-01-01'.
• Other SQL dialects' names aren't available. iif and iff fail with Catalog Error: Scalar Function with name ... does not exist!. Use if or CASE.

run("SELECT iif(true, 1, 2)")
run("SELECT iff(true, 1, 2)")

InternalError: Catalog Error: Scalar Function with name iif does not exist!
InternalError: Catalog Error: Scalar Function with name iff does not exist!

Similar functions

run("SELECT if(2 > 1, 'x', 'y'), CASE WHEN 2 > 1 THEN 'x' ELSE 'y' END, coalesce(NULL, 'b'), "
    "ifnull(NULL, 'b'), nullif('a', 'a')")

[('x', 'x', '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.