list_append

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns a list with one element added at the end. array_append and array_push_back are the same function.

Syntax

list_append(list, element)
array_append(list, element)
array_push_back(list, element)

Returns: a list of the common type of the list's elements and element.

Aliases: array_append, array_push_back.

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_append. 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 orders, each with a list of item numbers (items), a list of labels (labels) and the next item to add (next_item). Order 2 has a NULL item and no next item, order 3 has empty lists, and order 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_append"'

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}.orders"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, items BIGINT[], labels VARCHAR[], next_item BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['new', 'paid'], 7), "
    "(2, [3, NULL, 3], ['paid'], NULL), "
    "(3, [], [], 0), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_append adds element after the last element of list: list_append([1, 2], 3) is [1, 2, 3]. It's defined as list_concat(list, [element]), so it behaves like list_concat with a one-element list, and errors about argument types name list_concat.

The element and the list's elements are brought to a common type: list_append([1, 2], 2.5) is a DECIMAL(11,1)[] list, [1.0, 2.0, 2.5]. On a list of lists, the element is a list: list_append([[1]], [2, 3]) is [[1], [2, 3]].

It doesn't change the table: it returns a new list for each row.

NULLs and edge cases

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

run("SELECT list_append(NULL, 9), list_append(NULL::BIGINT[], 1), list_append([1, 2], NULL), "
    "list_append(NULL, NULL), list_append([], 1)")
run("SELECT list_append([1, 2], 2.5), typeof(list_append([1, 2], 2.5)), list_append([[1]], [2, 3])")
run("SELECT list_append([1, 2], 'x')")
run("SELECT list_append([1], [2])")
run("SELECT list_append(123, 9)")
run("SELECT list_append([1], 2, 3)")

[([9], [1], [1, 2, None], [None], [1])]
[([Decimal('1.0'), Decimal('2.0'), Decimal('2.5')], 'DECIMAL(11,1)[]', [[1], [2, 3]])]
InternalError: Binder Error: Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required
InternalError: Binder Error: Cannot concatenate lists of types INTEGER[] and INTEGER[][] - 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: Macro function list_append(l, e) requires 2 positional arguments, but 3 positional arguments were provided.

Examples

Add item 9 to every order, and the label 'shipped'. Order 4 has NULL lists, so it gets a list of one element:

run(f"SELECT id, list_append(items, 9), list_append(labels, 'shipped') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3, 9], ['new', 'paid', 'shipped']), (2, [3, None, 3, 9], ['paid', 'shipped']), (3, [9], ['shipped']), (4, [9], ['shipped'])]

Add each order's own next_item. Order 2 has no next item, so a NULL element is added; order 4 has neither, so the result is [NULL]. array_append and array_push_back give the same lists:

run(f"SELECT id, list_append(items, next_item), array_append(items, next_item), array_push_back(items, next_item) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the orders that have more than three items once item 9 is added:

run(f"SELECT id FROM {TABLE} WHERE len(list_append(items, 9)) > 3 ORDER BY id LIMIT 10")

[(1,), (2,)]

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_append() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list column and the Query Engine computes list_append() for each row it returns.
• In WHERE, the scan sends the list 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:

plan(f"SELECT id FROM {TABLE} WHERE len(list_append(items, 9)) > 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (len(list_concat(items, [9│
│          ])) > 3)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           items           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down condition. In id > 1 AND len(list_append(items, 9)) > 3, 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 len(list_append(items, 9)) > 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (len(list_concat(items, [9│
│          ])) > 3)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           items           │
│                           │
└───────────────────────────┘

• Filter on the stored list, not the appended one. len(list_append(items, 9)) > 3 is the same as len(items) > 2 for a non-NULL list; the shorter condition saves building a new list for every row.
• Keep values you filter on outside lists. If you often test a list's length, store it in its own column when you write the rows, so the filter can be pushed down.

Limitations and errors

• One element at a time. A third argument fails with Macro function list_append(l, e) requires 2 positional arguments, but 3 positional arguments were provided. To add several elements, use list_concat with a list of them.
• The element must match the list's type. A string added to an INTEGER[] fails with Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required. Cast the element, or the list, to a common type.
• The first argument must be a list. A number, string, date, boolean or BLOB fails with No function matches the given name and argument types 'list_concat(...)'.
• A NULL list counts as empty. list_append(NULL, 9) is [9], not NULL. If a NULL list should stay NULL, test it first: CASE WHEN items IS NULL THEN NULL ELSE list_append(items, 9) END.

run(f"SELECT id, CASE WHEN items IS NULL THEN NULL ELSE list_append(items, 9) END FROM {TABLE} ORDER BY id LIMIT 10")

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

Similar functions

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

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

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• list_prepend: add an element at the start.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.