Troubleshooting Queries

This section walks through a few high-level errors you may encounter with Rockset. This section helps you diagnose and fix problems with SQL errors.

When writing SQL queries, you may encounter these common error types:

• Resource Exhausted: High Memory Usage
• Resource Exhausted: VI is too Small
• Query Deadline Exceeded
• Query Syntax Issues
• Query Result Set Too Large
• Query Error: Collection Doesn’t Exist
• Query Error: SQL Function Doesn’t Exist
• Query Error: Not Passing all Parameters
• Query Error: Passing the Wrong Value in a SQL Function Argument
• Query Error: Invalid Arguments for SQL Transformations and Rollups

🚧

Query Results Ordering

Standard JSON itself does not guarantee the preservation of key order within objects. The results of your query may not be guaranteed to appear in a specific order. To ensure accuracy and consistency in retrieving data, it is strongly recommended that you explicitly specify the field name rather than relying on the default order in which the results are presented. We do return an array of column names in the correct order which you can alternatively refer to.

Resource Exhausted: High Memory Usage

This error occurs if a query has excessively high memory usage.

(ResourceExceeded) RESOURCE_EXHAUSTED: This query consumed 100MB of memory, which exceeds the limit of 100MB. 
You can find a guide for optimizing your queries at https://rockset.com/docs/query-composition/. You can also 
reach out to us in Live Chat, so an agent can help optimize your instance configuration or assist you with 
query tuning. This occurred while running AggregationOperator.

To address this error, re-write the SQL query to use less memory. The Rockset Query Performance page is a useful resource. If you’re struggling, you can email [email protected] to get more help.

Resource Exhausted: VI is too Small

This occurs if the number and complexity of queries sent to a Virtual Instance exceeds total memory usage limits for that VI's size.

(ResourceExceeded) RESOURCE_EXHAUSTED: The instance size you are using does not have the memory capacity to execute the current workload. 
Please upgrade to a larger instance size, or reduce the rate or complexity of the queries you are running.

There are four ways to address this error:

• Increase your VI size to handle your workload.
• Consider using an Ingest Transformation and Rollups (where applicable) when you create the Collection. Since computation happens partially at ingest, transformations and rollups might reduce your computational load and resolve the error.
• Reduce the rate at which you run queries.
• Rewrite the query to use less memory (again, the query performance page can be useful for this).

If you can’t resolve the issue, send an email to [email protected].

Query Deadline Exceeded

Queries have a two-minute deadline for completion, after which an error occurs.

(QueryTimeout) DEADLINE_EXCEEDED: Deadline Exceeded

You can fix this error using the four methods described in Resource Exhausted: VI is too Small above. Alternatively, you can extend the query timeout up to 30 minutes by executing an async query.

Query Syntax Issues

(QueryError) INVALID_ARGUMENT: Incorrect query syntax near the end of the query. 
Please inspect your query for correct syntax at 25:2

To fix query syntax issues, check the SQL reference page to see if the syntax in the suggested line of code is correct.

Query Result Set Too Large

This error occurs when a result exceeds 100,000 rows.

(ResourceExceeded) RESOURCE_EXHAUSTED: Query result contains more than 100000 rows. 
Enable pagination in your query request to bypass this limit: 
https://rockset.com/docs/query-results-pagination/

To fix result sets that are too large:

• Set a LIMIT clause to limit the number of rows in the results.
• Use our Pagination REST API.

Query Error: Collection Doesn’t Exist

This error occurs when querying a collection that does not exist.

(QueryError) INVALID_ARGUMENT: Collection "commons.mytable" in "census-getcensu08-19-21-iv5q" does not exist

To address this error:

• Check whether the collection exists or if there is a typo in the collection name.
• Make sure you’re using the correct Workspace (i.e., yourWorkspace.collectionName).

Query Error: SQL Function Doesn’t Exist

This error occurs when attempting to execute a SQL function that does not exist or contains a typo.

(QueryError) INVALID_ARGUMENT: Function `char_length` not found. at 1:12; 
HINT: Function not found.

See our SQL reference page to find supported functions.

Query Error: Not Passing all Parameters

This error occurs if a Query Lambda is called without passing all parameters.

(QueryError) INVALID_ARGUMENT: Named parameter "featured" required, but not given at 25:12

Ensure all expected values are passed for the query parameters in the query request. Check the documentation for the appropriate SDK for more details.

Query Error: Passing the Wrong Value in a SQL Function Argument

This error occurs when the wrong type of value is passed for the SQL function argument.

(QueryError) INVALID_ARGUMENT: array_flatten() requires each element to be of array type

Ensure you are passing the right data type to the SQL function. Again, our SQL reference page is likely useful for this error.

Query Error: Invalid Arguments for SQL Transformations and Rollups

This error occurs when running an ingest transformation on a field that does not exist in a collection.

(QueryError) INVALID_ARGUMENT: Field `livestream_id` not found in collection `prod.livestream_status_rollup` at 2:8

Double check that your ingest transformation SQL references fields that exist in the relevant collection.