BIT values and casts

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Bit strings. A BIT value is a string of 0s and 1s of any length, such as '1011'::BIT. You build one with a cast, from a VARCHAR of 0s and 1s or from an integer, and cast it to VARCHAR to read it. BIT isn't a table column type: store bit strings as VARCHAR and cast them in the query.

Syntax

'bits'::BIT
CAST(value AS BIT)
TRY_CAST(value AS BIT)
CAST(bit_value AS type)

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_bit_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 five feature-flag sets, stored as VARCHAR strings of 0s and 1s. 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_bit_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}.flags"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, bits VARCHAR, mask VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '1011', '1100'), "
    "(2, '0', '1'), "
    "(3, NULL, NULL), "
    "(4, '10000000', '11111111'), "
    "(5, '0110', '0011')")

[]
[]
[(5,)]

What it does

A cast to BIT reads its input as a sequence of bits:

• A VARCHAR must hold only the characters 0 and 1. Spaces, a 0b prefix or any other character fail the cast, and so does an empty string. TRY_CAST returns NULL instead.
• An integer gives its two's-complement bits, as many as the type has: CAST(42 AS BIT) is 32 bits (42 is an INTEGER), CAST(42::TINYINT AS BIT) is '00101010'.
• A DOUBLE gives the 64 bits of its floating-point form, and a BOOLEAN gives 8 bits.

The client receives a BIT value as bytes in an internal encoding ('1011'::BIT arrives as b'\x04\xfb'), so cast it to VARCHAR to read it, as the examples on this page do. Casting back the other way, '101'::BIT is 5 as an INTEGER or BIGINT, and '1'::BIT is true as a BOOLEAN.

Two BIT values are equal when they have the same bits and the same length: '1011'::BIT = '01011'::BIT is false. A string literal compared with a BIT value is cast to 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 CAST(NULL AS BIT), TRY_CAST('1021' AS BIT), TRY_CAST('' AS BIT)")
run("SELECT ''::BIT")
run("SELECT '1021'::BIT")
run("SELECT ' 1011'::BIT")
run("SELECT CAST(CAST(-1::SMALLINT AS BIT) AS VARCHAR), CAST(CAST(true AS BIT) AS VARCHAR), "
    "'1011'::BIT = '01011'::BIT, length(CAST(repeat('10', 50000) AS BIT))")

[(None, None, None)]
OSError: Cannot cast empty string to BIT
OSError: Invalid character encountered in string -> bit conversion: '2'
OSError: Invalid character encountered in string -> bit conversion: ' '
[('1111111111111111', '00000001', False, 100000)]

Examples

Cast each stored string to BIT, and show what the client receives with and without a cast back to VARCHAR:

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

[(1, b'\x04\xfb', '1011'), (2, b'\x07\xfe', '0'), (3, None, None), (4, b'\x00\x80', '10000000'), (5, b'\x04\xf6', '0110')]

The bits of integers of each size, of a DOUBLE and of a BOOLEAN:

run("SELECT CAST(CAST(42::TINYINT AS BIT) AS VARCHAR), CAST(CAST(42 AS BIT) AS VARCHAR), "
    "CAST(CAST(2.5::DOUBLE AS BIT) AS VARCHAR)")

[('00101010', '00000000000000000000000000101010', '0100000000000100000000000000000000000000000000000000000000000000')]

Read bits back as numbers, as a boolean and as bytes:

run("SELECT CAST('101'::BIT AS INTEGER), CAST('00000000000000000000000000101010'::BIT AS INTEGER), "
    "CAST('101'::BIT AS BIGINT), CAST('1'::BIT AS BOOLEAN), CAST('101'::BIT AS BLOB)")

[(5, 42, 5, True, b'\x05')]

Compare bit strings, and join two of them with ||. The result of || is a VARCHAR, not a BIT value:

run("SELECT '1011'::BIT = '1011'::BIT, '1011'::BIT = '1011', '1011'::BIT < '1100'::BIT, "
    "CAST('1011' AS BIT) || CAST('01' AS BIT), typeof(CAST('1011' AS BIT) || CAST('01' AS BIT))")

[(True, True, True, '101101', 'VARCHAR')]

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). The table holds VARCHAR strings, so a cast to BIT is always computed by the Query Engine:

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

The plan of a filter on the cast shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE CAST(bits AS BIT) = '1011'::BIT")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(bits AS BIT) = 1011)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            bits           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the stored string. bits = '1011' compares the raw VARCHAR column with a constant, so it's pushed down to VAST DataBase and rows that don't match never leave storage. It finds the same rows as CAST(bits AS BIT) = '1011'::BIT as long as the strings are stored without leading spaces or other characters. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE bits = '1011'")
run(f"SELECT id FROM {TABLE} WHERE bits = '1011' ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,)]

• Cast only the rows you need. Combine the cast with a condition on a raw column, such as id > 100: that condition is still evaluated in VAST DataBase, so the cast runs only on the rows that pass it.

Limitations and errors

• No BIT columns. CREATE TABLE with a BIT or BIT[] column, and CREATE TABLE ... AS SELECT of a BIT value, fail with BIT type is not supported in table definitions. Store a VARCHAR of 0s and 1s, or an integer, and cast it in the query.

run(f"CREATE TABLE {SCHEMA}.bits_tbl (id BIGINT, b BIT)")

InternalError: Binder Error: BIT type is not supported in table definitions. Column "b" has type: BIT

• One bad string fails the query. CAST(col AS BIT) on a column with a value that isn't 0s and 1s fails with Invalid character encountered in string -> bit conversion. Use TRY_CAST(col AS BIT) to get NULL for those rows.
• The client receives bytes. Without a cast to VARCHAR, a BIT value arrives as bytes in an internal encoding. Cast it to VARCHAR before you return it.
• Sorting doesn't follow text order. ORDER BY CAST(bits AS BIT) and < between bit strings of different lengths follow the internal encoding, not the order of the text: '011'::BIT < '1'::BIT is true, and so is '10000000'::BIT < '0'::BIT. To sort in text order, sort by the VARCHAR column.

run(f"SELECT id, bits FROM {TABLE} WHERE bits IS NOT NULL ORDER BY CAST(bits AS BIT) LIMIT 10")
run("SELECT '011'::BIT < '1'::BIT, '10000000'::BIT < '0'::BIT")

[(4, '10000000'), (5, '0110'), (1, '1011'), (2, '0')]
[(True, True)]

Similar functions

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

[('101', '00000101', '00000000000000000000000000000101', '100', '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.
• CAST and :: and TRY_CAST.
• Bitwise operators on BIT values.
• Where filters run and pushdowns.