---
title: "SELECT"
slug: "dev-database-reference-sql-select"
updated: 2026-10-30T18:19:11Z
published: 2026-10-30T18:19:11Z
canonical: "docs.vastdata.com/dev-database-reference-sql-select"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 `&lt;from-item&gt;` 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

| Clause | What works | What doesn't |
| --- | --- | --- |
| `SELECT` list | Columns, `*`, expressions and [functions](/develop/docs/dev-database-reference-sql-functions-index), aliases, struct fields (`location.lat`), list elements (`scores[1]`), `unnest(&lt;list&gt;)`, `row_number() OVER ()` | Other window functions, and `OVER (ORDER BY ...)` or `OVER (PARTITION BY ...)`. `DISTINCT`. Scalar subqueries. |
| `FROM` | One table; a table in a snapshot; a derived table `(SELECT ...)`; `range()`, `generate_series()`, `unnest()` | Joins of any kind, including comma joins. File functions such as `read_parquet()` and `read_csv()`. |
| `WITH` | A CTE, also referenced twice with `UNION ALL` | `WITH ... AS MATERIALIZED`, `WITH RECURSIVE` |
| `WHERE` | Comparisons, `BETWEEN`, `IS [NOT] NULL`, `AND`, `OR`, `LIKE`, `ILIKE`, `SIMILAR TO`, `GLOB`, functions, `IN` lists ([below](/develop/docs/dev-database-reference-sql-select#in-lists)) | `IN (SELECT ...)`, `EXISTS`, comparisons with a subquery |
| Aggregates | Aggregate functions over the whole result, without `GROUP BY`, with `FILTER (WHERE ...)` ([below](/develop/docs/dev-database-reference-sql-select#aggregates)) | `GROUP BY`; `DISTINCT` inside an aggregate |
| `ORDER BY` | With `LIMIT` (a top-N query): several keys, `ASC`, `DESC`, `NULLS LAST`, `OFFSET` | `ORDER BY` without `LIMIT` |
| `LIMIT`, `OFFSET` | Constants, with or without `ORDER BY`. Without `ORDER BY`, which rows you get isn't defined. |  |
| Set operations | `UNION ALL`, also with `ORDER BY ... LIMIT` | `UNION`, `INTERSECT`, `EXCEPT` |

- 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](/develop/docs/dev-database-reference-sql-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:

| Evaluated in VAST DataBase (pushed down) | Evaluated in the Query Engine |
| --- | --- |
| `=`, `&lt;&gt;`, `&lt;`, `&lt;=`, `&gt;`, `&gt;=` with a constant, `BETWEEN`, `IS [NOT] NULL` | A function or arithmetic on the column: `upper(city) = &apos;OSLO&apos;`, `id % 2 = 0`, `n * 2 &gt; 4`, `CAST(id AS VARCHAR) = &apos;2&apos;` |
| `AND` of such conditions; `OR` on one column (`id = 1 OR id = 3`) | `OR` across columns (`id = 1 OR temperature &gt; 20`) |
| A struct field: `location.lat &lt; 0` | `LIKE` with a wildcard (`&apos;L%&apos;`, `&apos;%im%&apos;`), `ILIKE` |
| `DATE` and `TIMESTAMP` comparisons, also with a constant expression (`taken_at &gt; TIMESTAMP &apos;2026-01-01&apos; + INTERVAL 1 DAY`) | `IN` lists of up to four values that aren't consecutive integers |
| `LIKE` with no wildcard (it becomes `=`); `IN` lists of consecutive integers |  |

Constant expressions are computed first, so `temperature &gt; 1 + 1` and `id = CAST(&apos;2&apos; 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](/develop/docs/dev-database-reference-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 `OR`s, which it evaluates itself.
- **Five or more values that aren't consecutive integers**, or five or more strings: the query fails with `The operator &apos;HASH_JOIN&apos; type=32 is currently not supported.`

For a longer list, write the `OR`s 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](/develop/docs/dev-database-reference-sql-functions-index). A query fails with `The operator &apos;UNGROUPED_AGGREGATE&apos; 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/&lt;snapshot&gt;` after the bucket name: `&quot;&lt;bucket&gt;/.snapshot/&lt;snapshot&gt;/&lt;schema&gt;&quot;.&lt;table&gt;`. Writing to it fails with `Access Denied`. Your platform team takes the snapshots; see [snapshots and replication](/develop/docs/dev-database-operations-snapshots-and-replication#snapshots).

### Topics

An Event Broker topic is a table in the `kafka_topics` schema of its bucket; see [query topics as tables](/develop/docs/dev-event-broker-guides-querying-topics).

## 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](/develop/docs/dev-database-reference-sql-select#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](/develop/docs/dev-database-reference-sql-explain) to see the plan.

## Example

The examples use the `run()` helper and the environment variables described in the [SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction). 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 `OR`s 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 `&lt;snapshot&gt;` 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.

| You wrote | Message |
| --- | --- |
| A join, `IN (SELECT ...)`, `EXISTS`, or an `IN` list of five or more values that aren't consecutive integers | `The operator &apos;HASH_JOIN&apos; type=32 is currently not supported.` |
| A scalar subquery in `WHERE` | `The operator &apos;NESTED_LOOP_JOIN&apos; type=31 is currently not supported.` |
| A scalar subquery in the `SELECT` list | `The operator &apos;CROSS_PRODUCT&apos; type=33 is currently not supported.` |
| `GROUP BY`, `SELECT DISTINCT`, `UNION`, `INTERSECT`, `EXCEPT` | `The operator &apos;HASH_GROUP_BY&apos; type=9 is currently not supported.` |
| `count(DISTINCT ...)`, or `min()`, `max()`, `any_value()` on `VARCHAR`, or an aggregate the engine doesn't have | `The operator &apos;UNGROUPED_AGGREGATE&apos; type=8 is currently not supported for this usage.` |
| `ORDER BY` without `LIMIT` | `The operator &apos;ORDER_BY&apos; type=1 is currently not supported for this usage.` |
| `OVER (ORDER BY ...)` or `OVER (PARTITION BY ...)` | `The operator &apos;WINDOW&apos; type=6 is currently not supported.` |
| `WITH ... AS MATERIALIZED` | `The operator &apos;CTE_SCAN&apos; type=26 is currently not supported.` |
| `WITH RECURSIVE` | `The operator &apos;REC_CTE&apos; type=41 is currently not supported.` |
| A `MAP` or `TIMESTAMP WITH TIME ZONE` column in the result | `Unsupported column type &apos;&lt;type&gt;&apos; of column &apos;&lt;column&gt;&apos; in &apos;VAST_SCAN &apos;.` |
| `read_parquet()`, `read_csv()` | `Permission Error: Cannot access file &quot;&lt;path&gt;&quot; - file system operations are disabled by configuration` |
| A table that doesn't exist | `Catalog Error: Table with name &lt;table&gt; does not exist!` (the error code is `NoSuchBucket`) |
| A failed `CAST` of a value, such as `CAST(city AS INTEGER)` | `OSError`: `Could not convert string &apos;&lt;value&gt;&apos; to INT32` (status 17) |
| Two statements in one request | `query: &apos;&lt;statement&gt;&apos; contains 2 statements, only 1 statement is supported.` |

## 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](/develop/docs/dev-database-integrations-trino#what-vast-database-does-for-your-query), [Spark](/develop/docs/dev-database-integrations-spark#what-vast-database-does-for-your-query) and the [engine comparison](/develop/docs/dev-database-reference-sql-engine-comparison).

See also: [EXPLAIN](/develop/docs/dev-database-reference-sql-explain), [functions](/develop/docs/dev-database-reference-sql-functions-index), [querying](/develop/docs/dev-database-guides-querying#vast-query-engine), [not supported](/develop/docs/dev-database-reference-sql-not-supported).