sha256

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Hashes. Returns the SHA-256 digest of a string or of bytes, as 64 lowercase hexadecimal digits. A string is hashed as its UTF-8 bytes, so sha256('hello') and sha256(encode('hello')) are equal.

Syntax

sha256(x)

Returns: VARCHAR, 64 lowercase hexadecimal digits.

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_sha256. 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 files: a name, the same text as bytes in data, and the name's SHA-256 digest stored when the row is written. 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_sha256"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, data BLOB, digest VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'hello', '\\x68\\x65\\x6C\\x6C\\x6F'::BLOB, sha256('hello')), "
    "(2, '', ''::BLOB, sha256('')), "
    "(3, 'h\u00e9llo', encode('h\u00e9llo'), sha256('h\u00e9llo')), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

sha256(x) computes the SHA-256 digest of the bytes of x and returns it as text: 64 hexadecimal digits, in lowercase.

• A string is hashed as its UTF-8 bytes. sha256('hello'), sha256('hello'::BLOB) and sha256(encode('hello')) give the same digest.
• The digest is exact about the bytes. 'hello' and 'Hello' have different digests, and so do é written as one code point and e followed by a combining accent (U+0301), though they look the same.
• The result is lowercase. Compare it with lowercase hex text, or apply lower() to a digest you got elsewhere: upper(sha256('abc')) = sha256('abc') is false.
• Only text and bytes. To hash a number, a date or a UUID, cast it to VARCHAR first: sha256(CAST(42 AS VARCHAR)).

NULLs and edge cases

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

run("SELECT sha256(NULL), sha256(''), sha256(''::BLOB), sha256('abc')")
run("SELECT sha256('hello') = sha256('hello'::BLOB), sha256('hello') = sha256(encode('hello')), sha256('Hello')")
run("SELECT sha256('\u00e9'), sha256('e\u0301'), sha256('\\x00'::BLOB)")
run("SELECT length(sha256('a')), typeof(sha256('a')), sha256(repeat('a', 100000))")

[(None, 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', 'ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad')]
[(True, True, '185f8db32271fe25f561a6fc938b2e264306ec304eda518007d1764826381969')]
[('4a99557e4033c3539de2eb65472017cad5f9557f7a0625a09f1c3f6e2ba69c4c', 'bf12767b0f2a56b2190075bae8169f656e3ce8d6357d4aff184bc6c7ea48f9f6', '6e340b9cffb37a989ca544e6bb780a2c78901d3fb33738768511a30617afa01d')]
[(64, 'VARCHAR', '6d1cf22d7cc09b085dfc25ee1a1f3ae0265804c607bc2074ad253bcc82fd81ee')]

Examples

Hash the text and the bytes of each row. Rows 1 and 3 hold the same text in name and data, so the digests match; the empty row has the digest of no bytes, and the NULL row gives NULL:

run(f"SELECT id, sha256(name), sha256(name) = sha256(data) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824', True), (2, 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', True), (3, '3c48591d8d098a4538f5e013dfcf406e948eac4d3277b10bf614e295d6068179', True), (4, None, None)]

Find the rows whose name has a given digest, and check the digests stored at load time against the names:

run(f"SELECT id, name FROM {TABLE} "
    "WHERE sha256(name) = '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824' ORDER BY id LIMIT 10")
run(f"SELECT id, digest = sha256(name) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'hello')]
[(1, True), (2, True), (3, True), (4, None)]

Hash a number by casting it to text first:

run("SELECT sha256(CAST(42 AS VARCHAR))")

[('73475cb40a568e8da8a045ced110137e159f890ac4da883b6b17dc651b3a8049',)]

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

• In the SELECT list, the scan reads the column and the Query Engine hashes it for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine hashes each value 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 sha256(name) = '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (sha256(name) =      │
│'2cf24dba5fb0a30e26e83b2ac5│
│b9e29e1b161e5c1fa7425e73043│
│       362938b9824')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the digest if you look rows up by it. Compute it when you write the rows, as the setup block does for digest. A comparison of the stored column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage.
• Put the function on the constant side. sha256('hello') in digest = sha256('hello') is computed once, before the scan, so the comparison is still pushed down. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE digest = sha256('hello')")

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

Limitations and errors

• Strings and bytes only. A number, a decimal, a date, a boolean, a list, a UUID or a BIT value fails with No function matches the given name and argument types, for example 'sha256(INTEGER_LITERAL)'. Cast the value to VARCHAR first.
• One argument. sha256('a', 'b') fails the same way. To hash several values together, concatenate them with a separator first, such as sha256(a || '|' || b).
• Other SHA variants aren't available. sha512, sha384, sha224 and sha2(x, 256) fail with Scalar Function with name ... does not exist!. Use sha256, or compute the others in your application.

run("SELECT sha256(123)")
run("SELECT sha2('a', 256)")

InternalError: Binder Error: No function matches the given name and argument types 'sha256(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name sha2 does not exist!

Similar functions

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

[(32, 40, 64, 'HUGEINT', 'UBIGINT')]

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.
• encode: turn a string into its UTF-8 bytes.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.