log10

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Powers and logarithms. Returns the base-10 logarithm of a number, as a DOUBLE. log(x) with one argument returns the same. Zero and negative numbers fail the query.

Syntax

log10(x)

Returns: DOUBLE, whatever the argument type.

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_log10. 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 files and their sizes in bytes. One file is empty; 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_log10"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, size BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1536), "
    "(2, 5000000), "
    "(3, 1), "
    "(4, 0), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

log10(x) returns the power that 10 must be raised to to give x: log10(1) is 0.0, log10(1000) is 3.0 and log10(0.001) is -3.0. It's the same as log with one argument. The argument is converted to DOUBLE first, so integers, DECIMAL and FLOAT values all work and all give a DOUBLE; so does a string that holds a number.

The base-10 logarithm tells you the order of magnitude of a number, so it's a common way to put very different sizes on one scale: log10(1e308) is 308.0, and log10(5e-324::DOUBLE), the smallest DOUBLE above 0, is -323.3062153431158.

Zero and negative numbers are errors, not infinity or NaN: log10(0) fails with cannot take logarithm of zero, and log10(-1) with cannot take logarithm of a negative number. The error stops the whole query: if one row of a column is 0 or negative, log10() of that column fails for every row, also in WHERE. A negative zero (-0.0::DOUBLE) counts as zero, and '-inf'::DOUBLE as negative. log10('inf'::DOUBLE) is inf and log10('nan'::DOUBLE) is nan.

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 log10(NULL), log10(1), log10(1000), log10(0.001), log10(1e308), log10(5e-324::DOUBLE)")
run("SELECT log10('inf'::DOUBLE), log10('nan'::DOUBLE), typeof(log10(100::INTEGER)), log10(100::FLOAT)")
run(f"SELECT log10('12') FROM {TABLE} LIMIT 1")
run("SELECT log10(0)")
run("SELECT log10(-0.0::DOUBLE)")
run("SELECT log10(-1)")
run("SELECT log10('-inf'::DOUBLE)")
run(f"SELECT log10('abc') FROM {TABLE} LIMIT 1")
run("SELECT log10(true)")

[(None, 0.0, 3.0, -3.0, 308.0, -323.3062153431158)]
[(inf, nan, 'DOUBLE', 2.0)]
[(1.0791812460476249,)]
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 'log10(BOOLEAN)'. You might need to add explicit type casts.

Examples

The base-10 logarithm of each file size, and the number of digits in it, floor(log10(size)) + 1. The empty file is left out with size > 0, because log10(0) fails:

run(f"SELECT id, size, log10(size), floor(log10(size)) + 1 FROM {TABLE} WHERE size > 0 ORDER BY id LIMIT 10")

[(1, 1536, 3.186391215695493, 4.0), (2, 5000000, 6.698970004336019, 7.0), (3, 1, 0.0, 1.0)]

Keep only the files of 1,000 bytes or more, by their logarithm. size > 0 is evaluated in VAST DataBase, so the empty file never reaches log10():

run(f"SELECT id, size FROM {TABLE} WHERE size > 0 AND log10(size) >= 3 ORDER BY id LIMIT 10")

[(1, 1536), (2, 5000000)]

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

• In the SELECT list, the scan reads the column and the Query Engine computes log10() for each row it returns.
• In WHERE, the scan sends the column for every row that passes the pushed-down conditions, and a FILTER step in the Query Engine computes log10() and keeps the rows that match. Without a condition such as size > 0, every row's value reaches log10(), and a zero fails the query.

The plan of the WHERE example shows the FILTER step above the scan. It holds only the log10() condition: size > 0 is evaluated in the scan:

plan(f"SELECT id FROM {TABLE} WHERE size > 0 AND log10(size) >= 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (log10(CAST(size AS DOUBLE│
│         )) >= 3.0)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            size           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Raise 10 to the constant instead. A larger number always has a larger logarithm, so log10(size) >= 6 is the same as size >= 1000000. That comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage, and it never takes a logarithm, so the empty file doesn't fail it. log10(size) >= 6 on its own fails on that file:

run(f"SELECT id FROM {TABLE} WHERE log10(size) >= 6 ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE size >= 1000000 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE size >= 1000000")

OSError: cannot take logarithm of zero
[(2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store the logarithm if you filter on it often. Compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In id > 100 AND log10(size) >= 3, id > 100 is still evaluated in VAST DataBase, so log10() runs only on the rows that pass it.

Limitations and errors

• Zero or a negative value fails the whole query. log10(size) over the whole table fails with cannot take logarithm of zero, because one file is empty, and returns no rows at all. To skip those rows, filter them out with a pushed-down condition such as WHERE size > 0. To keep them in the result with a NULL, use CASE WHEN size > 0 THEN log10(size) END.
• Numbers only. A boolean, date, interval, BLOB or list fails with No function matches the given name and argument types; the error lists the one form log10 accepts, log10(DOUBLE) -> DOUBLE. A string is converted when the query runs, and one that isn't a number fails with Could not convert string 'abc' to DOUBLE.

run(f"SELECT log10(size) FROM {TABLE}")
run(f"SELECT id, CASE WHEN size > 0 THEN log10(size) END FROM {TABLE} ORDER BY id LIMIT 10")

OSError: cannot take logarithm of zero
[(1, 3.186391215695493), (2, 6.698970004336019), (3, 0.0), (4, None), (5, None)]

Similar functions

run("SELECT log10(100), log(100), log(10, 100), ln(100), log2(100)")

[(2.0, 2.0, 2.0, 4.605170185988092, 6.643856189774724)]

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