list_where

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns the elements of a list whose matching entry in a mask list is true. array_where is the same function.

Syntax

list_where(list, mask)
array_where(list, mask)

Returns: a list of the same type as list.

Aliases: array_where.

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_where. 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 test runs, each with a list of test names (tests), a list of durations in seconds (seconds) and a mask of which tests passed (passed). Run 2 has a NULL test name and a mask longer than its list, run 3 has empty lists, and run 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_where"'

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}.test_runs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, tests VARCHAR[], seconds BIGINT[], passed BOOLEAN[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['login', 'search', 'upload'], [3, 12, 40], [true, false, true]), "
    "(2, ['login', NULL], [5, 7], [false, true, true]), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_where walks list and mask side by side and keeps each element whose mask entry is true, in their original order: list_where([1, 2, 3], [true, false, true]) is [1, 3].

The mask doesn't have to be as long as the list:

• A shorter mask keeps nothing past its end: list_where([1, 2, 3], [true]) is [1].
• A longer mask adds a NULL element for each true entry past the end of the list: list_where([1, 2], [true, true, true]) is [1, 2, NULL].

The mask is often built from the list itself with list_transform: list_where([1, 2, 3], list_transform([1, 2, 3], x -> x > 1)) is [2, 3]. A mask stored in a column lets you keep the elements that match another list, such as the names of the tests that passed.

NULLs and edge cases

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

run("SELECT list_where(NULL, [true]), list_where([1, 2], NULL), list_where([1, 2, 3], []), "
    "list_where([1, 2, 3], [false, false, false]), list_where([1, 2, 3], [true]), "
    "list_where([1, 2], [true, true, true]), list_where([], [true])")
run("SELECT list_where([1, 2], [NULL, true])")
run("SELECT list_where([1, 2], [1, 0])")
run("SELECT list_where([1, 2], true)")
run("SELECT list_where(123, [true, false, true])")

[(None, None, [], [], [1], [1, 2, None], [None])]
OSError: NULLs are not allowed as list elements in the second input parameter.
InternalError: Binder Error: No function matches the given name and argument types 'list_where(INTEGER[], INTEGER[])'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'list_where(INTEGER[], BOOLEAN)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'list_where(INTEGER_LITERAL, BOOLEAN[])'. You might need to add explicit type casts.

Examples

The tests that passed in each run, and their durations, using the passed mask. Run 2's mask is one longer than its lists, so the result ends with a NULL; run 4's NULL lists give NULL:

run(f"SELECT id, list_where(tests, passed), array_where(seconds, passed) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['login', 'upload'], [3, 40]), (2, [None, None], [7, None]), (3, [], []), (4, None, None)]

The names of the tests that took more than 10 seconds, with a mask built from seconds:

run(f"SELECT id, list_where(tests, list_transform(seconds, s -> s > 10)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['search', 'upload']), (2, []), (3, []), (4, None)]

Keep the runs in which 'login' passed:

run(f"SELECT id FROM {TABLE} WHERE list_contains(list_where(tests, passed), 'login') ORDER BY id LIMIT 10")

[(1,)]

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 VAST DataBase doesn't evaluate list functions, so list_where() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list columns and the Query Engine computes list_where() for each row it returns.
• In WHERE, the scan sends the lists for every row, and a FILTER step in the Query Engine computes the condition 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, which reads both list columns:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(list_where(tests, passed), 'login')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  list_contains(list_where │
│ (tests, passed), 'login') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           tests           │
│           passed          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down condition. In id > 1 AND list_contains(list_where(tests, passed), 'login'), id > 1 is evaluated in VAST DataBase, so the list condition runs only on the rows that pass it. The FILTER step in the plan holds only the list condition:

plan(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_contains(list_where(tests, passed), 'login')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  list_contains(list_where │
│ (tests, passed), 'login') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           tests           │
│           passed          │
│                           │
└───────────────────────────┘

• To filter by the elements' own values, use list_filter. On a list without NULL elements, list_filter(seconds, s -> s > 10) gives the same elements as list_where(seconds, list_transform(seconds, s -> s > 10)) with one function instead of two.
• Keep values you filter on outside lists. If queries often ask whether a given test passed, store that in its own column when you write the rows, so the filter can be pushed down.

Limitations and errors

• No NULLs in the mask. A NULL entry fails the query with NULLs are not allowed as list elements in the second input parameter. A mask built with list_transform from a list that has NULL elements has NULL entries; turn them into false with coalesce: list_transform(l, x -> coalesce(x > 1, false)).
• A BOOLEAN[] mask only. A mask of 1 and 0, of strings, or a single boolean fails with No function matches the given name and argument types 'list_where(...)'.
• Mask and list lengths aren't checked. A mask of a different length doesn't fail: a shorter one drops the elements past its end, and a longer one adds NULLs. Compare len(list) and len(mask) if they must match.

run("SELECT list_where([3, NULL, 10], list_transform([3, NULL, 10], x -> coalesce(x > 1, false)))")
run(f"SELECT id, len(tests) = len(passed) FROM {TABLE} ORDER BY id LIMIT 10")

[([3, 10],)]
[(1, True), (2, False), (3, True), (4, None)]

Similar functions

run("SELECT list_where(l, [false, true, true, false, false]), list_select(l, [2, 3]), list_filter(l, x -> x > 1), "
    "[x FOR x IN l IF x > 1] FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([1, None], [1, None], [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_select: pick elements by position.
• list_filter: keep the elements that match a condition.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.