--- title: "product" slug: "dev-database-reference-sql-functions-aggregate-product" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-product" --- > ## 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. # product *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › Sums and averages.* Multiplies the non-NULL values of a column or expression together, and returns the result as a `DOUBLE`. ## Syntax ``` product(x) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | a number: any integer type, `FLOAT`, `DOUBLE` or `DECIMAL` | The values to multiply. NULLs are skipped. | **Returns:** `DOUBLE`, for every input type. ## 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_aggregate_product`. 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 four years of a fund: its growth factor that year (1.10 is 10% up), the number of share classes and a fee. The last year has no growth figure or fee yet: ``` 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_aggregate_product"' 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}.fund_years" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, growth DOUBLE, classes INTEGER, fee DECIMAL(10,2))") run(f"INSERT INTO {TABLE} VALUES " "(1, 1.10, 1, 2.00), " "(2, 0.95, 2, 0.50), " "(3, 1.20, 3, 1.50), " "(4, NULL, 4, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `product(x)` is an aggregate: it multiplies the non-NULL values of `x` over the rows of the query and returns one `DOUBLE`. Integers and `DECIMAL` values are multiplied as `DOUBLE` too, so a large product is rounded, not exact, and a product too large or too small for a `DOUBLE` doesn't fail: - **Too large:** the result is `inf` (or `-inf`). The product of the integers 1 to 171 is `inf`; 1 to 20 is `2.43290200817664e+18`. - **Too small:** the result is `0.0`. 0.5 multiplied by itself 1,100 times is `0.0`. A zero among the values makes the product 0. With negative values too, the result can be a negative zero, `-0.0`. ## NULLs and edge cases | Input | Result | | --- | --- | | NULL values | Skipped | | Only NULLs, or `product(NULL)` | NULL | | No rows (an empty table, or a `WHERE` that matches nothing) | NULL | | `product(2.5)` with no `FROM` | `2.5` | | A zero among the values | `0.0`, or `-0.0` when there are negative values too | | A product beyond the `DOUBLE` range | `inf` or `-inf` | | A product closer to 0 than a `DOUBLE` can hold | `0.0` | | `NaN` among the values, or `inf` times 0 | `nan` | | A `BOOLEAN` | Error: `No function matches the given name and argument types 'product(BOOLEAN)'` | | A string, date, time, timestamp or list | Error: `No function matches the given name and argument types`, for example `'product(VARCHAR)'` | | `product(DISTINCT x)`, `product(x ORDER BY y)` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | 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-aggregate-product#set-up-the-examples): ``` run(f"SELECT product(growth), product(classes), product(fee) FROM {TABLE}") run(f"SELECT product(growth) FROM {TABLE} WHERE id = 4") run(f"SELECT product(growth) FROM {TABLE} WHERE id > 100") run("SELECT product(NULL), product(2.5)") run("SELECT product(x) FROM (VALUES (-1.5::DOUBLE), (0.0::DOUBLE), (2.0::DOUBLE)) v(x)") run("SELECT product(x + 1) FROM range(20) t(x)") run("SELECT product(x + 1) FROM range(171) t(x)") run("SELECT product(0.5) FROM range(1100) t(x)") run("SELECT product(x) FROM (VALUES ('inf'::DOUBLE), (-0.0::DOUBLE)) v(x)") run(f"SELECT product(id > 1) FROM {TABLE}") run(f"SELECT product(DISTINCT classes) FROM {TABLE}") ``` ``` [(1.2539999999999998, 24.0, 1.5)] [(None,)] [(None,)] [(None, 2.5)] [(-0.0,)] [(2.43290200817664e+18,)] [(inf,)] [(0.0,)] [(nan,)] InternalError: Binder Error: No function matches the given name and argument types 'product(BOOLEAN)'. You might need to add explicit type casts. InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. ``` ## Examples The growth over the three years with a figure: the product of the yearly factors, as is and rounded to three decimals: ``` run(f"SELECT product(growth), round(product(growth), 3) FROM {TABLE}") ``` ``` [(1.2539999999999998, 1.254)] ``` The growth from year 2 on. The `WHERE` condition is evaluated in VAST DataBase, before the product: ``` run(f"SELECT product(growth) FROM {TABLE} WHERE id >= 2") ``` ``` [(1.14,)] ``` The product of an expression, and of a subset with [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause): ``` run(f"SELECT product(classes * 2), product(growth) FILTER (WHERE growth > 1) FROM {TABLE}") ``` ``` [(384.0, 1.32)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes. A `product` query has two steps, which [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) shows: 1. **A scan in VAST DataBase** (`VAST_SCAN`). It reads only the columns the product uses, and evaluates the `WHERE` conditions it can [push down](/develop/docs/dev-database-reference-pushdowns), so rows that don't match never leave storage. 2. **One aggregate step** (`UNGROUPED_AGGREGATE`) in the Query Engine, which multiplies the values the scan sends and returns one row. The plan of the `WHERE` example. The condition doesn't appear in the plan, because the scan evaluates it: ``` plan(f"SELECT product(growth) FROM {TABLE} WHERE id >= 2") ``` ``` ┌───────────────────────────┐ │ UNGROUPED_AGGREGATE │ │ ──────────────────── │ │ Aggregates: │ │ product(growth) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: growth │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Filter in `WHERE`, not in `FILTER (WHERE ...)`.** A `WHERE` condition that compares a column with a constant is evaluated in VAST DataBase; a `FILTER` condition is evaluated in the aggregate step, after the scan has sent every row. - **Compute several aggregates in one query.** They share one scan and one aggregate step. - **Keep functions out of the `WHERE` condition.** A function of a column isn't pushed down, so the scan sends every row. ## Limitations and errors - **Numbers only, `BOOLEAN` excluded.** Unlike [sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum), `product` doesn't take a `BOOLEAN`: `product(b)` fails with `No function matches the given name and argument types 'product(BOOLEAN)'`. A string, a date, a timestamp or a list fails the same way. - **Always a `DOUBLE`.** A product of integers or `DECIMAL` values is computed in floating point, so a large one is rounded, and an overflow gives `inf`, not an error. Check for it with [isinf](/develop/docs/dev-database-reference-sql-functions-numeric-isinf) if the inputs can be large. - **No `GROUP BY`, `DISTINCT` or `ORDER BY` inside.** A product per group fails with `The operator 'HASH_GROUP_BY' type=9 is currently not supported.`; `product(DISTINCT x)` and `product(x ORDER BY y)` fail with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` - **Window forms.** `product(x) OVER (ROWS UNBOUNDED PRECEDING)` runs, as a running product in the order the scan returns the rows. Other `OVER (...)` forms fail with `The operator 'WINDOW' type=6 is currently not supported.` See [window functions](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions). ``` run("SELECT isinf(product(x + 1)) FROM range(171) t(x)") run(f"SELECT id, product(growth) OVER (ORDER BY id) FROM {TABLE}") ``` ``` [(True,)] InternalError: The operator 'WINDOW' type=6 is currently not supported. ``` ## Similar functions | Function | Returns | | --- | --- | | `product(x)` | the non-NULL values multiplied together, as a `DOUBLE` | | [sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum) `(x)` | the values added up, exactly for integers and `DECIMAL` | | [geomean](/develop/docs/dev-database-reference-sql-functions-aggregate-geomean) `(x)` | the n-th root of the product of n positive values | | `exp(sum(ln(x)))` ([exp](/develop/docs/dev-database-reference-sql-functions-numeric-exp), [ln](/develop/docs/dev-database-reference-sql-functions-numeric-ln)) | the product of positive values, through logarithms; it can differ from `product` in the last digits | | [list_product](/develop/docs/dev-database-reference-sql-functions-lists-list-product) `(list)` | the product of the elements of one list, per row | ``` run(f"SELECT product(classes), exp(sum(ln(classes))), geomean(classes), sum(classes) FROM {TABLE}") run("SELECT list_product([1, 2, 3, 4])") ``` ``` [(24.0, 23.999999999999993, 2.213363839400643, Decimal('10'))] [(24.0,)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate): all the aggregates, and the ones that aren't available. - [sum](/develop/docs/dev-database-reference-sql-functions-aggregate-sum), [geomean](/develop/docs/dev-database-reference-sql-functions-aggregate-geomean) and [list_product](/develop/docs/dev-database-reference-sql-functions-lists-list-product). - [Pushdowns](/develop/docs/dev-database-reference-pushdowns) and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain).