nextafter

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Floating-point values. Returns the floating-point value next to x in the direction of y: the smallest step up or down that a DOUBLE (or, for two FLOAT arguments, a FLOAT) can take from x.

Syntax

nextafter(x, y)

Returns: FLOAT if both arguments are FLOAT, otherwise DOUBLE. Integers and DECIMAL values are converted to DOUBLE, and so is a FLOAT paired with a DOUBLE.

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_nextafter. 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 five thresholds, stored both as a DOUBLE (x) and as a FLOAT (f). Row 3 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_nextafter"'

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}.thresholds"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x DOUBLE, f FLOAT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, 2.5), (2, -1.25, -1.25), (3, NULL, NULL), (4, 0.0, 0.0), (5, 1.0, 1.0)")

[]
[]
[(5,)]

What it does

Floating-point numbers are spaced apart: between 1.0 and the next DOUBLE above it there's nothing. nextafter(x, y) returns that neighbour of x, on the side of y:

• If y is greater than x, the next value up: nextafter(1.0, 2.0) is 1.0000000000000002.
• If y is less than x, the next value down: nextafter(1.0, 0.0) is 0.9999999999999999.
• If y equals x, x itself: nextafter(1.0, 1.0) is 1.0.

The size of the step depends on the type and on the size of x. At 1 it's 2.220446049250313e-16 for a DOUBLE (nextafter(1.0, 2.0) - 1.0) and 1.1920928955078125e-07 for a FLOAT. Next to 0 it's the smallest value the type can hold: nextafter(0.0::DOUBLE, 1) is 5e-324.

Use it to turn a strict bound into an inclusive one (on a DOUBLE column, x > 1.0 is x >= nextafter(1.0, 'inf'::DOUBLE)), or to find how precisely a value can be stored.

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 nextafter(NULL, 1), nextafter(1, NULL), nextafter(1.0, 2.0), nextafter(1.0, 0.0), nextafter(1.0, 1.0)")
run("SELECT nextafter(0.0::DOUBLE, 1), nextafter(0.0::DOUBLE, -1), nextafter(-0.0::DOUBLE, 0), "
    "nextafter(5e-324::DOUBLE, 0)")
run("SELECT nextafter(1.7976931348623157e308, 'inf'::DOUBLE), nextafter('inf'::DOUBLE, 0), "
    "nextafter('-inf'::DOUBLE, 0), nextafter('nan'::DOUBLE, 1), nextafter(1, 'nan'::DOUBLE)")
run("SELECT nextafter(1::FLOAT, 2::FLOAT), typeof(nextafter(1::FLOAT, 2::FLOAT)), "
    "nextafter(1::FLOAT, 2::DOUBLE), typeof(nextafter(1::FLOAT, 2::DOUBLE))")
run("SELECT nextafter(2.45, 3), typeof(nextafter(2.45, 3)), nextafter('12', 10)")
run("SELECT nextafter('abc', 10)")
run("SELECT nextafter(DATE '2026-10-16', 10)")
run("SELECT nextafter(1)")

[(None, None, 1.0000000000000002, 0.9999999999999999, 1.0)]
[(5e-324, -5e-324, 0.0, 0.0)]
[(inf, 1.7976931348623157e+308, -1.7976931348623157e+308, nan, nan)]
[(1.0000001192092896, 'FLOAT', 1.0000000000000002, 'DOUBLE')]
[(2.4500000000000006, 'DOUBLE', 11.999999999999998)]
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'nextafter(DATE, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'nextafter(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The next value above each threshold, as a DOUBLE and as a FLOAT. Cast the direction to FLOAT (10::FLOAT) to get a FLOAT result for a FLOAT column:

run(f"SELECT id, nextafter(x, 10), nextafter(f, 10::FLOAT) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.5000000000000004, 2.500000238418579), (2, -1.2499999999999998, -1.2499998807907104), (3, None, None), (4, 5e-324, 1.401298464324817e-45), (5, 1.0000000000000002, 1.0000001192092896)]

The step to the next value up at each threshold, for each type. It grows with the size of the value, and it's larger for a FLOAT:

run(f"SELECT id, x, nextafter(x, 'inf'::DOUBLE) - x, nextafter(f, 'inf'::FLOAT) - f "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.5, 4.440892098500626e-16, 2.384185791015625e-07), (2, -1.25, 2.220446049250313e-16, 1.1920928955078125e-07), (3, None, None, None), (4, 0.0, 5e-324, 1.401298464324817e-45), (5, 1.0, 2.220446049250313e-16, 1.1920928955078125e-07)]

x >= nextafter(1.0, 'inf'::DOUBLE) keeps the same rows as x > 1.0:

run(f"SELECT id FROM {TABLE} WHERE x >= nextafter(1.0, 'inf'::DOUBLE) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE x > 1.0 ORDER BY id LIMIT 10")

[(1,)]
[(1,)]

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:

• On a column, in the SELECT list, the scan reads the column and the Query Engine computes nextafter() for each row it returns.
• On a column, in WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes nextafter() and keeps the rows that match.
• On constants, as in x >= nextafter(1.0, 'inf'::DOUBLE), the Query Engine computes it once, before the scan.

The plan of a WHERE condition on nextafter(x, 10) shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE nextafter(x, 10) > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (nextafter(x, 10.0) > 1.0)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             x             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Apply it to the constant, not the column. In a bound such as x >= nextafter(1.0, 'inf'::DOUBLE), the right-hand side is a constant, so the scan in VAST DataBase can evaluate the condition:

plan(f"SELECT id FROM {TABLE} WHERE x >= nextafter(1.0, 'inf'::DOUBLE)")

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

Limitations and errors

• Two arguments, DOUBLE or FLOAT. A single argument, a date, a boolean, a BLOB, a list or an interval fails with No function matches the given name and argument types. A string is converted to DOUBLE first, and fails with Could not convert string ... to DOUBLE if it isn't a number.
• No DECIMAL or integer result. A DECIMAL or integer argument is converted to DOUBLE, and the result is a DOUBLE.
• A FLOAT result needs two FLOAT arguments. A FLOAT paired with a DOUBLE gives a DOUBLE step, which is much smaller than a FLOAT step.
• It can overflow. The next value up from the largest DOUBLE is inf, with no error.

Similar functions

run("SELECT nextafter(1.0, 2.0) - 1.0, nextafter(1::FLOAT, 2::FLOAT) - 1::FLOAT, "
    "isfinite(nextafter(1.7976931348623157e308, 'inf'::DOUBLE))")

[(2.220446049250313e-16, 1.1920928955078125e-07, False)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: nextafter and the other floating-point functions.
• isnan, isinf and isfinite.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.