+ (addition)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Arithmetic operators. Adds two numbers. The result has the wider of the two types, and an integer result that doesn't fit its type stops the query. add(a, b) is the same function, and + also adds days to a date. A unary +a, or add(a), returns a unchanged.

Syntax

a + b
add(a, b)
+a
add(a)

Returns: the wider of the two types: two INTEGER values give an INTEGER, an INTEGER and a BIGINT give a BIGINT, and with a DOUBLE the result is a DOUBLE. Two DECIMAL(p, s) values give a DECIMAL(p + 1, s). A DATE plus an integer is a DATE. The unary forms return the type of a.

Aliases: add(a, b); add(a) for the unary +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_addition. 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 order lines: a quantity and an extra quantity (INTEGER), a price and a fee (DECIMAL), a weight (DOUBLE) and a ship date (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_addition"'

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}.order_lines"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, qty INTEGER, extra INTEGER, price DECIMAL(10, 2), fee DECIMAL(10, 2), weight DOUBLE, shipped DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 12, 3, 2.45, 0.10, 2.5, DATE '2026-10-16'), "
    "(2, -18, 2, -1.35, 0.20, 0.25, DATE '2026-10-17'), "
    "(3, 7, 3, 10.05, 0.05, 0.1, DATE '2026-12-31'), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

a + b adds two numbers, and add(a, b) is the same function with the same results: add(7, 2) and 7 + 2 are both 9.

The result type is the wider of the two operand types, and the sum is computed in that type:

• Integers stay integers. An INTEGER column plus an INTEGER constant is an INTEGER, so qty + 2147483647 overflows when qty is 12; cast one side to BIGINT first to get a BIGINT sum. A sum that doesn't fit its type fails with Overflow in addition of INT32 (12 + 2147483647)!, and the same holds for every integer type, unsigned ones included.
• DECIMAL sums are exact: 0.1 + 0.2 is the DECIMAL(3,1) 0.3. Two DECIMAL(10,2) values give a DECIMAL(11,2), one digit wider, so 99999999.99 + 1.00 fits. A number literal such as 0.1 is a DECIMAL.
• DOUBLE and FLOAT sums are binary floating point: 0.1::DOUBLE + 0.2::DOUBLE is 0.30000000000000004, and 0.1::FLOAT + 0.2::FLOAT is 0.30000001192092896. A sum too large for the type is inf, not an error, and 'inf'::DOUBLE + '-inf'::DOUBLE is nan. Adding a DOUBLE to a large integer turns the integer into a DOUBLE first: 9007199254740993 + 0.0::DOUBLE is 9007199254740992.0.

Dates. DATE + n and n + DATE, with n an integer, are the date n days later: DATE '2026-10-16' + 1 is 2026-10-17. Dates, times, timestamps and intervals have more + forms; see date and time arithmetic.

Lists. + on two lists joins them, like list_concat.

Precedence. *, /, // and % bind more tightly than +: 2 + 3 * 4 is 14 and 2 + 7 // 2 is 5. Use parentheses to add first: (2 + 3) * 4 is 20.

Unary plus. +a and add(a) return a unchanged, in its own type: +(-5) is -5, and +(5::TINYINT) is a TINYINT.

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 1 + NULL, NULL + 1, 0.1 + 0.2, typeof(0.1 + 0.2), 0.1::DOUBLE + 0.2::DOUBLE, 0.1::FLOAT + 0.2::FLOAT")
run("SELECT 9007199254740993 + 0.0::DOUBLE, 9007199254740993 + 0, 'inf'::DOUBLE + '-inf'::DOUBLE")
run("SELECT 2 + 3 * 4, (2 + 3) * 4, 2 + 7 // 2, DATE '2026-10-16' + 1")
run(f"SELECT qty + 2147483647 FROM {TABLE} WHERE id = 1")
run("SELECT +(-5), add(-5), add(7, 2), typeof(+(5::TINYINT))")
run(f"SELECT '12' + qty FROM {TABLE} WHERE id = 1")
run("SELECT 1 + true")
run("SELECT add(1, 2, 3)")

