Applies to VAST Cluster 5.5.
The VAST Query Engine in VAST Cluster 5.5 supports a defined subset of SQL. This page lists what it refuses, the error you get, and what to do instead. Most of these work in Trino and Spark, which read the same tables; see the engine comparison.
The errors are shown as the reference examples print them: the exception class, then the server's message without its request ID.
| Feature | Error | Workaround |
|---|---|---|
Joins: JOIN of any kind, a comma join |
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported. |
Query each table and combine the results in your application, or use Trino or Spark. |
Subqueries: IN (SELECT ...), EXISTS (...) |
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported. |
Run the subquery first, and put its values in the outer query. |
Scalar subqueries: WHERE x > (SELECT ...); (SELECT ...) in the SELECT list |
InternalError: The operator 'NESTED_LOOP_JOIN' type=31 is currently not supported.; in the SELECT list, ... 'CROSS_PRODUCT' type=33 ... |
Run the subquery first, and put its value in the outer query as a constant. |
UPDATE ... FROM another table |
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported. |
Read the values from the other table, then run one UPDATE per value, or update with the Python SDK, Trino or Spark. |
Long IN lists: five or more values that aren't consecutive integers, or five or more strings |
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported. |
Write the list as ORs on the column: id = 1 OR id = 3 OR ..., which is pushed down. Lists of consecutive integers work at any length, and lists of up to four values work. See IN lists. |
GROUP BY, SELECT DISTINCT |
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported. |
Group in Trino or Spark, or read the columns with the Python SDK and group them in Arrow (table.group_by()). For one group, filter with WHERE and aggregate without GROUP BY. |
DISTINCT in an aggregate (count(DISTINCT x), sum(DISTINCT x)), min(), max() or any_value() on VARCHAR, and aggregates such as string_agg(), median(), arg_max() |
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. list() fails with the HASH_GROUP_BY error. |
For a distinct count, approx_count_distinct(x). For a string's length, aggregate an expression such as min(length(s)). Otherwise use Trino or Spark. |
ORDER BY without LIMIT |
InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage. |
Add a LIMIT, which makes it a top-N query, or sort the result in your application. |
Window functions, except row_number() OVER () |
InternalError: The operator 'WINDOW' type=6 is currently not supported. (for OVER (ORDER BY ...) and OVER (PARTITION BY ...)) |
row_number() OVER (), with nothing in the parentheses, works. For other window functions, use Trino or Spark, or compute them in your application. |
UNION, INTERSECT, EXCEPT |
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported. |
UNION ALL works. Remove duplicates in your application. |
WITH ... AS MATERIALIZED, WITH RECURSIVE |
InternalError: The operator 'CTE_SCAN' type=26 is currently not supported.; ... 'REC_CTE' type=41 ... |
A plain WITH works. |
Reservoir sampling (TABLESAMPLE RESERVOIR(...)) |
InternalError: The operator 'RESERVOIR_SAMPLE' type=16 is currently not supported. |
Use LIMIT, or sample in your application. |
CREATE TABLE ... AS SELECT |
InternalError: Not implemented Error: not supported |
CREATE TABLE, then INSERT INTO ... SELECT (CREATE TABLE). |
Views: CREATE [OR REPLACE] VIEW, and so DROP VIEW |
InternalError: internal error: This functionality is not implemented yet (specific message not set) (status 58). DROP VIEW of a view that doesn't exist fails with Catalog Error: View with name <view> does not exist! |
Keep the query in your application, or create views in Trino or Spark. |
Column constraints and defaults: NOT NULL, PRIMARY KEY, UNIQUE, CHECK, DEFAULT |
InternalError: internal error: This functionality is not implemented yet (specific message not set) (status 58) |
Check values in your application. SORT KEY is the only constraint. |
| Generated columns | InternalError: Cannot create column "<column>": Generated columns are not supported in this version (possibly more generated columns not reported) |
Compute the value in your application or in the INSERT ... SELECT. |
Changing a column: ALTER COLUMN ... SET DATA TYPE, SET DEFAULT; ADD CONSTRAINT ... PRIMARY KEY |
InternalError: internal error: This functionality is not implemented yet (specific message not set) (status 58); for a primary key, Not implemented Error: BindAlterAddIndex not supported by this catalog |
Add a new column, copy the values with UPDATE and a CAST, and drop the old column (ALTER TABLE). |
Upserts: INSERT OR REPLACE, INSERT OR IGNORE, ON CONFLICT |
InternalError: Binder Error: There are no UNIQUE/PRIMARY KEY Indexes that refer to this table, ON CONFLICT is a no-op |
DELETE the old rows and INSERT the new ones in one transaction (a connection with autocommit=False). |
SET, RESET, SET VARIABLE, PRAGMA |
InternalError: The operator 'SET' type=69 is currently not supported. (profile a query) |
Pass settings as ADBC connection options (search session settings). |
PREPARE, EXECUTE |
InternalError: Serialization Error: Unsupported type for deserialization of LogicalOperator! |
Build each statement as a string. |
Bound parameters (cur.execute(sql, params), with ? or $1) |
ProgrammingError: INVALID_STATE: Cannot configure ingestion options on a statement configured for query (adbc-driver-vastdb 0.0.32) |
Put the values in the SQL text, quoting strings yourself. For many rows, use ADBC bulk ingest (cur.adbc_ingest()). |
BEGIN, COMMIT, ROLLBACK in SQL |
InternalError: The operator 'TRANSACTION' type=58 is currently not supported. |
Open the connection with autocommit=False, and call conn.commit() or conn.rollback(); or run the SQL in a Python SDK transaction (SQL reference). |
| Several statements in one request | InternalError: query: '<statements>' contains 2 statements, only 1 statement is supported. |
Call execute() once per statement. |
EXPLAIN ANALYZE |
InternalError: The operator 'EXPLAIN_ANALYZE' type=63 is currently not supported. (profile a query) |
Use EXPLAIN for the plan, and time the query in your client (EXPLAIN). |
SHOW TABLES without a schema, SHOW ALL TABLES |
An internal error message; for SHOW ALL TABLES, The operator 'ORDER_BY' type=1 is currently not supported for this usage. |
SHOW TABLES "<bucket>/<schema>" (SHOW). |
Reading MAP columns |
InternalError: Unsupported column type 'MAP(VARCHAR, INTEGER)' of column '<column>' in 'VAST_SCAN '. |
Select the table's other columns. Read the map column with the Python SDK, Trino or Spark. |
Time-zone types: reading a TIMESTAMP WITH TIME ZONE column; creating a TIME WITH TIME ZONE column |
InternalError: Unsupported column type 'TIMESTAMP WITH TIME ZONE' of column '<column>' in 'VAST_SCAN '.; Binder Error: TIME WITH TIME ZONE type is not supported in table definitions. ... |
Store times as TIMESTAMP in UTC (data types in SQL). |
Time-zone functions: current_date, today(), timezone(), AT TIME ZONE, date_trunc() on a time-zone value, CAST(now() AS DATE), now() - INTERVAL ... |
For example InternalError: Catalog Error: Scalar Function with name "current_date" is not in the catalog, but it exists in the icu extension., and Binder Error: No function matches the given name and argument types '-(TIMESTAMP WITH TIME ZONE, INTERVAL)'. ... |
Cast first: CAST(now() AS TIMESTAMP) - INTERVAL 1 DAY works, and is pushed down. |
| Partitioned tables: any statement on one, and creating one | Refused: the message says the table is partitioned (data modeling) | Create and use partitioned tables with the Python SDK, Trino or Spark. |
| Row filters and column masks: any statement on a table where one applies to you, for any engine | query_engine is not supported for tables with row_filter=<mask> (vector search security) |
Query the table with Trino. Column allow and deny rules do work in the Query Engine. |
Functions that read files: read_parquet(), read_csv() |
InternalError: Permission Error: Cannot access file "<path>" - file system operations are disabled by configuration |
Import the Parquet files into a table, or read them with the Python SDK. |
| Writing to a snapshot | InternalError: internal error: Access Denied (status 1) |
Snapshots are read-only. Write to the live table. |
Fixed-size array columns, such as INTEGER[4] |
Binder Error: ARRAY type is not supported in table definitions. ... (vector search SQL) |
Use a VECTOR(...) column or a list (INTEGER[]). |
| Projection and partition DDL | There are no such statements (5.5.0 release notes). | Manage semi-sorted projections and partitions with the Python SDK. |
Trino and Spark
Trino and Spark run GROUP BY, views and partitioned tables on the same VAST DataBase tables, and Trino runs joins; see Trino, Spark and the engine comparison.