lcm

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Arithmetic. Returns the least common multiple of two integers: the smallest positive number that both divide. least_common_multiple is the same function.

Syntax

lcm(a, b)
least_common_multiple(a, b)

Returns: BIGINT, or HUGEINT when an argument is a HUGEINT. Smaller integer types such as TINYINT give BIGINT.

Aliases: least_common_multiple.

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_lcm. 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 maintenance tasks, each made of two jobs that repeat every a and every b days. 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_lcm"'

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

[]
[]
[(5,)]

What it does

lcm(a, b) returns the smallest positive integer that both a and b divide: lcm(4, 6) is 12. Two jobs that repeat every 4 and every 6 days next fall on the same day after 12 days.

• Signs are ignored. The result is never negative: lcm(-4, 6), lcm(4, -6) and lcm(-4, -6) are all 12.
• Zero gives zero. If either argument is 0, the result is 0, including lcm(0, 0).
• Integers only. A DECIMAL (such as 1.5 or 4.0), a DOUBLE or a FLOAT fails; cast it to BIGINT first.
• A string that holds a number is converted. lcm('12', 4) returns 12; a string that isn't a number fails when the query runs.

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 lcm(NULL, 4), lcm(4, NULL), lcm(4, 6), lcm(-4, 6), lcm(4, -6), lcm(-4, -6), lcm(0, 5), lcm(0, 0)")
run("SELECT lcm(4::HUGEINT, 6::HUGEINT), typeof(lcm(4::HUGEINT, 6::HUGEINT)), typeof(lcm(4::TINYINT, 6::TINYINT))")
run("SELECT lcm(9223372036854775807, 2)")
run("SELECT lcm(-9223372036854775808::BIGINT, 1)")
run("SELECT lcm(1.5, 4)")
run(f"SELECT lcm('12', 4) FROM {TABLE} LIMIT 1")
run(f"SELECT lcm('abc', 4) FROM {TABLE} LIMIT 1")

[(None, None, 12, 12, 12, 12, 0, 0)]
[(Decimal('12'), 'HUGEINT', 'BIGINT')]
OSError: lcm value is out of range
OSError: Type INT128 with value 9223372036854775808 can't be cast because the value is out of range for the destination type INT64
InternalError: Binder Error: No function matches the given name and argument types 'lcm(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts.
[(12,)]
OSError: Could not convert string 'abc' to INT64

Examples

The day on which the two jobs of each task next fall together, with both spellings of the function:

run(f"SELECT id, a, b, lcm(a, b), least_common_multiple(a, b) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 4, 6, 12, 12), (2, 3, 5, 15, 15), (3, -4, 6, 12, 12), (4, 0, 5, 0, 0), (5, None, None, None, None)]

Keep only the tasks whose jobs coincide less often than every 12 days:

run(f"SELECT id, a, b FROM {TABLE} WHERE lcm(a, b) > 12 ORDER BY id LIMIT 10")

[(2, 3, 5)]

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (lcm(a, b) > 12)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             a             │
│             b             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the multiple if you filter on it often. Compute it when you write the rows and keep it in its own column: a comparison of that column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for a raw column compared with a constant has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE a = 4")

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

• Combine it with a pushed-down condition. In id > 100 AND lcm(a, b) > 12, id > 100 is still evaluated in VAST DataBase, so lcm() runs only on the rows that pass it.

Limitations and errors

• Integers only. A DECIMAL, DOUBLE, FLOAT, boolean, date, interval, BLOB or list fails with No function matches the given name and argument types. The error lists the two forms lcm accepts: lcm(BIGINT, BIGINT) -> BIGINT and lcm(HUGEINT, HUGEINT) -> HUGEINT. A UHUGEINT fails too. Cast other values first: lcm(CAST(x AS BIGINT), 4).
• The result must fit the type. A multiple too big for BIGINT fails with lcm value is out of range, as lcm(4294967296, 4294967297) does; so does one too big for HUGEINT.
• The smallest BIGINT fails. lcm(-9223372036854775808::BIGINT, 1) fails with Type INT128 with value 9223372036854775808 can't be cast because the value is out of range for the destination type INT64, because its absolute value doesn't fit a BIGINT.
• Strings are converted when the query runs. A string argument is read as a number, and one that isn't a number fails with Could not convert string 'abc' to INT64.

run("SELECT lcm(4294967296, 4294967297)")
run("SELECT lcm(1.5::DOUBLE, 4)")
run("SELECT lcm(CAST(1.5::DOUBLE AS BIGINT), 4)")

OSError: lcm value is out of range
InternalError: Binder Error: No function matches the given name and argument types 'lcm(DOUBLE, INTEGER_LITERAL)'. You might need to add explicit type casts.
[(4,)]

Similar functions

run("SELECT lcm(12, 18), least_common_multiple(12, 18), gcd(12, 18), greatest_common_divisor(12, 18), 12 * 18 // gcd(12, 18)")

[(36, 36, 6, 6, 36)]

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.
• gcd, multiplication and integer division.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.