Bitwise operators on BIT values

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Bit strings. & (AND), | (OR), xor(), ~ (NOT) and the shifts << and >> work on BIT values bit by bit, and return a BIT value of the same length: '1100'::BIT & '1010'::BIT is '1000'. The same operators on integers are on their own pages (Similar functions).

Syntax

a & b
a | b
xor(a, b)
~a
a << n
a >> n

Returns: BIT, the same length as a.

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_bitwise_operators_on_bit. 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 and a mask for each, stored as VARCHAR strings of 0s and 1s of the same length in each row. 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_bitwise_operators_on_bit"'

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

Each operator works position by position:

The result always has the length of a: a shift drops the bits that move out, and a shift by the length or more gives all 0s. xor is a function, not an operator: ^ is the power operator and # isn't an operator.

One operand can be a string literal of 0s and 1s when the other is a BIT value: '1100' & '1010'::BIT works. A VARCHAR column must be cast: CAST(bits 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 '101'::BIT & NULL, NULL | '101'::BIT, xor('101'::BIT, NULL), '1011'::BIT << NULL, NULL::BIT >> 1")
run("SELECT CAST(xor('1100'::BIT, '1100'::BIT) AS VARCHAR), CAST('1100'::BIT << 4 AS VARCHAR), "
    "CAST('1100'::BIT >> 100 AS VARCHAR), CAST('1100'::BIT >> -1 AS VARCHAR)")
run("SELECT CAST('1100'::BIT << -1 AS VARCHAR)")
run("SELECT '1100'::BIT & '10'::BIT")
run("SELECT '1100' & '1010'")

[(None, None, None, None, None)]
[('0000', '0000', '0000', '0000')]
OSError: Cannot left-shift by negative number -1
OSError: Cannot AND bit strings of different sizes
InternalError: Binder Error: Could not choose a best candidate function for the function call "&(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

Combine each row's flags with its mask. The operators return BIT values, so each is cast to VARCHAR:

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

[(1, '1011', '1100', '1000', '1111', '0111'), (2, '0', '1', '0', '1', '1'), (3, None, None, None, None, None), (4, '10000000', '11111111', '10000000', '11111111', '01111111'), (5, '0110', '0011', '0010', '0111', '0101')]

Invert and shift each row's flags:

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

[(1, '1011', '0100', '1100', '0010'), (2, '0', '1', '0', '0'), (3, None, None, None, None), (4, '10000000', '01111111', '00000000', '00100000'), (5, '0110', '1001', '1000', '0001')]

Keep the rows that have at least one of their mask's flags set:

run(f"SELECT id, bits, mask FROM {TABLE} "
    f"WHERE bit_count(CAST(bits AS BIT) & CAST(mask AS BIT)) > 0 ORDER BY id LIMIT 10")

[(1, '1011', '1100'), (4, '10000000', '11111111'), (5, '0110', '0011')]

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 an operator applied to a column, so these operators are always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the operator for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes the operator 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_count(CAST(bits AS BIT) & CAST(mask AS BIT)) > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (bit_count((CAST(bits AS │
│  BIT) & CAST(mask AS BIT))│
│           ) > 0)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            bits           │
│            mask           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare stored strings when you test for one exact pattern. 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. The plan has no FILTER step:

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

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

• Store a flag you filter on often in its own column. A BOOLEAN or integer column compared with a constant is pushed down; a mask test on a bit string isn't.
• Combine it with a pushed-down condition. In id > 100 AND bit_count(...) > 0, id > 100 is still evaluated in VAST DataBase, so the operators run only on the rows that pass it.

Limitations and errors

• Same length only. &, | and xor on bit strings of different lengths fail with Cannot AND bit strings of different sizes (or OR, XOR). Pad the shorter one with bitstring first.

run("SELECT CAST(bitstring('10', 4) & '1110'::BIT AS VARCHAR)")

[('0010',)]

• No negative left shift. << -1 fails with Cannot left-shift by negative number -1; >> -1 returns all 0s.
• Don't mix BIT and integer operands. 123 & '1100'::BIT fails with No function matches the given name and argument types '&(INTEGER_LITERAL, BIT)'. Cast both to BIT, or both to integers.
• No ^ or # for XOR, and no ~~. ^ is the power operator and fails on BIT values with No function matches the given name and argument types '^(BIT, BIT)'; # is a syntax error. ~~ is a different operator, which takes two VARCHAR values, so ~~b fails with No function matches the given name and argument types '~~(BIT)'.

run("SELECT '1100'::BIT ^ '1010'::BIT")
run("SELECT CAST(~~'1100'::BIT AS VARCHAR)")

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

• xor isn't for booleans. xor(true, false) fails with No function matches the given name and argument types 'xor(BOOLEAN, BOOLEAN)'.

Similar functions

The same operators work on integers, on their own pages. They return an integer:

OR on integers is on bitwise OR.

run("SELECT CAST('1100'::BIT & '1010'::BIT AS VARCHAR), 12 & 10, CAST(xor('1100'::BIT, '1010'::BIT) AS VARCHAR), "
    "xor(12, 10), CAST(~'1100'::BIT AS VARCHAR), ~12")
run("SELECT CAST('1100'::BIT << 1 AS VARCHAR), 12 << 1, CAST('1100'::BIT >> 1 AS VARCHAR), 12 >> 1")

[('1000', 8, '0110', 6, '0011', -13)]
[('1000', 24, '0110', 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 bit string functions.
• BIT values and casts, bitstring and bit_count.
• Where filters run and pushdowns.