BLOB values, comparisons and ||

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. A BLOB is a string of bytes. This page shows how to write BLOB constants, how casts to and from BLOB behave, how BLOB values compare, and how || joins them.

Syntax

'\x25\x50\x44\x46'::BLOB
BLOB '\xAA'
CAST(s AS BLOB)
CAST(b AS VARCHAR)
a = b    a <> b    a < b    a <= b    a > b    a >= b
a BETWEEN low AND high
a IN (b1, b2, ...)
a || b

Returns: BOOLEAN for comparisons; BLOB for a || b when both sides are BLOB.

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_blob_values. 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 rows with two BLOB columns: file headers and trailers. Row 2 has empty bytes, row 3 has NULLs, row 4 holds the UTF-8 bytes of héllo, and row 5 bytes that aren't text:

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

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}.chunks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, head BLOB, tail BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '\\x25\\x50\\x44\\x46'::BLOB, '\\x68\\x69'::BLOB), "
    "(2, ''::BLOB, ''::BLOB), "
    "(3, NULL, NULL), "
    "(4, encode('h\u00e9llo'), '\\x00\\x7F'::BLOB), "
    "(5, '\\x00\\x01'::BLOB, '\\xFF\\xFE'::BLOB), "
    "(6, '\\x61'::BLOB, '\\x61'::BLOB)")

[]
[]
[(6,)]

What it does

Writing a constant. Cast a string to BLOB. Each ASCII character stands for its own byte, and \x followed by two hex digits, in either case, stands for any byte: '\x41\x42'::BLOB, 'AB'::BLOB and '\x41B'::BLOB are the same two bytes, and '\xaa'::BLOB = '\xAA'::BLOB is true. BLOB '\xAA' is the same as '\xAA'::BLOB. A NUL byte is \x00: 'a\x00b'::BLOB is three bytes. A character outside ASCII must be written as its bytes, such as \xC3\xA9 for é; to get the UTF-8 bytes of any text, use encode. The client receives BLOB values as Python bytes.

Casts. CAST(s AS BLOB) turns ASCII text into its bytes, and inserting a string into a BLOB column does the same. CAST(b AS VARCHAR) writes each byte that isn't printable ASCII as a \x escape in capitals: CAST('\xFF\x41'::BLOB AS VARCHAR) is '\xFFA'. That includes UTF-8 text: the bytes of héllo cast to 'h\xC3\xA9llo'. To read bytes as UTF-8 text, use decode.

Comparisons. BLOB values compare byte by byte, each byte as a number from 0 to 255; when one value is a prefix of the other, the shorter comes first. So ''::BLOB < '\x00'::BLOB, 'ab'::BLOB < 'b'::BLOB and '\xFF'::BLOB > 'a'::BLOB are all true. A string constant on the other side is cast to BLOB: 'ab'::BLOB = 'ab' is true. BETWEEN, IN and ORDER BY use the same order.

Joining. a || b joins two BLOB values into one. If either side is NULL, the result is NULL.

NULLs and edge cases

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

run("SELECT CAST(NULL AS BLOB), 'ab'::BLOB || NULL, ''::BLOB, 'a\\x00b'::BLOB, octet_length('a\\x00b'::BLOB)")
run("SELECT ''::BLOB < '\\x00'::BLOB, '\\xFF'::BLOB > 'a'::BLOB, 'ab'::BLOB < 'b'::BLOB, '\\xaa'::BLOB = '\\xAA'::BLOB")
run("SELECT 'ab'::BLOB || 'cd', typeof('ab'::BLOB || 'cd'), 'ab'::BLOB || 'cd'::BLOB")
run("SELECT CAST('\\xFF\\x41'::BLOB AS VARCHAR), BLOB '\\xAA'")
run("SELECT '\\x4'::BLOB")
run("SELECT '\u00e9'::BLOB")
run("SELECT CAST(42 AS BLOB)")

[(None, None, b'', b'a\x00b', 3)]
[(True, True, True, True)]
[('abcd', 'VARCHAR', b'abcd')]
[('\\xFFA', b'\xaa')]
InternalError: Conversion Error: Invalid hex escape code encountered in string -> blob conversion of string "\\x4": unterminated escape code at end of blob
InternalError: Conversion Error: Invalid byte encountered in STRING -> BLOB conversion of string "é". All non-ascii characters must be escaped with hex codes (e.g. \\xAA)
OSError: Unimplemented type for cast (INTEGER -> BLOB)

