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:

1. Navigate to the Collection Details page for your collection in the Rockset Console.
2. 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.
3. In the original tab displaying your Collection Details, click the "Delete Collection" button. Wait at least 30 seconds for the collection deletion to complete.
4. 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.