ln

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Powers and logarithms. Returns the natural logarithm (base e) of a number, as a DOUBLE. It's the inverse of exp. Zero and negative numbers fail the query.

Syntax

ln(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_ln. 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 three days of trading in one share: the opening and closing prices and the number of shares traded. On one day, no shares were traded; 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_ln"'

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}.trades"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, open_price DOUBLE, close_price DOUBLE, volume BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 100.0, 110.0, 5000), "
    "(2, 50.0, 45.0, 0), "
    "(3, 20.0, 20.0, 120), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

ln(x) returns the power that e must be raised to to give x: ln(1) is 0.0, ln(exp(1)) is 1.0, and ln(100) is 4.605170185988092. 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.

Zero and negative numbers are errors, not infinity or NaN: ln(0) fails with cannot take logarithm of zero, and ln(-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, ln() of that column fails for every row. A negative zero (-0.0::DOUBLE) counts as zero, and '-inf'::DOUBLE as negative. Keep those values away from ln() with WHERE or CASE; see Limitations and errors.

ln('inf'::DOUBLE) is inf and ln('nan'::DOUBLE) is nan. Very small positive numbers work: ln(5e-324::DOUBLE), the smallest DOUBLE above 0, is -744.4400719213812.

log(x) isn't the natural logarithm. In the VAST Query Engine, log(x) with one argument is the base-10 logarithm: log(exp(1)) is 0.4342944819032518. Use ln for base e.

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

[(None, 0.0, 1.0, 4.605170185988092, 709.1962086421661, -744.4400719213812)]
[(inf, nan, 'DOUBLE')]
[(2.4849066497880004,)]
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 'ln(BOOLEAN)'. You might need to add explicit type casts.

Examples

The log return of each day, ln(close_price / open_price): positive when the price rose, negative when it fell, and 0 when it didn't move:

run(f"SELECT id, open_price, close_price, ln(close_price / open_price) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 100.0, 110.0, 0.09531017980432493), (2, 50.0, 45.0, -0.10536051565782628), (3, 20.0, 20.0, 0.0), (4, None, None, None)]

Keep only the days on which the price rose:

run(f"SELECT id, open_price, close_price FROM {TABLE} WHERE ln(close_price / open_price) > 0 ORDER BY id LIMIT 10")

[(1, 100.0, 110.0)]

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

• In the SELECT list, the scan reads the column and the Query Engine computes ln() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes ln() and keeps the rows that match. No rows are skipped in storage, so every row's value reaches ln(), including a zero that fails the query.

A function of a constant is different: it's computed once, before the scan, so volume > exp(5) compares volume with the constant 148.4131591025766 (see the plan in Make it faster).

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

plan(f"SELECT id FROM {TABLE} WHERE ln(close_price / open_price) > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (ln((close_price /    │
│     open_price)) > 0.0)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        close_price        │
│         open_price        │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Apply exp to the constant instead. A larger number always has a larger logarithm, so ln(volume) > 5 is the same as volume > exp(5) for positive values. The second form doesn't call ln() on any row, so it also keeps the zero volume away from it: in WHERE, ln(volume) > 5 fails on that row, and volume > exp(5) doesn't. On a DOUBLE column, such a comparison with a constant is pushed down to VAST DataBase (as score > 0 is on the exp page). volume is a BIGINT, though, so the plan casts it to DOUBLE to compare it with 148.4131591025766, and that comparison runs in a FILTER step:

run(f"SELECT id FROM {TABLE} WHERE ln(volume) > 5 ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE volume > exp(5) ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE volume > exp(5)")

OSError: cannot take logarithm of zero
[(1,)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(volume AS DOUBLE) > │
│     148.4131591025766)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           volume          │
│             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 ln(close_price / open_price) > 0, id > 100 is still evaluated in VAST DataBase, so ln() runs only on the rows that pass it.

Limitations and errors

• Zero or a negative value fails the whole query. ln(volume) fails with cannot take logarithm of zero because one day has a volume of 0, and returns no rows at all. To skip those rows, filter them out with a pushed-down condition such as WHERE volume > 0. To keep them in the result with a NULL, use CASE WHEN volume > 0 THEN ln(volume) 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 ln accepts, ln(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.
• No log1p or naturallog. They fail with Scalar Function with name ... does not exist!. Use ln(1 + x), but for x very close to 0 it loses precision: ln(1 + 1e-17) is 0.0.

run(f"SELECT ln(volume) FROM {TABLE}")
run(f"SELECT id, ln(volume) FROM {TABLE} WHERE volume > 0 ORDER BY id LIMIT 10")
run(f"SELECT id, CASE WHEN volume > 0 THEN ln(volume) END FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT log1p(1)")
run("SELECT ln(1 + 1e-10), ln(1 + 1e-17)")

OSError: cannot take logarithm of zero
[(1, 8.517193191416238), (3, 4.787491742782046)]
[(1, 8.517193191416238), (2, None), (3, 4.787491742782046), (4, None)]
InternalError: Catalog Error: Scalar Function with name log1p does not exist!
[(1.000000082690371e-10, 0.0)]

Similar functions

run("SELECT ln(100), log(100), log10(100), log2(100), log(10, 100), log(2, 100), log(exp(1), 100)")
run("SELECT ln(8) / ln(2), log2(8), ln(exp(2))")

[(4.605170185988092, 2.0, 2.0, 6.643856189774724, 2.0, 6.643856189774724, 4.605170185988092)]
[(3.0, 3.0, 2.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 numeric functions.
• exp, log, log10 and log2.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.