// (integer division)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Arithmetic operators. a // b divides two integers and drops the fraction toward zero: 7 // 2 is 3, and -7 // 2 is -3. The result keeps the integer type, and dividing by zero returns NULL. On DECIMAL, FLOAT and DOUBLE values, // doesn't drop the fraction: 7.5 // 2 is 3.75.

Syntax

a // b

Returns: for two integers of the same type, that type: INTEGER // INTEGER is an INTEGER. FLOAT // FLOAT is 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_operators_integer_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 log events: an offset in seconds (INTEGER, negative for an event before the reference time), a size in bytes (BIGINT), a ratio (DOUBLE) and an amount (DECIMAL(10,2)). 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_integer_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}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, seconds INTEGER, bytes BIGINT, ratio DOUBLE, amount DECIMAL(10,2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 3725, 1536, 7.5, 7.50), "
    "(2, -90, 5000000, -7.5, -7.50), "
    "(3, 0, 0, 0.0, 0.00), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

On integers, a // b divides and drops the fraction toward zero: the result is the integer part of the exact quotient, whatever its sign.

For a negative quotient, this isn't rounding down: -3.5 becomes -3, not -4. To round down, use fdiv: fdiv(-7, 2) is -4.0. The remainder that goes with // is % (modulo), so a = (a // b) * b + a % b.

Dividing by zero returns NULL for every argument type, also DOUBLE: 1 // 0 and 1.0::DOUBLE // 0 are both NULL. (The / operator returns inf instead.)

Not integer division for other types. With a DECIMAL, FLOAT or DOUBLE argument, // divides without dropping the fraction: 7.5 // 2 is 3.75, the same as 7.5 / 2, and -0.5::DOUBLE // 1 is -0.5. A DECIMAL argument gives a DOUBLE. To get a whole number, use fdiv (rounded down) or trunc of the quotient (toward zero).

A numeric string is converted. In '7200' // seconds, the string is converted to the type of the other side. A string that isn't a number fails with Could not convert string 'abc' to INT32.

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, -7 // 2, 7 // -2, -7 // -2")
run("SELECT 1 // 0, 1.0::DOUBLE // 0, 7.5 // 2, typeof(7.5 // 2), -0.5::DOUBLE // 1, 2.45 // 0.7")
run("SELECT 'inf'::DOUBLE // 1, 1 // 'inf'::DOUBLE, 'nan'::DOUBLE // 1")
run("SELECT (-128)::TINYINT // (-1)::TINYINT")
run(f"SELECT '7200' // seconds, 'abc' // seconds FROM {TABLE} WHERE id = 1")
run("SELECT INTERVAL 1 DAY // 2")

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

Examples

An offset in whole minutes and the seconds left over, and a size in whole KiB. Event 2 is 90 seconds before the reference time, and both // and % keep the sign of its negative offset:

run(f"SELECT id, seconds // 60, seconds % 60, bytes // 1024 FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 62, 5, 1), (2, -1, -30, 4882), (3, 0, 0, 0), (4, None, None, None)]

The types of the results:

run(f"SELECT typeof(seconds // 60), typeof(bytes // 1024), typeof(ratio // 2), typeof(amount // 2) FROM {TABLE} WHERE id = 1")

[('INTEGER', 'BIGINT', 'DOUBLE', 'DOUBLE')]

On a DOUBLE and a DECIMAL, // keeps the fraction; fdiv rounds down and trunc of the quotient drops the fraction toward zero:

run(f"SELECT id, ratio // 2, amount // 2, fdiv(ratio, 2), trunc(ratio / 2) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3.75, 3.75, 3.0, 3.0), (2, -3.75, -3.75, -4.0, -3.0), (3, 0.0, 0.0, 0.0, 0.0), (4, None, None, None, None)]

Keep only the events in the 62nd minute:

run(f"SELECT id, seconds FROM {TABLE} WHERE seconds // 60 = 62 ORDER BY id LIMIT 10")

[(1, 3725)]

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 a column, so seconds // 60 is computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes the quotient for each row it returns.
• In WHERE, the scan sends the column 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:

plan(f"SELECT id FROM {TABLE} WHERE seconds // 60 = 62")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   ((seconds // 60) = 62)  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          seconds          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Write the range instead. For non-negative values, seconds // 60 = 62 is the same as seconds >= 3720 AND seconds < 3780, a range on the raw column that's pushed down to VAST DataBase. The plan has no FILTER step. For negative values the range is different, because // drops toward zero: seconds // 60 = 0 holds from -59 to 59.

plan(f"SELECT id FROM {TABLE} WHERE seconds >= 3720 AND seconds < 3780")
run(f"SELECT id, seconds FROM {TABLE} WHERE seconds >= 3720 AND seconds < 3780 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1, 3725)]

• Store a bucket you filter on often. If you often filter on the minute or another bucket, compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In id > 100 AND seconds // 60 = 62, id > 100 is still evaluated in VAST DataBase, so the division runs only on the rows that pass it.

Limitations and errors

• The smallest value of a signed integer type divided by -1 stops the query. Its positive counterpart doesn't fit the type, so (-128)::TINYINT // -1 fails with Overflow in division of -128 / -1. Cast to a wider type first.
• No DIV operator and no div() or intdiv() function. 7 DIV 2 is a syntax error, and the functions fail with Scalar Function with name … does not exist!. Use // or divide.
• No INTERVAL division. 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 CAST((-128)::TINYINT AS INTEGER) // -1")
run("SELECT div(7, 2)")
run("SELECT 7 DIV 2")

[(128,)]
InternalError: Catalog Error: Scalar Function with name div does not exist!
InternalError: Parse query: 'SELECT 7 DIV 2' failed with: Parser Error: syntax error at or near "2"

Similar functions

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

[(-3, -3, -4.0, -3.5, -1)]
[(3.75, 3.75, 3.0, 3.75, Decimal('1.5'))]
[(None, None, inf, 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.
• / (division), % (modulo), divide and fdiv.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.