Operators and predicates

Applies to VAST Cluster 5.5.

Operators combine values in SELECT lists, WHERE clauses and UPDATE ... SET, and a predicate is a condition built from them. This page covers where each predicate runs, in VAST DataBase or in the Query Engine, and how to write filters that VAST DataBase evaluates, then gives an entry for each operator group: what it does, its operand and result types, what it does with NULLs, and an example.

Functions on this page

How operators and predicates run

The Query Engine runs on the cluster's CNodes. A query's plan, which EXPLAIN shows, starts with a scan in VAST DataBase (VAST_SCAN): it reads only the columns the query uses, and evaluates the WHERE conditions it can push down, so rows that don't match never leave storage. A condition the scan can't evaluate becomes a FILTER step in the Query Engine, which tests every row the scan returns. Operators in the SELECT list are always computed in the Query Engine, on the rows the scan returns.

The scan evaluates a condition when it compares one column, as stored, with a constant, and an AND of such conditions, where each part can OR tests on the same column. The Query Engine computes constant expressions first, and rewrites some conditions into that shape: qty > 1 + 1, qty * 2 > 10 on an integer column, and CAST(ts AS DATE) = DATE '2026-10-02' on a TIMESTAMP column all push down.

An AND is split: in in_stock AND qty > 5, the scan evaluates qty > 5 and the FILTER step only in_stock, so the scan still drops the rows that fail qty > 5. Other clients have their own rules; see pushdowns.

Make filters faster

• Compare the column as stored with a constant. Move arithmetic to the constant side: price > 0.4 / 2, not price * 2 > 0.4. A function of a constant is computed once; a function of the column runs on every row in the Query Engine.
• Match the constant to the column's type. On an INTEGER column, qty >= 6 pushes down; qty > 5.5 casts the column to DECIMAL and runs in the Query Engine.
• Test a boolean column with = true or = false. WHERE in_stock and WHERE NOT in_stock run in the Query Engine; WHERE in_stock = true pushes down.
• Write a list of values as ORs on one column. id = 1 OR id = 3 OR id = 5 pushes down; id IN (1, 3, 5) runs in the Query Engine, and an IN list of five or more such values fails.
• Use a range instead of a prefix pattern. name >= 'b' AND name < 'c' pushes down; name LIKE 'b%' doesn't. Text compares in byte order, so the range is case-sensitive, like LIKE.
• Rewrite NOT BETWEEN as two ranges. qty < 5 OR qty > 12 pushes down.

Examples on this page

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_operators. 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() helper, and creates a table of five stock items. The last two have NULLs. It also defines where_it_runs(), which prints the rows a condition selects and whether VAST DataBase evaluated it (no FILTER step in the plan) or the Query Engine did:

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

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)

TABLE = f"{SCHEMA}.items"

def where_it_runs(condition):
    """Print the ids a WHERE condition selects, and where the condition runs."""
    try:
        with conn.cursor() as cur:
            cur.execute(f"EXPLAIN SELECT id FROM {TABLE} WHERE {condition}")
            plan = cur.fetchall()[0][1]
            cur.execute(f"SELECT id FROM {TABLE} WHERE {condition} ORDER BY id LIMIT 10")
            ids = [row[0] for row in cur.fetchall()]
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{condition:<48} {type(e).__name__}: {server_message(e)}")
    else:
        where = "Query Engine (FILTER)" if "FILTER" in plan else "pushed down"
        print(f"{condition:<48} {str(ids):<16} {where}")

run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, qty INTEGER, price DOUBLE, "
    "in_stock BOOLEAN, added DATE, tags VARCHAR[], bin STRUCT(aisle INTEGER, shelf INTEGER))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'bolt', 12, 0.25, true, DATE '2026-10-01', ['metal', 'small'], {'aisle': 1, 'shelf': 2}), "
    "(2, 'Bracket', 5, 3.5, true, DATE '2026-10-02', ['metal'], {'aisle': 3, 'shelf': 1}), "
    "(3, 'cable_tie', 40, 0.05, false, DATE '2026-10-03', ['plastic', 'small'], {'aisle': 2, 'shelf': 4}), "
    "(4, 'washer', 7, 0.1, NULL, DATE '2026-10-04', NULL, NULL), "
    "(5, NULL, 0, NULL, false, NULL, ['misc'], {'aisle': NULL, 'shelf': NULL})")

[]
[]
[(5,)]

Comparisons

Comparison operators

