array_value

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a list. Returns a fixed-size array of its arguments, in order: an array whose type includes its length, such as INTEGER[3]. For a list of any length, use list_value or [...].

Syntax

array_value(value, ...)

Argument	Type	Description
`value`	any type; all of them must have one common type	The elements of the array, in order. At least one.

Returns: a fixed-size array (T[n], where n is the number of arguments) of the arguments' common type.

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_array_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 points with x and y coordinates. The third has no y 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_array_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}.points"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x BIGINT, y BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 3, 4), "
    "(2, -1, 0), "
    "(3, 5, NULL), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

array_value(a, b, ...) returns the array [a, b, ...]. Its type records the number of elements: array_value(1, 2, 3) is an INTEGER[3], where list_value(1, 2, 3) is an INTEGER[]. The Python driver returns both as a Python list.

One element type. The arguments must have a common type: array_value(1.5, 2) is a DECIMAL(11,1)[2] (the Python driver returns its elements as Decimal values). A number and a string don't combine: array_value(1, 'a') fails, as list_value(1, 'a') does, but with a different message (see Limitations and errors).
Arrays of lists. array_value([1], [2, 3]) is an INTEGER[][2]: two lists, of any length.
NULL arguments become NULL elements. array_value(1, NULL) is [1, NULL].
List functions take it. len, array_length, list_extract and [i] work on a fixed-size array, and it compares equal to a list with the same elements: array_value(1, 2) = [1, 2] is true. list_concat returns a list (INTEGER[]), and a cast to a list type such as BIGINT[] turns it into a list.

NULLs and edge cases

Input	Result
`array_value(NULL)`	`[NULL]`
`array_value(1, NULL)`	`[1, NULL]`
`array_value(42)`	`[42]`
`array_value(1.5, 2)`	`[1.5, 2.0]`, of type `DECIMAL(11,1)[2]`
`len(array_value(1, 2, 3))`	`3`
`array_value(1, 2)[2]`	`2`
`array_value(1, 2) = [1, 2]`	`true`
`array_value()`	Error: `array_value requires at least one argument`
`array_value(1, 'a')`	Error: `Cannot combine types INTEGER and VARCHAR - an explicit cast is required`

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

run("SELECT array_value(NULL), array_value(1, NULL), array_value(42), array_value(1.5, 2), typeof(array_value(1.5, 2))")
run("SELECT len(array_value(1, 2, 3)), array_value(1, 2)[2], array_value(1, 2) = [1, 2]")
run("SELECT array_value()")
run("SELECT array_value(1, 'a')")

[([None], [1, None], [42], [Decimal('1.5'), Decimal('2.0')], 'DECIMAL(11,1)[2]')]
[(3, 2, True)]
InternalError: Invalid Input Error: array_value requires at least one argument
InternalError: Not implemented Error: Cannot combine types INTEGER and VARCHAR - an explicit cast is required

Examples

Each point as a two-element array. A NULL column becomes a NULL element:

run(f"SELECT id, array_value(x, y) FROM {TABLE} ORDER BY id LIMIT 10")

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

Turn the array into a list with a cast, or by adding to it with list_concat:

run(f"SELECT id, array_value(x, y)::BIGINT[], list_concat(array_value(x, y), [0]) FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the points at (3, 4):

run(f"SELECT id FROM {TABLE} WHERE array_value(x, y) = [3, 4] 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 array is always built by the Query Engine:

In the SELECT list, the scan reads the columns and the Query Engine builds the array 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 array and keeps the rows that match.

The plan of the WHERE example shows where the condition runs:

plan(f"SELECT id FROM {TABLE} WHERE array_value(x, y) = [3, 4]")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (array_value(x, y) = [3, 4│
│             ])            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             x             │
│             y             │
│             id            │
│                           │
└───────────────────────────┘

An array built this way is never NULL, so array_value(x, y) IS NOT NULL is always true: it returns every row, and its plan has no FILTER step.

Make it faster

Compare the columns, not an array of them. x = 3 AND y = 4 is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE x = 3 AND y = 4")

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

Limitations and errors

At least one argument. array_value() fails with Invalid Input Error: array_value requires at least one argument. For an empty list, use [] or []::BIGINT[].
No mixing of numbers and strings. array_value(1, 'a') fails with Not implemented Error: Cannot combine types INTEGER and VARCHAR - an explicit cast is required. Cast the values to one type first.

run("SELECT array_value(CAST(1 AS VARCHAR), 'a'), []::BIGINT[]")

[(['1', 'a'], [])]

Similar functions

Function	Builds	Type of `(1, 2)`
`array_value`	a fixed-size array	`INTEGER[2]`
list_value, `[1, 2]`	a list	`INTEGER[]`
`array_value(...)::INTEGER[]`	a list from a fixed-size array	`INTEGER[]`

run("SELECT typeof(array_value(1, 2)), typeof(list_value(1, 2)), typeof([1, 2]), typeof(array_value(1, 2)::INTEGER[])")

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

Clean up:

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

[]
[]