SELECT

Applies to VAST Cluster 5.5.

Queries one table, a table in a snapshot, or an Event Broker topic. The Query Engine reads one table per query: joins and subqueries aren't supported in VAST Cluster 5.5.

Syntax

[WITH <name> AS (<select>) [, ...]]
SELECT <expression> [[AS] <alias>] [, ...]
FROM <from-item>
[WHERE <condition>]
[ORDER BY <expression> [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...] LIMIT <n> [OFFSET <m>]]
[LIMIT <n> [OFFSET <m>]]
[UNION ALL <select> ...]

where <from-item> is one of:

"<bucket>/<schema>".<table>
"<bucket>/.snapshot/<snapshot>/<schema>".<table>
"<bucket>/kafka_topics"."<topic>"
(<select>) [AS] <alias>
range(<n>) | generate_series(<start>, <stop>) | unnest(<list>)

Parameters and rules

• A statement reads one table. To combine tables, query each one and combine the results in your application, or use Trino or Spark.
• Not supported has the error for each refused form and the workaround.

Where filters run

The scan (VAST_SCAN) evaluates a condition in VAST DataBase when the condition compares one column with a constant. Other conditions run in the Query Engine, on the rows the scan returns:

Constant expressions are computed first, so temperature > 1 + 1 and id = CAST('2' AS BIGINT) push down. The same holds for every function family: string, numeric, date and time, cast and conditional, list and JSON functions on a column all run in the Query Engine. Pushdowns has the rules for the other clients.

IN lists

How an IN list runs depends on its values:

• Consecutive integers, of any length (id IN (1, 2, 3, 4, 5)): the engine turns the list into a range, which is pushed down.
• Up to four other values (id IN (1, 3, 50, 70), or four strings): the engine rewrites the list as ORs, which it evaluates itself.
• Five or more values that aren't consecutive integers, or five or more strings: the query fails with The operator 'HASH_JOIN' type=32 is currently not supported.

For a longer list, write the ORs yourself: id = 1 OR id = 3 OR id = 50 OR id = 70 OR id = 90 works, and is pushed down.

Aggregates

An aggregate query returns one row for the whole result: there's no GROUP BY. Every documented aggregate function works, alone or together, on expressions too (sum(n * 2) + 1), and with FILTER (WHERE ...). The list of functions is in functions. A query fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. if an aggregate uses DISTINCT, or is one the engine can't run this way, such as median(), string_agg() or arg_max(). Also, min(), max() and any_value() take only fixed-size types: they fail on VARCHAR, but work on numbers, DATE and TIMESTAMP. For an approximate distinct count, use approx_count_distinct().

The rowid pseudo-column

rowid returns each row's row ID, and you can use it in SELECT and WHERE. On a plain table it's a UBIGINT, starting from 0; on a sorted table it's a large DECIMAL(38,0).

Snapshots

To read a table as it was in a snapshot, put .snapshot/<snapshot> after the bucket name: "<bucket>/.snapshot/<snapshot>/<schema>".<table>. Writing to it fails with Access Denied. Your platform team takes the snapshots; see snapshots and replication.

Topics

An Event Broker topic is a table in the kafka_topics schema of its bucket; see query topics as tables.

What happens on the cluster

The table scan runs in VAST DataBase, next to the data. It returns only the columns the query uses, and only the rows that pass the conditions it can evaluate (where filters run). The Query Engine then does the rest on the rows that come back: other conditions, expressions, aggregates (UNGROUPED_AGGREGATE in the plan) and top-N sorting (TOP_N). Only the result goes to your client. Use EXPLAIN to see the plan.

Example

The examples use the run() helper and the environment variables described in the SQL reference. This block connects, and creates the schema docs_sql_select with five rows:

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_select"'

def connect(autocommit=True):
    return 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=autocommit,
    )

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)

conn = connect()
TABLE = f"{SCHEMA}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, city VARCHAR, temperature DOUBLE, taken_at TIMESTAMP, location STRUCT(lat DOUBLE, lon DOUBLE))")
run(f"""INSERT INTO {TABLE} VALUES
    (1, 'Lisbon', 21.5, TIMESTAMP '2026-01-01 10:00:00', {{'lat': 38.7, 'lon': -9.1}}),
    (2, 'Oslo', 4.0, TIMESTAMP '2026-01-02 10:00:00', {{'lat': 59.9, 'lon': 10.7}}),
    (3, 'Lima', 18.9, TIMESTAMP '2026-01-03 10:00:00', {{'lat': -12.0, 'lon': -77.0}}),
    (4, 'Perth', NULL, TIMESTAMP '2026-01-04 10:00:00', {{'lat': -31.9, 'lon': 115.9}}),
    (5, 'Cairo', 30.2, TIMESTAMP '2026-01-05 10:00:00', {{'lat': 30.0, 'lon': 31.2}})""")