=, <> (or !=), <, <=, > and >= compare two values of the same kind: numbers, text, dates and timestamps, booleans, BLOB and UUID values, lists and structs.

• Returns: BOOLEAN.
• Types: a constant is cast to the column's type, so qty = '12' works and pushes down. A text column compared with a number is cast to the number's type, so name = 12 fails with Could not convert string 'bolt' to INT32. Text compares case-sensitively, in byte order: 'Bracket' < 'bolt' is true.
• NULLs: a comparison with NULL is NULL, so WHERE qty > NULL selects no rows. To compare NULLs as values, use IS DISTINCT FROM.
• Pushed down: against a constant of the column's type, or a constant expression such as 1 + 1 or TIMESTAMP '2026-10-01 00:00:00' + INTERVAL 1 DAY.

for condition in ["qty >= 7", "name <> 'bolt'", "name < 'b'", "added > DATE '2026-10-02'",
                  "qty > 1 + 1", "qty > 5.5", "qty > id", "price * 2 > 0.4", "price > 0.4 / 2"]:
    where_it_runs(condition)
run("SELECT 'Bracket' < 'bolt', 1 = NULL")

qty >= 7                                         [1, 3, 4]        pushed down
name <> 'bolt'                                   [2, 3, 4]        pushed down
name < 'b'                                       [2]              pushed down
added > DATE '2026-10-02'                        [3, 4]           pushed down
qty > 1 + 1                                      [1, 2, 3, 4]     pushed down
qty > 5.5                                        [1, 3, 4]        Query Engine (FILTER)
qty > id                                         [1, 2, 3, 4]     Query Engine (FILTER)
price * 2 > 0.4                                  [1, 2]           Query Engine (FILTER)
price > 0.4 / 2                                  [1, 2]           pushed down
[(True, None)]

BETWEEN

True if a value lies in a range, ends included: x BETWEEN a AND b is x >= a AND x <= b.

• Syntax: x [NOT] BETWEEN a AND b
• Returns: BOOLEAN. With a greater than b, it's false.
• NULLs: NULL if x, or a bound it depends on, is NULL.
• Pushed down: BETWEEN yes; NOT BETWEEN no. Write x < a OR x > b instead.

for condition in ["qty BETWEEN 5 AND 12", "qty NOT BETWEEN 5 AND 12", "qty < 5 OR qty > 12"]:
    where_it_runs(condition)

qty BETWEEN 5 AND 12                             [1, 2, 4]        pushed down
qty NOT BETWEEN 5 AND 12                         [3, 5]           Query Engine (FILTER)
qty < 5 OR qty > 12                              [3, 5]           pushed down

IN

True if a value equals one in a list.

• Syntax: x [NOT] IN (v1, v2, ...)
• Returns: BOOLEAN.
• NULLs: NULL if x is NULL, or if x isn't found and the list holds a NULL: 2 IN (1, NULL) and 2 NOT IN (1, NULL) are both NULL.
• Pushed down: a list of consecutive integers, of any length, becomes a range and pushes down. Any other list of up to four values, numbers or text, is evaluated in the Query Engine.
• Errors: a list of five or more values that aren't consecutive integers fails with The operator 'HASH_JOIN' type=32 is currently not supported., as does IN (SELECT ...). Write the values as ORs on the column instead, which pushes down at any length (IN lists).

for condition in ["id IN (1, 2, 3)", "id IN (1, 3, 5)", "id = 1 OR id = 3 OR id = 5",
                  "name IN ('bolt', 'washer')", "id IN (1, 3, 5, 7, 9)", "id = 1 OR id = 3 OR id = 5 OR id = 7 OR id = 9"]:
    where_it_runs(condition)

id IN (1, 2, 3)                                  [1, 2, 3]        pushed down
id IN (1, 3, 5)                                  [1, 3, 5]        Query Engine (FILTER)
id = 1 OR id = 3 OR id = 5                       [1, 3, 5]        pushed down
name IN ('bolt', 'washer')                       [1, 4]           Query Engine (FILTER)
id IN (1, 3, 5, 7, 9)                            InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
id = 1 OR id = 3 OR id = 5 OR id = 7 OR id = 9   [1, 3, 5]        pushed down

IS DISTINCT FROM

Compares two values and treats NULL as a value: x IS DISTINCT FROM y is true when they differ or only one is NULL. IS NOT DISTINCT FROM is its opposite.

• Syntax: x IS [NOT] DISTINCT FROM y
• Returns: BOOLEAN, never NULL.
• Pushed down: no.

