list_resize

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns a list cut, or padded, to a given number of elements. array_resize is the same function.

Syntax

list_resize(list, size)
list_resize(list, size, fill)
array_resize(list, size)
array_resize(list, size, fill)

Returns: a list of the same type as list.

Aliases: array_resize.

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_resize. 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 players, each with a list of scores (scores), a list of badges (badges) and a default score (base). Player 2 has a NULL score, player 3 has empty lists, and player 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_resize"'

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}.players"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, scores BIGINT[], badges VARCHAR[], base BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['gold', 'silver', 'bronze'], 2), "
    "(2, [3, NULL], ['silver'], -1), "
    "(3, [], [], 0), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_resize returns a list of exactly size elements:

• A longer list is cut to its first size elements: list_resize([1, 2, 3], 2) is [1, 2]. fill isn't used.
• A shorter list is padded at the end with fill, or with NULLs if there's no fill: list_resize([1, 2, 3], 4) is [1, 2, 3, NULL], and list_resize([1, 2, 3], 4, 42) is [1, 2, 3, 42].
• Only new positions are filled. A NULL element already in the list stays NULL: list_resize(scores, 3, 0) on [3, NULL] gives [3, NULL, 0].

fill is converted to the list's element type: list_resize([1], 3, 2.5) is the INTEGER[] list [1, 3, 3], with 2.5 rounded to 3. size is converted to a whole number in the same way, so a size of 2.5 gives three elements. On a list of lists, fill is a list: list_resize([[1]], 2, [0]) is [[1], [0]].

NULLs and edge cases

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

run("SELECT list_resize(NULL, 2), list_resize(NULL, 2, 0), list_resize([1, 2], NULL), list_resize([1], 3, NULL), "
    "list_resize([1, 2, 3], 0), list_resize([], 3)")
run("SELECT list_resize([1, 2], 2.5), list_resize([1, 2], '3'), list_resize([1], 3, 2.5), "
    "typeof(list_resize([1], 3, 2.5)), list_resize(['a'], 3, 'x'), len(list_resize([1], 100000))")
run("SELECT list_resize([1, 2], -1)")
run("SELECT list_resize([1], 3, 'x')")
run("SELECT list_resize([], 3, 'x')")
run("SELECT list_resize(123, 2)")

[(None, None, [], [1, None, None], [], [None, None, None])]
[([1, 2, None], [1, 2, None], [1, 3, 3], 'INTEGER[]', ['a', 'x', 'x'], 100000)]
OSError: Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64
OSError: Could not convert string 'x' to INT32
OSError: Unimplemented type for cast (VARCHAR -> "NULL")
InternalError: Binder Error: No function matches the given name and argument types 'list_resize(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Keep each player's first two scores, and pad every list of scores to four with 0. Player 3's empty list is padded too; player 4's NULL list stays NULL:

run(f"SELECT id, list_resize(scores, 2), list_resize(scores, 4, 0) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2], [1, 2, 3, 0]), (2, [3, None], [3, None, 0, 0]), (3, [None, None], [0, 0, 0, 0]), (4, None, None)]

Pad to three with a value from another column, and pad the badges with 'none'. array_resize gives the same lists:

run(f"SELECT id, list_resize(scores, 3, base), array_resize(scores, 3, base), list_resize(badges, 3, 'none') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], [1, 2, 3], ['gold', 'silver', 'bronze']), (2, [3, None, -1], [3, None, -1], ['silver', 'none', 'none']), (3, [0, 0, 0], [0, 0, 0], ['none', 'none', 'none']), (4, None, None, None)]

Keep the players whose top two badges include 'silver':

run(f"SELECT id FROM {TABLE} WHERE list_contains(list_resize(badges, 2), 'silver') ORDER BY id LIMIT 10")

[(1,), (2,)]

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_resize() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list column and the Query Engine computes list_resize() 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_contains(list_resize(badges, 2), 'silver')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(list_resize │
│   (badges, 2), 'silver')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           badges          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down condition. In base >= 0 AND list_contains(list_resize(badges, 2), 'silver'), base >= 0 is evaluated in VAST DataBase, so the list condition runs only on the rows that pass it. The FILTER step in the plan holds only the list condition:

plan(f"SELECT id FROM {TABLE} WHERE base >= 0 AND list_contains(list_resize(badges, 2), 'silver')")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ list_contains(list_resize │
│   (badges, 2), 'silver')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           badges          │
│             id            │
│                           │
└───────────────────────────┘

• Pad when you write, if every reader pads. If every query pads the lists to the same length, store them padded, so queries read them as they are.

Limitations and errors

• size can't be negative. list_resize([1, 2], -1) fails with Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64. If size comes from a calculation, clamp it with greatest(n, 0).
• fill must convert to the element type. A string that isn't a number, used to pad an INTEGER[], fails with Could not convert string 'x' to INT32. A fill that does convert is changed silently: 2.5 becomes 3 in an INTEGER[].
• An empty list literal has no element type. list_resize([], 3, 'x') fails with Unimplemented type for cast (VARCHAR -> "NULL"). Give the list a type when it's a literal; a list column always has one.
• A NULL size gives an empty list, not NULL.

run("SELECT list_resize([1, 2], greatest(-1, 0)), list_resize([]::VARCHAR[], 3, 'x')")

[([], ['x', 'x', 'x'])]

Similar functions

run("SELECT list_resize(l, 2), list_slice(l, 2, 3), l[2:3], array_pop_back(l), list_append(l, 0) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

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

Clean up:

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

[]
[]

See also

• list_slice: take a range of elements.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.