product

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Sums and averages. Multiplies the non-NULL values of a column or expression together, and returns the result as a DOUBLE.

Syntax

product(x)

Returns: DOUBLE, for every input type.

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_aggregate_product. 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 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, "<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

The same cases, as the cluster returns them. They use the table and helpers from 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:

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 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, 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, 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 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.

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

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: all the aggregates, and the ones that aren't available.
• sum, geomean and list_product.
• Pushdowns and EXPLAIN.