>> (shift right)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Bitwise operators. a >> n shifts the bits of a right by n places, which divides a by 2 to the power n and rounds down: 256 >> 4 is 16. A negative number keeps its sign, and a shift past the width of the type gives 0.

Syntax

a >> n

Returns: the integer type of the arguments: 256 >> 4 is an INTEGER. For a BIT value, 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_operators_shift_right. 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 n, each with a number of places k to shift it by. 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_operators_shift_right"'

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}.shifts"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, n INTEGER, k INTEGER)")
run(f"INSERT INTO {TABLE} VALUES (1, 256, 4), (2, -16, 2), (3, 100000, 7), (4, NULL, NULL)")

[]
[]
[(4,)]

What it does

a >> n moves every bit of a down by n places and drops the bits that fall off the end, so the result is a divided by 2 to the power n, rounded down: 100 >> 3 is 12.

• Negative numbers keep their sign. The places at the top are filled with copies of the sign bit, so -16 >> 2 is -4 and -1 >> 10 stays -1. Rounding down means towards minus infinity: -100 >> 3 is -13, where -100 // 8 is -12.
• A shift past the width gives 0. 256 >> 40 on an INTEGER is 0, and so is -1 >> 63; the largest value of a type shifted by its width in bits is 0 too. A negative n gives 0 too: 256 >> -1 is 0. None of these is an error.
• Unsigned types fill with zeros: 255::UTINYINT >> 1 is 127.

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 256 >> 4, 256 >> 8, 256 >> 9, 256 >> 0, 256 >> 40")
run("SELECT -16 >> 2, -1 >> 10, -1 >> 63, 256 >> -1")
run("SELECT (-128)::TINYINT >> 1, 255::UTINYINT >> 1, 9223372036854775807 >> 62, 1::HUGEINT >> 200")
run("SELECT NULL >> 1, 1 >> NULL")
run("SELECT 1.5 >> 1")
run(f"SELECT 'abc' >> k FROM {TABLE} LIMIT 1")

[(16, 1, 0, 256, 0)]
[(-4, -1, 0, 0)]
[(-64, 127, 1, Decimal('0'))]
[(None, None)]
InternalError: Binder Error: No function matches the given name and argument types '>>(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call ">>(STRING_LITERAL, INTEGER)". In order to select one, please add explicit type casts.

Examples

Shift each value by its own number of places. -16 keeps its sign:

run(f"SELECT id, n, k, n >> k FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 256, 4, 16), (2, -16, 2, -4), (3, 100000, 7, 781), (4, None, None, None)]

Take the high part of each value: n >> 4 drops the lowest 4 bits. Keep the rows where it's larger than 10:

run(f"SELECT id, n >> 4 FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE (n >> 4) > 10 ORDER BY id LIMIT 10")

[(1, 16), (2, -1), (3, 6250), (4, None)]
[(1,), (3,)]

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 columns and the Query Engine computes >> for each row it returns.
• In WHERE, the scan sends the columns 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 FROM {TABLE} WHERE (n >> 4) > 10")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      ((n >> 4) > 10)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             n             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Rewrite a shifted comparison as a range on the column. (n >> 10) = 1 holds for exactly the values from 1024 up to, not including, 2048, so n >= 1024 AND n < 2048 gives the same rows. That range is a comparison of the raw column with constants, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE n >= 1024 AND n < 2048")

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

• Store the part you filter on. If you often filter on the high bits of a value, keep them in their own column when you write the rows.

Limitations and errors

• 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. A string literal fails with Could not choose a best candidate function, because it could be a BIT or an integer; cast it.
• Rounds towards minus infinity. For a negative a, a >> n and a // 2^n can differ by 1. Use // to round towards zero.
• A shift past the width isn't an error. It gives 0, for negative values too, and so does a negative n. Check n yourself if a shift that large means bad data.

run("SELECT -100 >> 3, -100 // 8, CAST('12' AS INTEGER) >> 1")

[(-13, -12, 6)]

Similar functions

run("SELECT 100 >> 3, -100 >> 3, 100 // 8, -100 // 8, 100 << 3")
run("SELECT -100 << 3")

[(12, -13, 12, -12, 800)]
OSError: Cannot left-shift negative number -100

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.
• << (shift left), & (bitwise AND).
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.