This topic describes how to troubleshoot common errors of SQL queries, including the following types of errors: OTSUnsupportOperation, OTSQuotaExhausted, OTSParameterInvalid, and OtsRequestTimeout.

Error code Error message Description Solution
OTSUnsupportOperation Operation not supported Tablestore SQL supports only some SQL statements. If you execute a SQL statement that is not supported by Tablestore, an error is reported. Execute SQL statements that are supported by Tablestore.

SQL statements that are supported by Tablestore are updated on a regular basis. You can visit the official website to obtain the most recent information about the SQL statements supported by Tablestore. For information about SQL statements supported by Tablestore, see SQL features.

text_match is only supported for TEXT field in filter conditions in search index text_match and text_match_phrase are incorrectly used. You can use text_match and text_match_phrase only for TEXT fields in search indexes as filter conditions. Use the full-text search feature in a correct way. For more information, see Full-text search.
text_match_phrase is only supported for TEXT field in filter conditions in search index
OTSQuotaExhausted The sql scanned rows of kv exceeds the quota! Search quota is 100000 rows, table quota is 100000 rows When you use the KV engine as the query engine, the number of rows that are scanned or the volume of data that is scanned exceeds the upper limit.
  • Use a search index to accelerate the query.

    If an aggregation function such as COUNT, SUM, or AVG, or the GROUPBY clause is included in the SQL statement for a query, or if a query is performed by using a non-primary key, we recommend that you configure a search index to accelerate the query.

    If a search index is configured but the error is still reported, check whether the search index contains the field based on which the query is performed.

  • If no search index is configured, make sure that the query conditions contain the primary key columns and comply with the leftmost matching principle. Then, add the limit parameter to the SQL statement to specify the maximum number of rows that can be returned.
Note The maximum number of rows that can be scanned is 100,000, the maximum volume of data that can be scanned is 128 MB, and the maximum scanning duration is 30 seconds.
The sql scanned rows of search exceeds the quota! Search quota is 100000 rows, table quota is 100000 rows When you use a search index as the query engine, the number of rows that are scanned or the volume of data that is scanned exceeds the upper limit.
  • Add the limit parameter to the SQL statement to limit the number of rows that can be returned.
  • Optimize the query conditions in the SQL statement to push down some SQL computing tasks to the search index. For information about computation pushdown, see Computing pushdown.
The sql duration time exceed the quota! Quota is 30 seconds The execution of the SQL statement times out.
  • Use a search index to accelerate the query.

    If an aggregation function such as COUNT, SUM, or AVG, or the GROUPBY clause is included in the SQL statement for a query, or if a query is performed by using a non-primary key, we recommend that you configure a search index to accelerate the query.

    If a search index is configured but the error is still reported, check whether the search index contains the field based on which the query is performed.

  • If no search index is configured, make sure that the query conditions contain the primary key columns and comply with the leftmost matching principle. Then, add the limit parameter to the SQL statement to specify the maximum number of rows that can be returned.
OTSParameterInvalid Field type mismatch, actual: STRING, expect: INTEGER, col: xxx, primary key: {"PrimaryKeys":[{"ColumnName":"xxx","Value":"xxx","PrimaryKeyOption":0}]} The data types of attribute columns in the data table do not match the data types of attribute columns in SQL. Data tables in Tablestore do not require schemas. You can write data of multiple types to an attribute column. Fields in SQL require schemas and each field must use a specific type.

When you use SQL to query data, make sure that the data types of attribute columns in data tables match the data types of attribute columns in SQL. You can change the type of data in a specific attribute column of a specific row in the data table based on the error message that is returned.

Table 'instancename.tablename' doesn't exist The table for which you want to create a mapping table does not exist. You can execute the CREATE TABLE statement in SQL to create a mapping table only for an existing table or search index. Make sure that the table or search index for which you want to create a mapping table exists. For more information, see Create mapping tables for tables and Create mapping tables for search indexes.
Field type 'DECIMAL(11,0)' is not supported SQL does not support the DECIMAL data type. SQL queries support only some data types. For more information, see Data type mappings.
Search engine: length of field value is longer than 32 for the [WILDCARD_QUERY] query The LIKE condition that is specified exceeds 32 characters in length. Specify a shorter LIKE condition.
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column The SQL statement that is used for a query contains the GROUPBY clause but does not contain aggregation functions. Specify aggregation functions in the SQL statement. For more information, see Query data.
Offset + limit exceeds the quota! quota is 150000 rows The sum of the values of the offset and limit parameters exceeds the upper limit. For example, if the value of the offset parameter is a, the value of the limit parameter is b, and the sum of a and b exceeds the upper limit, this error is reported. The upper limit is 150,000. Make sure that the sum of the values of the offset and limit parameters does not exceed 150,000.
Unknown column 'xxx' in field list A field name that is specified in the SQL statement is not included in the mapping table.

This error message is returned if you add a field to a data table but do not add the field to the mapping table that is created for the data table.

Add the field that you want to query to the mapping table. For more information, see Update attribute columns of mapping tables.
OtsRequestTimeout Search engine: search timeout, please retry The search index is accessed when the query is being performed and the execution of the query times out. Check whether the query feature that you want to use requires you to configure predefined rules for the search index. For example, if you want to use the ORDER BY clause, you can configure presorting when you create a search index. For more information, see Create search indexes.