log2

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Powers and logarithms. Returns the base-2 logarithm of a number: the power you raise 2 to in order to get it. log2(8) is 3.0. It has no aliases; log(2, x) gives the same result.

Syntax

log2(x)

Returns: DOUBLE.

Aliases: none. log(2, x) returns the same value.

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_log2. 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 codes, each with the number of distinct values it has to tell apart. 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_log2"'

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}.codes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, distinct_values DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'shift', 3), "
    "(2, 'weekday', 8), "
    "(3, 'sensor', 1024), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

log2(x) returns the y for which 2 to the power y is x. Every power of 2 gives a whole number: log2(8) is 3.0, log2(1024) is 10.0 and log2(0.5) is -1.0. Other numbers give a fraction: log2(3) is 1.584962500721156.

The argument is converted to DOUBLE, and so is the result, whatever the argument's type: log2(100::INTEGER) and log2(100::FLOAT) both return a DOUBLE. A string of digits is converted too, so log2('12') returns 3.584962500721156. The logarithm is only defined for numbers greater than 0: zero and negative numbers are errors, not NULL.

NULLs and edge cases

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

run("SELECT log2(NULL), log2(1), log2(0.5), log2(5e-324::DOUBLE), log2('inf'::DOUBLE), log2('nan'::DOUBLE)")
run("SELECT log2('12')")
run("SELECT log2(0)")
run("SELECT log2(-0.0::DOUBLE)")
run("SELECT log2(-1)")
run("SELECT log2('-inf'::DOUBLE)")
run("SELECT log2('abc')")
run("SELECT log2('\\xAA'::BLOB)")
run("SELECT log2(DATE '2026-10-16')")
run("SELECT log2(true)")
run("SELECT log2([1, 2])")
run("SELECT log2(INTERVAL 1 DAY)")

[(None, 0.0, -1.0, -1074.0, inf, nan)]
[(3.584962500721156,)]
OSError: cannot take logarithm of zero
OSError: cannot take logarithm of zero
OSError: cannot take logarithm of a negative number
OSError: cannot take logarithm of a negative number
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'log2(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'log2(DATE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'log2(BOOLEAN)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'log2(INTEGER[])'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'log2(INTERVAL)'. You might need to add explicit type casts.

Examples

The base-2 logarithm of each code's number of distinct values, and, rounded up with ceil, the number of bits it takes to give each value its own number:

run(f"SELECT id, name, log2(distinct_values), ceil(log2(distinct_values)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'shift', 1.584962500721156, 2.0), (2, 'weekday', 3.0, 3.0), (3, 'sensor', 10.0, 10.0), (4, None, None, None)]

Keep only the codes that need more than 2 bits:

run(f"SELECT id, name FROM {TABLE} WHERE log2(distinct_values) > 2 ORDER BY id LIMIT 10")

[(2, 'weekday'), (3, 'sensor')]

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 log2() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes log2() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes log2() 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 log2(distinct_values) > 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (log2(distinct_values) > 2│
│            .0)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│      distinct_values      │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the column, not its logarithm. log2() grows with its argument, so log2(x) > 2 keeps the same rows as x > 4 (2 to the power 2). A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage, and log2() isn't computed at all, so a zero or a negative value in the column can't fail the query. The plan for distinct_values > 4 has no FILTER step:

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

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

• Store the logarithm if you can't rewrite the condition. Compute it when you write the rows and keep it in its own column: a comparison of that column is pushed down, log2(x) in WHERE isn't.
• Combine it with a pushed-down condition. In id > 100 AND log2(x) > 2, id > 100 is still evaluated in VAST DataBase, so log2() runs only on the rows that pass it.

Limitations and errors

• Greater than 0 only. log2(0) fails with cannot take logarithm of zero and a negative number with cannot take logarithm of a negative number. On a table, one such row fails the whole query, not just that row: NULL is the only value that gives a NULL. Filter on the raw column (see Make it faster) or keep zeros and negative numbers out of the column you take the logarithm of.
• Numbers only. A BLOB, a date, a boolean, a list or an interval fails with No function matches the given name and argument types; log2 takes a DOUBLE. A string is converted to DOUBLE, so a string that isn't a number fails with Could not convert string ... to DOUBLE.
• The result is a DOUBLE, whatever the type of the argument.
• No exp2. To raise 2 to a power, the inverse of log2, use pow: pow(2, 10) returns 1024.0.

run("SELECT exp2(10)")
run("SELECT pow(2, 10)")

InternalError: Catalog Error: Scalar Function with name exp2 does not exist!
[(1024.0,)]

Similar functions

run("SELECT log2(8), log(2, 8), ln(8), log10(8), log(8), ln(8) / ln(2)")

[(3.0, 3.0, 2.0794415416798357, 0.9030899869919435, 0.9030899869919435, 3.0)]

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 powers and logarithms.
• log, ln, log10 and pow.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.