hash

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Hashes. Returns a 64-bit hash of one or more values of any type, as an unsigned UBIGINT. Use it to spread rows into buckets or to take a repeatable sample. It isn't a cryptographic digest: for that, use sha256.

Syntax

hash(x [, ...])

Returns: UBIGINT, an unsigned 64-bit integer.

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_binary_hash_uuid_hash. 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 six events. Rows 1 and 3 have the same user and count, rows 2 and 5 have 0.0 and -0.0 as their amount, 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_binary_hash_uuid_hash"'

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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, user_name VARCHAR, n INTEGER, amount DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 42, 42.0), "
    "(2, 'grace', 7, 0.0), "
    "(3, 'ada', 42, 1.5), "
    "(4, 'linus', 1, 2.5), "
    "(5, 'grace', 9, '-0.0'::DOUBLE), "
    "(6, NULL, NULL, NULL)")

[]
[]
[(6,)]

What it does

hash(x, ...) combines its arguments into one 64-bit value. The same arguments always give the same value, and different arguments almost always give different values, so hash(key) % n spreads rows evenly over n buckets.

• Equal values hash equally across integer types. hash(42), hash(42::TINYINT) and hash(42::BIGINT) are equal, and so are a string and the same bytes as a BLOB.
• The type family matters. hash(42) differs from hash(42.0) (a DECIMAL) and from hash('42'), and hash(42::DOUBLE) from hash(42::FLOAT). Cast your keys to one type before you compare hashes.
• 0.0 and -0.0 hash the same as DOUBLE.
• Several arguments are hashed together, in order. hash('a', 'b') differs from hash('b', 'a'), and hash('hello', 1) from hash('hello').
• Any type works: numbers, text, BLOB, BOOLEAN, dates, UUID, BIT, lists and structs.

NULLs and edge cases

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

run("SELECT hash(NULL) IS NULL, hash(NULL) = hash(NULL::VARCHAR), hash(NULL) = hash(NULL::INTEGER), "
    "hash(NULL) = hash(NULL, NULL), hash(NULL, 1) IS NULL")
run("SELECT hash(42) = hash(42::BIGINT), hash(42::TINYINT) = hash(42::BIGINT), hash(42) = hash(42.0), "
    "hash(42) = hash('42'), hash(42::DOUBLE) = hash(42::FLOAT), hash(0.0::DOUBLE) = hash(-0.0::DOUBLE)")
run("SELECT hash('hello') = hash('hello'::BLOB), hash('a', 'b') = hash('b', 'a'), hash('hello') = hash('hello', 1)")
run("SELECT typeof(hash(true)), typeof(hash(DATE '2026-10-16')), typeof(hash([1, 2, 3])), "
    "typeof(hash({'a': 1})), typeof(hash('nan'::DOUBLE)), typeof(hash('a'))")
run("SELECT hash()")

[(False, True, True, False, False)]
[(True, True, False, False, False, True)]
[(True, False, False)]
[('UBIGINT', 'UBIGINT', 'UBIGINT', 'UBIGINT', 'UBIGINT', 'UBIGINT')]
InternalError: Binder Error: No function matches the given name and argument types 'hash()'. You might need to add explicit type casts.

Examples

Put each user in one of four buckets. Rows with the same user land in the same bucket, and so does the NULL row on every run:

run(f"SELECT id, user_name, hash(user_name) % 4 AS bucket FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'ada', 3), (2, 'grace', 2), (3, 'ada', 3), (4, 'linus', 1), (5, 'grace', 2), (6, None, 1)]

Hash a key made of two columns, and compare hashes of the same number stored as an integer and as a DOUBLE. Rows 1 and 3 have the same key; n and amount hold 42 in row 1, but as different types, so their hashes differ. In row 6 both are NULL, which hash alike:

run(f"SELECT id, hash(user_name, n) = hash('ada', 42), hash(n) = hash(amount) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, hash(amount) = hash(0.0::DOUBLE) FROM {TABLE} WHERE id IN (2, 5) ORDER BY id LIMIT 10")

[(1, True, False), (2, False, False), (3, True, False), (4, False, False), (5, False, False), (6, False, True)]
[(2, True), (5, True)]

Take a repeatable sample of about half of the rows, and count how many fall in it. The same rows are chosen every time the query runs:

run(f"SELECT id FROM {TABLE} WHERE hash(id) % 2 = 0 ORDER BY id LIMIT 10")
run(f"SELECT count(*), sum(CASE WHEN hash(id) % 2 = 0 THEN 1 ELSE 0 END) FROM {TABLE}")

[(1,), (2,), (3,), (4,), (5,)]
[(6, Decimal('5'))]

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

• In the SELECT list, the scan reads the columns and the Query Engine hashes them for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine hashes each row and keeps the ones that match. A sample by hash(id) % 2 = 0 still reads the whole table.

The plan of the sampling query shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE hash(id) % 2 = 0")

┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│    ((hash(id) % 2) = 0)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Store the bucket if you filter on it often. Compute hash(key) % n when you write the rows and keep it in its own column: bucket = 3 is pushed down, hash(key) % 4 = 3 isn't.

Limitations and errors

• Not a digest to share. Don't compare the values with hashes computed by another system; use sha256 for a digest that any system reproduces.
• Not for security. It's a fast hash for spreading values, not a cryptographic one.
• No GROUP BY on a bucket. GROUP BY hash(id) % 2 fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Count one bucket at a time with sum(CASE WHEN ... THEN 1 ELSE 0 END), as in the sampling example.
• At least one argument. hash() fails with No function matches the given name and argument types 'hash()'.
• Other hash names aren't available. xxhash64, murmurhash3_32 and fnv_hash fail with Scalar Function with name ... does not exist!.

run(f"SELECT hash(id) % 2 AS bucket, count(*) FROM {TABLE} GROUP BY hash(id) % 2 ORDER BY bucket LIMIT 10")
run("SELECT xxhash64('a')")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Catalog Error: Scalar Function with name xxhash64 does not exist!

Similar functions

run("SELECT typeof(hash('a', 1)), typeof(md5('a')), typeof(md5_number('a')), typeof(sha1('a')), typeof(sha256('a'))")

[('UBIGINT', 'VARCHAR', 'HUGEINT', 'VARCHAR', 'VARCHAR')]

Clean up:

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

[]
[]

See also

• Binary, hash and UUID functions: all the hash functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.