list_grade_up

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Sort a list. Returns the positions of a list's elements in sorted order: first the position of the smallest element, then the next, and so on. Positions start at 1. array_grade_up and grade_up are the same function.

Syntax

list_grade_up(l)
list_grade_up(l, order)
list_grade_up(l, order, nulls)
array_grade_up(l[, order[, nulls]])
grade_up(l[, order[, nulls]])

Returns: BIGINT[], as long as l.

Aliases: array_grade_up, grade_up.

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_grade_up. 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_grade_up"'

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_grade_up(l) doesn't move the elements; it tells you where they are. For [30, 10, 20] it returns [2, 3, 1]: the smallest value is at position 2, the next at position 3, the largest at position 1.

• Reorder a list with it. list_select(l, list_grade_up(l)) picks the elements in that order, so it returns the sorted list: [10, 20, 30].
• Reorder another list with it. When two lists hold matching elements (names and scores, say), list_select(names, list_grade_up(scores)) puts the names in the order of their scores. This is what list_sort can't do.
• Ties keep their original order. [5, 5, 1, 5] returns [3, 1, 2, 4].
• order and nulls work as in list_sort. NULL elements are ranked last by default: [2, NULL, 1] returns [3, 1, 2], and with 'ASC', 'NULLS FIRST' it returns [2, 3, 1]. 'DESC' ranks the largest first.
• Values compare as in list_sort. Text sorts by character code: ['b', 'a', 'B'] returns [3, 2, 1].

NULLs and edge cases

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

run("SELECT list_grade_up(NULL), list_grade_up([]), list_grade_up([2, NULL, 1]), "
    "list_grade_up([2, NULL, 1], 'ASC', 'NULLS FIRST'), list_grade_up([30, 10, 20], 'DESC')")
run("SELECT list_grade_up([5, 5, 1, 5]), list_grade_up([2, 1], NULL), typeof(list_grade_up([2, 1]))")
run("SELECT list_grade_up([2, 1], 'UP')")
run("SELECT list_grade_up('abc')")

[(None, [], [3, 1, 2], [2, 3, 1], [1, 3, 2])]
[([3, 1, 2, 4], None, 'BIGINT[]')]
InternalError: Not implemented Error: Enum value: unrecognized value "UP" for enum "OrderType"
InternalError: Binder Error: No function matches the given name and argument types 'list_grade_up(STRING_LITERAL)'. You might need to add explicit type casts.

Examples

The positions that sort each row's numbers, and the same with array_grade_up in descending order and grade_up with NULLs first:

run(f"SELECT id, nums, list_grade_up(nums), array_grade_up(nums, 'DESC'), grade_up(nums, 'ASC', 'NULLS FIRST') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], [1, 2, 3], [3, 2, 1], [1, 2, 3]), (2, [3, None, 3], [1, 3, 2], [1, 3, 2], [2, 1, 3]), (3, [], [], [], []), (4, None, None, None, None), (5, [-5, 0, -5, 10], [1, 3, 2, 4], [4, 2, 1, 3], [1, 3, 2, 4])]

Sort a list with its own grade, and reorder the words of each row by the numbers in the same row. Rows 1, 2 and 3 have as many words as numbers:

run(f"SELECT id, list_select(nums, list_grade_up(nums)), list_select(words, list_grade_up(nums)) "
    f"FROM {TABLE} WHERE id <= 3 ORDER BY id LIMIT 10")

[(1, [1, 2, 3], ['a', 'b', 'c']), (2, [3, 3, None], ['b', 'B', None]), (3, [], [])]

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

• In the SELECT list, a projection step computes the positions for each row the scan returns.
• In WHERE, the scan sends the list for every row, and a FILTER step computes the positions and keeps the rows that match. No rows are skipped in storage.

A condition on the position of the smallest element shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_grade_up(nums)[1] = 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│       (array_extract      │
│ (list_grade_up(nums), 1) =│
│             1)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         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_grade_up() 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_sort to sort a list by its own values. list_select(l, list_grade_up(l)) returns the same list as list_sort(l), in one function instead of two.
• Store matching lists in sorted order if queries always read them that way, so no query has to reorder them.

Limitations and errors

• Only the accepted words. Another order or nulls value fails with Not implemented Error: Enum value: unrecognized value ....
• Lists only. Another type fails with No function matches the given name and argument types; the error lists the accepted forms, list_grade_up(ANY[]), list_grade_up(ANY[], VARCHAR) and list_grade_up(ANY[], VARCHAR, VARCHAR).

Similar functions

run("SELECT list_grade_up(l), list_select(l, list_grade_up(l)), list_sort(l), list_reverse_sort(l), list_position(l, 10) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([2, 1, 4, 5, 3], [1, 3, 3, 10, None], [1, 3, 3, 10, None], [10, 3, 3, 1, None], 5)]

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_select: pick elements by position.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.