even

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Sign and rounding. Rounds a number away from zero to the next even integer: 2.5 becomes 4, 3 becomes 4 and -0.5 becomes -2. An even integer stays as it is. The result is a DOUBLE.

Syntax

even(x)

Returns: DOUBLE.

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_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 four bookings: a number of guests (INTEGER), a length of stay in nights (DOUBLE) and a price (DECIMAL). 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_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}.bookings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, guests INTEGER, nights DOUBLE, price DECIMAL(10, 2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 3, 2.5, 2.45), "
    "(2, 2, 0.1, -1.35), "
    "(3, 7, -0.5, 10.00), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

even(x) rounds x away from zero, to the nearest even integer that is at least as far from zero as x:

Any fraction moves the value to the next even integer, so even(0.1) is already 2. even isn't a rounding to the nearest: for halves that go to the even digit (banker's rounding), use round_even.

even has a single form, for DOUBLE, so every argument is converted to DOUBLE first and the result is always a DOUBLE: even(2) is 2.0, and even(2.45) on a DECIMAL is the DOUBLE 4.0. Above 2^53 a DOUBLE can't hold every integer, so even(9007199254740993::BIGINT) returns 9007199254740992.0. A string that holds a number is converted too: even('12') is 12.0.

On special DOUBLE values, nan, inf, -inf and -0.0 stay as they are, and the smallest positive DOUBLE, 5e-324, becomes 2.0.

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 even(NULL), even(0), even(-0.0::DOUBLE), signbit(even(-0.0::DOUBLE)), even(5e-324::DOUBLE)")
run("SELECT even('nan'::DOUBLE), even('inf'::DOUBLE), even(1.7976931348623157e308), even(9007199254740993::BIGINT)")
run("SELECT even(2), typeof(even(2)), even(2.45), typeof(even(2.45)), even('12')")
run("SELECT even('abc')")
run("SELECT even(2.5, 1)")
run("SELECT even(true)")

[(None, 0.0, -0.0, True, 2.0)]
[(nan, inf, 1.7976931348623157e+308, 9007199254740992.0)]
[(2.0, 'DOUBLE', 4.0, 'DOUBLE', 12.0)]
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'even(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'even(BOOLEAN)'. You might need to add explicit type casts.

Examples

Round each column to an even number. Each result is a DOUBLE, also for the INTEGER and DECIMAL columns:

run(f"SELECT id, even(guests), even(nights), even(price) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 4.0, 4.0, 4.0), (2, 2.0, 2.0, -2.0), (3, 8.0, -2.0, 10.0), (4, None, None, None)]

Seat guests at tables for two: the number of seats to set is the guest count rounded up to an even number, cast back to an integer:

run(f"SELECT id, guests, CAST(even(guests) AS INTEGER) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3, 4), (2, 2, 2), (3, 7, 8), (4, None, None)]

Keep the bookings that need four seats:

run(f"SELECT id, guests FROM {TABLE} WHERE even(guests) = 4 ORDER BY id LIMIT 10")

[(1, 3)]

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 even() is always computed by the Query Engine:

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (even(CAST(guests AS   │
│       DOUBLE)) = 4.0)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           guests          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Write the condition on the raw column. For whole numbers, even(guests) = 4 is the same as guests >= 3 AND guests <= 4, which compares the column with constants and 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 guests >= 3 AND guests <= 4")

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

• Use even() in the SELECT list, where it runs only on the rows the scan returns.

Limitations and errors

• Always a DOUBLE. Cast the result back if you need an integer: CAST(even(n) AS INTEGER). Large BIGINT values lose precision on the way; see What it does.
• Not a rounding to the nearest. even(0.1) is 2. For banker's rounding, use round_even; for the nearest integer, use round.
• A string that isn't a number fails the query. even('abc') fails with Could not convert string 'abc' to DOUBLE.
• One argument only. There's no number of places: even(2.5, 1) fails with No function matches the given name and argument types.
• Numbers only. A date, a boolean, a BLOB, a list or an interval fails with No function matches the given name and argument types.

run("SELECT even(0.1), round(0.1), round_even(0.1, 0)")
run("SELECT CAST(even(3) AS INTEGER)")

[(2.0, Decimal('0'), 0.0)]
[(4,)]

Similar functions

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

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

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