~ (bitwise NOT)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Bits. ~x returns x with every bit flipped, in the same type as x. For a signed integer that's -x - 1: ~12 is -13. For an unsigned integer it's the largest value of the type minus x: ~5::UTINYINT is 250.

Syntax

~x

Returns: the type of x.

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_bitwise_not. 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 values, each as an integer k and as a bit string bits. 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_bitwise_not"'

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, k INTEGER, bits VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES (1, 7, '0111'), (2, 0, '0'), (3, -128, '10000000'), (4, NULL, NULL)")

[]
[]
[(4,)]

What it does

~ flips each bit of its argument. Integers are in two's complement, so for a signed type ~x equals -x - 1: ~0 is -1, ~(-1) is 0, and ~12 is -13 (the same as xor(12, -1)). It can't overflow: ~ of the largest BIGINT is the smallest. For an unsigned type, flipping the bits gives the type's largest value minus x: ~0::UTINYINT is 255 and ~0::UBIGINT is 18446744073709551615. The result has the type of the argument, so ~ of a TINYINT is a TINYINT.

On a BIT value, ~ flips each bit and keeps the length: ~'1100'::BIT is 0011. Cast the result to VARCHAR to see it as a string of 0s and 1s. See Bitwise operators on BIT values.

~ binds less tightly than + and - but more tightly than =: ~ 5 + 1 is ~(5 + 1), -7, and ~ 5 = -6 is (~5) = -6, true. Use parentheses when you mix them.

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 ~ NULL, ~ CAST(NULL AS INTEGER), ~ 0, ~ (-1), ~ 12, ~ ~ 5")
run("SELECT ~ 127::TINYINT, ~ (-128)::TINYINT, ~ 9223372036854775807")
run("SELECT ~ 0::UTINYINT, ~ 5::UTINYINT, ~ 0::UINTEGER, typeof(~ 0::SMALLINT), typeof(~ 0::UBIGINT)")
run("SELECT ~ 5 + 1, (~ 5) + 1, ~ 5 = -6")
run("SELECT CAST(~'1100'::BIT AS VARCHAR)")
run("SELECT ~ 1.5")
run("SELECT ~ 1.5::DOUBLE")
run("SELECT ~ true")
run("SELECT ~ '5'")

[(None, None, -1, 0, -13, 5)]
[(-128, 127, -9223372036854775808)]
[(255, 250, 4294967295, 'SMALLINT', 'UBIGINT')]
[(-7, -5, True)]
[('0011',)]
InternalError: Binder Error: No function matches the given name and argument types '~(DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '~(DOUBLE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '~(BOOLEAN)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "~(STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

The complement of each value as an INTEGER, as a UTINYINT and as a bit string:

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

[(1, 7, -8, 248, '1000'), (2, 0, -1, 255, '1'), (3, -128, 127, 127, '01111111'), (4, None, None, None, None)]

Keep the rows whose complement is -8:

run(f"SELECT id, k FROM {TABLE} WHERE ~ k = -8 ORDER BY id LIMIT 10")

[(1, 7)]

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 operation on a column, so ~ on a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes ~ for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes ~ 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, k FROM {TABLE} WHERE ~ k = -8")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             k             │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│        (~(k) = -8)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             k             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Move ~ to the constant. ~ is its own inverse, so ~ k = c is the same as k = ~c. An operator on constants is computed before the scan, so the comparison of the raw column can be pushed down to VAST DataBase:

plan(f"SELECT id, k FROM {TABLE} WHERE k = ~(-8)")
run(f"SELECT id, k FROM {TABLE} WHERE k = ~(-8) ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             k             │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             k             │
│             id            │
│                           │
└───────────────────────────┘
[(1, 7)]

Limitations and errors

• Integers and BIT only. A DECIMAL, DOUBLE, BOOLEAN, DATE, BLOB, VARINT or list fails with No function matches the given name and argument types, for example '~(DOUBLE)'. Cast the value to an integer type first. For booleans, use NOT.
• Cast strings first. A string literal fails with Could not choose a best candidate function for the function call "~(STRING_LITERAL)", and a VARCHAR column with '~(VARCHAR)'. Cast to an integer type, or to BIT for a string of 0s and 1s.
• Leave a space in ~ ~. ~~ is the LIKE operator: ~~'1100'::BIT fails with No function matches the given name and argument types '~~(BIT)'. Write ~ ~ x.
• ~ between two values is a regular expression match. s ~ 'H.*d' tests whether the whole of s matches the pattern; see regexp_full_match.
• BIT results come back as bytes. The driver returns a BIT value as bytes; cast it to VARCHAR to read it.

run("SELECT ~ CAST(1.5::DOUBLE AS INTEGER), NOT true")
run("SELECT ~ CAST('5' AS INTEGER), CAST(~ CAST('0101' AS BIT) AS VARCHAR)")
run("SELECT CAST(~~'1100'::BIT AS VARCHAR)")
run("SELECT CAST(~ ~'1100'::BIT AS VARCHAR)")

[(-3, False)]
[(-6, '1010')]
InternalError: Binder Error: No function matches the given name and argument types '~~(BIT)'. You might need to add explicit type casts.
[('1100',)]

Similar functions

run("SELECT ~ 5, -5 - 1, xor(5, -1), ~ 5::UTINYINT, 255 - 5")
run("SELECT 12 & 10, 12 | 10, xor(12, 10), ~12")

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

Clean up:

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

[]
[]

See also

• Operators and predicates: all the operators.
• Bitwise operators on BIT values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.