list_bit_xor

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Bitwise list aggregates. Combines the elements of a list with bitwise exclusive OR: a bit is set in the result if it's set in an odd number of the non-NULL elements. It works on lists of integers and of BIT values, and returns one value per row.

Syntax

list_bit_xor(l)

Returns: the element type of the list: list_bit_xor of a BIGINT[] is a BIGINT, of a TINYINT[] a TINYINT, of a BIT[] a 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_list_bit_xor. 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 six rows, each with a list of bit masks. Row 2 has an empty list, row 3 has no list, and row 5 has a NULL element:

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_list_bit_xor"'

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}.masks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, masks BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3]), (2, []), (3, NULL), (4, [-1, 0]), (5, [7, NULL, 1]), (6, [255, 15, 240])")

[]
[]
[(6,)]

What it does

list_bit_xor(l) applies exclusive OR across the elements of l, skipping NULL elements:

• list_bit_xor([12, 10]) is 6: 1100 XOR 1010 is 0110. list_bit_xor([12, 10, 6]) is 0.
• A list of one element returns that element: list_bit_xor([5]) is 5.
• NULL elements are skipped: list_bit_xor([12, NULL, 10]) is 6.
• A list with no non-NULL elements, empty or all NULL, returns NULL.
• Negative integers take part in two's complement: list_bit_xor([-1, 1]) is -2.
• For a list of BIT values, every element must have the same number of bits. Cast the result to VARCHAR to read it, as for any BIT value (BIT values).

NULLs and edge cases

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

run("SELECT list_bit_xor(NULL), list_bit_xor(NULL::INTEGER[]), list_bit_xor([]::INTEGER[]), "
    "list_bit_xor([NULL, NULL]::INTEGER[]), list_bit_xor([12, NULL, 10]), list_bit_xor([-1, 1])")
run("SELECT list_bit_xor([9223372036854775807, -9223372036854775808]), list_bit_xor([255::UTINYINT, 15::UTINYINT]), "
    "typeof(list_bit_xor([255::UTINYINT, 15::UTINYINT]))")
run("SELECT CAST(list_bit_xor(['1100'::BIT, '1010'::BIT]) AS VARCHAR)")
run("SELECT list_bit_xor(['1100'::BIT, '10'::BIT])")
run("SELECT list_bit_xor([true, false])")
run(f"SELECT list_bit_xor(123) FROM {TABLE} LIMIT 1")

[(None, None, None, None, 6, -2)]
[(-1, 240, 'UTINYINT')]
[('0110',)]
OSError: Cannot XOR bit strings of different sizes
InternalError: Binder Error: No matching aggregate function
InternalError: Binder Error: No function matches the given name and argument types 'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

The parity of each bit across the masks in a row. Row 5's NULL element is skipped, and rows 2 and 3 have nothing to combine:

run(f"SELECT id, masks, list_bit_xor(masks) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], 0), (2, [], None), (3, None, None), (4, [-1, 0], -1), (5, [7, None, 1], 6), (6, [255, 15, 240], 0)]

Keep the rows where the lowest bit is set in an odd number of masks:

run(f"SELECT id, masks FROM {TABLE} WHERE list_bit_xor(masks) & 1 = 1 ORDER BY id LIMIT 10")

[(4, [-1, 0])]

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 scan reads a list column whole, and list_bit_xor() always runs in the Query Engine:

• In the SELECT list, a projection step computes the result for each row after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step above the scan computes the condition.

The plan of the WHERE example shows the FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE list_bit_xor(masks) & 1 = 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    ((list_aggr(masks,     │
│    'bit_xor') & 1) = 1)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           masks           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the combined mask if you filter on it often. Compute it when you write the rows and keep it in its own column: a comparison of that column with a constant is evaluated in VAST DataBase, and the function of the list isn't.
• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_bit_xor runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

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

Limitations and errors

• Integers and BIT values only. BOOLEAN, DECIMAL, VARCHAR and DATE lists, and lists of lists, fail with Binder Error: No matching aggregate function.
• The argument must be a list. Anything else fails with Binder Error: No function matches the given name and argument types 'list_aggr(...)'; the message names list_aggr because the function is a form of list_aggregate.
• BIT elements must have the same size. Otherwise the query fails with Cannot XOR bit strings of different sizes.
• The bit_xor aggregate isn't available. SELECT bit_xor(x) FROM t fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. To combine values within a row, put them in a list and use list_bit_xor; to combine them across rows, do it in your client.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run(f"SELECT bit_xor(id) FROM {TABLE}")

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Similar functions

run("SELECT list_bit_xor(l), list_bit_and(l), list_bit_or(l), list_aggregate(l, 'bit_xor'), xor(12, 10) "
    "FROM (SELECT [12, 10] AS l)")

[(6, 8, 14, 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 bit and binary functions.
• Bitwise operators on BIT values and BIT values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.