list_sum

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Returns the sum of the elements of a list, one value per row. NULL elements are skipped. It's the same as list_aggregate(l, 'sum').

Syntax

list_sum(l)

Returns: HUGEINT for a list of integers or booleans (ADBC returns it as DECIMAL(38, 0), which Python prints as Decimal); DECIMAL(38, s) for a list of decimals; DOUBLE for a list of DOUBLE.

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_lists_list_sum. 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 five rows of lists of numbers and measurements. Row 3 has empty lists, row 4 has no values, and row 5 has measurements close to the largest DOUBLE:

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_lists_list_sum"'

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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], vals DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], [0.5, 2.5, -1.0]), "
    "(2, [3, NULL, 3], [2.0, NULL]), "
    "(3, [], []), "
    "(4, NULL, NULL), "
    "(5, [-5, 0, -5, 10], [1e308, 1e308, -1e-308])")

[]
[]
[(5,)]

What it does

list_sum(l) adds up the elements of l that aren't NULL. list_sum([1, 2, 3]) returns 6, and list_sum([1, NULL, 2]) returns 3.

• Integer sums don't overflow. The sum of a list of integers is a HUGEINT, whatever the element type (TINYINT, INTEGER, BIGINT), so list_sum([9223372036854775807, 1]) returns 9223372036854775808, one more than the largest BIGINT.
• Decimals stay exact. list_sum([0.1, 0.2]) returns Decimal('0.3'), a DECIMAL(38, 1). The same values as DOUBLE give 0.30000000000000004.
• DOUBLE sums follow floating point. A sum too large for DOUBLE returns inf, not an error; inf plus -inf returns nan, and a NaN element makes the sum nan.
• Booleans count as 1 and 0. list_sum([true, true, false]) returns 2.

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 list_sum(NULL), list_sum(NULL::BIGINT[]), list_sum([]::BIGINT[]), list_sum([NULL]::BIGINT[]), list_sum([1, NULL, 2])")
run("SELECT list_sum([9223372036854775807, 1]), list_sum([-9223372036854775808, -1]), list_sum([1.25, 2.50])")
run("SELECT list_sum([0.1::DOUBLE, 0.2::DOUBLE]), list_sum([1e308, 1e308]), "
    "list_sum(['inf'::DOUBLE, -'inf'::DOUBLE]), list_sum([true, true, false])")
run("SELECT list_sum(['a'])")
run(f"SELECT list_sum(123) FROM {TABLE} LIMIT 1")

[(None, None, None, None, Decimal('3'))]
[(Decimal('9223372036854775808'), Decimal('-9223372036854775809'), Decimal('3.75'))]
[(0.30000000000000004, inf, nan, Decimal('2'))]
InternalError: Binder Error: No matching aggregate function
InternalError: Binder Error: No function matches the given name and argument types 'list_aggr(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

The sum of each row's numbers and measurements. Row 5's measurements are too large to add up as DOUBLE:

run(f"SELECT id, list_sum(nums), list_sum(vals) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, Decimal('6'), 2.0), (2, Decimal('6'), 2.0), (3, None, None), (4, None, None), (5, Decimal('0'), inf)]

The result types, for integers, decimals and doubles:

run("SELECT typeof(list_sum([1, 2])), typeof(list_sum([1.5, 2.5])), typeof(list_sum([1.5::DOUBLE]))")

[('HUGEINT', 'DECIMAL(38,1)', 'DOUBLE')]

The rows whose numbers add up to more than 3:

run(f"SELECT id FROM {TABLE} WHERE list_sum(nums) > 3 ORDER BY id LIMIT 10")

[(1,), (2,)]

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). The scan reads a list column whole, and list_sum() always runs in the Query Engine. It adds up within each row's list, so it's a scalar function: it returns one value per row and needs no GROUP BY.

• In the SELECT list, a projection step computes the sum of each row's list after the scan. The plan has no FILTER step.
• In WHERE, the scan sends the list for every row, and a FILTER step computes the sum 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 list_sum(nums) > 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (list_aggr(nums, 'sum') > │
│             3)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_sum() runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

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

• Store the sum if you filter on it often. Compute it when you write the rows and keep it in its own column: total > 3 is pushed down, list_sum(nums) > 3 isn't.

Limitations and errors

• Numbers and booleans only. A list of text, intervals or lists fails with No matching aggregate function. A value that isn't a list fails with an error that names list_aggr, the function list_sum is built on.
• An empty list returns NULL. Use coalesce(list_sum(l), 0) where you need 0.
• Not across rows. list_sum adds up within one row's list. The query below, which totals values over the rows of a table in subqueries, fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run(f"SELECT (SELECT sum(v) FROM (SELECT unnest(nums) AS v FROM {TABLE})) AS total, "
    f"(SELECT sum(list_sum(nums)) FROM {TABLE}) AS total2")

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Similar functions

run("SELECT list_sum(l), list_aggregate(l, 'sum'), list_reduce(list_filter(l, x -> x IS NOT NULL), (a, x) -> a + x), "
    "list_product(l), list_avg(l) FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[(Decimal('17'), Decimal('17'), 17, 90.0, 4.25)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• List, struct and map functions: all the list aggregates.
• sum: the aggregate over rows.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.