list_intersect

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns the elements of the first list that are also in the second, each once. The order of the result isn't guaranteed. array_intersect is the same function.

Syntax

list_intersect(list1, list2)
array_intersect(list1, list2)

Returns: a list of list1's type, such as INTEGER[].

Aliases: array_intersect.

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_intersect. 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 users, each with the list of permissions they hold. User 2 holds read twice and a NULL permission, user 3 holds none, and the last row 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_intersect"'

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}.users"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, perms VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['read', 'write', 'admin']), "
    "(2, ['read', 'read', NULL]), "
    "(3, []), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

list_intersect(list1, list2) keeps the elements of list1 that are also in list2:

• Each element once. Duplicates are removed: list_intersect([1, 1, 2], [1, 1]) is [1].
• The order isn't guaranteed. list_intersect([1, 2, 3], [2, 3, 4]) can come back as [3, 2]. Wrap the call in list_sort when the order matters.
• The result has list1's type. list_intersect([1, 2], [2.0]) is [2], an INTEGER[].
• Elements compare as in list_contains: case-sensitive for strings (['a', 'B'] and ['A', 'B'] give ['B']), whole elements for nested lists.
• NULL elements are dropped. list_intersect([1, NULL], [NULL]) is [].

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_intersect(NULL, [1]), list_intersect([1, 2], NULL), list_intersect([1, NULL], [NULL]), "
    "list_intersect([1, 2], []), list_intersect([], [1]), list_intersect([1, 2], [3])")
run("SELECT list_sort(list_intersect([1, 2, 3], [2, 3, 4])), list_intersect([1, 1, 2], [1]), "
    "list_intersect([1, 2], [2.0]), typeof(list_intersect([1, 2], [2.0]))")
run("SELECT list_intersect(['a', 'B'], ['A', 'B']), list_intersect([[1], [2]], [[2]])")
run("SELECT list_intersect([1, 2], ['a'])")

[(None, [], [], [], [], [])]
[([2, 3], [1], [2], 'INTEGER[]')]
[(['B'], [[2]])]
InternalError: Binder Error: list_contains: Cannot match element of type 'INTEGER' in a list of type 'VARCHAR[]' - an explicit cast is required

Examples

The permissions each user holds out of read and admin, sorted. User 2's duplicate read appears once and its NULL is dropped; user 3 holds none of them; user 4's NULL list gives NULL:

run(f"SELECT id, list_sort(list_intersect(perms, ['read', 'admin'])) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['admin', 'read']), (2, ['read']), (3, []), (4, None)]

How many of the two permissions each user holds:

run(f"SELECT id, len(list_intersect(perms, ['read', 'admin'])) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2), (2, 1), (3, 0), (4, None)]

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 list columns whole, and list_intersect() is always computed by the Query Engine:

• In the SELECT list, the scan sends the list column and the Query Engine builds the intersection for each row it returns.
• In WHERE, a FILTER step in the Query Engine builds it for every row the scan sends. No rows are skipped in storage.

list_intersect is a macro built on other list functions, which is why some of its errors name list_contains or list_distinct (see Limitations and errors). The plan of a condition on the result shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE len(list_intersect(perms, ['read', 'admin'])) > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (len(list_filter     │
│ (list_distinct(perms))) > │
│             1)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           perms           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• To test for a shared element, use list_has_any. list_has_any(perms, ['read', 'admin']) (or perms && ['read', 'admin']) answers the question directly, as a BOOLEAN; for lists without NULL elements, len(list_intersect(...)) > 0 gives the same answer.
• To test for all of them, use list_has_all. perms @> ['read', 'admin'] asks directly what len(list_intersect(perms, ['read', 'admin'])) = 2 asks.
• Pair it with a pushed-down condition. A comparison of a plain column with a constant, such as id > 1, is evaluated in VAST DataBase, so the rows it rules out never reach the FILTER step.

run(f"SELECT id, list_has_any(perms, ['read', 'admin']), perms @> ['read', 'admin'] FROM {TABLE} "
    f"WHERE id > 1 ORDER BY id LIMIT 10")

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

Limitations and errors

• Both arguments must be lists. A scalar list1 fails with an error that names list_distinct, such as No function matches the given name and argument types 'list_distinct(INTEGER_LITERAL)'; a scalar list2 fails with one that names list_contains.
• The element types must be compatible. A list of numbers and a list of strings fail with list_contains: Cannot match element of type ... - an explicit cast is required.
• No union or difference functions. array_union, array_except and list_except fail with Scalar Function with name ... does not exist!. For a union, use list_distinct(list_concat(l1, l2)); for the elements of l1 that aren't in l2, use list_filter(l1, x -> NOT list_contains(l2, x)).

run("SELECT list_intersect(123, [1])")
run("SELECT array_except([1, 2, 3], [2])")
run("SELECT list_sort(list_distinct(list_concat([1, 2], [2, 3]))), list_filter([1, 2, 3], x -> NOT list_contains([2], x))")

InternalError: Binder Error: No function matches the given name and argument types 'list_distinct(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name array_except does not exist!
[([1, 2, 3], [1, 3])]

Similar functions

run("SELECT list_intersect(l, [3, 99]), list_has_any(l, [3, 99]), list_has_all(l, [3, 99]), "
    "list_filter(l, x -> list_contains([3, 99], x)) FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([3], True, False, [3, 3])]

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_distinct: remove duplicates from one list.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.