Examples

Compare and join the two columns of each row. Row 3 has NULLs, so every result is NULL:

run(f"SELECT id, head < tail, head = tail, head || tail FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, False, b'%PDFhi'), (2, False, True, b''), (3, None, None, None), (4, False, False, b'h\xc3\xa9llo\x00\x7f'), (5, True, False, b'\x00\x01\xff\xfe'), (6, False, True, b'aa')]

Sort by the bytes. Empty bytes come first, and the NULL comes last:

run(f"SELECT id, head FROM {TABLE} ORDER BY head LIMIT 10")

[(2, b''), (5, b'\x00\x01'), (1, b'%PDF'), (6, b'a'), (4, b'h\xc3\xa9llo'), (3, None)]

Find the rows that start with a PDF header, and show the bytes as text. The cast writes the bytes of row 4 as \x escapes:

run(f"SELECT id FROM {TABLE} WHERE head = '\\x25\\x50\\x44\\x46'::BLOB ORDER BY id LIMIT 10")
run(f"SELECT id, CAST(head AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")

[(1,)]
[(1, '%PDF'), (2, ''), (3, None), (4, 'h\\xC3\\xA9llo'), (5, '\\x00\\x01'), (6, 'a')]

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). What runs where depends on the condition:

• A comparison of a BLOB column with a constant (=, >= and the other comparisons) and IS NULL are pushed down to VAST DataBase: rows that don't match never leave storage, and the plan has no FILTER step.
• IN with a list of BLOB constants isn't pushed down: the scan sends every row and a FILTER step in the Query Engine tests it.
• ||, casts and functions of a column are computed in the Query Engine, after the scan.

The plan of the equality test has no FILTER step; the plan of the IN test has one:

plan(f"SELECT id FROM {TABLE} WHERE head = '\\x25\\x50\\x44\\x46'::BLOB")
plan(f"SELECT id FROM {TABLE} WHERE head IN ('\\x61'::BLOB, ''::BLOB)")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ((head = 'a'::BLOB) OR   │
│     (head = ''::BLOB))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            head           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the stored column with a constant. head = '\x25\x50\x44\x46'::BLOB is pushed down; decode(head) = '%PDF' makes the Query Engine test every row.
• Build the constant with a function if that's easier. A function of a constant is computed once, so head = from_hex('25504446'), head = from_base64('JVBERg==') and head = encode('a') are still pushed down:

plan(f"SELECT id FROM {TABLE} WHERE head = from_hex('25504446')")

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

Limitations and errors

• Text functions don't take a BLOB. length(), substring(), upper() and LIKE fail with No function matches the given name and argument types. Use octet_length for the size, or decode the bytes to text first.
• Few casts. A BLOB casts to and from VARCHAR and BIT (BIT values and casts). CAST(42 AS BLOB), CAST(b AS INTEGER) and CAST(b AS UUID) fail with Unimplemented type for cast.
• Non-ASCII text in a constant. A cast of text with a character outside ASCII fails with Invalid byte encountered in STRING -> BLOB conversion, and so does CAST(col AS BLOB) on a column that holds such text. Use encode.
• A string on one side of || gives a string. 'ab'::BLOB || 'cd' and concat('ab'::BLOB, 'cd'::BLOB) return a VARCHAR. Cast the string side to BLOB to keep bytes.
• min() and max() of a BLOB column aren't available. They fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Use ORDER BY col LIMIT 1.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run("SELECT length('hello'::BLOB)")
run("SELECT 'ab'::BLOB LIKE 'a%'")
run(f"SELECT min(head) FROM {TABLE}")

InternalError: Binder Error: No function matches the given name and argument types 'length(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '~~(BLOB, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Similar functions

run("SELECT CAST(b AS VARCHAR), decode(b), hex(b), to_base64(b), octet_length(b) "
    "FROM (SELECT encode('h\u00e9') AS b)")

[('h\\xC3\\xA9', 'hé', '68C3A9', 'aMOp', 3)]

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 binary functions.
• Data types in SQL: BLOB columns.
• The concatenation operator on strings, lists and other types.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.