ceil

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Sign and rounding. Rounds a number up, towards positive infinity, to the nearest integer value. ceiling is the same function.

Syntax

ceil(x)
ceiling(x)

Returns: DECIMAL(p, 0) for a DECIMAL(p, s), FLOAT for a FLOAT, and DOUBLE for a DOUBLE or an integer.

Aliases: ceiling.

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_ceil. 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) and a count (BIGINT). Row 3 holds a count above 2^53, and 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_ceil"'

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), n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, 2.45, 12), "
    "(2, -1.25, -1.35, -18), "
    "(3, -0.5, 99999999.99, 9007199254740993), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

ceil(x) returns the smallest integer value that is greater than or equal to x. Any fraction rounds up, however small: ceil(2.0000001::DOUBLE) is 3.0, and ceil(-2.5) is -2.

The result keeps the kind of number you pass in:

• DECIMAL: the result is a DECIMAL with the same precision and no decimal places. ceil(2.45) is the DECIMAL(3,0) 3, and ceil(99999999.99::DECIMAL(10,2)) is the DECIMAL(10,0) 100000000. A number literal such as 2.5 is a DECIMAL.
• DOUBLE and FLOAT: the result has the same type and is a whole number, such as 3.0. Rounding a value between -1 and 0 up gives a negative zero, -0.0. nan, inf and -inf stay as they are.
• Integers: there's no integer form of ceil. An integer is converted to DOUBLE, and the result is a DOUBLE. Above 2^53 a DOUBLE can't hold every integer, so ceil(9007199254740993::BIGINT) returns 9007199254740992.0. An integer is already a whole number: leave it as it is, or use trunc, which keeps the integer type.

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 ceil(NULL), ceil(2.5), ceil(-2.5), ceil(-0.5), typeof(ceil(2.5))")
run("SELECT ceil(-0.5::DOUBLE), signbit(ceil(-0.5::DOUBLE)), ceil(2.0000001::DOUBLE), ceil(5e-324::DOUBLE), "
    "ceil('nan'::DOUBLE), ceil('inf'::DOUBLE)")
run("SELECT ceil(99999999.99::DECIMAL(10,2)), typeof(ceil(99999999.99::DECIMAL(10,2)))")
run("SELECT ceil(9007199254740993::BIGINT), typeof(ceil(9007199254740993::BIGINT))")
run("SELECT ceil(2.45, 1)")
run("SELECT ceil('12')")

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

Examples

Round each column up. ceiling gives the same result as ceil, and the BIGINT count comes back as a DOUBLE:

run(f"SELECT id, ceil(x), ceiling(x), ceil(price), ceil(n) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3.0, 3.0, Decimal('3'), 12.0), (2, -1.0, -1.0, Decimal('-1'), -18.0), (3, -0.0, -0.0, Decimal('100000000'), 9007199254740992.0), (4, None, None, None, None)]

Round up to one decimal place. ceil has no places argument, so scale, round up and scale back; the result is a DOUBLE:

run(f"SELECT id, x, ceil(x * 10) / 10 FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.5, 2.5), (2, -1.25, -1.2), (3, -0.5, -0.5), (4, None, None)]

Keep the readings that round up to 3:

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

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

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

Make it faster

• Write the condition as a range on the raw column. ceil(x) = 3 is the same as x > 2 AND x <= 3, 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 AND x <= 3")

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

• A function of constants is fine. It's computed once, before the scan: a condition such as x > round(2.4) + sqrt(0.25) is still a column compared with a constant, and is pushed down.

Limitations and errors

• No number of places. ceil(x, n) fails with No function matches the given name and argument types. Use ceil(x * 10) / 10 for one place, ceil(x * 100) / 100 for two, and so on; see Examples.
• Integers become DOUBLE. See What it does. Large BIGINT values can lose precision.
• Strings aren't converted. ceil('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.
• No ceil_div. It fails with Scalar Function with name ceil_div does not exist!. Round up a division with ceil(a / b).

run("SELECT ceil(2.71, 1)")
run("SELECT ceil(2.71 * 10) / 10, ceil(7 / 2)")

InternalError: Binder Error: No function matches the given name and argument types 'ceil(DECIMAL(3,2), INTEGER_LITERAL)'. You might need to add explicit type casts.
[(2.8, 4.0)]

Similar functions

ceil, floor, trunc and round return a DECIMAL for these DECIMAL literals; round_even and even return a DOUBLE:

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

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