run("SELECT NULL IS DISTINCT FROM NULL, 1 IS DISTINCT FROM NULL, NULL = NULL")
where_it_runs("qty IS DISTINCT FROM 12")

[(False, True, None)]
qty IS DISTINCT FROM 12                          [2, 3, 4, 5]     Query Engine (FILTER)

Struct fields

A field of a struct column, written column.field, compares like a column. Comparing it with a constant pushes down; IS NULL on it doesn't.

for condition in ["bin.aisle = 3", "bin.aisle > 1", "bin.aisle IS NULL"]:
    where_it_runs(condition)

bin.aisle = 3                                    [2]              pushed down
bin.aisle > 1                                    [2, 3]           pushed down
bin.aisle IS NULL                                [4, 5]           Query Engine (FILTER)

NULL and boolean tests

IS NULL and IS NOT NULL

True if a value is NULL, or isn't.

• Syntax: x IS NULL, x IS NOT NULL
• Returns: BOOLEAN, never NULL.
• Pushed down: on a number or text column. On a list or struct column, or a struct field, it's evaluated in the Query Engine.

for condition in ["name IS NULL", "name IS NOT NULL", "tags IS NULL", "bin IS NULL"]:
    where_it_runs(condition)

name IS NULL                                     [5]              pushed down
name IS NOT NULL                                 [1, 2, 3, 4]     pushed down
tags IS NULL                                     [4]              Query Engine (FILTER)
bin IS NULL                                      [4]              Query Engine (FILTER)

Boolean tests

A BOOLEAN column can be a condition by itself, and IS TRUE and IS NOT TRUE test it without NULLs: x IS NOT TRUE is true for false and for NULL.

• Syntax: WHERE flag, NOT flag, flag = true, flag IS [NOT] TRUE
• Returns: BOOLEAN.
• Pushed down: flag = true and flag = false. The bare column, NOT flag and IS [NOT] TRUE are evaluated in the Query Engine.

for condition in ["in_stock", "in_stock = true", "NOT in_stock", "in_stock = false", "in_stock IS NOT TRUE"]:
    where_it_runs(condition)

in_stock                                         [1, 2]           Query Engine (FILTER)
in_stock = true                                  [1, 2]           pushed down
NOT in_stock                                     [3, 5]           Query Engine (FILTER)
in_stock = false                                 [3, 5]           pushed down
in_stock IS NOT TRUE                             [3, 4, 5]        Query Engine (FILTER)

Logic

AND, OR and NOT

Combine conditions.

• Syntax: a AND b, a OR b, NOT a
• Returns: BOOLEAN.
• NULLs: three-valued logic: NULL AND false is false, NULL OR true is true, and the other combinations with NULL are NULL.
• Pushed down: an AND of conditions that push down; an OR of tests on the same column; NOT of one comparison. An OR across columns, and NOT of an AND, are evaluated in the Query Engine.

for condition in ["qty > 5 AND price < 1", "id = 1 OR qty > 20", "qty < 6 OR qty > 30",
                  "NOT (qty > 5)", "NOT (qty > 5 AND id < 3)"]:
    where_it_runs(condition)
run("SELECT NULL AND false, NULL OR true, NULL AND true")

qty > 5 AND price < 1                            [1, 3, 4]        pushed down
id = 1 OR qty > 20                               [1, 3]           Query Engine (FILTER)
qty < 6 OR qty > 30                              [2, 3, 5]        pushed down
NOT (qty > 5)                                    [2, 5]           pushed down
NOT (qty > 5 AND id < 3)                         [2, 3, 4, 5]     Query Engine (FILTER)
[(False, True, None)]

Pattern matching

LIKE and ILIKE

LIKE matches text against a pattern in which % stands for any run of characters and _ for one character. ILIKE does the same, ignoring case. The operators ~~, !~~, ~~* and !~~* are LIKE, NOT LIKE, ILIKE and NOT ILIKE.

• Syntax: s [NOT] LIKE pattern [ESCAPE c], s [NOT] ILIKE pattern [ESCAPE c]
• Escapes: there's no default escape character, so \_ matches a backslash and any character. Name one with ESCAPE, as in name LIKE 'cable\_%' ESCAPE '\'. The functions like_escape(s, pattern, c), not_like_escape, ilike_escape and not_ilike_escape do the same.
• Returns: BOOLEAN. LIKE is case-sensitive.
• NULLs: NULL if s or the pattern is NULL.
• Errors: an escape of more than one character fails with Invalid escape string. Escape string must be empty or one character.
• Pushed down: only LIKE without a wildcard, which becomes =. LIKE with a wildcard, and every ILIKE, are evaluated in the Query Engine. For a prefix, a range such as name >= 'b' AND name < 'c' pushes down.

