octet_length

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Returns the number of bytes in a BLOB, or the number of bytes a BIT value takes when its bits are packed eight to a byte. For the bytes of a string, use strlen.

Syntax

octet_length(x)

Returns: BIGINT.

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_octet_length. 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 attachments, each with its bytes and a bit pattern stored as text (a table can't have a BIT column). 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_octet_length"'

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}.attachments"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, data BLOB, bits VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '\\x25\\x50\\x44\\x46'::BLOB, '1011'), "
    "(2, ''::BLOB, '0'), "
    "(3, encode('h\u00e9llo'), '11111111111111111'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

On a BLOB, octet_length counts the bytes: octet_length('hello'::BLOB) is 5. A zero byte counts like any other.

On a BIT value, it counts the bytes needed to hold the bits, eight to a byte, rounded up: 1 to 8 bits take one byte, 9 bits take two, and 1,000 bits take 125. For the number of bits, use length() or bit_length() on the BIT value.

Not on a VARCHAR. A string's byte count depends on its encoding, so octet_length doesn't take one. Use strlen for the bytes of a string's UTF-8 form, or octet_length(encode(s)), which gives the same number: 'héllo' has 5 characters and 6 bytes, because é takes two bytes in UTF-8.

NULLs and edge cases

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

run(f"SELECT id, octet_length(data) FROM {TABLE} WHERE id = 4 LIMIT 10")
run("SELECT octet_length(NULL::BLOB), octet_length(''::BLOB), octet_length('\\x00'::BLOB), "
    "octet_length('11111111'::BIT), octet_length('111111111'::BIT)")
run("SELECT octet_length(NULL)")
run("SELECT octet_length('abc')")
run("SELECT octet_length('abc'::VARCHAR)")
run("SELECT octet_length(123)")

[(4, None)]
[(None, 0, 1, 1, 2)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "octet_length("NULL")". In order to select one, please add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "octet_length(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'octet_length(VARCHAR)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'octet_length(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The size of each attachment in bytes, and the bytes of its bit pattern once cast to BIT:

run(f"SELECT id, octet_length(data), octet_length(CAST(bits AS BIT)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 4, 1), (2, 0, 1), (3, 6, 3), (4, None, None)]

Keep only the attachments larger than 3 bytes:

run(f"SELECT id FROM {TABLE} WHERE octet_length(data) > 3 ORDER BY id LIMIT 10")

[(1,), (3,)]

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 octet_length() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes octet_length() for each row it returns.
• In WHERE, the scan sends the whole BLOB column for every row, and a FILTER step in the Query Engine computes octet_length() 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 octet_length(data) > 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (octet_length(data) > 3) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            data           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the size if you filter on it often. Compute it when you write the rows and keep it in its own BIGINT column: size > 3 is a comparison of a column with a constant, which VAST DataBase evaluates, so the BLOB column isn't read for rows that don't match. octet_length(data) > 3 reads every BLOB.

Limitations and errors

• BLOB and BIT only. A VARCHAR fails with No function matches the given name and argument types 'octet_length(VARCHAR)'; use strlen or octet_length(encode(s)).
• Untyped constants are ambiguous. A string constant or a bare NULL could be a BLOB or a BIT, and fails with Could not choose a best candidate function. Cast it: 'abc'::BLOB.
• BIT values are built in the query. The setup stores the bits as text and casts them with CAST(bits AS BIT).

Similar functions

run("SELECT octet_length(encode('\u00e9')), strlen('\u00e9'), length('\u00e9'), bit_length('\u00e9')")
run("SELECT octet_length('101101'::BIT), length('101101'::BIT), bit_length('101101'::BIT)")

[(2, 2, 1, 16)]
[(1, 6, 6)]

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 functions in this family.
• Data types in SQL: BLOB columns.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.