list_has_any

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns true if two lists have at least one element in common. array_has_any and the operator l1 && l2 are the same.

Syntax

list_has_any(list1, list2)
array_has_any(list1, list2)
list1 && list2

Returns: BOOLEAN.

Aliases: array_has_any, &&.

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_any. 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 recipes, each with a list of ingredients. Recipe 2 has a NULL ingredient, recipe 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_any"'

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}.recipes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, ingredients VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['flour', 'egg', 'milk']), "
    "(2, ['rice', NULL]), "
    "(3, []), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

list_has_any(list1, list2) is true when some element of list2 is also an element of list1:

• One shared element is enough. list_has_any([1], [2, 1]) is true; list_has_any([1, 2], [3]) is false.
• An empty list shares nothing, so list_has_any([1, 2], []) and list_has_any([], []) are false.
• Elements compare as in list_contains: by value for numbers ([2.0] matches [1, 2]), case-sensitive for strings (['B'] doesn't match ['a', 'b']), whole elements for nested lists.
• NULL elements don't match each other: list_has_any([1, NULL], [NULL]) is false. list_has_all treats the same lists differently (true).
• && is the operator form, for lists only: true && false isn't a logical AND (use AND).

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_any(NULL, [1]), list_has_any([1, 2], NULL), list_has_any([1, NULL], [NULL]), "
    "list_has_any([1, 2], []), list_has_any([], [])")
run("SELECT list_has_any([1], [2, 1]), list_has_any([1, 2], [3]), list_has_any([1, 2], [2.0]), "
    "list_has_any(['a', 'b'], ['B']), list_has_any([[1]], [[1], [2]])")
run("SELECT [1, 2, 3] && [1, 2, 3], [] && []")
run("SELECT list_has_any([1, 2], ['a'])")

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

Examples

Which recipes use egg or rice, with the function and with the operator. Recipe 3 has no ingredients, so the answer is false; recipe 4's NULL list gives NULL:

run(f"SELECT id, list_has_any(ingredients, ['egg', 'rice']), ingredients && ['egg', 'rice'] "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

The recipes to avoid for someone who can't eat egg or milk:

run(f"SELECT id FROM {TABLE} WHERE ingredients && ['egg', 'milk'] 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 list columns whole, and every condition on a list, list_has_any() 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 the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE ingredients && ['egg', 'milk']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (ingredients && ['egg',  │
│          'milk'])         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        ingredients        │
│             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.
• Test several values in one call. list_has_any(ingredients, ['egg', 'milk']) asks in one condition what list_contains(ingredients, 'egg') OR list_contains(ingredients, 'milk') asks in two.

run(f"SELECT id FROM {TABLE} WHERE id > 1 AND list_has_any(ingredients, ['egg', 'rice']) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE list_contains(ingredients, 'egg') OR list_contains(ingredients, 'milk') "
    f"ORDER BY id LIMIT 10")

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

Limitations and errors

• Both arguments must be lists. A scalar fails with No function matches the given name and argument types, for example 'list_has_any(INTEGER[], INTEGER_LITERAL)'. To test one value, use list_contains. && on two booleans fails the same way ('&&(BOOLEAN, BOOLEAN)').
• The element types must be compatible. Lists of numbers and of strings fail with cannot compare lists of different types.
• Other SQL dialects' names aren't available. arrays_overlap fails with Scalar Function with name arrays_overlap does not exist!. Use list_has_any or &&.

run("SELECT list_has_any([1, 2], 1)")
run("SELECT true && false")
run("SELECT arrays_overlap([1, 2], [2, 3])")

InternalError: Binder Error: No function matches the given name and argument types 'list_has_any(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '&&(BOOLEAN, BOOLEAN)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name arrays_overlap does not exist!

Similar functions

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

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

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.