list_mad

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Aggregate a list. Returns the median absolute deviation of the elements of a list: the median of how far each element is from the list's median. NULL elements are skipped.

Syntax

list_mad(l)

Returns: DOUBLE for a list of integers or DOUBLE values; a DECIMAL for a list of DECIMAL values; an INTERVAL for a list of dates.

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_mad. 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: whole numbers (nums) and measurements (vals). Row 2 has a NULL element, row 3 has empty lists, row 4 has no values, and row 5's measurements are 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_mad"'

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_mad(l) finds the median of the non-NULL elements of l, takes the absolute difference of each element from it, and returns the median of those differences:

• [1.0, 2.0, 3.0, 10.0] has a median of 2.5; the differences are 1.5, 0.5, 0.5 and 7.5, and their median is 1.0.
• One element far from the others moves it little: the 10.0 above counts no more than the 3.0. The standard deviation of the same list, list_stddev_pop, is 3.5355339059327378.
• A single element, or elements that are all the same, give 0.0.

The result keeps the kind of value in the list. A list of dates gives an interval: for 1, 3 and 10 January the median is 3 January, and the result is 2 days. Python receives an interval as a MonthDayNano value.

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_mad(NULL), list_mad(NULL::BIGINT[]), list_mad([]::DOUBLE[]), list_mad([NULL, NULL]::DOUBLE[])")
run("SELECT list_mad([1.0]), list_mad([5, 5, 5, 5]), list_mad([1, 2, 3, 10]), typeof(list_mad([1, 2])), list_mad([1.0, 2.0])")
run("SELECT list_mad([DATE '2026-01-01', DATE '2026-01-03', DATE '2026-01-10'])")
run("SELECT list_mad(['NaN'::DOUBLE, 1.0, 2.0]), list_mad(['inf'::DOUBLE, 1.0, 2.0]), list_mad([1e308, -1e308, 1e308])")
run(f"SELECT list_mad(123) FROM {TABLE} LIMIT 1")

[(None, None, None, None)]
[(Decimal('0.0'), 0.0, 1.0, 'DOUBLE', Decimal('0.5'))]
[(MonthDayNano(months=0, days=2, nanoseconds=0),)]
[(0.0, 1.0, 0.0)]
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 median absolute deviation of each row's numbers and measurements. Row 5's numbers [-5, 0, -5, 10] have a median of -2.5 and a deviation of 2.5. Row 5's measurements hold 1e308 twice, so the median deviation is 0.0; list_mad returns a value here where list_var_pop fails:

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

[(1, 1.0, 1.5), (2, 0.0, 0.0), (3, None, None), (4, None, None), (5, 2.5, 0.0)]

The rows whose numbers spread by more than 1:

run(f"SELECT id, nums FROM {TABLE} WHERE list_mad(nums) > 1 ORDER BY id LIMIT 10")

[(5, [-5, 0, -5, 10])]

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_mad() always runs in the Query Engine:

• In the SELECT list, a projection step computes list_mad() for each row the scan returns.
• In WHERE, the scan sends the list for every row, and a FILTER step computes list_mad() 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_mad(nums) > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (list_aggr(CAST(nums AS  │
│  DOUBLE[]), 'mad') > 1.0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         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_mad() 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 deviation if you filter on it often. Compute it when you write the rows and keep it in its own column: mad > 1 is pushed down, list_mad(nums) > 1 isn't.

Limitations and errors

• NaN and infinity don't fail, but the result doesn't describe the spread. ['NaN'::DOUBLE, 1.0, 2.0] gives 0.0 and ['inf'::DOUBLE, 1.0, 2.0] gives 1.0. Keep such values out of the lists you pass to list_mad.
• The result type follows the elements. Cast the list to DOUBLE[] if you want a DOUBLE from a list of DECIMAL values.
• Lists only. Any other type fails with No function matches the given name and argument types. The error names list_aggr, the list aggregate that list_mad runs on, and its accepted form list_aggr(ANY[], VARCHAR, [ANY...]) -> ANY.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Similar functions

run("SELECT list_mad(l), list_stddev_pop(l), list_stddev_samp(l), list_sem(l) "
    "FROM (SELECT [1.0, 2.0, 3.0, 10.0]::DOUBLE[] AS l)")

[(1.0, 3.5355339059327378, 4.08248290463863, 1.7677669529663689)]

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 functions.
• list_median: the middle value of a list.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.