[(None, None, Decimal('0.3'), 'DECIMAL(3,1)', 0.30000000000000004, 0.30000001192092896)]
[(9007199254740992.0, 9007199254740993, nan)]
[(14, 20, 5, datetime.date(2026, 10, 17))]
OSError: Overflow in addition of INT32 (12 + 2147483647)!
[(-5, -5, 9, 'TINYINT')]
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 '+(INTEGER_LITERAL, BOOLEAN)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'add(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Add quantities, prices and fees, add to a weight, and move a ship date one day later. add() gives the same result as +:

run(f"SELECT id, qty + extra, add(qty, extra), price + fee, weight + 1, shipped + 1 FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 15, 15, Decimal('2.55'), 3.5, datetime.date(2026, 10, 17)), (2, -16, -16, Decimal('-1.15'), 1.25, datetime.date(2026, 10, 18)), (3, 10, 10, Decimal('10.10'), 1.1, datetime.date(2027, 1, 1)), (4, None, None, None, None, None)]

The type of each sum:

run(f"SELECT typeof(qty + extra), typeof(price + fee), typeof(qty + weight), typeof(shipped + 1) FROM {TABLE} WHERE id = 1")

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

An INTEGER sum that doesn't fit stops the query. Cast one side to BIGINT to get a BIGINT sum:

run(f"SELECT qty + 2147483647 FROM {TABLE} WHERE id = 1")
run(f"SELECT CAST(qty AS BIGINT) + 2147483647 FROM {TABLE} WHERE id = 1")

OSError: Overflow in addition of INT32 (12 + 2147483647)!
[(2147483659,)]

Keep the lines whose total quantity is more than 10:

run(f"SELECT id, qty, extra FROM {TABLE} WHERE qty + extra > 10 ORDER BY id LIMIT 10")

[(1, 12, 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:

• In the SELECT list, the scan reads the columns and the Query Engine computes the sum for each row it returns.
• In WHERE, a sum of two columns is computed by the Query Engine: the scan sends both columns for every row, and a FILTER step computes the sum and keeps the rows that match. No rows are skipped in storage.
• In WHERE, an integer column plus a constant, compared with a constant, such as qty + 1 > 10, has no FILTER step in its plan: the condition is evaluated in VAST DataBase. The function form add(qty, 1) > 10 isn't: it runs in a FILTER step.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE qty + extra > 10")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    ((qty + extra) > 10)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            qty            │
│           extra           │
│             id            │
│                           │
└───────────────────────────┘

The plans of qty + 1 > 10 and add(qty, 1) > 10: the first is only a scan, the second has a FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE qty + 1 > 10")
plan(f"SELECT id FROM {TABLE} WHERE add(qty, 1) > 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    ("add"(qty, 1) > 10)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            qty            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Write +, not add(), in WHERE. qty + 1 > 10 is evaluated in VAST DataBase, so rows that don't match never leave storage; add(qty, 1) > 10 makes the scan send every row to a FILTER step. See the plans in How it runs in VAST.
• Store a sum you filter on often. A condition on the sum of two columns, such as qty + extra > 10, always runs in a FILTER step. If you filter on a total often, compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In id > 1 AND qty + extra > 10, id > 1 is still evaluated in VAST DataBase, so the sum is computed only on the rows that pass it.

Limitations and errors

• Integer overflow stops the query. One row whose sum doesn't fit its integer type fails the whole query, for example with Overflow in addition of INT32 (12 + 2147483647)!. Cast a side to a wider type, such as BIGINT, before you add. HUGEINT, UHUGEINT and DECIMAL(38) sums overflow the same way.
• Strings aren't converted. '12' + qty and 1 + '2' fail with Could not choose a best candidate function: a string could be a number or a date. Cast it: CAST('12' AS INTEGER) + qty. To join strings, use the || operator or concat.
• No booleans. 1 + true fails with No function matches the given name and argument types. Cast the boolean to an integer first.
• Time zones. A TIMESTAMP WITH TIME ZONE plus an INTERVAL fails with No function matches the given name and argument types. Cast the value to TIMESTAMP first; see no time-zone support.
• add() takes one or two arguments. add(1, 2, 3) fails with No function matches the given name and argument types. Write 1 + 2 + 3.

run(f"SELECT CAST('12' AS INTEGER) + qty FROM {TABLE} WHERE id = 1")
run("SELECT 1 + CAST(true AS INTEGER), 1 + 2 + 3")

[(24,)]
[(2, 6)]

Similar functions

run("SELECT add(7, 2), 7 + 2, subtract(7, 2), 7 - 2, multiply(7, 2), 7 * 2")
run("SELECT [1, 2, 3] + [4, 5], list_concat([1, 2, 3], [4, 5])")

[(9, 9, 5, 5, 14, 14)]
[([1, 2, 3, 4, 5], [1, 2, 3, 4, 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 operators.
• - (subtraction) and * (multiplication).
• Date and time arithmetic.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.