least

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Largest and smallest argument. Returns the smallest of its arguments, compared within one row. NULL arguments are skipped. To find the smallest value across rows, use the aggregate min.

Syntax

least(a [, b, ...])

Returns: the combined type of the arguments.

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_conditional_and_cast_least. 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 players with scores from three rounds and some values missing. 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_conditional_and_cast_least"'

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}.scores"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, r1 INTEGER, r2 BIGINT, r3 DOUBLE, name VARCHAR, team VARCHAR, "
    "joined DATE, played TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 12, 18, 9.5, 'ada', 'Blue', DATE '2026-10-01', TIMESTAMP '2026-10-16 18:00:00'), "
    "(2, 20, NULL, 15.0, 'Grace', 'red', DATE '2026-10-17', TIMESTAMP '2026-10-16 19:30:00'), "
    "(3, -4, 2, NULL, 'alan', NULL, DATE '2026-10-05', NULL), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

least(a, b, c) compares its arguments with each other and returns the smallest. It skips NULL arguments, so the result is NULL only if every argument is NULL.

The arguments are combined into one type first: an INTEGER and a BIGINT give a BIGINT, an integer and a DOUBLE give a DOUBLE, 1 and 2.5 give a DECIMAL(11,1), and a DATE and a TIMESTAMP give a TIMESTAMP. A text literal with a number is converted to the number's type, so least(1, '2') is the INTEGER 1.

How values order depends on their type:

DATE, TIMESTAMP, TIME, BLOB, UUID and structs order as well.

NULLs and edge cases

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

run("SELECT least(NULL, 1), least(1, NULL), least(1, NULL, 3), least(NULL, NULL), "
    "least(1), least('', 'a'), least(-0.0::DOUBLE, 0.0::DOUBLE), least(1, true)")
run("SELECT least('a', 'B'), least('a', 'A'), least('10', '9'), least('\u00e9', 'f'), "
    "least('nan'::DOUBLE, 1), least('inf'::DOUBLE, 'nan'::DOUBLE), least(true, false)")
run("SELECT least(INTERVAL 1 DAY, INTERVAL 25 HOUR), least(INTERVAL 1 MONTH, INTERVAL 30 DAY), "
    "least([1, 2], [1, 3]), least([1, 2], [1, 2, 0]), least([1, NULL], [1, 2]), "
    "least({'a': 1}, {'a': 2})")
run("SELECT least(1, 2.5), typeof(least(1, 2.5)), least(1, '2'), typeof(least(1, '2')), "
    "least(DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00')")
run("SELECT least()")
run("SELECT least(1, [1])")
run("SELECT least(DATE '2026-10-16', 1)")

[(1, 1, 1, None, 1, '', -0.0, 1)]
[('B', 'A', '10', 'f', 1.0, inf, False)]
[(MonthDayNano(months=0, days=0, nanoseconds=90000000000000), MonthDayNano(months=0, days=30, nanoseconds=0), [1, 2], [1, 2], [1, 2], {'a': 1})]
[(Decimal('1.0'), 'DECIMAL(11,1)', 1, 'INTEGER', datetime.datetime(2026, 10, 16, 0, 0))]
InternalError: Binder Error: No function matches the given name and argument types 'least()'. You might need to add explicit type casts.
InternalError: Binder Error: Cannot combine types of INTEGER_LITERAL and INTEGER[] - an explicit cast is required
InternalError: Binder Error: Cannot combine types of DATE and INTEGER_LITERAL - an explicit cast is required

Examples

The worst round of each player. Missing rounds are skipped; player 4 has none, so the result is NULL. r1 is an INTEGER and r2 a BIGINT, so least(r1, r2) is a BIGINT; with the DOUBLE r3 it's a DOUBLE:

