factorial

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Arithmetic. factorial(n) returns n! = 1 × 2 × … × n, as a 128-bit integer (HUGEINT). The postfix operator n! is the same function. It takes an INTEGER or a smaller integer type, and the largest result is 33!.

Syntax

factorial(n)
n!

Returns: HUGEINT, a 128-bit integer. The ADBC driver returns it as a DECIMAL(38,0), which Python prints as a Decimal.

Aliases: the postfix operator n!.

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_factorial. 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 relay teams: the number of runners as an INTEGER, and the same number as a BIGINT. 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_factorial"'

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

[]
[]
[(4,)]

What it does

factorial(n) multiplies the whole numbers from 1 to n. It's the number of ways to put n things in order: 4 runners can run a relay in 4! = 24 orders.

• 0! and 1! are 1, and so is the factorial of any negative number: factorial(-5) is 1.
• The result is a HUGEINT, so it holds 20! (2432902008176640000), which is past the BIGINT range at 21!, up to 33! (8683317618811886495518194401280000000). factorial(34) stops the query with Value out of range.
• The argument must be an INTEGER or smaller. A BIGINT column fails with No function matches the given name and argument types 'factorial(BIGINT)'; cast it: factorial(CAST(n AS INTEGER)). A numeric string is converted: factorial('12') is 479001600.

The postfix ! operator

n! is the same function. 5!, 5 ! and (5)! are all 120, and (2 + 3)! is 120. Two things to watch:

• A minus sign is applied first: -(5)! is (-5)!, which is 1, not -120.
• ! can't be followed by another operator: (5)! + 1 fails with Scalar Function with name ! does not exist!. Use factorial(5) + 1, or keep ! at the end of the expression: factorial(5) = (5)! works.

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 factorial(NULL), (NULL)!, factorial(0), factorial(1), factorial(-1), factorial(-5)")
run("SELECT factorial(5), typeof(factorial(5)), factorial(20), factorial(21), factorial(33)")
run("SELECT factorial(34)")
run("SELECT 5!, 5 !, (5)!, (2 + 3)!, -(5)!, factorial(5) = (5)!")
run("SELECT (5)! + 1")
run("SELECT factorial(5::TINYINT), factorial('12')")
run("SELECT factorial(5::BIGINT)")
run("SELECT factorial(5::UINTEGER)")
run("SELECT factorial(1.5)")

[(None, None, Decimal('1'), Decimal('1'), Decimal('1'), Decimal('1'))]
[(Decimal('120'), 'HUGEINT', Decimal('2432902008176640000'), Decimal('51090942171709440000'), Decimal('8683317618811886495518194401280000000'))]
OSError: Value out of range
[(Decimal('120'), Decimal('120'), Decimal('120'), Decimal('120'), Decimal('1'), True)]
InternalError: Catalog Error: Scalar Function with name ! does not exist!
[(Decimal('120'), Decimal('479001600'))]
InternalError: Binder Error: No function matches the given name and argument types 'factorial(BIGINT)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'factorial(UINTEGER)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'factorial(DECIMAL(2,1))'. You might need to add explicit type casts.

Examples

The number of running orders for each team, with factorial() and with !:

run(f"SELECT id, runners, factorial(runners), (runners)! FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 4, Decimal('24'), Decimal('24')), (2, 5, Decimal('120'), Decimal('120')), (3, 0, Decimal('1'), Decimal('1')), (4, None, None, None)]

A BIGINT column has to be cast first:

run(f"SELECT id, factorial(runners_big) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, factorial(CAST(runners_big AS INTEGER)) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: No function matches the given name and argument types 'factorial(BIGINT)'. You might need to add explicit type casts.
[(1, Decimal('24')), (2, Decimal('120')), (3, Decimal('1')), (4, None)]

Keep only the teams with exactly 120 running orders:

run(f"SELECT id, runners FROM {TABLE} WHERE factorial(runners) = 120 ORDER BY id LIMIT 10")

[(2, 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 factorial() is always computed by the Query Engine:

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

A run-time error on any row cancels the whole query: if one row holds 34 or more, factorial() of that column fails with Value out of range, even when you want the other rows.

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

plan(f"SELECT id FROM {TABLE} WHERE factorial(runners) = 120")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (factorial(runners) = 120)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          runners          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the argument, not the factorial. Factorials of positive numbers grow with n, so factorial(runners) = 120 is the same as runners = 5 for positive values, and that comparison is pushed down to VAST DataBase. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE runners = 5")

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

• Keep values of 34 or more away from factorial(). One such row fails the whole query. Check the column's largest value first, for example with max(runners). A guard such as CASE WHEN runners <= 33 THEN factorial(runners) END is shown in Limitations and errors.

Limitations and errors

• 33! is the largest result. factorial(34) and anything larger fail with Value out of range, and one such row cancels the query. For large n, use lgamma: lgamma(n + 1) is the natural logarithm of n!, as a DOUBLE.
• INTEGER or smaller only. BIGINT, UINTEGER, DECIMAL, DOUBLE and FLOAT fail with No function matches the given name and argument types. Cast to INTEGER. For a non-integer, use gamma: gamma(x + 1) is the factorial of x for whole numbers, as a DOUBLE.
• Negative numbers give 1, not an error or NULL.
• The postfix ! can't be followed by another operator. See The postfix ! operator.

run(f"SELECT id, CASE WHEN runners <= 33 THEN factorial(runners) END FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT factorial(5) + 1, lgamma(35)")

[(1, Decimal('24')), (2, Decimal('120')), (3, Decimal('1')), (4, None)]
[(Decimal('121'), 88.58082754219767)]

Similar functions

run("SELECT factorial(5), (5)!, gamma(6), exp(lgamma(6))")

[(Decimal('120'), Decimal('120'), 120.0, 119.99999999999997)]

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 arithmetic functions.
• gamma, lgamma and gcd.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.