[]
[]
[(5,)]

Filter, project and sort. The first query's conditions are pushed down; the second's function runs in the Query Engine:

run(f"SELECT id, city, location.lat FROM {TABLE} "
    "WHERE temperature > 15 AND location.lat < 0 ORDER BY id LIMIT 10")
run(f"SELECT id, city FROM {TABLE} WHERE upper(city) = 'OSLO' ORDER BY id LIMIT 10")
run(f"SELECT id, temperature FROM {TABLE} ORDER BY temperature DESC NULLS LAST, id LIMIT 2 OFFSET 1")
run(f"SELECT id FROM {TABLE} ORDER BY id")

[(3, 'Lima', -12.0)]
[(2, 'Oslo')]
[(1, 21.5), (3, 18.9)]
InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.

Aggregates, with and without FILTER. GROUP BY and count(DISTINCT ...) fail:

run(f"SELECT count(*), round(avg(temperature), 2), count(*) FILTER (WHERE temperature > 20) FROM {TABLE}")
run(f"SELECT city, count(*) FROM {TABLE} GROUP BY city")
run(f"SELECT count(DISTINCT city) FROM {TABLE}")
run(f"SELECT approx_count_distinct(city) FROM {TABLE}")

[(5, 18.65, 2)]
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
[(5,)]

IN lists: consecutive integers work at any length, five other values fail, and the same values written as ORs work:

run(f"SELECT id FROM {TABLE} WHERE id IN (1, 2, 3, 4, 5, 6) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE id IN (1, 3, 5, 70, 90) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE id = 1 OR id = 3 OR id = 5 OR id = 70 OR id = 90 ORDER BY id LIMIT 10")

[(1,), (2,), (3,), (4,), (5,)]
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
[(1,), (3,), (5,)]

A CTE, a derived table and UNION ALL:

run(f"WITH warm AS (SELECT id, city FROM {TABLE} WHERE temperature > 20) "
    "SELECT * FROM warm ORDER BY id LIMIT 10")
run(f"SELECT * FROM (SELECT id, temperature * 1.8 + 32 AS fahrenheit FROM {TABLE}) f "
    "WHERE fahrenheit > 80 ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE id < 3 UNION ALL SELECT id FROM {TABLE} WHERE id > 4 ORDER BY id LIMIT 10")

[(1, 'Lisbon'), (5, 'Cairo')]
[(5, 86.36)]
[(1,), (2,), (5,)]

row_number() OVER () numbers the rows in no particular order, so this block prints only the numbers. rowid is the row ID:

with conn.cursor() as cur:
    cur.execute(f"SELECT id, row_number() OVER () AS rn FROM {TABLE}")
    print(sorted(rn for _, rn in cur.fetchall()))
run(f"SELECT typeof(rowid) FROM {TABLE} LIMIT 1")
run(f"SELECT id, row_number() OVER (ORDER BY id) FROM {TABLE}")

[1, 2, 3, 4, 5]
[('UBIGINT',)]
InternalError: The operator 'WINDOW' type=6 is currently not supported.

A join, and a subquery, fail. A table that doesn't exist gives a Catalog Error:

run(f"SELECT a.id FROM {TABLE} a JOIN {TABLE} b ON a.id = b.id")
run(f"SELECT id FROM {TABLE} WHERE temperature > (SELECT avg(temperature) FROM {TABLE})")
run(f"SELECT id FROM {SCHEMA}.no_such_table")

InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
InternalError: The operator 'NESTED_LOOP_JOIN' type=31 is currently not supported.
InternalError: Catalog Error: Table with name no_such_table does not exist!

To read a snapshot, use the same query with the snapshot's path. Replace <snapshot> with the name of a snapshot that holds the table:

run(f'SELECT count(*) FROM "{BUCKET}/.snapshot/<snapshot>/docs_sql_select".readings')

Clean up:

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

[]
[]

Errors

The class is InternalError unless noted.

Trino and Spark

Trino and Spark run joins, GROUP BY, DISTINCT and full ORDER BY on the same tables, and push down a different set of filters; see Trino, Spark and the engine comparison.

See also: EXPLAIN, functions, querying, not supported.