array_pop_back

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Returns a list without its last element.

Syntax

array_pop_back(list)

Returns: a value of the same type as list: a list, or a VARCHAR for a VARCHAR.

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_pop_back. 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 sensors, each with a list of readings (readings) and a list of status codes (codes). Sensor 2 has a NULL reading, sensor 3 has one reading and an empty list of codes, and sensor 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_array_pop_back"'

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}.sensors"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, readings DOUBLE[], codes VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [0.5, 2.5, -1.0], ['ok', 'ok', 'warn']), "
    "(2, [2.0, NULL], ['ok', NULL]), "
    "(3, [1.5], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

array_pop_back returns the list without its last element: array_pop_back([1, 2, 3]) is [1, 2]. A list of one element gives an empty list, and an empty list stays empty. On a list of lists it removes the last inner list: array_pop_back([[1, 2], [3]]) is [[1, 2]].

To remove more than one element, call it again (array_pop_back(array_pop_back([1, 2, 3])) is [1]) or take a slice with list_slice.

It also takes a VARCHAR and removes the last character: array_pop_back('abc') is 'ab'. It doesn't take a BLOB.

NULLs and edge cases

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

run("SELECT array_pop_back(NULL), array_pop_back(NULL::BIGINT[]), array_pop_back([]), array_pop_back([]::BIGINT[]), "
    "array_pop_back([1]), array_pop_back([NULL, NULL])")
run("SELECT array_pop_back([[1, 2], [3]]), array_pop_back(array_pop_back([1, 2, 3])), array_pop_back('abc')")
run("SELECT array_pop_back('\\xAA'::BLOB)")
run("SELECT array_pop_back(123)")

[(None, None, [], [], [], [None])]
[([[1, 2]], [1], 'ab')]
InternalError: Binder Error: No function matches the given name and argument types 'len(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'len(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Drop the last reading and the last code of each sensor. Sensor 3's single reading leaves an empty list, and sensor 4's NULL lists stay NULL:

run(f"SELECT id, array_pop_back(readings), array_pop_back(codes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [0.5, 2.5], ['ok', 'ok']), (2, [2.0], ['ok']), (3, [], []), (4, None, None)]

Keep the sensors that reported 'ok' before their latest code. Sensor 3 has no codes and sensor 4 has NULL, so neither is kept:

run(f"SELECT id FROM {TABLE} WHERE list_contains(array_pop_back(codes), 'ok') 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 array_pop_back() is always computed by the Query Engine:

• In the SELECT list, the scan reads the list column and the Query Engine computes array_pop_back() 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 a condition on the shortened list shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE array_pop_back(codes) = ['ok', 'ok']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (array_slice(codes, [],  │
│ (len(codes) - 1)) = ['ok',│
│           'ok'])          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down condition. In id > 1 AND array_pop_back(codes) = ['ok'], id > 1 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 id > 1 AND array_pop_back(codes) = ['ok']")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (array_slice(codes, [],  │
│(len(codes) - 1)) = ['ok'])│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           codes           │
│                           │
└───────────────────────────┘

• Keep values you filter on outside lists. If queries often test the latest reading, store it in its own column when you write the rows, so the filter can be pushed down.

Limitations and errors

• Takes a list or a VARCHAR. A number, date, boolean or BLOB fails with No function matches the given name and argument types; the error names len(...), which array_pop_back is defined with, and lists the types it takes: len(VARCHAR), len(BIT) and len(ANY[]).
• One element at a time. It takes no count. Nest the calls, or use list_slice to keep the first n elements.
• A NULL list returns NULL; an empty list returns an empty list. Test with IS NULL or len() if you need to tell them apart.

Similar functions

run("SELECT array_pop_back(l), array_pop_front(l), list_resize(l, 2), list_slice(l, 2, 3), list_last(l), l[-1] "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

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

Clean up:

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

[]
[]

See also

• array_pop_front: remove the first element.
• list_append: add an element at the end.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.