List comprehensions

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Lambdas. A list comprehension, [expression FOR x IN l IF condition], builds a new list from the elements of l: it keeps the elements for which the condition is true and computes the expression for each of them. It does what list_filter followed by list_transform does, written as one expression.

Syntax

[expression FOR x IN l]
[expression FOR x IN l IF condition]
[expression FOR x, i IN l IF condition]

Returns: a list of the expression's 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_lists_list_comprehension. 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 (numbers, measurements, words and lists of lists). Row 3 has empty lists and row 4 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_lists_list_comprehension"'

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[], words VARCHAR[], nested BIGINT[][])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], [0.5, 2.5, -1.0], ['a', 'b', 'c'], [[1, 2], [3]]), "
    "(2, [3, NULL, 3], [2.0, NULL], ['b', NULL, 'B'], [[], NULL, [4]]), "
    "(3, [], [], [], []), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], [1e308, 1e308, -1e-308], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [[5, NULL], [], [6, 7, 8]])")

[]
[]
[(5,)]

What it does

[expression FOR x IN l IF condition] goes through the elements of l in order. For each element, it sets x to it, tests condition, and, if it's true, adds expression to the result. Without IF, every element is kept.

• [x * 2 FOR x IN [1, 2, 3]] returns [2, 4, 6]: transform only.
• [x FOR x IN [1, 2, 3] IF x > 1] returns [2, 3]: filter only.
• [x * 10 FOR x IN [1, 2, 3] IF x <> 2] returns [10, 30]: both.
• The position is available. With FOR x, i IN l, i is the position of x, starting at 1: [x + i FOR x, i IN [10, 20, 30]] returns [11, 22, 33], and [x FOR x, i IN [10, 20, 30] IF i > 1] returns [20, 30].
• The expression can use the row's columns, as in [x + id FOR x IN nums IF x > 1].
• Comprehensions nest. [[y * 10 FOR y IN x] FOR x IN [[1, 2], [3]]] returns [[10, 20], [30]].

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT [x * 2 FOR x IN NULL], [x FOR x IN NULL::BIGINT[]], [x FOR x IN []], [x * 2 FOR x IN [3, NULL, 3]]")
run("SELECT [x FOR x IN [1, NULL, 3] IF x IS NOT NULL], [x FOR x IN [1, 2] IF x + 1]")
run("SELECT [x FOR x IN 123]")
run("SELECT [x FOR x IN [1, 2] IF y > 1]")

[(None, None, [], [6, None, 6])]
[([1, 3], [1, 2])]
InternalError: Binder Error: failed to bind function, either: Invalid LIST argument during lambda function binding!
InternalError: Binder Error: Referenced column "y" not found in FROM clause!

Examples

Double each number, and keep only the numbers above 1 with the row's id added:

run(f"SELECT id, [x * 2 FOR x IN nums], [x + id FOR x IN nums IF x > 1] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [2, 4, 6], [3, 4]), (2, [6, None, 6], [5, 5]), (3, [], []), (4, None, None), (5, [-10, 0, -10, 20], [15])]

Work on text and lists of lists: add ! to each word that isn't NULL, and take the length of each inner list:

run(f"SELECT id, [x || '!' FOR x IN words IF x IS NOT NULL], [len(x) FOR x IN nested] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['a!', 'b!', 'c!'], [2, 1]), (2, ['b!', 'B!'], [0, None, 1]), (3, [], []), (4, None, None), (5, ['é!', 'Zoë!', '🇫🇷!', '!'], [2, 0, 3])]

Use the position, nest comprehensions, and filter a generated list:

run("SELECT [x + i FOR x, i IN [10, 20, 30]], [x FOR x, i IN [10, 20, 30] IF i > 1], "
    "[[y * 10 FOR y IN x] FOR x IN [[1, 2], [3]]], [x FOR x IN range(5) IF x % 2 = 0]")

[([11, 22, 33], [20, 30], [[10, 20], [30]], [0, 2, 4])]

The rows that have at least one number above 1:

run(f"SELECT id FROM {TABLE} WHERE len([x FOR x IN nums IF x > 1]) > 0 ORDER BY id LIMIT 10")

[(1,), (2,), (5,)]

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 a comprehension always runs in the Query Engine. Its IF condition filters the elements of a list, not the rows of the table:

• In the SELECT list, a projection step builds each row's new list after the scan. The plan has no FILTER step.
• In WHERE, the scan sends the list for every row, and a FILTER step builds the list 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 len([x FOR x IN nums IF x > 1]) > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (len(list_apply      │
│ (list_filter(nums))) > 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 the comprehension 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      │
│                           │
└───────────────────────────┘

• Test for one value with list_contains. To ask whether a list holds a value, list_contains(l, 3) says it in one function.
• Store values you filter on in their own column. A condition on a list always runs in the Query Engine; a comparison of a plain column with a constant is pushed down.

Limitations and errors

• A list only. [x FOR x IN 123] fails with Invalid LIST argument during lambda function binding!.
• Names must exist. A name in the expression or condition that is neither the loop variable nor a column fails with Referenced column "..." not found in FROM clause!.
• Write a condition that returns BOOLEAN. IF x + 1 isn't rejected and keeps every element in the case above.
• 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 [x * 10 FOR x IN l IF x > 1], list_transform(list_filter(l, x -> x > 1), x -> x * 10) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")
run("SELECT [x * 2 FOR x IN l], list_transform(l, x -> x * 2), [x FOR x IN l IF x > 1], list_filter(l, x -> x > 1) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([30, 30, 100], [30, 30, 100])]
[([6, 2, None, 6, 20], [6, 2, None, 6, 20], [3, 3, 10], [3, 3, 10])]

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, and how lambdas work.
• list_transform, list_filter and list_reduce: the lambda functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.