divide

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Arithmetic. divide(a, b) divides a by b. On integers it's integer division, like the // operator: divide(7, 2) is 3. On DECIMAL and floating-point values it keeps the fraction. Dividing by zero returns NULL, for every type.

Syntax

divide(a, b)

Returns: for two integers of the same type, that type: divide(INTEGER, INTEGER) is an INTEGER. FLOAT arguments give a FLOAT; DOUBLE and DECIMAL arguments give a 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_divide. 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 three batches: a number of items and a number of boxes (INTEGER; the item count of batch 2 is negative, a return), a weight in kilograms (DOUBLE) and a cost (DECIMAL(10,2)). Batch 3 has no boxes, 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_divide"'

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}.batches"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, items INTEGER, boxes INTEGER, weight DOUBLE, cost DECIMAL(10,2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 25, 4, 12.5, 19.99), "
    "(2, -7, 2, 3.0, 4.25), "
    "(3, 10, 0, 5.0, 0.00), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

divide(a, b) is the function form of integer division, with one difference from the operators: a zero divisor always gives NULL.

Toward zero, not down. For a negative quotient, dropping the fraction isn't rounding down: divide(-7, 2) is -3, not -4. To round down, use fdiv.

Compared with the operators: divide() gives the same results as //, including NULL for an integer divided by 0. The / operator is different: it always returns a DOUBLE with the fraction, and a DOUBLE divided by 0 is inf there, but NULL with divide().

DECIMAL values are divided as DOUBLE, so the result isn't exact: divide(2.45, 0.7) is 3.5000000000000004.

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 divide(NULL, 1), divide(1, NULL), divide(7, 2), divide(-7, 2), divide(7, -2)")
run("SELECT divide(7.0, 2), typeof(divide(7.0, 2)), divide(7::FLOAT, 2::FLOAT), typeof(divide(7::FLOAT, 2::FLOAT))")
run("SELECT divide(1, 0), divide(1.0::DOUBLE, 0), divide(-1.0::DOUBLE, 0), divide(2.45, 0.7)")
run(f"SELECT divide('12', boxes), divide('abc', boxes) FROM {TABLE} WHERE id = 1")
run("SELECT divide((-128)::TINYINT, (-1)::TINYINT)")
run("SELECT divide(INTERVAL 1 DAY, 2)")

[(None, None, 3, -3, -3)]
[(3.5, 'DOUBLE', 3.5, 'FLOAT')]
[(None, None, None, 3.5000000000000004)]
OSError: Could not convert string 'abc' to INT32
OSError: Overflow in division of -128 / -1
InternalError: Binder Error: No function matches the given name and argument types 'divide(INTERVAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Full boxes per batch, the weight per box and the cost per box. Batch 3 has no boxes, so all three are NULL, not an error and not inf:

run(f"SELECT id, divide(items, boxes), divide(weight, boxes), divide(cost, boxes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 6, 3.125, 4.9975), (2, -3, 1.5, 2.125), (3, None, None, None), (4, None, None, None)]

The same with the operators: // gives the same result on integers, and / gives inf for a DOUBLE divided by 0:

run(f"SELECT id, items // boxes, weight / boxes FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 6, 3.125), (2, -3, 1.5), (3, None, inf), (4, None, None)]

Keep only the batches with more than two items per box:

run(f"SELECT id, items, boxes FROM {TABLE} WHERE divide(items, boxes) > 2 ORDER BY id LIMIT 10")

[(1, 25, 4)]

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (divide(items, boxes) > 2)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           items           │
│           boxes           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Write a range on the raw column for a constant divisor. For non-negative values, divide(items, 4) > 2 holds exactly when items >= 12, a comparison with a constant that's pushed down to VAST DataBase. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE items >= 12")

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

• Store a ratio you filter on often. A quotient of two columns always runs in the Query Engine. Compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In boxes > 0 AND divide(items, boxes) > 2, boxes > 0 is evaluated in VAST DataBase, so divide() runs only on the rows that pass it.

Limitations and errors

• Integer division on integers. divide(7, 2) is 3, not 3.5. For the fraction, divide a DOUBLE: divide(7::DOUBLE, 2) is 3.5, or use /.
• The smallest value of a signed integer type divided by -1 stops the query. Its positive counterpart doesn't fit the type, so divide((-128)::TINYINT, (-1)::TINYINT) fails with Overflow in division of -128 / -1. Cast to a wider type first.
• No div() or intdiv(). Both fail with Scalar Function with name … does not exist!, and 7 DIV 2 is a syntax error. Use divide() or //.
• No INTERVAL division. divide(INTERVAL 1 DAY, 2) fails with No function matches the given name and argument types. Booleans, dates, BLOB values and lists fail the same way.

run("SELECT divide(7::DOUBLE, 2), divide(CAST((-128)::TINYINT AS INTEGER), -1)")
run("SELECT div(7, 2)")
run("SELECT intdiv(7, 2)")

[(3.5, 128)]
InternalError: Catalog Error: Scalar Function with name div does not exist!
InternalError: Catalog Error: Scalar Function with name intdiv does not exist!

Similar functions

run("SELECT divide(7, 2), 7 // 2, 7 / 2, fdiv(7, 2), mod(7, 2)")
run("SELECT divide(-7, 2), -7 // 2, -7 / 2, fdiv(-7, 2), mod(-7, 2)")
run("SELECT divide(1.0::DOUBLE, 0), 1.0::DOUBLE // 0, 1.0::DOUBLE / 0, fdiv(1.0::DOUBLE, 0), mod(1.0::DOUBLE, 0)")

[(3, 3, 3.5, 3.0, 1)]
[(-3, -3, -3.5, -4.0, -1)]
[(None, None, inf, inf, nan)]

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 arithmetic functions.
• // (integer division), / (division), % (modulo) and fdiv.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.