list_reverse

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns the elements of a list in reverse order. array_reverse is the same function.

Syntax

list_reverse(list)
array_reverse(list)

Returns: a list of the same type.

Aliases: array_reverse.

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_reverse. 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 routes, each with a list of stops (stops, in travel order) and a list of distances (legs). Route 2 has a NULL stop, route 3 has empty lists, and route 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_reverse"'

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[], legs DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['depot', 'mill', 'port'], [0.5, 2.5, 4.0]), "
    "(2, ['port', NULL, 'depot'], [2.0, 1.0]), "
    "(3, [], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_reverse returns the same elements, last first: list_reverse([1, 2, 3]) is [3, 2, 1]. It doesn't sort: NULL elements and repeated values stay where they are, counted from the other end. list_reverse([1, NULL, 3]) is [3, NULL, 1].

On a list of lists, only the outer list is reversed: list_reverse([[1, 2], [3]]) is [[3], [1, 2]].

It takes lists only. To reverse a string, use reverse.

NULLs and edge cases

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

run("SELECT list_reverse(NULL), list_reverse([]), list_reverse([1, NULL, 3]), list_reverse([[1, 2], [3]]), "
    "list_reverse(array_value(1, 2))")
run("SELECT list_reverse('abc')")
run("SELECT list_reverse(123)")

[(None, [], [3, None, 1], [[3], [1, 2]], [2, 1])]
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

Each route's stops and distances for the way back. Route 2's NULL stop stays in the middle:

run(f"SELECT id, list_reverse(stops), array_reverse(legs) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['port', 'mill', 'depot'], [4.0, 2.5, 0.5]), (2, ['depot', None, 'port'], [1.0, 2.0]), (3, [], []), (4, None, None)]

The last stop of each route is the first element of the reversed list:

run(f"SELECT id, list_reverse(stops)[1] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'port'), (2, 'depot'), (3, None), (4, None)]

Find the routes whose way back is port, mill, depot:

run(f"SELECT id FROM {TABLE} WHERE list_reverse(stops) = ['port', 'mill', 'depot'] 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 a list column whole, and VAST DataBase doesn't evaluate list functions, so list_reverse() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list column and the Query Engine computes list_reverse() 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 list_reverse(stops) = ['port', 'mill', 'depot']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (array_slice(stops, [], []│
│ , -1) = ['port', 'mill',  │
│         'depot'])         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           stops           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare with a reversed constant instead. list_reverse(stops) = ['port', 'mill', 'depot'] is the same as stops = ['depot', 'mill', 'port'], which saves reversing every row's list. A condition on a list still runs in a FILTER step either way:

plan(f"SELECT id FROM {TABLE} WHERE stops = ['depot', 'mill', 'port']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (stops = ['depot', 'mill',│
│          'port'])         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           stops           │
│             id            │
│                           │
└───────────────────────────┘

• Take the last element directly. stops[-1] or list_last returns the last element without building a reversed list.
• Pair a list condition with a pushed-down condition on a plain column, such as id > 1, so the list condition runs only on the rows that pass it.

Limitations and errors

• Lists only. A VARCHAR or BLOB fails with Not implemented Error: Slice with steps has not been implemented for string types; use reverse for a string. A number, date or boolean fails with ARRAY_SLICE can only operate on LISTs and VARCHARs.
• Not a sort. To order the elements from largest to smallest, use list_reverse_sort or list_sort(l, 'DESC').
• One level. Inner lists keep their order. To reverse them too, apply it to each: list_transform(l, x -> list_reverse(x)).

run("SELECT reverse('abc'), list_transform([[1, 2], [3]], x -> list_reverse(x))")

[('cba', [[2, 1], [3]])]

Similar functions

run("SELECT list_reverse(l), list_reverse_sort(l), list_sort(l, 'DESC'), list_sort(l) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

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

Clean up:

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

[]
[]

See also

• list_reverse_sort: sort a list in descending order.
• reverse: reverse a string.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.