list_has_all

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns true if every element of the second list is also in the first list. array_has_all and the operator l1 @> l2 are the same; l2 <@ l1 asks the same question with the lists the other way round.

Syntax

list_has_all(list, sublist)
array_has_all(list, sublist)
list @> sublist
sublist <@ list

Returns: BOOLEAN.

Aliases: array_has_all, @>; <@ with the arguments swapped.

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_has_all. 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 job candidates, each with a list of skills. Candidate 2 has a NULL skill, candidate 3 has 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_has_all"'

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}.candidates"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, skills VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['sql', 'python', 'go']), "
    "(2, ['sql', NULL]), "
    "(3, []), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

list_has_all(list, sublist) is true when each element of sublist is somewhere in list, like a set test:

• Order and repeats don't matter. list_has_all([1, 2, 3], [3, 1]) and list_has_all([1, 2], [1, 1, 1]) are true.
• An empty sublist is always contained, even in an empty list: list_has_all([], []) is true, list_has_all([], [1]) is false.
• Elements compare as in list_contains: by value for numbers ([1.0] is in [1, 2]), case-sensitive for strings, whole elements for nested lists.
• NULL elements in sublist don't have to be found: list_has_all([1, NULL], [NULL]) is true, and so is ['sql', NULL] <@ ['sql', 'python', 'go', 'rust'] (candidate 2 in the examples), although the second list has no NULL. list_has_any treats [NULL] differently (false).
• @> and <@ are operator forms. list @> sublist reads "list contains sublist"; sublist <@ list reads "sublist is contained in list".

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_has_all(NULL, [1]), list_has_all([1, 2], NULL), list_has_all([1, NULL], [NULL]), "
    "list_has_all([1, 2], []), list_has_all([], []), list_has_all([], [1])")
run("SELECT list_has_all([1, 2, 3], [3, 1]), list_has_all([1, 2], [1, 1, 1]), list_has_all([1, 2], [2, 4]), "
    "list_has_all([1, 2], [1.0]), list_has_all([[1], [2]], [[2]])")
run("SELECT [1, 2, 3] @> [3, 1], [1] <@ [1, 2], [] <@ [1]")
run("SELECT list_has_all([1, 2], ['a'])")

[(None, None, True, True, True, False)]
[(True, True, False, True, True)]
[(True, True, True)]
InternalError: Binder Error: 'list_has_all' cannot compare lists of different types: 'INTEGER' and 'VARCHAR'

Examples

Which candidates have both sql and python, with the function and with the operator. Candidate 3 has no skills, so the answer is false; candidate 4's NULL list gives NULL:

run(f"SELECT id, list_has_all(skills, ['sql', 'python']), skills @> ['sql', 'python'] FROM {TABLE} ORDER BY id LIMIT 10")

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

The other way round: the candidates whose skills are all on an approved list. An empty list of skills passes, and candidate 2's NULL skill doesn't stop it from passing:

run(f"SELECT id FROM {TABLE} WHERE skills <@ ['sql', 'python', 'go', 'rust'] ORDER BY id LIMIT 10")

[(1,), (2,), (3,)]

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 every condition on a list, list_has_all(), @> and <@ included, runs in the Query Engine:

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

The plan of a WHERE with @> shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE skills @> ['sql', 'python']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (skills @> ['sql', 'python│
│            '])            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           skills          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• 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.
• Keep values you filter on often outside the list. If most queries require one particular skill, store it in its own column when you write the rows, so that condition can be pushed down and list_has_all runs on fewer rows.

run(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_has_all(skills, ['sql']) ORDER BY id LIMIT 10")

[(2,)]

Limitations and errors

• Both arguments must be lists. A scalar first argument fails with No function matches the given name and argument types, for example 'list_has_all(INTEGER_LITERAL, INTEGER[])'; so does a scalar second argument ('list_has_all(INTEGER[], INTEGER_LITERAL)'). To test one value, use list_contains.
• The element types must be compatible. Lists of numbers and of strings fail with cannot compare lists of different types; the operator forms name the operator, such as '@>' cannot compare lists of different types.

run("SELECT list_has_all([1, 2], 1)")
run("SELECT [1] @> ['a']")

InternalError: Binder Error: No function matches the given name and argument types 'list_has_all(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: '@>' cannot compare lists of different types: 'INTEGER' and 'VARCHAR'

Similar functions

run("SELECT list_has_all(l, [3, 10]), l @> [3, 10], [3] <@ l, list_has_any(l, [3, 99]), l && [3, 99], "
    "list_contains(l, 3) FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[(True, True, True, True, True, True)]

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 overlap and containment: the &&, @> and <@ operators.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.