for condition in ["name LIKE 'bolt'", "name LIKE 'b%'", "name >= 'b' AND name < 'c'", "name LIKE '%a%'",
                  "name ILIKE 'b%'", "name LIKE 'cable\\_%' ESCAPE '\\'"]:
    where_it_runs(condition)

name LIKE 'bolt'                                 [1]              pushed down
name LIKE 'b%'                                   [1]              Query Engine (FILTER)
name >= 'b' AND name < 'c'                       [1]              pushed down
name LIKE '%a%'                                  [2, 3, 4]        Query Engine (FILTER)
name ILIKE 'b%'                                  [1, 2]           Query Engine (FILTER)
name LIKE 'cable\_%' ESCAPE '\'                  [3]              Query Engine (FILTER)

GLOB

Matches text against a shell-style pattern: * for any run of characters, ? for one character, and [...] for one of a set. Case-sensitive. The operator ~~~ is the same.

• Syntax: s GLOB pattern
• Returns: BOOLEAN.
• NULLs: NULL if either side is NULL.
• Pushed down: no, even without wildcards.

run("SELECT 'bolt' GLOB 'b?lt', 'bolt' GLOB '[ab]*', 'bolt' GLOB 'B*'")
where_it_runs("name GLOB '*_*'")

[(True, True, False)]
name GLOB '*_*'                                  [3]              Query Engine (FILTER)

SIMILAR TO and regular-expression match

SIMILAR TO and the operator ~ are true if a regular expression matches the whole text; NOT SIMILAR TO and !~ are their opposites. Both are case-sensitive. To match part of the text, use regexp_matches() (regular expressions).

