xor

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Bits. xor(a, b) returns an integer whose bits are set where exactly one of a and b has them set: the bitwise exclusive OR. Use it to flip flags in a bit mask, or to find the bits in which two values differ. There's no XOR operator.

Syntax

xor(a, b)

Returns: the integer type of the arguments. When they differ, the result is the wider type: xor(12::TINYINT, 300) is an INTEGER.

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_numeric_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 four pairs of integers. 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_numeric_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}.pairs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, a INTEGER, b INTEGER)")
run(f"INSERT INTO {TABLE} VALUES (1, 12, 10), (2, -18, 2), (3, 7, 3), (4, NULL, NULL)")

[]
[]
[(4,)]

What it does

xor compares the two values bit by bit and sets a bit in the result where exactly one of them has it set: xor(12, 10) is 6, because 12 is 1100 in binary, 10 is 1010, and they differ in 0110. A value combined with itself gives 0, and with 0 gives itself. Negative numbers are in two's complement: xor of the largest value of a type with -1 flips every bit and gives the smallest, so xor(2147483647, -1) is -2147483648.

Applying the same mask twice gives back the original value, so xor(perms, 2) turns the bit with value 2 on where it's off and off where it's on. On BIT values xor works bit by bit as well; see Bitwise operators on BIT values.

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 xor(12, 10), xor(-1, 0), xor(5, 5), xor(NULL, NULL), xor(1, NULL), xor(NULL, 1)")
run("SELECT xor(2147483647, -1), xor(12::TINYINT, 300), typeof(xor(12::TINYINT, 300)), "
    "xor(12::UTINYINT, 255::UTINYINT), typeof(xor(12::UTINYINT, 255::UTINYINT))")
run(f"SELECT id, xor('12', a) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT xor('abc', a) FROM {TABLE} LIMIT 1")
run("SELECT xor(1.5, 1)")
run("SELECT xor(true, false)")

[(6, -1, 0, None, None, None)]
[(-2147483648, 288, 'INTEGER', 243, 'UTINYINT')]
[(1, 0), (2, -30), (3, 11), (4, None)]
OSError: Could not convert string 'abc' to INT32
InternalError: Binder Error: No function matches the given name and argument types 'xor(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'xor(BOOLEAN, BOOLEAN)'. You might need to add explicit type casts.

Examples

The exclusive OR of each pair, and the number of bits in which the two values differ (bit_count of the result):

run(f"SELECT id, a, b, xor(a, b), bit_count(xor(a, b)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 12, 10, 6, 2), (2, -18, 2, -20, 29), (3, 7, 3, 4, 1), (4, None, None, None, None)]

Flip the bit with value 2 in a; flipping it again gives back a. Then keep the rows where the two values differ in more than 2 bits:

run(f"SELECT id, a, xor(a, 2), xor(xor(a, 2), 2) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE bit_count(xor(a, b)) > 2 ORDER BY id LIMIT 10")

[(1, 12, 14, 12), (2, -18, -20, -18), (3, 7, 5, 7), (4, None, None, None)]
[(2,)]

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 xor() on a column is always computed by the Query Engine:

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

The plan of a WHERE condition on xor() shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE xor(a, 1) = 13")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (xor(a, 1) = 13)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             a             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Move the mask to the constant side. Because applying a mask twice gives back the value, xor(a, 1) = 13 holds for the same rows as a = xor(13, 1). xor() of constants is computed once, so a = xor(13, 1) is a comparison of the raw column with a constant, which is pushed down to VAST DataBase. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE a = xor(13, 1)")
run(f"SELECT id FROM {TABLE} WHERE a = xor(13, 1) ORDER BY id LIMIT 10")

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

Limitations and errors

• No XOR operator. 12 # 10 fails with Parser Error: syntax error at or near "#", and there's no bitxor function. Use xor(a, b).
• Integers and BIT only. A DECIMAL, DOUBLE, FLOAT, BOOLEAN, DATE, INTERVAL, BLOB or list fails with No function matches the given name and argument types, for example 'xor(DOUBLE, INTEGER)'. Cast the value to an integer type first.
• No boolean exclusive OR. xor(true, false) fails. For two booleans, a <> b is true when exactly one of them is.
• A string must be a whole number. A string next to an integer is converted to that integer's type at run time; xor('abc', a) fails with Could not convert string 'abc' to INT32.

run("SELECT 12 # 10")
run("SELECT bitxor(12, 10)")
run("SELECT true <> false, true <> true")

InternalError: Parse query: 'SELECT 12 # 10' failed with: Parser Error: syntax error at or near "#"
InternalError: Catalog Error: Scalar Function with name bitxor does not exist!
[(True, False)]

Similar functions

run("SELECT xor(12, 10), 12 & 10, 12 | 10, ~12")

[(6, 8, 14, -13)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• Operators and predicates: the bitwise operators.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.