run(f"SELECT id, least(r1, r2), least(r1, r2, r3), typeof(least(r1, r2, r3)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 12, 9.5, 'DOUBLE'), (2, 20, 15.0, 'DOUBLE'), (3, -4, -4.0, 'DOUBLE'), (4, None, None, 'DOUBLE')]

A ceiling for each value, and the earlier of two dates. least(r1, 15) caps the scores at 15. A DATE and a TIMESTAMP give a TIMESTAMP:

run(f"SELECT id, least(r1, 15), least(joined, played) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 12, datetime.datetime(2026, 10, 1, 0, 0)), (2, 15, datetime.datetime(2026, 10, 16, 19, 30)), (3, -4, datetime.datetime(2026, 10, 5, 0, 0)), (4, 15, None)]

Text compares by character code, so 'Blue' is smaller than 'ada'. Adding COLLATE nocase to the first argument doesn't change these results:

run(f"SELECT id, least(name, team), least(name COLLATE nocase, team) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Blue', 'Blue'), (2, 'Grace', 'Grace'), (3, 'alan', 'alan'), (4, None, None)]

least() compares values in one row and min() values across rows; least() of two min() results gives the smallest value in two columns:

run(f"SELECT min(r1), min(r2), least(min(r1), min(r2)) FROM {TABLE}")

[(-4, 2, -4)]

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). What happens to least() depends on its arguments:

• On constants only, such as least(1, 2), it's computed once while the query is planned, and a comparison of a column with the result can still be pushed down to VAST DataBase.
• On a column in the SELECT list, the scan reads the columns and the Query Engine computes least() for each row it returns.
• On a column in WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes least() and keeps the rows that match. No rows are skipped in storage.

The first plan has a FILTER step above the scan; the second, with least() on constants, has none:

plan(f"SELECT id FROM {TABLE} WHERE least(r1, 0) < 0")
plan(f"SELECT id FROM {TABLE} WHERE r1 < least(1, 2)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (least(r1, 0) < 0)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             r1            │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Compare the raw column when you can. least(r1, 0) < 0 keeps the same rows as r1 < 0, because 0 is never less than 0, and r1 < 0 is a comparison of a column with a constant, which VAST DataBase can evaluate:

plan(f"SELECT id FROM {TABLE} WHERE r1 < 0")
run(f"SELECT id FROM {TABLE} WHERE r1 < 0 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(3,)]

• Store the value if you filter on it often. Compute the worst score when you write the rows and keep it in its own column, which a comparison with a constant can filter in VAST DataBase.

Limitations and errors

• The arguments must combine. A text column with a number column fails with Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required; a DATE with a number, or a number with a list, fails the same way. Cast one of them.
• At least one argument. least() fails with Binder Error: No function matches the given name and argument types 'least()'. The error lists the form it takes: least(ANY, [ANY...]) -> ANY.
• Large integers become HUGEINT. least(9223372036854775807, 9223372036854775808), and a UBIGINT with a negative number, give a HUGEINT, which ADBC returns as a DECIMAL(38, 0).
• least_ignore_nulls isn't available. It fails with Catalog Error: Scalar Function with name least_ignore_nulls does not exist!. least already skips NULLs.

run(f"SELECT least(r1, name) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, least(CAST(r1 AS VARCHAR), name) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT typeof(least(9223372036854775807, 9223372036854775808)), typeof(least(1::UBIGINT, -1))")
run("SELECT least_ignore_nulls(1, NULL)")

InternalError: Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required
[(1, '12'), (2, '20'), (3, '-4'), (4, None)]
[('HUGEINT', 'HUGEINT')]
InternalError: Catalog Error: Scalar Function with name least_ignore_nulls does not exist!

Similar functions

run("SELECT least(2, 1), greatest(2, 1), coalesce(NULL, 3, 1), CASE WHEN 2 < 1 THEN 2 ELSE 1 END")
run(f"SELECT min(r1), least(min(r1), min(r2)) FROM {TABLE}")

[(1, 2, 3, 1)]
[(-4, -4)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Conditional and conversion functions: all the conditional and conversion functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.