/ (division)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Arithmetic operators. a / b divides a by b and returns a DOUBLE, also when both are integers: 7 / 2 is 3.5. Dividing by zero doesn't raise an error; it returns infinity or NaN.

Syntax

a / b

Returns: DOUBLE, for integers and DECIMAL values too. Two FLOAT values give a FLOAT.

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_operators_division. 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 taxi trips: the distance in kilometres and the duration in minutes (INTEGER), the fare (DECIMAL(10,2)) and the fuel used in litres (DOUBLE). Trip 2 has a duration of 0, trip 3 is all zeros, 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_operators_division"'

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}.trips"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, km INTEGER, minutes INTEGER, fare DECIMAL(10,2), litres DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 42, 35, 61.50, 3.5), "
    "(2, 7, 0, 12.00, 0.6), "
    "(3, 0, 0, 0.00, 0.0), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

a / b is the quotient as a binary floating-point number. Integers are converted to DOUBLE first (the plan in How it runs in VAST shows the casts), so the fraction is kept: 7 / 2 is 3.5, and -7 / 2 is -3.5. To drop the fraction, use // (integer division) or divide.

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

Large integers lose precision. A DOUBLE holds integers exactly only up to 9007199254740992 (2 to the power 53): 9007199254740993 / 1 is 9007199254740992.0.

Dividing by zero returns a value, not an error:

inf and nan are valid DOUBLE values: they don't stop the query, and they carry on into later arithmetic: infinity divided by infinity is nan. Test for them with isnan and isinf, or keep a zero divisor out of the division (see Limitations and errors).

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 NULL / 1, 1 / NULL, 7 / 2, typeof(7 / 2), -7 / 2, 7::FLOAT / 2::FLOAT, typeof(7::FLOAT / 2::FLOAT)")
run("SELECT 1 / 0, -1 / 0, 0 / 0, isnan(0 / 0), 1 / -0.0::DOUBLE")
run("SELECT 2.45 / 0.7, typeof(2.45 / 0.7), 9007199254740993 / 1")
run(f"SELECT '12' / km FROM {TABLE} WHERE id = 1")
run("SELECT true / 2")

[(None, None, 3.5, 'DOUBLE', -3.5, 3.5, 'FLOAT')]
[(inf, -inf, nan, True, -inf)]
[(3.5000000000000004, 'DOUBLE', 9007199254740992.0)]
OSError: Could not convert string '12' to INTERVAL
InternalError: Binder Error: No function matches the given name and argument types '/(BOOLEAN, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The average speed in km/h, the fare per kilometre and the fuel per 100 km. Trip 2 has a duration of 0, so its speed is inf; trip 3 divides 0 by 0, so its values are nan:

run(f"SELECT id, km / minutes * 60, fare / km, litres / km * 100 FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 72.0, 1.4642857142857142, 8.333333333333332), (2, inf, 1.7142857142857142, 8.571428571428571), (3, nan, nan, nan), (4, None, None, None)]

Keep only the trips faster than 1 km a minute. Trips 2 and 3 are kept as well: 7 / 0 is inf and 0 / 0 is nan, and both compare as greater than 1. Add minutes > 0 to leave them out:

run(f"SELECT id, km, minutes FROM {TABLE} WHERE km / minutes > 1 ORDER BY id LIMIT 10")

[(1, 42, 35), (2, 7, 0), (3, 0, 0)]

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 quotient of two columns, so km / minutes is computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the quotient for each row it returns.
• In WHERE, the scan sends both columns for every row, and a FILTER step in the Query Engine computes the quotient 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, and the conversion of both columns to DOUBLE:

plan(f"SELECT id FROM {TABLE} WHERE km / minutes > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   ((CAST(km AS DOUBLE) /  │
│  CAST(minutes AS DOUBLE)) │
│           > 1.0)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             km            │
│          minutes          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Multiply the constant instead of dividing the column. litres / 2 > 1 runs in a FILTER step; litres > 1 * 2 compares the raw column with a constant, which is computed once and pushed down to VAST DataBase. Keep the direction of the comparison in mind when the divisor is negative.

plan(f"SELECT id FROM {TABLE} WHERE litres / 2 > 1")
plan(f"SELECT id FROM {TABLE} WHERE litres > 1 * 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   ((litres / 2.0) > 1.0)  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           litres          │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         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. If you often filter on the speed, compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In minutes > 0 AND km / minutes > 1, minutes > 0 is evaluated in VAST DataBase, so the division runs only on the rows that pass it.

Limitations and errors

• Division by zero isn't an error. It returns inf, -inf or nan. To get NULL instead, divide by nullif(b, 0), or use divide, which returns NULL for a zero divisor. To skip those rows, add b <> 0 to WHERE.
• The result is always floating point. Even DECIMAL values are divided as DOUBLE, so the result is rounded. For integer division, use //; for a quotient rounded down, fdiv.
• Strings aren't converted. '12' / km fails with Could not convert string '12' to INTERVAL, because the string is read as an interval. Cast it to a number first: CAST('12' AS DOUBLE) / km.
• Booleans, dates, BLOB values and lists fail with No function matches the given name and argument types.

run(f"SELECT id, km / nullif(minutes, 0) * 60, divide(km::DOUBLE, minutes) * 60 FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, km / minutes * 60 FROM {TABLE} WHERE minutes <> 0 ORDER BY id LIMIT 10")
run(f"SELECT CAST('12' AS DOUBLE) / km FROM {TABLE} WHERE id = 1")

[(1, 72.0, 72.0), (2, None, None), (3, None, None), (4, None, None)]
[(1, 72.0)]
[(0.2857142857142857,)]

Similar functions

run("SELECT 7 / 2, 7 // 2, divide(7, 2), fdiv(7, 2), 7 % 2")
run("SELECT -7 / 2, -7 // 2, divide(-7, 2), fdiv(-7, 2), -7 % 2")
run("SELECT 1 / 0, 1 // 0, divide(1, 0), fdiv(1, 0), 1 % 0")

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

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Operators and predicates: all the arithmetic operators.
• // (integer division), % (modulo), divide and fdiv.
• isnan and isinf: test for nan and inf.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.