list_concat

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns the elements of one list followed by the elements of another. list_cat, array_concat and array_cat are the same function, and so are l1 + l2 and add(l1, l2). l1 || l2 joins lists too, but returns NULL when either list is NULL.

Syntax

list_concat(list1, list2)
list_cat(list1, list2)
array_concat(list1, list2)
array_cat(list1, list2)
list1 + list2
add(list1, list2)
list1 || list2

Returns: a list of the two lists' common element type.

Aliases: list_cat, array_concat, array_cat, +, add. || differs on NULLs.

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_concat. 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 shopping carts: the items, the extras, the item prices and the fees. The second cart has no extras or fees (NULL), the third has empty lists 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_concat"'

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}.carts"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, items VARCHAR[], extras VARCHAR[], prices BIGINT[], fees DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['pen', 'ink'], ['bag'], [3, 4], [0.5]), "
    "(2, ['cup'], NULL, [2], NULL), "
    "(3, [], [], [], []), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_concat(l1, l2) returns the elements of l1 followed by those of l2: list_concat([1, 2], [3]) is [1, 2, 3]. Duplicates are kept, NULL elements are kept, and nothing is sorted.

• One element type. The element types are combined: list_concat([1], [2.5]) is a DECIMAL(11,1)[] list, and a BIGINT[] list joined with a DOUBLE[] list is a DOUBLE[] list. Lists that can't be combined, such as INTEGER[] and VARCHAR[], fail.
• Two lists at a time. For three or more, chain || or + ([1] || [2] || [3]) or nest the calls (list_concat(list_concat(a, b), c)).
• A NULL list counts as empty for list_concat, its aliases, + and add: list_concat(['cup'], NULL) is ['cup'].
• || returns NULL if either list is NULL: ['cup'] || NULL is NULL. Use it when a missing list should make the result missing.

NULLs and edge cases

+ and add give the same results on NULLs. The || operator returns NULL for NULL || [9], [1] || NULL and NULL::BIGINT[] || NULL::BIGINT[], and [1] || 2 (a list and an element) fails with Cannot concatenate types INTEGER[] and INTEGER - an explicit cast is required.

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

run("SELECT list_concat(NULL, [9]), NULL + [9], add(NULL, [9]), NULL || [9]")
run("SELECT list_concat([1], NULL), [1] + NULL, [1] || NULL")
run("SELECT list_concat(NULL::BIGINT[], NULL::BIGINT[]), NULL::BIGINT[] || NULL::BIGINT[], list_concat(NULL, NULL)")
run("SELECT list_concat([], []), list_concat([NULL], [NULL])")
run("SELECT list_concat([1], ['a'])")
run("SELECT list_concat(123, [9])")
run("SELECT [1] || 2")

[([9], [9], [9], None)]
[([1], [1], None)]
[([], None, '')]
[([], [None, None])]
InternalError: Binder Error: Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required
InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER_LITERAL, INTEGER[])'. You might need to add explicit type casts.
InternalError: Binder Error: Cannot concatenate types INTEGER[] and INTEGER - an explicit cast is required

Examples

The items and extras of each cart as one list, with each form. Cart 2 has no extras: list_concat and its aliases return its items, and || returns NULL:

run(f"SELECT id, list_concat(items, extras), list_cat(items, extras), array_concat(items, extras), "
    f"array_cat(items, extras) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, items + extras, add(items, extras), items || extras FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag']), (2, ['cup'], ['cup'], ['cup'], ['cup']), (3, [], [], [], []), (4, [], [], [], [])]
[(1, ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag']), (2, ['cup'], ['cup'], None), (3, [], [], []), (4, [], [], None)]

Prices and fees in one list: BIGINT[] joined with DOUBLE[] gives a DOUBLE[] list. Three lists with ||:

run(f"SELECT id, list_concat(prices, fees), items || extras || ['receipt'] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [3.0, 4.0, 0.5], ['pen', 'ink', 'bag', 'receipt']), (2, [2.0], None), (3, [], ['receipt']), (4, [], None)]

Keep the carts with more than two things in them, counting extras:

run(f"SELECT id FROM {TABLE} WHERE len(list_concat(items, extras)) > 2 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). A list column is read whole, and every condition on a list runs in the Query Engine, so list_concat() and its operators are always computed there:

• In the SELECT list, the scan reads both lists and the Query Engine joins them for each row it returns.
• In WHERE, the scan sends both lists for every row, and a FILTER step in the Query Engine joins them 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(list_concat(items, extras)) > 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (len(list_concat(items,  │
│        extras)) > 2)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           items           │
│           extras          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Combine it with a pushed-down condition. A list condition always runs after the scan. In id > 100 AND len(list_concat(items, extras)) > 2, id > 100 is still evaluated in VAST DataBase, so the lists are joined only for the rows that pass it.
• Store the counts if you filter on them often. Keep the number of items and extras in their own columns when you write the rows: a comparison of a column with a constant is pushed down to VAST DataBase.

Limitations and errors

• Two arguments only. list_concat([1], [2], [3]) fails with No function matches the given name and argument types 'list_concat(INTEGER[], INTEGER[], INTEGER[])'. Chain || or +, or nest the calls.
• Lists only. list_concat of a string, a number, a BLOB, a date or a boolean fails with No function matches the given name and argument types. To add one element, use list_append or list_prepend.
• Element types must combine. Cast one list first: list_concat(CAST([1] AS VARCHAR[]), ['a']).
• Two untyped NULLs give an empty string. list_concat(NULL, NULL) returns ''. With typed NULLs or columns, the result is [].
• No union or difference functions. array_union, array_except and list_except fail with Scalar Function with name ... does not exist!. For a union without duplicates, use list_distinct on the joined list; its order isn't the order of the inputs.

run("SELECT list_concat([1], [2], [3])")
run("SELECT list_concat(list_concat([1], [2]), [3]), list_concat(CAST([1] AS VARCHAR[]), ['a'])")
run("SELECT list_distinct(list_concat([1, 2], [2, 3]))")

InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER[], INTEGER[], INTEGER[])'. You might need to add explicit type casts.
[([1, 2, 3], ['1', 'a'])]
[([3, 1, 2],)]

Similar functions

run("SELECT list_concat([1, 2], [3]), [1, 2] || [3], [1, 2] + [3], list_append([1, 2], 3), list_prepend(0, [1, 2]), "
    "flatten([[1, 2], [3]]), list_distinct(list_concat([1, 2], [2, 3]))")

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

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_append, list_prepend and flatten.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.