- Querying Your Data
- SQL Reference
- SQL Troubleshooting
SQL Troubleshooting
This page covers common error patterns and missteps in Rockset queries and collections, and how to fix them.
#Empty Results
#Double-Quoted String Literals
In Rockset, double quotes are reserved for collection names and field names. String literals must be wrapped in single quotes.
Sample Invalid Query:
SELECT
field
FROM
"collection"
WHERE
field = "value"
Correct Query:
SELECT
field
FROM
"collection"
WHERE
field = 'value'
#Mismatched Query and Data Types
Rockset is strongly typed, and will evaluate to false if you attempt to compare values of different
types. If you are unsure of what types exist in a collection or field, you can easily check in that
collection's console page or with a call to DESCRIBE "collection"
.
Sample Invalid Query:
-- field is comprised of string values such '10'
SELECT
field
FROM
collection
WHERE
field = 10
Correct Query:
-- will select values such as '10' as well as 10
SELECT
field
FROM
collection
where
cast(field as int) = 10
or
-- will select only '10'
SELECT
field
FROM
collection
WHERE
field = '10'
#Query Parse Errors
#Unescaped Collection and Field Names
Certain characters, such as '-', must be escaped in collection and field names. Wrap collection and field names in double quotes.
Sample Invalid Query:
SELECT
*
FROM
"collection-name"
Correct Query:
SELECT
*
FROM
"collection-name"
#Accessing Nested Fields without Full Path
In any nested field path, Rockset assumes the first token is the collection name. Attempting to access nested fields without a fully qualified path will result in an error.
Sample Invalid Query:
SELECT
field."nested-field"
FROM
collection
Correct Query:
SELECT
c.field."nested-field"
FROM
collection c
or
SELECT
collection.field."nested-field"
FROM
collection
#Collection Errors
#Out-of-Sync Collections
Collections sourced from external systems such as Amazon DynamoDB or MongoDB can occasionally become out-of-sync with the data source. This happens rarely when Rockset's replication pipeline encounters errors or bugs.
You can re-sync a collection by dropping and recreating it:
- Navigate to the Collection Details page for your collection in the Rockset Console.
- Click the "Clone this Collection" button. This will open the Create Collection form in a new tab, and populate the configuration from your existing collection. Double-check that this configuration is correct, but do not click the Create Collection button.
- In the original tab displaying your Collection Details, click the "Delete Collection" button. Wait at least 30 seconds for the collection deletion to complete.
- Click the "Create Collection" button in the Collection Creation tab to recreate the collection.
Note that recreating collections will incur additional ingest costs in Rockset and potentially additional charges on the data source (e.g. RCU charges in Amazon DynamoDB). Furthermore, because re-syncing involves dropping and recreating a collection, there will be a period of unavailability. Thus, if you choose to re-sync a collection, we recommend that you do so during a scheduled maintenance window.