bit_length

Applies to VAST Cluster 5.5.

Functions › Text basics › Length. Returns the number of bits in a string's UTF-8 encoding (eight per byte), or the number of bits in a BIT value. It has no aliases.

Syntax

bit_length(s)
bit_length(bits)

Returns: BIGINT.

Aliases: none.

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_bit_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 events. Each event has a label and a set of flags, stored as a string of 0 and 1 characters. 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_bit_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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, label VARCHAR, flags VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'login', '101'), "
    "(2, 'café order', '11110000'), "
    "(3, '', '1'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

bit_length measures a value in bits:

On a string, bit_length follows the byte count, not the character count: 'José' has 4 characters, 5 bytes and 40 bits, and 'e\u0301' (e and a combining accent, displayed as é) has 2 code points, 3 bytes and 24 bits. On a BIT value it counts the bits as written, so '101'::BIT gives 3, not a multiple of 8.

A table column can't have the BIT type: CREATE TABLE with a BIT column fails with BIT type is not supported in table definitions. Store bit strings as VARCHAR and cast them in the query, as the examples do with CAST(flags AS BIT).

NULLs and edge cases

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

run("SELECT bit_length(NULL), bit_length(''), bit_length('é'), bit_length('e\u0301'), bit_length('101'::BIT)")
run("SELECT bit_length('\\xAA'::BLOB)")
run("SELECT bit_length(['a', 'b'])")
run("SELECT bit_length(123)")

[(None, 0, 16, 24, 3)]
InternalError: Binder Error: No function matches the given name and argument types 'bit_length(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'bit_length(VARCHAR[])'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'bit_length(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The bits of each label, next to its bytes: bit_length is always 8 times strlen on a string. The flags are cast to BIT, so bit_length counts the flags themselves:

run(f"SELECT id, bit_length(label), strlen(label), bit_length(CAST(flags AS BIT)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 40, 5, 3), (2, 88, 11, 8), (3, 0, 0, 1), (4, None, None, None)]

Keep only the labels that take more than 64 bits (8 bytes). café order has 10 characters and 11 bytes:

run(f"SELECT id, label FROM {TABLE} WHERE bit_length(label) > 64 ORDER BY id LIMIT 10")

[(2, 'café order')]

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (bit_length(label) > 64) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           label           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the size if you filter on it often. bit_length(s) > 64 and strlen(s) > 8 select the same rows, and both are computed in a FILTER step. Compute the size when you write the rows and keep it in its own column: label_bytes > 8 is pushed down to VAST DataBase, bit_length(label) > 64 isn't.
• Test for empty strings with = '', not bit_length(s) = 0. A comparison of the raw column with a constant is pushed down, so rows that don't match never leave storage. The plan for label = '' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE label = ''")

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

• Combine it with a pushed-down condition. In id > 1 AND bit_length(label) > 64, id > 1 is still evaluated in VAST DataBase, so bit_length() runs only on the rows that pass it.

Limitations and errors

• Strings and bit strings only. A number, a date, a boolean or a list fails with No function matches the given name and argument types; the error lists bit_length(VARCHAR) and bit_length(BIT) as the forms it accepts.
• No BIT columns. CREATE TABLE with a BIT column fails with BIT type is not supported in table definitions. Store the bits as a VARCHAR of 0 and 1 characters and use bit_length(CAST(col AS BIT)).
• Not for BLOB. bit_length of a BLOB fails the same way. Multiply the byte count instead: 8 * octet_length(b).

run("SELECT bit_length('\\xAA\\xBB'::BLOB)")
run("SELECT 8 * octet_length('\\xAA\\xBB'::BLOB)")

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

Similar functions

run("SELECT bit_length('José'), strlen('José'), length('José'), length_grapheme('José')")
run("SELECT bit_length('101'::BIT), length('101'::BIT)")

[(40, 5, 4, 4)]
[(3, 3)]

Clean up:

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

[]
[]

See also

• Text basics: all the text functions.
• Bit strings: bit_length and the other functions on BIT values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.