list_reverse_sort

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Sort a list. Returns the elements of a list sorted from largest to smallest. An optional argument chooses whether NULL elements go last (the default) or first. array_reverse_sort is the same function.

Syntax

list_reverse_sort(l)
list_reverse_sort(l, nulls)
array_reverse_sort(l)
array_reverse_sort(l, nulls)

Returns: a list of the same type as l.

Aliases: array_reverse_sort.

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_sort. 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 five rows of lists: numbers, words and measurements. Row 3 has empty lists and row 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_sort"'

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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], words VARCHAR[], vals DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['a', 'b', 'c'], [0.5, 2.5, -1.0]), "
    "(2, [3, NULL, 3], ['b', NULL, 'B'], [2.0, NULL]), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [1e308, 1e308, -1e-308])")

[]
[]
[(5,)]

What it does

list_reverse_sort(l) returns the elements of l from largest to smallest, and keeps duplicates: [-5, 0, -5, 10] sorts to [10, 0, -5, -5]. It gives the same result as list_sort(l, 'DESC').

• NULL elements go last by default: [2, NULL, 1] sorts to [2, 1, NULL]. Pass 'NULLS FIRST' to put them first.
• Values compare as in list_sort, in reverse. NaN comes before inf; text sorts by character code, so lower case comes before capitals (['b', 'A', 'a', 'B'] sorts to ['b', 'a', 'B', 'A']); true comes before false; the latest date comes first; and lists compare element by element ([[2], [1, 9], [1], []]).
• The second argument is only for NULLs. It takes no direction: 'DESC' or 'ASC' there is an error. For ascending order, use list_sort.

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_sort(NULL), list_reverse_sort([]), list_reverse_sort([NULL, NULL]), list_reverse_sort([2, NULL, 1])")
run("SELECT list_reverse_sort([2, NULL, 1], 'NULLS FIRST'), list_reverse_sort([2, NULL, 1], 'NULLS LAST'), "
    "list_reverse_sort([2, 1], NULL)")
run("SELECT list_reverse_sort(['NaN'::DOUBLE, 1.0, -'inf'::DOUBLE, 'inf'::DOUBLE, NULL]), "
    "list_reverse_sort(['b', 'A', 'a', 'B']), list_reverse_sort([[2], [1, 9], [1], []])")
run("SELECT list_reverse_sort([2, NULL, 1], 'DESC')")
run("SELECT list_reverse_sort([2, NULL, 1], 'NULLS FIRST', 'ASC')")
run("SELECT list_reverse_sort(123)")

[(None, [], [None, None], [2, 1, None])]
[([None, 2, 1], [2, 1, None], None)]
[([nan, inf, 1.0, -inf, None], ['b', 'a', 'B', 'A'], [[2], [1, 9], [1], []])]
InternalError: Not implemented Error: Enum value: unrecognized value "DESC" for enum "OrderByNullType"
InternalError: Binder Error: No function matches the given name and argument types 'list_reverse_sort(INTEGER[], STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'list_reverse_sort(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each row's numbers from largest to smallest, the same with NULLs first, and the words in reverse order with array_reverse_sort:

run(f"SELECT id, list_reverse_sort(nums), list_reverse_sort(nums, 'NULLS FIRST'), array_reverse_sort(words) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [3, 2, 1], [3, 2, 1], ['c', 'b', 'a']), (2, [3, 3, None], [None, 3, 3], ['b', 'B', None]), (3, [], [], []), (4, None, None, None), (5, [10, 0, -5, -5], [10, 0, -5, -5], ['🇫🇷', 'é', 'Zoë', ''])]

The largest two measurements of each row: sort in reverse, then take the first two elements with a slice:

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

[(1, [2.5, 0.5]), (2, [2.0, None]), (3, []), (4, None), (5, [1e+308, 1e+308])]

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 list_reverse_sort() always runs in the Query Engine:

• In the SELECT list, a projection step sorts each row's list after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step sorts it and keeps the rows that match. No rows are skipped in storage.

A condition on the largest element shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_reverse_sort(nums)[1] > 5")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│       (array_extract      │
│ (list_reverse_sort(nums), │
│           1) > 5)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_reverse_sort() runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

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

• Use list_max for the largest element. list_max(l) returns it directly.
• Store values you filter on in their own column. A condition on a list always runs in the Query Engine; a comparison of a plain column with a constant is pushed down.

Limitations and errors

• No direction argument. 'DESC' or 'ASC' as the second argument fails with Enum value: unrecognized value ... for enum "OrderByNullType", and a third argument fails with No function matches the given name and argument types.
• Lists only. Another type fails with No function matches the given name and argument types; the error lists the accepted forms, list_reverse_sort(ANY[]) and list_reverse_sort(ANY[], VARCHAR).

Similar functions

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

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

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_sort: ascending and descending order.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.