bin

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary text. Returns the bits of a value as a string of 0s and 1s: for a string, eight digits for each byte of its UTF-8 encoding; for an integer, its value in base 2. to_binary is the same function. To read the digits back into bytes, use from_binary.

Syntax

bin(s)
bin(n)
to_binary(s)
to_binary(n)

Returns: VARCHAR.

Aliases: to_binary.

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_string_bin. 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 words and numbers. 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_string_bin"'

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}.words"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, word VARCHAR, n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'a', 5), "
    "(2, 'hi', 97), "
    "(3, '', -1), "
    "(4, 'caf\u00e9', 255), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

bin has two forms, chosen by the argument's type:

A string is written byte by byte, so a character outside ASCII takes 16, 24 or 32 digits: é is two bytes in UTF-8, an emoji four. A negative integer is written as its 64-bit two's complement: bin(-1) is sixty-four 1s. The string '5' and the number 5 give different results: bin('5') writes the byte of the character 5 (00110101).

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 bin(NULL), bin(''), bin('ab'), bin('\u00e9'), bin('e\u0301'), bin('5'), bin(5), bin(-1)")
run("SELECT to_binary('a'), to_binary(5), length(bin('abc'))")
run("SELECT bin('\\xAA'::BLOB)")
run("SELECT bin(1.5)")

[(None, '', '0110000101100010', '1100001110101001', '011001011100110010000001', '00110101', '101', '1111111111111111111111111111111111111111111111111111111111111111')]
[('01100001', '101', 24)]
InternalError: Binder Error: No function matches the given name and argument types 'bin(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'bin(DECIMAL(2,1))'. You might need to add explicit type casts.

Examples

Each word's bytes, and the number of digits. café has 4 characters and 5 bytes, so 40 digits:

run(f"SELECT id, word, bin(word), length(bin(word)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'a', '01100001', 8), (2, 'hi', '0110100001101001', 16), (3, '', '', 0), (4, 'café', '0110001101100001011001101100001110101001', 40), (5, None, None, None)]

Each number in base 2:

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

[(1, 5, '101'), (2, 97, '1100001'), (3, -1, '1111111111111111111111111111111111111111111111111111111111111111'), (4, 255, '11111111'), (5, None, None)]

Write the bytes and read them back: from_binary returns a BLOB, and decode turns it back into a VARCHAR:

run(f"SELECT id, from_binary(bin(word)), decode(from_binary(bin(word))) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, b'a', 'a'), (2, b'hi', 'hi'), (3, b'', ''), (4, b'caf\xc3\xa9', 'café'), (5, None, None)]

Keep the words that take more than two bytes:

run(f"SELECT id, word FROM {TABLE} WHERE length(bin(word)) > 16 ORDER BY id LIMIT 10")

[(4, 'café')]

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). bin() is a function of a column, so it's computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes bin() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes bin() and keeps the rows that match.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE length(bin(word)) > 16")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (length(bin(word)) > 16) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            word           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Count bytes with strlen, not length(bin(s)). strlen(s) > 2 keeps the same rows as length(bin(s)) > 16 without building a string eight times the size.
• Compare the raw column when you can. A comparison of a column with a constant can be evaluated in VAST DataBase; a condition on bin(column) is always a FILTER step. See pushdowns.

Limitations and errors

• No BLOB. bin('\xAA'::BLOB) fails with No function matches the given name and argument types 'bin(BLOB)'. To see a BLOB's bytes, use hex, or decode it to VARCHAR first if it holds UTF-8 text.
• No dates, booleans, decimals or lists. They fail with the same error. Cast a decimal to an integer type, or a date to VARCHAR, first. The error lists the types bin accepts.

Similar functions

run("SELECT bin('a'), to_binary('a'), hex('a'), from_binary('01100001'), unbin('01100001'), "
    "from_hex('61'), bin(97), to_base(97, 2)")

[('01100001', '01100001', '61', b'a', b'a', b'a', '1100001', '1100001')]

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 binary and encoding functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.