trunc

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Sign and rounding. Drops the fractional part of a number, so it rounds towards zero: down for a positive number, up for a negative one. An integer comes back unchanged, in its own type.

Syntax

trunc(x)

Returns: the type of x for an integer, FLOAT or DOUBLE; DECIMAL(p, 0) for a DECIMAL(p, s).

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_trunc. 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_trunc"'

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

trunc(x) cuts off everything after the decimal point: trunc(2.7) is 2 and trunc(-2.7) is -2. For a positive number that's the same as floor; for a negative number it's the same as ceil.

The result keeps the type of x:

• Integers: the value comes back unchanged, in the same type. trunc(9007199254740993::BIGINT) is the BIGINT 9007199254740993, with no loss of precision. (ceil, floor and round turn an integer into a DOUBLE.)
• DECIMAL: the result is a DECIMAL with the same precision and no decimal places: trunc(2.45::DECIMAL(10,2)) is a DECIMAL(10,0). A number literal such as 2.7 is a DECIMAL.
• DOUBLE and FLOAT: the result has the same type and is a whole number, such as 2.0. Truncating a value between -1 and 0 gives a negative zero, -0.0. nan, inf and -inf stay as they are.

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

[(None, Decimal('2'), Decimal('-2'), Decimal('0'), 'DECIMAL(10,0)')]
[(-0.0, True, 0.0, nan, -inf)]
[(9007199254740993, 'BIGINT', 'INTEGER')]
InternalError: Binder Error: No function matches the given name and argument types 'trunc(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 "trunc(STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

Truncate each column. The BIGINT count keeps its type and its exact value:

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

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

Cut a value to one decimal place, or two. trunc has no places argument, so scale, truncate and scale back; the result is a DOUBLE:

run(f"SELECT id, x, trunc(x * 10) / 10 FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT trunc(2.789 * 100) / 100, trunc(-2.789 * 100) / 100")

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

Keep the readings whose whole part is 2:

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (trunc(x) = 2.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, trunc(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. trunc(x, n) fails with No function matches the given name and argument types, also with a negative n. Use trunc(x * 100) / 100 for two places, and so on; see Examples. To round to n places instead, use round(x, n).
• No truncate. It fails with Scalar Function with name truncate does not exist!. Use trunc.
• Strings aren't converted. trunc('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 truncate a date or timestamp, use date_trunc.

run("SELECT trunc(2.789, 2)")
run("SELECT truncate(2.789, 2)")
run("SELECT trunc(2.789 * 100) / 100, round(2.789, 2)")

InternalError: Binder Error: No function matches the given name and argument types 'trunc(DECIMAL(4,3), INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name truncate does not exist!
[(2.78, Decimal('2.79'))]

Similar functions

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

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

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