• Syntax: s [NOT] SIMILAR TO pattern, s ~ pattern, s !~ pattern
• Returns: BOOLEAN.
• NULLs: NULL if either side is NULL.
• Errors: an invalid pattern fails when the query is planned, as in missing ): (.
• Pushed down: no.

run("SELECT 'bolt' SIMILAR TO 'b.*', 'bolt' SIMILAR TO 'o', 'bolt' ~ 'b', 'bolt' ~ 'b.*'")
where_it_runs("name SIMILAR TO '[a-c].*'")

[(True, False, False, True)]
name SIMILAR TO '[a-c].*'                        [1, 3]           Query Engine (FILTER)

Starts with

s ^@ prefix is true if s starts with prefix, as are the functions starts_with(s, prefix) and prefix(s, prefix).

• Returns: BOOLEAN. An empty prefix matches everything.
• NULLs: NULL if either side is NULL.
• Pushed down: no. Use a range on the column instead, as under LIKE.

where_it_runs("name ^@ 'b'")

name ^@ 'b'                                      [1]              Query Engine (FILTER)

Arithmetic

Arithmetic in WHERE is evaluated in the Query Engine, except where the planner can rewrite an integer comparison onto the column (see how operators run). Multiplication binds more tightly than addition, so 2 + 3 * 4 is 14. Date and time arithmetic is on the date and time page.

Addition, subtraction and multiplication

a + b, a - b, a * b, and the negation -a.

• Arguments: numbers. DATE, TIMESTAMP and INTERVAL values too (date and time).
• Returns: the wider of the two types: INTEGER with INTEGER gives INTEGER, BIGINT with INTEGER gives BIGINT, and with a DOUBLE it's DOUBLE. DECIMAL stays exact: 0.1 + 0.2 is 0.3, as a Decimal.
• NULLs: NULL if either side is NULL.
• Errors: a result too large for its integer type stops the query, as in Overflow in multiplication of INT32 (40 * 1000000000)!. Cast to BIGINT first for larger results.

run(f"SELECT qty + 1, qty * 2, -qty, typeof(id + qty), 0.1 + 0.2 FROM {TABLE} WHERE id = 1")
run(f"SELECT qty * 1000000000 FROM {TABLE} WHERE id = 3")
run(f"SELECT CAST(qty AS BIGINT) * 1000000000 FROM {TABLE} WHERE id = 3")

[(13, 24, -12, 'BIGINT', Decimal('0.3'))]
OSError: Overflow in multiplication of INT32 (40 * 1000000000)!
[(40000000000,)]

Division and remainder

a / b divides and always returns a DOUBLE, also for integers. a // b is integer division, which drops the fraction toward zero, and a % b is the remainder, which takes the sign of a.

• Arguments: numbers.
• Returns: / a DOUBLE; // and % the type of the arguments.
• NULLs: NULL if either side is NULL.
• Division by zero: / returns inf; // and % return NULL. None of them raises an error.

run("SELECT 7 / 2, 7 // 2, -7 // 2, 7 % 2, -7 % 2")
run("SELECT 1 / 0, 1 // 0, 1 % 0")

[(3.5, 3, -3, 1, -1)]
[(inf, None, None)]

Power and absolute value

a ** b and a ^ b raise a to the power b; @ a is the absolute value, like abs(a).

• Returns: ** and ^ a DOUBLE; @ the type of a.
• NULLs: NULL in, NULL out.

run("SELECT 2 ** 10, 2 ^ 0.5, @ -5")

[(1024.0, 1.4142135623730951, 5)]

Bitwise operators

These work on integers (TINYINT to HUGEINT, signed and unsigned) and return the type of their arguments. They also work on bit strings (binary, hash and UUID functions). On a DECIMAL they fail with No function matches the given name and argument types; cast to an integer first. In WHERE, they're evaluated in the Query Engine.

AND, OR, XOR and NOT on bits

a & b (AND), a | b (OR), xor(a, b) (exclusive OR) and ~a (NOT). There's no XOR operator: # is a syntax error.

• Returns: the integer type of the arguments. ~ of a signed integer is negative for a positive one: ~12 is -13.
• NULLs: NULL if either side is NULL.

run("SELECT 12 & 10, 12 | 10, xor(12, 10), ~12")
run(f"SELECT id, qty & 6 FROM {TABLE} ORDER BY id LIMIT 10")

[(8, 14, 6, -13)]
[(1, 4), (2, 4), (3, 0), (4, 6), (5, 0)]

Shifts

a << n shifts the bits of a left by n; a >> n shifts them right. A right shift past the width gives 0.

• Returns: the integer type of a.
• NULLs: NULL if either side is NULL.
• Errors: a left shift that overflows fails with Overflow in left shift (1 << 31); a negative shift with Cannot left-shift by negative number -1.

run("SELECT 1 << 4, 256 >> 4, 256 >> 40")
run("SELECT 1 << 31")

[(16, 16, 0)]
OSError: Overflow in left shift (1 << 31)

List operators

|| joins lists, and strings too; &&, @> and <@ test lists against each other.

Concatenation

a || b joins two strings, two lists, or two BLOB values. Other values are turned into text first: 1 || 2 is the VARCHAR '12'.

• Returns: VARCHAR for text and other values; a list for lists.
• NULLs: NULL if either side is NULL. To skip NULLs, use concat() (string functions).

run(f"SELECT name || '-' || id, name || NULL FROM {TABLE} ORDER BY id LIMIT 2")
run("SELECT 1 || 2, [1, 2] || [3]")

[('bolt-1', None), ('Bracket-2', None)]
[('12', [1, 2, 3])]

List overlap and containment

l1 && l2 is true if the lists share an element; l1 @> l2 if l1 contains every element of l2; l1 <@ l2 if every element of l1 is in l2.

• Arguments: two lists of comparable element types. Lists of different types, such as INTEGER and VARCHAR, fail with '&&' cannot compare lists of different types: 'INTEGER' and 'VARCHAR'.
• Returns: BOOLEAN. Every list contains the empty list.
• NULLs: NULL if either list is NULL. NULL elements don't match each other: [1, NULL] && [NULL] is false.
• Pushed down: no. More list functions are on lists and structs.

run(f"SELECT id, tags && ['small'], tags @> ['metal'] FROM {TABLE} ORDER BY id LIMIT 10")
where_it_runs("tags && ['small', 'misc']")

[(1, True, True), (2, False, True), (3, True, False), (4, None, None), (5, False, False)]
tags && ['small', 'misc']                        [1, 3, 5]        Query Engine (FILTER)

Not available in the VAST Query Engine

run(f"SELECT id FROM {TABLE} WHERE id IN (SELECT 1)")

InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.

Clean up:

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

[]
[]

See also

• SELECT (where filters run, IN lists) and EXPLAIN.
• Pushdowns: which conditions VAST DataBase evaluates, for every client.
• String functions, numeric functions and date and time functions.
• Functions.

Function pages for operators

The arithmetic and bitwise operators have their own pages, with every type, edge case and error: