- (subtraction and negation)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Arithmetic operators. a - b subtracts b from a, and -a negates a. subtract(a, b) and subtract(a) are the same operations written as functions. On integers the result keeps the integer type, and a result that doesn't fit it stops the query.

Syntax

a - b
-a
subtract(a, b)
subtract(a)

Returns: for two numbers of the same type, that type: INTEGER - INTEGER is an INTEGER, DOUBLE - DOUBLE a DOUBLE. A DECIMAL result gets one more digit: DECIMAL(10,2) - DECIMAL(10,2) is a DECIMAL(11,2). -a has the type of a. DATE - INTEGER is a DATE.

Aliases: subtract(a, b) for a - b, and subtract(a) for -a.

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_subtraction. 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 stock counts for three products: the count at opening and at closing (INTEGER), the price and cost (DECIMAL(10,2)), the storage temperature (DOUBLE), the number of free bins (UTINYINT, an unsigned 8-bit integer) and the shipping date. 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_subtraction"'

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}.stock"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, opening INTEGER, closing INTEGER, price DECIMAL(10,2), "
    "cost DECIMAL(10,2), temp DOUBLE, bins UTINYINT, shipped DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 120, 95, 19.99, 12.50, 21.5, 5, DATE '2026-10-16'), "
    "(2, 40, 52, 4.25, 4.75, -3.25, 0, DATE '2026-10-01'), "
    "(3, 0, 0, 0.00, 0.00, 0.0, 200, DATE '2026-01-01'), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

a - b is the difference of two numbers, and -a is a with its sign changed. The function forms do the same: subtract(7, 2) is 5, and subtract(-5) is 5.

Integers stay integers. Two INTEGER values give an INTEGER, two BIGINT values a BIGINT, and so on for each integer type. If the result doesn't fit the type, the query stops with an overflow error, such as Overflow in subtraction of INT32 (…)!. This also applies to the unsigned types (UTINYINT, USMALLINT, UINTEGER, UBIGINT): they can't hold a negative number, so 0 - 1 on a UTINYINT is an error, Overflow in subtraction of UINT8 (0 - 1)!.

Negating an unsigned value wraps around. -a on an unsigned type doesn't fail: -(1::UTINYINT) is 255, the largest UTINYINT, and -(0::UBIGINT) is 0. Negating the smallest value of a signed type fails, because its positive counterpart doesn't fit: -((-128)::TINYINT) stops with Overflow in negation of integer!.

DECIMAL is exact, and the result gets one more digit before the decimal point: DECIMAL(10,2) - DECIMAL(10,2) is a DECIMAL(11,2), so the smallest DECIMAL(10,2) minus 1 still fits. FLOAT and DOUBLE results don't raise overflow errors; 'inf'::DOUBLE - 'inf'::DOUBLE is nan.

Dates: DATE - INTEGER subtracts days and returns a DATE: DATE '2026-10-16' - 3 is 2026-10-13. Date and time arithmetic is on the date and time page.

Two minus signs: - -5 (with a space) is 5, but --5 starts a SQL comment, so SELECT --5 is a syntax error. Write -(-5) or - -5.

The unary minus binds before **: -2 ** 2 is (-2) ** 2, which is 4.0, not -4.0. Write -(2 ** 2) for -4.

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, subtract(7, 2), subtract(-5), - -5, -2 ** 2")
run("SELECT 'inf'::DOUBLE - 'inf'::DOUBLE, signbit(-(0.0::DOUBLE)), -(1::UTINYINT), typeof(-(1::UTINYINT))")
run(f"SELECT bins - CAST(1 AS UTINYINT) FROM {TABLE} WHERE id = 2")
run("SELECT -((-128)::TINYINT)")
run(f"SELECT closing - 2147483647 - 2 FROM {TABLE} WHERE id = 3")
run(f"SELECT '12' - opening FROM {TABLE} WHERE id = 1")
run("SELECT true - 1")
run("SELECT --5")

[(None, None, 5, 5, 5, 4.0)]
[(nan, True, 255, 'UTINYINT')]
OSError: Overflow in subtraction of UINT8 (0 - 1)!
OSError: Overflow in negation of integer!
OSError: Overflow in subtraction of INT32 (-2147483647 - 2)!
InternalError: Binder Error: Could not choose a best candidate function for the function call "-(STRING_LITERAL, INTEGER)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '-(BOOLEAN, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Parse query: 'SELECT --5' failed with: Parser Error: SELECT clause without selection list

Examples

The change in stock, the margin, the temperature negated, and the same change with subtract():

run(f"SELECT id, closing - opening, price - cost, -temp, subtract(closing, opening) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, -25, Decimal('7.49'), -21.5, -25), (2, 12, Decimal('-0.50'), 3.25, 12), (3, 0, Decimal('0.00'), -0.0, 0), (4, None, None, None, None)]

The types of the results, including a BIGINT minus an INTEGER:

run(f"SELECT typeof(closing - opening), typeof(price - cost), typeof(-temp), typeof(id - opening) FROM {TABLE} WHERE id = 1")

[('INTEGER', 'DECIMAL(11,2)', 'DOUBLE', 'BIGINT')]

Three days before each shipping date:

run(f"SELECT id, shipped - 3 FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 10, 13)), (2, datetime.date(2026, 9, 28)), (3, datetime.date(2025, 12, 29)), (4, None)]

Keep only the products whose stock went down:

run(f"SELECT id, opening, closing FROM {TABLE} WHERE closing - opening < 0 ORDER BY id LIMIT 10")

[(1, 120, 95)]

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 arithmetic on two columns, so closing - opening is computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the difference 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 difference 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 closing - opening < 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((closing - opening) < 0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          closing          │
│          opening          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Subtracting a constant from an integer column is pushed down. closing - 5 > 50, compared with a constant, is evaluated in VAST DataBase: its plan is only a scan, with no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE closing - 5 > 50")

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

• Don't negate the column. -closing < -50 isn't rewritten: it runs in a FILTER step. Write closing > 50 instead:

plan(f"SELECT id FROM {TABLE} WHERE -closing < -50")
plan(f"SELECT id FROM {TABLE} WHERE closing > 50")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (-(closing) < -50)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          closing          │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store a difference you filter on often. A difference of two columns always runs in the Query Engine. If you often filter on closing - opening, 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 closing - opening < 0, id > 100 is still evaluated in VAST DataBase, so the subtraction runs only on the rows that pass it.

Limitations and errors

• Integer overflow stops the query. A result outside the range of its integer type fails with Overflow in subtraction of …! (or Overflow in negation of integer! for -a), and one such row cancels the whole query. Cast to a wider type first: CAST(closing AS BIGINT) - ….
• Unsigned types can't go below 0. Subtraction on UTINYINT, USMALLINT, UINTEGER or UBIGINT fails when the result would be negative. Cast to a signed type first. Negation doesn't fail; it wraps around (see What it does).
• No strings. '12' - opening fails with Could not choose a best candidate function. Cast the string first: CAST('12' AS INTEGER) - opening.
• Booleans, BLOB values and lists fail with No function matches the given name and argument types.
• TIMESTAMP WITH TIME ZONE values can't be subtracted; see date and time.

run(f"SELECT CAST(closing AS BIGINT) - 2147483647 - 2 FROM {TABLE} WHERE id = 3")
run(f"SELECT CAST(bins AS INTEGER) - 1 FROM {TABLE} WHERE id = 2")
run(f"SELECT CAST('12' AS INTEGER) - opening FROM {TABLE} WHERE id = 1")

[(-2147483649,)]
[(-1,)]
[(-108,)]

Similar functions

run("SELECT 7 - 2, subtract(7, 2), -7, subtract(7), 7 + 2, abs(2 - 7)")

[(5, 5, -7, -7, 9, 5)]

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.
• + (addition), * (multiplication) and abs.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.