bitstring

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Bit strings. Returns a BIT value of a given length, made from a shorter bit string padded on the left with 0s: bitstring('101', 8) is '00000101'.

Syntax

bitstring(bits, length)

Returns: BIT.

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_bitstring. 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 five feature-flag sets, stored as VARCHAR strings of 0s and 1s of different lengths. The third 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_bitstring"'

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}.flags"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, bits VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '1011'), "
    "(2, '0'), "
    "(3, NULL), "
    "(4, '10000000'), "
    "(5, '0110')")

[]
[]
[(5,)]

What it does

bitstring adds 0s to the left of bits until the result is length bits long. A length equal to the length of bits returns the same bits. The first argument can be a VARCHAR of 0s and 1s, including a VARCHAR column, or a BIT value; the result is a BIT value, so cast it to VARCHAR to read it.

Use it to bring bit strings of different lengths to one length, for example before you combine them with the bitwise operators, which need operands of the same length.

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 bitstring(NULL, 20), bitstring('101', NULL), bitstring(NULL::BIT, 4)")
run("SELECT CAST(bitstring('101', 3) AS VARCHAR), CAST(bitstring('0', 1) AS VARCHAR), "
    "CAST(bitstring('101', '8') AS VARCHAR)")
run("SELECT bitstring('101', 2)")
run("SELECT bitstring('101', -1)")
run("SELECT bitstring('', 4)")
run("SELECT bitstring('102', 4)")
run("SELECT bitstring('101', 2.5)")

[(None, None, None)]
[('101', '0', '00000101')]
OSError: Length must be equal or larger than input string
OSError: The bitstring length cannot be negative
OSError: Cannot cast empty string to BIT
OSError: Invalid character encountered in string -> bit conversion: '2'
InternalError: Binder Error: No function matches the given name and argument types 'bitstring(STRING_LITERAL, DECIMAL(2,1))'. You might need to add explicit type casts.

Examples

Pad each stored string to 8 bits. The VARCHAR column and its cast to BIT give the same result:

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

[(1, '1011', '00001011', '00001011'), (2, '0', '00000000', '00000000'), (3, None, None, None), (4, '10000000', '10000000', '10000000'), (5, '0110', '00000110', '00000110')]

Pad, then combine with a mask of the same length:

run(f"SELECT id, CAST(bitstring(bits, 8) & '00001111'::BIT AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '00001011'), (2, '00000000'), (3, None), (4, '00000000'), (5, '00000110')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes bitstring() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes bitstring() and keeps the rows that match. No rows are skipped in storage.

The plan of a filter on bitstring() shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE bitstring(bits, 8) = '00001011'::BIT")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (bitstring(bits, 8) =   │
│          00001011)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            bits           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the padded form. If you filter on bit strings of one length, pad them when you write the rows. A comparison of the raw column with a constant, such as bits = '00001011', is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

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

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

• Combine it with a pushed-down condition. In id > 100 AND bitstring(bits, 8) = '00001011'::BIT, id > 100 is still evaluated in VAST DataBase, so bitstring() runs only on the rows that pass it.

Limitations and errors

• The length can't be shorter than the input. A bit string longer than length fails the query with Length must be equal or larger than input string. Choose a length at least as long as the longest value.
• Only 0s and 1s. Any other character in bits fails with Invalid character encountered in string -> bit conversion, and an empty string with Cannot cast empty string to BIT.
• Two arguments, of the right types. A number, BLOB, date, boolean or list as bits, a decimal length, or a call with one argument fails with No function matches the given name and argument types. The error lists the forms bitstring accepts: bitstring(VARCHAR, INTEGER) and bitstring(BIT, INTEGER).

run("SELECT bitstring(101, 8)")
run("SELECT bitstring('101')")

InternalError: Binder Error: No function matches the given name and argument types 'bitstring(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'bitstring(STRING_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT CAST(bitstring('101', 8) AS VARCHAR), CAST('101'::BIT AS VARCHAR), CAST(CAST(5 AS BIT) AS VARCHAR), bin(5)")

[('00000101', '101', '00000000000000000000000000000101', '101')]

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 bit string functions.
• BIT values and casts and bitwise operators on BIT values.
• Where filters run and pushdowns.