create_sort_key

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Sorting. Builds a BLOB from one or more values, each with a sort specifier such as 'ASC NULLS LAST', so that comparing the BLOBs byte by byte orders the rows as ORDER BY with those keys would. One value then stands for a sort order over several columns.

Syntax

create_sort_key(key1, specifier1)
create_sort_key(key1, specifier1, key2, specifier2, ...)

Returns: BLOB.

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_utility_create_sort_key. 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 six tasks with a priority and a name. Task 5 has no priority 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_utility_create_sort_key"'

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}.tasks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, priority BIGINT, name VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2, 'deploy'), "
    "(2, 1, 'backup'), "
    "(3, 2, 'audit'), "
    "(4, -1, 'archive'), "
    "(5, NULL, 'cleanup'), "
    "(6, NULL, NULL)")

[]
[]
[(6,)]

What it does

create_sort_key encodes each key so that a byte-by-byte comparison of the results follows its specifier, and joins the encoded keys, most significant first. For a single BIGINT key with 'ASC NULLS LAST', the keys of -1, 0, 1, 2 and 5 compare in that order, and the key of a NULL compares after all of them.

Use it where one value has to carry a sort order over several columns, for example to keep the sort key of the last row you returned and compare the next rows with it.

NULLs and edge cases

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

run(f"SELECT id, priority, create_sort_key(priority, 'ASC NULLS LAST'), create_sort_key(priority, 'ASC NULLS FIRST') FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT create_sort_key(1, NULL)")
run(f"SELECT create_sort_key(priority, name) FROM {TABLE} LIMIT 1")
run("SELECT create_sort_key(42, 'DESC')")
run("SELECT create_sort_key(42, 'SIDEWAYS')")
run("SELECT create_sort_key('a', 'ASC NULLS LAST', 1)")

[(1, 2, b'\x01\x80\x00\x00\x00\x00\x00\x00\x02', b'\x02\x80\x00\x00\x00\x00\x00\x00\x02'), (2, 1, b'\x01\x80\x00\x00\x00\x00\x00\x00\x01', b'\x02\x80\x00\x00\x00\x00\x00\x00\x01'), (3, 2, b'\x01\x80\x00\x00\x00\x00\x00\x00\x02', b'\x02\x80\x00\x00\x00\x00\x00\x00\x02'), (4, -1, b'\x01\x7f\xff\xff\xff\xff\xff\xff\xff', b'\x02\x7f\xff\xff\xff\xff\xff\xff\xff'), (5, None, b'\x02\x00\x00\x00\x06\x00\x00\x00\x00', b'\x01\x00\x00\x00\x06\x00\x00\x00\x00'), (6, None, b'\x02\x00\x00\x00\x06\x00\x00\x00\x00', b'\x01\x00\x00\x00\x06\x00\x00\x00\x00')]
InternalError: Binder Error: sort_specifier cannot be NULL
InternalError: Binder Error: sort_specifier must be a constant value - but got name
InternalError: Binder Error: create_sort_key modifier must end with either NULLS FIRST or NULLS LAST
InternalError: Binder Error: create_sort_key modifier must start with either ASC or DESC
InternalError: Binder Error: Arguments to create_sort_key must be [key1, sort_specifier1, key2, sort_specifier2, ...]

Examples

The tasks by priority, highest first with the tasks without a priority last, then by name. The second query sorts on the columns directly, for comparison:

run(f"SELECT id, priority, name FROM {TABLE} "
    "ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10")
run(f"SELECT id, priority, name FROM {TABLE} ORDER BY priority DESC NULLS LAST, name ASC NULLS LAST LIMIT 10")

[(3, 2, 'audit'), (1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)]
[(3, 2, 'audit'), (1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)]

The tasks that come after task 3 in that order: compare each row's key with the key of task 3, built from its values (priority 2, name 'audit'):

run(f"SELECT id, priority, name FROM {TABLE} "
    "WHERE create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') "
    "> create_sort_key(2::BIGINT, 'DESC NULLS LAST', 'audit'::VARCHAR, 'ASC NULLS LAST') "
    "ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10")

[(1, 2, 'deploy'), (2, 1, 'backup'), (4, -1, 'archive'), (5, None, 'cleanup'), (6, None, None)]

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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so create_sort_key() is always computed by the Query Engine:

• In the SELECT list or ORDER BY, the scan reads the key columns and the Query Engine builds the key for each row.
• In WHERE, the scan sends the key columns for every row, and a FILTER step in the Query Engine builds the key 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 create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') "
     "> create_sort_key(2::BIGINT, 'DESC NULLS LAST', 'audit'::VARCHAR, 'ASC NULLS LAST')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (create_sort_key(priority,│
│  'DESC NULLS LAST', name, │
│ 'ASC NULLS LAST') > '\x01 │
│  \x7F\xFF\xFF\xFF\xFF\xFF │
│  \xFF\xFD\x01bveju\x00':  │
│           :BLOB)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          priority         │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Sort on the columns when that's all you need. ORDER BY priority DESC NULLS LAST, name is the plain way to get the order; build a key only when you need the order as one value.
• Narrow the rows first with a pushed-down condition. A comparison of a raw column with a constant, such as priority >= 1, is evaluated in VAST DataBase, so the key is built only for the rows that pass it. Its plan has no FILTER step:

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

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

Limitations and errors

• Every key needs a full specifier, with both the direction and the place of NULLs: 'ASC NULLS LAST', not 'ASC'. A shorter one fails with create_sort_key modifier must end with either NULLS FIRST or NULLS LAST.
• Specifiers are constants. A specifier taken from a column fails with sort_specifier must be a constant value.
• The key is a BLOB. Compare keys with each other, not with the original values, and don't parse the bytes: build the key you compare with from values, as in the second example.
• ORDER BY needs a LIMIT, as for any ORDER BY in the VAST Query Engine. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run(f"SELECT id FROM {TABLE} ORDER BY create_sort_key(priority, 'ASC NULLS LAST')")

InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Similar functions

run(f"SELECT id FROM {TABLE} ORDER BY priority DESC NULLS LAST, name ASC NULLS LAST LIMIT 10")
run(f"SELECT id FROM {TABLE} ORDER BY create_sort_key(priority, 'DESC NULLS LAST', name, 'ASC NULLS LAST') LIMIT 10")
run("SELECT list_sort([3, 1, 2])")

[(3,), (1,), (2,), (4,), (5,), (6,)]
[(3,), (1,), (2,), (4,), (5,), (6,)]
[([1, 2, 3],)]

Clean up:

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

[]
[]

See also

• SELECT: ORDER BY and LIMIT.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.