--- title: "* (multiplication)" slug: "dev-database-reference-sql-functions-operators-multiplication" updated: 2026-10-30T18:18:56Z published: 2026-10-30T18:18:56Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-operators-multiplication" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # * (multiplication) *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates) › Arithmetic operators.* `a * b` multiplies two numbers. `multiply(a, b)` is the same operation written as a function. On integers the result keeps the integer type, and a result that doesn't fit it stops the query; `DECIMAL` results are exact. ## Syntax ``` a * b multiply(a, b) ``` | Argument | Type | Description | | --- | --- | --- | | `a`, `b` | any integer type, `DECIMAL`, `FLOAT` or `DOUBLE`; or an `INTERVAL` and an integer | The values to multiply. | **Returns:** for two numbers of the same type, that type: `INTEGER * INTEGER` is an `INTEGER`, `DOUBLE * DOUBLE` a `DOUBLE`. A `DECIMAL` result adds the scales of the two arguments: `DECIMAL(10,2) * DECIMAL(10,2)` is a `DECIMAL(18,4)`. An `INTERVAL` times an integer is an `INTERVAL`. **Aliases:** `multiply(a, b)` for `a * b`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_operators_multiplication`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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 order lines: the quantity (`INTEGER`), the unit price (`DECIMAL(10,2)`), the weight of one item in kilograms (`DOUBLE`) and the number of packs (`UTINYINT`, an unsigned 8-bit integer). 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_multiplication"' 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, "") 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, unit_price DECIMAL(10,2), weight_kg DOUBLE, packs UTINYINT)") run(f"INSERT INTO {TABLE} VALUES " "(1, 3, 19.99, 1.25, 4), " "(2, 12, 4.25, 0.5, 255), " "(3, 0, 0.00, 0.0, 0), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `a * b` is the product of two numbers. The result depends on the types: | Arguments | Result | Example | Result of the example | | --- | --- | --- | --- | | Two integers of the same type | That integer type; an error if the product doesn't fit | `7 * 2` | `14` | | Two `DECIMAL` values | An exact `DECIMAL` whose scale is the sum of the two scales | `2.45 * 2.45` | `6.0025`, a `DECIMAL(6,4)` | | `FLOAT` or `DOUBLE` | A binary floating-point value, rounded | `0.1::DOUBLE * 3` | `0.30000000000000004` | | An `INTERVAL` and an integer | An `INTERVAL` | `INTERVAL 1 DAY * 3` | 3 days | **Integer overflow is an error.** Each integer type has a fixed range, and a product outside it stops the query: `Overflow in multiplication of INT32 (…)!`. The unsigned types (`UTINYINT`, `USMALLINT`, `UINTEGER`, `UBIGINT`) work the same way: `255 * 2` on a `UTINYINT` fails with `Overflow in multiplication of UINT8 (255 * 2)!`. Cast one side to a wider type, such as `BIGINT`, before you multiply. **`DECIMAL` can overflow too.** The result's precision grows with the arguments' precision, up to a limit; past it, the query stops with `Overflow in multiplication of DECIMAL(18) (…). You might want to add an explicit cast to a bigger decimal.` **`FLOAT` and `DOUBLE` don't raise errors.** A product too large for the type is infinity (`inf`), and infinity times 0 is `nan`. **A numeric string is converted.** In `'12' * qty`, the string is converted to the type of the other side, so it returns 36 for a `qty` of 3. A string that isn't a number fails with `Could not convert string 'abc' to INT32`. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL * 1`, `1 * NULL` | `NULL` | | `multiply(7, 2)` | `14`, the same as `7 * 2` | | `2.45 * 2.45` | `Decimal('6.0025')`, a `DECIMAL(6,4)` | | `0.1 * 3` | `Decimal('0.3')`: a literal with a decimal point is a `DECIMAL` | | `0.1::DOUBLE * 3` | `0.30000000000000004` | | `1e200::DOUBLE * 1e200::DOUBLE` | `inf` | | `'inf'::DOUBLE * 0::DOUBLE` | `nan` | | `3e38::FLOAT * 10::FLOAT` | `inf` | | An `INTEGER` product above 2147483647 | Error: `Overflow in multiplication of INT32 (…)!` | | A `UTINYINT` 255 times 2 | Error: `Overflow in multiplication of UINT8 (255 * 2)!` | | `99999999.99::DECIMAL(10,2) * 99999999.99::DECIMAL(10,2)` | Error: `Overflow in multiplication of DECIMAL(18) (9999999999 * 9999999999). You might want to add an explicit cast to a bigger decimal.` | | `'12' * qty` | the product, with `'12'` converted to the type of `qty` | | `'abc' * qty` | Error: `Could not convert string 'abc' to INT32` | | A boolean, date, `BLOB` or list | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-operators-multiplication#set-up-the-examples): ``` run("SELECT NULL * 1, 1 * NULL, multiply(7, 2), 7 * 2") run("SELECT 2.45 * 2.45, typeof(2.45 * 2.45), 0.1 * 3, 0.1::DOUBLE * 3") run("SELECT 1e200::DOUBLE * 1e200::DOUBLE, 'inf'::DOUBLE * 0::DOUBLE, 3e38::FLOAT * 10::FLOAT") run(f"SELECT qty * 2147483647 FROM {TABLE} WHERE id = 2") run(f"SELECT packs * CAST(2 AS UTINYINT) FROM {TABLE} WHERE id = 2") run("SELECT 99999999.99::DECIMAL(10,2) * 99999999.99::DECIMAL(10,2)") run(f"SELECT '12' * qty, typeof('12' * qty) FROM {TABLE} WHERE id = 1") run(f"SELECT 'abc' * qty FROM {TABLE} WHERE id = 1") run("SELECT true * 2") ``` ``` [(None, None, 14, 14)] [(Decimal('6.0025'), 'DECIMAL(6,4)', Decimal('0.3'), 0.30000000000000004)] [(inf, nan, inf)] OSError: Overflow in multiplication of INT32 (12 * 2147483647)! OSError: Overflow in multiplication of UINT8 (255 * 2)! OSError: Overflow in multiplication of DECIMAL(18) (9999999999 * 9999999999). You might want to add an explicit cast to a bigger decimal. [(36, 'INTEGER')] OSError: Could not convert string 'abc' to INT32 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 line total, the weight of each line and the same total with `multiply()`. The total is an exact `DECIMAL`: ``` run(f"SELECT id, qty * unit_price, qty * weight_kg, multiply(qty, unit_price) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, Decimal('59.97'), 3.75, Decimal('59.97')), (2, Decimal('51.00'), 6.0, Decimal('51.00')), (3, Decimal('0.00'), 0.0, Decimal('0.00')), (4, None, None, None)] ``` The types of the results: ``` run(f"SELECT typeof(qty * qty), typeof(qty * unit_price), typeof(unit_price * unit_price), typeof(qty * weight_kg), " f"typeof(id * qty) FROM {TABLE} WHERE id = 1") ``` ``` [('INTEGER', 'DECIMAL(18,2)', 'DECIMAL(18,4)', 'DOUBLE', 'BIGINT')] ``` A delivery window of one day per item, as an `INTERVAL`: ``` run(f"SELECT id, INTERVAL 1 DAY * qty FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, MonthDayNano(months=0, days=3, nanoseconds=0)), (2, MonthDayNano(months=0, days=12, nanoseconds=0)), (3, MonthDayNano(months=0, days=0, nanoseconds=0)), (4, None)] ``` Keep only the lines worth more than 50: ``` run(f"SELECT id, qty, unit_price FROM {TABLE} WHERE qty * unit_price > 50 ORDER BY id LIMIT 10") ``` ``` [(1, 3, Decimal('19.99')), (2, 12, Decimal('4.25'))] ``` ## 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 product of two columns, so `qty * unit_price` is computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns and the Query Engine computes the product 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 product 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 qty * unit_price > 50") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #2 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ((CAST(qty AS DECIMAL(18,0│ │ )) * unit_price) > 50.00) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ qty │ │ unit_price │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **An integer column times a constant is pushed down.** `qty * 2 > 10`, compared with a constant, is evaluated in VAST DataBase: its plan is only a scan, with no `FILTER` step. - **Move the constant yourself for `DOUBLE` and `DECIMAL` columns.** `weight_kg * 2 > 4` and `unit_price * 100 > 245` run in a `FILTER` step. Divide the constant instead: `weight_kg > 4 / 2` is computed once, before the scan, and pushed down. ``` plan(f"SELECT id FROM {TABLE} WHERE qty * 2 > 10") plan(f"SELECT id FROM {TABLE} WHERE weight_kg * 2 > 4") plan(f"SELECT id FROM {TABLE} WHERE weight_kg > 4 / 2") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ((weight_kg * 2.0) > 4.0) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ weight_kg │ │ id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Store a product you filter on often.** A product of two columns always runs in the Query Engine. If you often filter on the line total, 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 qty * unit_price > 50`, `id > 100` is still evaluated in VAST DataBase, so the multiplication runs only on the rows that pass it. ## Limitations and errors - **Integer overflow stops the query.** A product outside the range of its integer type fails with `Overflow in multiplication of …!`, and one such row cancels the whole query. Cast one side to a wider type first: `CAST(qty AS BIGINT) * 2147483647`. - **`DECIMAL` overflow stops the query too.** Cast to `DOUBLE` if you don't need an exact result. - **A string that isn't a number fails** with `Could not convert string '…' to INT32` (or the type of the other side). - **Booleans, dates, `BLOB` values and lists** fail with `No function matches the given name and argument types`. ``` run(f"SELECT CAST(qty AS BIGINT) * 2147483647 FROM {TABLE} WHERE id = 2") run("SELECT 99999999.99::DOUBLE * 99999999.99::DOUBLE") ``` ``` [(25769803764,)] [(9999999997999998.0,)] ``` ## Similar functions | Function | Does | Result for `3` and `16` | | --- | --- | --- | | `a * b`, `multiply(a, b)` | the product | `48` | | [<< (shift left)](/develop/docs/dev-database-reference-sql-functions-operators-shift-left) | multiplies an integer by a power of 2: `3 << 4` is `3 * 2 ** 4` | `48` | | [pow](/develop/docs/dev-database-reference-sql-functions-numeric-pow), `**` | a power, as a `DOUBLE` | `48.0` for `3 * pow(2, 4)` | | [/ (division)](/develop/docs/dev-database-reference-sql-functions-operators-division) | the quotient, as a `DOUBLE` | `0.1875` | ``` run("SELECT 3 * 16, multiply(3, 16), 3 << 4, 3 * pow(2, 4), 3 / 16") ``` ``` [(48, 48, 48, 48.0, 0.1875)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Operators and predicates](/develop/docs/dev-database-reference-sql-functions-operators-and-predicates#arithmetic): all the arithmetic operators. - [+ (addition)](/develop/docs/dev-database-reference-sql-functions-operators-addition), [- (subtraction)](/develop/docs/dev-database-reference-sql-functions-operators-subtraction) and [/ (division)](/develop/docs/dev-database-reference-sql-functions-operators-division). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.