round

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Sign and rounding. Rounds a number to the nearest integer, or to n decimal places. Halves are rounded away from zero: 2.5 becomes 3 and -2.5 becomes -3. A negative n rounds to tens, hundreds and so on.

Syntax

round(x)
round(x, n)

Returns: for a DECIMAL(p, s), a DECIMAL(p, n) when n is between 0 and s, DECIMAL(p, s) when n is larger than s, and DECIMAL(p, 0) without n or with a negative n. FLOAT for a FLOAT, and DOUBLE for a DOUBLE or an integer.

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. 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 measurements: a reading (DOUBLE), a price (DECIMAL), a number of places (INTEGER) and a count (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_round"'

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}.measurements"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x DOUBLE, price DECIMAL(10, 2), places INTEGER, n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, 2.45, 1, 1234), "
    "(2, -2.5, -1.35, 0, 15), "
    "(3, 1.005, 10.05, 2, 25), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

round(x, n) returns the value nearest to x that has at most n decimal places. When x is exactly halfway, it rounds away from zero, for DECIMAL, DOUBLE and FLOAT alike: round(0.5) is 1, round(2.5) is 3, round(-0.5) is -1 and round(-2.5) is -3. For halves that go to the even digit (banker's rounding), use round_even.

The result type

The result keeps the kind of number you pass in:

• DECIMAL: the result is exact, and keeps the precision of x. round(2.345, 2) is the DECIMAL(4,2) 2.35, round(1234.5678, -2) is the DECIMAL(8,0) 1200, and round(99999999.99::DECIMAL(10,2)) is the DECIMAL(10,0) 100000000. With more places than x has, x comes back unchanged: round(2.5, 20) is the DECIMAL(2,1) 2.5. A number literal such as 2.345 is a DECIMAL.
• DOUBLE and FLOAT: the result has the same type. Rounding a value between -0.5 and 0 gives a negative zero: round(-0.4::DOUBLE) is -0.0. nan, inf and -inf stay as they are.
• Integers: there's no integer form of round. An integer is converted to DOUBLE, and the result is a DOUBLE: round(5::TINYINT) is 5.0, and round(15, -1) is 20.0. Above 2^53 a DOUBLE can't hold every integer, so round(9007199254740993::BIGINT) returns 9007199254740992.0.

DECIMAL or DOUBLE

A DOUBLE holds a binary fraction, so a decimal value such as 1.005 isn't stored exactly, and rounding it to two places doesn't always give what the decimal digits suggest: round(1.005::DOUBLE, 2) is 1.0, while round(1.005, 2) on the DECIMAL literal is 1.01. A FLOAT holds fewer digits still: round(2.345::FLOAT, 2) comes back as 2.3499999046325684. When the decimal digits matter, as for money, store and round DECIMAL values.

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(NULL), round(2.5, NULL), round(0.5), round(1.5), round(2.5), round(-0.5), round(-2.5)")
run("SELECT round(2.5::DOUBLE), round(-2.5::DOUBLE), round(1.005, 2), round(1.005::DOUBLE, 2), "
    "round(2.5, 20), typeof(round(2.5, 20))")
run("SELECT round(2.5::DOUBLE, 400), round(2.5::DOUBLE, -400), round(-0.4::DOUBLE), signbit(round(-0.4::DOUBLE)), "
    "round('nan'::DOUBLE, 2), round('inf'::DOUBLE, 2)")
run("SELECT round(5::TINYINT), typeof(round(5::TINYINT)), round(2.45, '1')")
run("SELECT round(2.45, 1.6)")
run("SELECT round(2.5::DOUBLE, 2147483648)")
run("SELECT round('12')")

[(None, None, Decimal('1'), Decimal('2'), Decimal('3'), Decimal('-1'), Decimal('-3'))]
[(3.0, -3.0, Decimal('1.01'), 1.0, Decimal('2.5'), 'DECIMAL(2,1)')]
[(2.5, 0.0, -0.0, True, nan, inf)]
[(5.0, 'DOUBLE', Decimal('2.5'))]
InternalError: Binder Error: No function matches the given name and argument types 'round(DECIMAL(3,2), DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'round(DOUBLE, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "round(STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

Round each reading and price to an integer and to one or two places. Row 3's reading, 1.005, rounds to 1.0 at two places, because the DOUBLE doesn't hold 1.005 exactly:

run(f"SELECT id, round(x), round(x, 2), round(price), round(price, 1) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3.0, 2.5, Decimal('2'), Decimal('2.5')), (2, -3.0, -2.5, Decimal('-1'), Decimal('-1.4')), (3, 1.0, 1.0, Decimal('10'), Decimal('10.1')), (4, None, None, None, None)]

Round the counts to tens and hundreds. The BIGINT count comes back as a DOUBLE:

run(f"SELECT id, n, round(n, -1), round(n, -2) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1234, 1230.0, 1200.0), (2, 15, 20.0, 0.0), (3, 25, 30.0, 0.0), (4, None, None, None)]

Keep the readings that round to 3:

run(f"SELECT id, x FROM {TABLE} WHERE round(x) = 3 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() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes round() 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() 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(x) = 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (round(x) = 3.0)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             x             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Write the condition as a range on the raw column. For a positive target, round(x) = 3 is the same as x >= 2.5 AND x < 3.5, and that form 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 x >= 2.5 AND x < 3.5")

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

• The same goes for decimal places. round(price, 1) = 2.5 runs in a FILTER step; price >= 2.45 AND price < 2.55 is pushed down.
• round() of a constant is fine. A function of constants is computed once, before the scan, so x > round(2.4) + sqrt(0.25) is still a column compared with a constant. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE x > round(2.4) + sqrt(0.25)")

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

Limitations and errors

• On a DECIMAL, the number of places must be a constant. With a column, round(price, places) fails with Not implemented Error: ROUND(DECIMAL, INTEGER) with non-constant precision is not supported. Write the number of places in the query.
• n must be an integer. round(2.45, 1.6) fails with No function matches the given name and argument types, and so does a value of n too large for an INTEGER.
• One rounding mode. Halves always go away from zero; there's no third argument for the mode, and round(2.5, 0, 'half_even') fails with No function matches the given name and argument types. Use round_even for banker's rounding. round_half_up doesn't exist.
• Integers become DOUBLE. See The result type. Large BIGINT values can lose precision.
• Strings aren't converted for x. round('12') fails with Could not choose a best candidate function. Cast the string to a number first.
• Numbers only. A date, a boolean, a BLOB, a list or an interval fails with No function matches the given name and argument types. To round a date or timestamp, use date_trunc.

run(f"SELECT round(price, places) FROM {TABLE}")
run("SELECT round(2.5, 0, 'half_even')")
run("SELECT round(2.5, 0), round_even(2.5, 0)")

InternalError: Not implemented Error: ROUND(DECIMAL, INTEGER) with non-constant precision is not supported
InternalError: Binder Error: No function matches the given name and argument types 'round(DECIMAL(2,1), INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
[(Decimal('3'), 2.0)]

Similar functions

round, ceil, floor and trunc return a DECIMAL for these DECIMAL literals; round_even and even return a DOUBLE. At one decimal place, round(2.45, 1) is 2.5 and round(2.35, 1) is 2.4:

run("SELECT round(2.5), round_even(2.5, 0), ceil(2.5), floor(2.5), trunc(2.5), even(2.5)")
run("SELECT round(-2.5), round_even(-2.5, 0), ceil(-2.5), floor(-2.5), trunc(-2.5), even(-2.5)")
run("SELECT round(2.45), round_even(2.45, 0), ceil(2.45), floor(2.45), trunc(2.45), even(2.45)")
run("SELECT round(-0.5), round_even(-0.5, 0), ceil(-0.5), floor(-0.5), trunc(-0.5), even(-0.5)")
run("SELECT round(2.45, 1), round_even(2.45, 1), round(2.35, 1), round_even(2.35, 1)")

[(Decimal('3'), 2.0, Decimal('3'), Decimal('2'), Decimal('2'), 4.0)]
[(Decimal('-3'), -2.0, Decimal('-2'), Decimal('-3'), Decimal('-2'), -4.0)]
[(Decimal('2'), 2.0, Decimal('3'), Decimal('2'), Decimal('2'), 4.0)]
[(Decimal('-1'), -0.0, Decimal('0'), Decimal('-1'), Decimal('0'), -2.0)]
[(Decimal('2.5'), 2.5, Decimal('2.4'), 2.4)]

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, ceil, floor and trunc.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.