list_prepend

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 start. array_prepend is the same function; array_push_front does the same with the list as its first argument.

Syntax

list_prepend(element, list)
array_prepend(element, list)
array_push_front(list, element)

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

Aliases: array_prepend (same argument order). array_push_front takes the list first.

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_prepend. 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 queues, each with a list of job numbers (jobs), a list of owners (owners) and an urgent job to put first (urgent). Queue 2 has a NULL job and no urgent job, queue 3 has empty lists, and queue 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_prepend"'

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}.queues"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, jobs BIGINT[], owners VARCHAR[], urgent BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['ana', 'bo'], 7), "
    "(2, [3, NULL, 3], ['bo'], NULL), "
    "(3, [], [], 0), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_prepend puts element before the first element of list: list_prepend(0, [1, 2]) is [0, 1, 2]. The element comes first in the call, as it does in the result. array_push_front(list, element) gives the same list with the arguments the other way round.

It's defined as list_concat([element], list), 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_prepend(2.5, [1, 2]) is a DECIMAL(11,1)[] list, [2.5, 1.0, 2.0]. On a list of lists, the element is a list: list_prepend([0], [[1], [2]]) is [[0], [1], [2]].

NULLs and edge cases

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

run("SELECT list_prepend(1, NULL), list_prepend(1, NULL::BIGINT[]), list_prepend(NULL, [1, 2]), "
    "list_prepend(NULL, NULL), list_prepend(1, []), array_push_front([1], NULL)")
run("SELECT list_prepend(2.5, [1, 2]), typeof(list_prepend(2.5, [1, 2])), list_prepend([0], [[1], [2]])")
run("SELECT list_prepend('x', [1, 2])")
run("SELECT list_prepend([1, 2], 9)")
run("SELECT list_prepend(9, 123)")
run("SELECT list_prepend(9, [1], 2)")

[([1], [1], [None, 1, 2], [None], [1], [None, 1])]
[([Decimal('2.5'), Decimal('1.0'), Decimal('2.0')], 'DECIMAL(11,1)[]', [[0], [1], [2]])]
InternalError: Binder Error: Cannot concatenate lists of types VARCHAR[] and INTEGER[] - an explicit cast is required
InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER[][], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: Macro function list_prepend(e, l) requires 2 positional arguments, but 3 positional arguments were provided.

Examples

Put job 0 first in every queue, and the owner 'ops'. Queue 4 has NULL lists, so it gets a list of one element:

run(f"SELECT id, list_prepend(0, jobs), list_prepend('ops', owners) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [0, 1, 2, 3], ['ops', 'ana', 'bo']), (2, [0, 3, None, 3], ['ops', 'bo']), (3, [0], ['ops']), (4, [0], ['ops'])]

Put each queue's own urgent job first. Queue 2 has none, so a NULL element is added. array_prepend takes the same arguments; array_push_front takes the list first:

run(f"SELECT id, list_prepend(urgent, jobs), array_prepend(urgent, jobs), array_push_front(jobs, urgent) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Give list_reduce a starting value by putting it first: the sum of [1, 2] starting from 100 is 103:

run("SELECT list_reduce(list_prepend(100, [1, 2]), (a, x) -> a + x)")

[(103,)]

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

• In the SELECT list, the scan reads the list column and the Query Engine computes list_prepend() 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 a condition on the new list shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_prepend(urgent, jobs)[1] = 7")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (array_extract(list_concat│
│ (list_value(urgent), jobs)│
│         , 1) = 7)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           urgent          │
│            jobs           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the plain column instead. list_prepend(urgent, jobs)[1] = 7 is the same as urgent = 7, and a comparison of a plain column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for urgent = 7 has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE urgent = 7")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Pair a list condition with a pushed-down condition. In id > 1 AND len(list_prepend(0, jobs)) > 2, id > 1 is evaluated in VAST DataBase, so the list condition runs only on the rows that pass it.

Limitations and errors

• Mind the argument order. list_prepend and array_prepend take the element first; array_push_front takes the list first. The wrong order fails with No function matches the given name and argument types 'list_concat(INTEGER[][], INTEGER_LITERAL)'.
• One element at a time. A third argument fails with Macro function list_prepend(e, l) requires 2 positional arguments, but 3 positional arguments were provided. To add several elements, use list_concat.
• The element must match the list's type. A string put before an INTEGER[] fails with Cannot concatenate lists of types VARCHAR[] and INTEGER[] - an explicit cast is required. Cast the element, or the list, to a common type.
• A NULL list counts as empty. list_prepend(1, NULL) is [1], not NULL.

run("SELECT array_push_front(9, [1, 2])")

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

Similar functions

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

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

Clean up:

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

[]
[]

See also

• list_append: add an element at the end.
• array_pop_front: remove the first element.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.