list_slice

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Size and elements. Returns the part of a list from position begin to position end, both included, optionally taking every stepth element. array_slice is the same function, and l[begin:end] and l[begin:end:step] are the bracket forms of it. On a VARCHAR, it returns part of the string.

Syntax

list_slice(list, begin, end)
list_slice(list, begin, end, step)
array_slice(list, begin, end)
array_slice(list, begin, end, step)
list[begin:end]
list[begin:end:step]
list[:end]
list[begin:]

Returns: a list of the same type as list; a VARCHAR for a string.

Aliases: array_slice, list[begin:end], list[begin:end:step].

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_slice. 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 bus routes: the stops, the minutes from the start at each stop, a number of stops n and a label. The second route has a NULL stop, 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_slice"'

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}.routes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, stops VARCHAR[], minutes BIGINT[], n BIGINT, label VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['A', 'B', 'C', 'D'], [0, 10, 25, 40], 2, 'north'), "
    "(2, ['E', NULL, 'F'], [0, NULL, 9], 1, 'x'), "
    "(3, [], [], 1, ''), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_slice(l, begin, end) returns the elements at positions begin to end of l, both included, as a list. Positions count from 1, and a negative position counts from the end, -1 being the last element:

Because end is included, l[:-1] is the whole list, not the list without its last element: use l[:-2] for that.

• Positions outside the list are left out, not an error: list_slice([1, 2, 3, 4], 2, 100) is [2, 3, 4] and list_slice([1, 2, 3, 4], -10, 2) is [1, 2]. A begin of 0 is the same as 1.
• An empty range gives []: begin after end ([1, 2, 3, 4][3:1]), or a range past the end (list_slice([1, 2, 3, 4], 5, 6)).
• A negative step goes backwards from begin down to end, so begin must be the larger position: list_slice([1, 2, 3, 4], 1, 4, -1) is [].

On a string, list_slice(s, begin, end) and s[begin:end] return the characters from begin to end as a VARCHAR: list_slice('abcdef', 2, 3) is 'bc', and list_slice('abcdef', -3, -1) is 'def'. A step isn't available for strings. See also substring.

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_slice(NULL, 2, 3), list_slice([1, 2, 3], NULL, 2), list_slice([1, 2, 3], 1, 3, NULL), list_slice([], 1, 2)")
run("SELECT list_slice([1, 2, 3, 4], 0, 2), list_slice([1, 2, 3, 4], 2, 100), list_slice([1, 2, 3, 4], 3, 2), "
    "list_slice([1, 2, 3, 4], 1, 4, 10), list_slice([1, 2, 3, 4], 1.5, 3)")
run("SELECT [1, 2, 3, 4][:-1], [1, 2, 3, 4][:-2], [1, 2, 3, 4][-1:], list_slice('abcdef', 2, 3), 'abcdef'[2:3]")
run("SELECT list_slice([1, 2, 3, 4], 1, 4, 0)")
run("SELECT list_slice('abcdef', 1, 6, 2)")
run("SELECT list_slice(123, 1, 2)")

[(None, None, None, [])]
[([1, 2], [2, 3, 4], [], [1], [2, 3])]
[([1, 2, 3, 4], [1, 2, 3], [4], 'bc', 'bc')]
OSError: Slice step cannot be zero
InternalError: Not implemented Error: Slice with steps has not been implemented for string types, you can consider rewriting your query as follows:
InternalError: Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs

Examples

The first two stops of each route, the stops after the first, and the last two:

run(f"SELECT id, stops[:2], stops[2:], stops[-2:] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['A', 'B'], ['B', 'C', 'D'], ['C', 'D']), (2, ['E', None], [None, 'F'], [None, 'F']), (3, [], [], []), (4, None, None, None)]

Every second time with list_slice and a step, the stops in reverse, and the first n stops, with n from a column:

run(f"SELECT id, list_slice(minutes, 1, 4, 2), array_slice(stops, -1, 1, -1), stops[1:n] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [0, 25], ['D', 'C', 'B', 'A'], ['A', 'B']), (2, [0, 9], ['F', None, 'E'], ['E']), (3, [], [], []), (4, None, None, None)]

Keep the routes whose first two stops are A and B:

run(f"SELECT id FROM {TABLE} WHERE stops[1:2] = ['A', 'B'] 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_slice() and the slices are always computed there:

• In the SELECT list, the scan reads the list and the Query Engine slices it for each row it returns.
• In WHERE, the scan sends the whole list for every row, and a FILTER step in the Query Engine slices 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. The plan writes stops[1:2] as array_slice(stops, 1, 2):

plan(f"SELECT id FROM {TABLE} WHERE stops[1:2] = ['A', 'B']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (array_slice(stops, 1, 2) │
│       = ['A', 'B'])       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           stops           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the part you filter on. If queries often test the first stops of a route, keep them in their own columns when you write the rows: a comparison of a column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan of label = 'north' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE label = 'north'")

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

• Combine it with a pushed-down condition. In id > 100 AND stops[1:2] = ['A', 'B'], id > 100 is still evaluated in VAST DataBase, so the list is sliced only for the rows that pass it.

Limitations and errors

• No step without both bounds. l[::2] and l[::-1] fail with Parser Error: syntax error at or near "::", because :: is read as a cast. Write both bounds: l[1:len(l):2], or l[-1:1:-1] to reverse a list. list_reverse also reverses a list.
• A step of 0 fails with Slice step cannot be zero.
• No step on strings. list_slice('abcdef', 1, 6, 2) fails with Not implemented Error: Slice with steps has not been implemented for string types.
• Lists and strings only. A number, a date or a boolean fails with Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs.

run("SELECT [1, 2, 3, 4][::-1]")
run(f"SELECT id, minutes[1:len(minutes):2], minutes[-1:1:-1], list_reverse(minutes) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Parse query: 'SELECT [1, 2, 3, 4][::-1]' failed with: Parser Error: syntax error at or near "::"
[(1, [0, 25], [40, 25, 10, 0], [40, 25, 10, 0]), (2, [0, 9], [9, None, 0], [9, None, 0]), (3, [], [], []), (4, None, None, None)]

Similar functions

run("SELECT l[2:3], list_slice(l, 2, 3), list_select(l, [2, 3]), list_where(l, [false, true, true, false, false]), "
    "array_pop_front(l), array_pop_back(l), list_resize(l, 2), l[2] FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([1, None], [1, None], [1, None], [1, None], [1, None, 3, 10], [3, 1, None, 3], [3, 1], 1)]

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_extract and substring.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.