round_even

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Rounding. Rounds a number to n decimal places, sending a value that is exactly halfway to the even digit (banker's rounding): 2.5 becomes 2 and 3.5 becomes 4. roundbankers is the same function.

Syntax

round_even(x, n)
roundbankers(x, n)

Returns: DOUBLE, also for an integer or DECIMAL x. A FLOAT x gives a FLOAT.

Aliases: roundbankers.

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_round_even. 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 invoice lines with an amount in DOUBLE and a price in DECIMAL(10,2). 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_round_even"'

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}.lines"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, amount DOUBLE, price DECIMAL(10,2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, 2.50), "
    "(2, 3.5, -7.25), "
    "(3, -2.5, 12.35), "
    "(4, 0.125, 3.14), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

round_even(x, n) rounds x to n decimal places. A value that isn't halfway between two results rounds to the nearer one, as with round. A value exactly halfway goes to the result whose last digit is even:

Over many values, halves round up as often as down, so totals of rounded values don't drift upwards.

A decimal value that has no exact binary form may not count as a half: round_even(2.45, 1) returns 2.5, not 2.4, and round_even(2.55, 1) returns 2.6.

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 round_even(NULL, 0), round_even(2.5, NULL), round_even(-0.5, 0)")
run("SELECT round_even('NaN'::DOUBLE, 0), round_even('inf'::DOUBLE, 0)")
run("SELECT round_even(2.45, 1), round_even(2.55, 1), round_even(2.5, 400), round_even(2.5, -400)")
run("SELECT round_even(9223372036854775807, -1), round_even(2.5::FLOAT, 0), round_even(25, 0)")
run("SELECT typeof(round_even(2.5, 0)), typeof(round_even(2.5::DECIMAL(4,1), 0)), typeof(round_even(2.5::FLOAT, 0))")

[(None, None, -0.0)]
[(nan, inf)]
[(2.5, 2.6, 2.5, 0.0)]
[(9.223372036854776e+18, 2.0, 25.0)]
[('DOUBLE', 'DOUBLE', 'FLOAT')]

Examples

Each amount to a whole number and each price to one decimal place, with both names of the function. -7.25 becomes -7.2 and 12.35 becomes 12.4, the even digit in each case:

run(f"SELECT id, round_even(amount, 0), roundbankers(amount, 0), round_even(price, 1) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.0, 2.0, 2.5), (2, 4.0, 4.0, -7.2), (3, -2.0, -2.0, 12.4), (4, 0.0, 0.0, 3.1), (5, None, None, None)]

The lines whose amount rounds to 2:

run(f"SELECT id, amount FROM {TABLE} WHERE round_even(amount, 0) = 2 ORDER BY id LIMIT 10")

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

• In the SELECT list, the scan reads the column and the Query Engine computes round_even() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes round_even() 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 round_even(amount, 0) = 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CASE  WHEN ((((abs(amount│
│ ) * 10.0) % 10.0) = 5.0)) │
│  THEN ((round((amount / 2 │
│ .0), 0) * 2.0)) ELSE round│
│   (amount, 0) END = 2.0)  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           amount          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on a range of the raw column instead. round_even(amount, 0) = 2 keeps the amounts from 1.5 to 2.5, both included; amount >= 1.5 AND amount <= 2.5 returns the same rows, and a comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE amount >= 1.5 AND amount <= 2.5")

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

• Round last. Call round_even() in the SELECT list of the rows you return, and filter and sort on the unrounded column.

Limitations and errors

• Two arguments. round_even(2.5) fails with Macro function round_even(x, n) requires 2 positional arguments, but a single positional argument was provided. Pass 0 for whole numbers.
• n must be an integer. round_even(2.5, 1.7) fails with No function matches the given name and argument types 'round(DOUBLE, DECIMAL(2,1))'.
• Numbers only. A string, date, boolean, list or BLOB fails. The error names abs(), which round_even calls: for example No function matches the given name and argument types 'abs(DATE)'.
• The result is a DOUBLE, even for a DECIMAL. Cast it back if you need a DECIMAL: CAST(round_even(price, 1) AS DECIMAL(10,1)).

run("SELECT round_even(2.5)")
run("SELECT round_even(2.5, 1.7)")
run(f"SELECT round_even(DATE '2026-10-16', 0) FROM {TABLE} LIMIT 1")
run(f"SELECT id, CAST(round_even(price, 1) AS DECIMAL(10,1)) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: Macro function round_even(x, n) requires 2 positional arguments, but a single positional argument was provided.
InternalError: Binder Error: No function matches the given name and argument types 'round(DOUBLE, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'abs(DATE)'. You might need to add explicit type casts.
[(1, Decimal('2.5')), (2, Decimal('-7.2')), (3, Decimal('12.4')), (4, Decimal('3.1')), (5, None)]

Similar functions

run("SELECT round_even(2.5, 0), roundbankers(2.5, 0), round(2.5), even(2.5), trunc(2.5), floor(2.5)")
run("SELECT round_even(-2.5, 0), roundbankers(-2.5, 0), round(-2.5), even(-2.5), trunc(-2.5), floor(-2.5)")
run("SELECT round_even(0.5, 0), roundbankers(0.5, 0), round(0.5), even(0.5), trunc(0.5), floor(0.5)")

[(2.0, 2.0, Decimal('3'), 4.0, Decimal('2'), Decimal('2'))]
[(-2.0, -2.0, Decimal('-3'), -4.0, Decimal('-2'), Decimal('-3'))]
[(0.0, 0.0, Decimal('1'), 2.0, Decimal('0'), Decimal('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.
• round, even and trunc.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.