list_value

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a list. Returns a list of its arguments, in order. list_pack is the same function, and the bracket literal [a, b, ...] and ARRAY[a, b, ...] build the same list.

Syntax

list_value(value, ...)
list_pack(value, ...)
[value, ...]
ARRAY[value, ...]

Returns: a list (T[]) of the arguments' common type.

Aliases: list_pack, [...], ARRAY[...].

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_value. 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 sensor readings, each with a low and a high value. The third has no high value 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_lists_list_value"'

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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, low BIGINT, high BIGINT, unit VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 10, 20, 'C'), "
    "(2, -5, 5, 'C'), "
    "(3, 7, NULL, 'F'), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_value(a, b, ...) returns the list [a, b, ...]. The four forms are interchangeable: list_value(1, 2), list_pack(1, 2), [1, 2] and ARRAY[1, 2] all return the INTEGER[] list [1, 2].

• One element type. The elements are converted to one common type. [1, 2.5] is a DECIMAL(11,1)[] list (the Python driver returns its elements as Decimal values); with a DOUBLE, list_value(1, 2.5::DOUBLE) is a DOUBLE[] list. list_value(1, true) returns [1, 1].
• Lists of lists. [[1, 2], [3]] is an INTEGER[][] list. The inner lists can have different lengths.
• NULL arguments become NULL elements. list_value(1, NULL) is [1, NULL]; the list itself isn't NULL.
• A trailing comma is allowed in the literal: [1, 2,] is [1, 2].
• Lists compare element by element. [1, 2] = [1, 2] is true and [1, 2] = [2, 1] is false. < and > compare the first elements that differ, and a list that runs out first is the smaller: [1, 2] < [1, 3], [1, 2] < [1, 2, 0] and [2] > [1, 9] are all true. [1, NULL] = [1, NULL] is true.

NULLs and edge cases

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

run("SELECT list_value(NULL), list_value(1, NULL), list_value(), [], []::BIGINT[]")
run("SELECT typeof([]), typeof([]::BIGINT[]), list_value(1, 2.5), typeof(list_value(1, 2.5))")
run("SELECT list_value(1, true), [1, 2,], [1, NULL] = [1, NULL], [1, 2] < [1, 2, 0], [2] > [1, 9]")
run("SELECT list_value(1, 'a')")
run("SELECT list_value([1], ['a'])")

[([None], [1, None], [], [], [])]
[('"NULL"[]', 'BIGINT[]', [Decimal('1.0'), Decimal('2.5')], 'DECIMAL(11,1)[]')]
[([1, 1], [1, 2], True, True, True)]
OSError: Could not convert string 'a' to INT32
InternalError: Binder Error: Cannot create a list of types INTEGER[] and VARCHAR[] - an explicit cast is required

Examples

The low and high values of each reading as one list, in each of the four forms. A NULL column becomes a NULL element:

run(f"SELECT id, list_value(low, high), [low, high], list_pack(low, high), ARRAY[low, high] "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

A list of a column and a constant:

run(f"SELECT id, [unit, 'K'] FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['C', 'K']), (2, ['C', 'K']), (3, ['F', 'K']), (4, [None, 'K'])]

Keep the readings whose low and high values are exactly 10 and 20:

run(f"SELECT id FROM {TABLE} WHERE [low, high] = [10, 20] 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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so the list is always built by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds the list for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine builds the list and keeps the rows that match.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE [low, high] = [10, 20]")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (list_value(low, high) =  │
│         [10, 20])         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            low            │
│            high           │
│             id            │
│                           │
└───────────────────────────┘

A list built this way is never NULL, so list_value(low, high) IS NOT NULL is always true: it returns every row, and its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE list_value(low, high) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            low            │
│            high           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the columns, not a list of them. low = 10 AND high = 20 is pushed down to VAST DataBase, so rows that don't match never leave storage; [low, high] = [10, 20] runs in a FILTER step. The plan of the column comparisons has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE low = 10 AND high = 20")

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

• Build lists in the SELECT list of the rows you keep. Filter on plain columns first; the list is then built only for the rows that pass.

Limitations and errors

• The elements must have a common type. list_value(1, 'a') fails with Could not convert string 'a' to INT32, and lists of different element types fail with Cannot create a list of types INTEGER[] and VARCHAR[] - an explicit cast is required. Cast the values to one type first, for example [CAST(1 AS VARCHAR), 'a'].
• An empty list has no element type. [] is of type "NULL"[]. Cast it when the type matters: []::BIGINT[].
• No list aggregate across rows. list(id) and array_agg(id) fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported. list_value builds a list from the values of one row only.

run("SELECT [CAST(1 AS VARCHAR), 'a'], typeof([]::VARCHAR[])")
run(f"SELECT list(id) FROM {TABLE}")

[(['1', 'a'], 'VARCHAR[]')]
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Similar functions

run("SELECT list_value(1, 2), [1, 2], list_pack(1, 2), array_value(1, 2), range(1, 3), generate_series(1, 2)")
run("SELECT typeof(list_value(1, 2)), typeof([1, 2]), typeof(array_value(1, 2))")

[([1, 2], [1, 2], [1, 2], [1, 2], [1, 2], [1, 2])]
[('INTEGER[]', 'INTEGER[]', 'INTEGER[2]')]

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.
• Data types in SQL: list types.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.