floor

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Sign and rounding. Rounds a number down, towards negative infinity, to the nearest integer value.

Syntax

floor(x)

Returns: DECIMAL(p, 0) for a DECIMAL(p, s), 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_floor. 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_floor"'

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

floor(x) returns the largest integer value that is less than or equal to x. Any fraction rounds down: floor(2.5) is 2, and a negative value moves away from zero, so floor(-2.5) is -3 and floor(-0.5) is -1.

The result keeps the kind of number you pass in:

• DECIMAL: the result is a DECIMAL with the same precision and no decimal places. floor(2.45) is the DECIMAL(3,0) 2, and floor(99999999.99::DECIMAL(10,2)) is the DECIMAL(10,0) 99999999. 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 2.0. A negative zero stays -0.0, and nan, inf and -inf stay as they are.
• Integers: there's no integer form of floor. An integer is converted to DOUBLE, and the result is a DOUBLE. Above 2^53 a DOUBLE can't hold every integer, so floor(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 floor(NULL), floor(2.5), floor(-2.5), floor(-0.5), floor(-0.5::DOUBLE), typeof(floor(2.5))")
run("SELECT floor(2.0000001::DOUBLE), floor(5e-324::DOUBLE), floor(-0.0::DOUBLE), floor('nan'::DOUBLE), "
    "floor('inf'::DOUBLE)")
run("SELECT floor(99999999.99::DECIMAL(10,2)), typeof(floor(99999999.99::DECIMAL(10,2)))")
run("SELECT floor(9007199254740993::BIGINT), typeof(floor(9007199254740993::BIGINT))")
run("SELECT floor(2.45, 1)")
run("SELECT floor('12')")

[(None, Decimal('2'), Decimal('-3'), Decimal('-1'), -1.0, 'DECIMAL(2,0)')]
[(2.0, 0.0, -0.0, nan, inf)]
[(Decimal('99999999'), 'DECIMAL(10,0)')]
[(9007199254740992.0, 'DOUBLE')]
InternalError: Binder Error: No function matches the given name and argument types 'floor(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 "floor(STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

Round each column down. The BIGINT count comes back as a DOUBLE:

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

[(1, 2.0, Decimal('2'), 12.0), (2, -2.0, Decimal('-2'), -18.0), (3, -1.0, Decimal('99999999'), 9007199254740992.0), (4, None, None, None)]

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

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

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

Keep the readings from 2 up to, but not including, 3:

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

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

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

Make it faster

• Write the condition as a range on the raw column. floor(x) = 2 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. floor(x, n) fails with No function matches the given name and argument types. Use floor(x * 100) / 100 for two places, and so on. On negative values this differs from cutting off the digits: floor(-2.789 * 100) / 100 is -2.79, while trunc(-2.789 * 100) / 100 is -2.78.
• Integers become DOUBLE. See What it does. Large BIGINT values can lose precision.
• Strings aren't converted. floor('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 down, use date_trunc.

run("SELECT floor(2.71, 1)")
run("SELECT floor(2.789 * 100) / 100, floor(-2.789 * 100) / 100, trunc(-2.789 * 100) / 100")

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

Similar functions

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

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

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