Troubleshoot common SQL query errors

Error message

Description

Solution

Operation not supported

Tablestore SQL supports a subset of SQL features. An error is reported if you use unsupported SQL syntax.

Use only the features that are supported by Tablestore SQL.

SQL features are updated frequently. For more information about the latest feature set, see Supported SQL features.

If you want to use specific SQL syntax, you can join DingTalk group 23307953 (Tablestore Technical Support-2) or 36165029092 (Tablestore Technical Support-3) for assistance.

text_match is only supported for TEXT field in filter conditions in search index

This error occurs when you use a full-text index function. The `text_match` and `text_match_phrase` functions can be used only on TEXT fields in a search index and must be included in filter conditions.

Make sure that you use the full-text index feature correctly.

text_match_phrase is only supported for TEXT field in filter conditions in search index

Error message

Description

Solution

The sql scanned rows of kv exceeds the quota! Search quota is 100000 rows, table quota is 100000 rows

The number of rows scanned or the data volume exceeds the limit when the KV engine is used for queries.

You can use a secondary index or a search index to accelerate data queries. For more information, see Index selection policies.

• Accelerate queries using a search index

  If a query contains aggregate functions such as `count`, `sum`, or `avg`, a `group by` clause, or a filter condition that is based on non-primary key columns, you can create a search index to accelerate the query.

  If this error persists after you create a search index, check whether the search index includes all fields used in the query.

• If no search index is created, make sure that the filter condition includes primary key columns and follows the leftmost prefix matching rule. You can also add a `limit` parameter to the SQL statement to control the number of returned rows.

The sql scanned rows of search exceeds the quota! Search quota is 100000 rows, table quota is 100000 rows

The number of rows scanned or the data volume exceeds the limit when the search index engine is used for queries.

• Add a `limit` parameter to the SQL statement to control the number of returned rows.

• Optimize the filter conditions in the SQL statement to push down some SQL computing tasks to the search index. For more information about computation pushdown, see Computation pushdown.

The sql duration time exceed the quota! Quota is 30 seconds

The SQL query timed out.

You can use a secondary index or a search index to accelerate data queries. For more information, see Index selection policies.

• Accelerate queries using a search index

  If a query contains aggregate functions such as `count`, `sum`, or `avg`, a `group by` clause, or a filter condition that is based on non-primary key columns, you can create a search index to accelerate the query.

  If this error persists after you create a search index, check whether the search index includes all fields used in the query.

• If no search index is created, make sure that the filter condition includes primary key columns and follows the leftmost prefix matching rule. You can also add a `limit` parameter to the SQL statement to control the number of returned rows.

Error message

Description

Solution

Field type mismatch, actual: STRING, expect: INTEGER, col: xxx, primary key: {"PrimaryKeys":[{"ColumnName":"xxx","Value":"xxx","PrimaryKeyOption":0}]}

The data type of an attribute column in the data table does not match the data type of the attribute column in the SQL statement.

Tablestore data tables are schemaless and allow you to write data of different types to the same attribute column. However, SQL is strongly typed, and each field must have a specific type.

When you use SQL, make sure that the data type of an attribute column in the data table matches the data type of the corresponding attribute column in the SQL statement. You can modify the data type of the specified attribute column in the data table based on the information in the error message.

Table 'instancename.tablename' doesn't exist

The specified table does not exist when you try to create a mapping.

In SQL, the `CREATE TABLE` statement can create mappings only for existing tables or search indexes. Make sure that the table or search index for which you want to create a mapping exists. For more information, see Create a mapping for a table and Create a mapping for a search index.

Table 'instancename.tablename' already exist

A mapping already exists for the table.

You can use the DESCRIBE statement to view the schema of the existing mapping table. If the schema does not meet your query requirements, you can update the attribute columns of the mapping table. You cannot update the attribute columns of an automatically created mapping table. Make sure that your business is not affected before you delete and re-create the mapping table.

Field type 'DECIMAL(11,0)' is not supported

The DECIMAL data type is not supported by SQL.

SQL queries support only specific data types. For more information, see SQL data type mapping.

Search engine: length of field value is longer than 32 for the [WILDCARD_QUERY] query

The length of the `LIKE` condition exceeds 32 characters.

Shorten the query string or use a match phrase query. For more information, see The "length of field value is longer than 32 for the [WILDCARD_QUERY] query" error is reported when you use a wildcard query.

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

When you use a `GROUP BY` clause, a column in the `SELECT` list is not included in the `GROUP BY` clause and is not part of an aggregate function.

You can add aggregation conditions to the SQL statement. For more information, see Query data.

Offset + limit exceeds the quota! quota is 150000 rows

The sum of `offset` and `limit` exceeds the maximum value. When you use the `limit a,b` expression, the sum of `a` and `b` cannot exceed 150,000.

Make sure that the sum of `offset` and `limit` is less than or equal to 150,000.

If you require a larger sum of `offset` and `limit`, you can join DingTalk group 23307953 (Tablestore Technical Support-2) or 36165029092 (Tablestore Technical Support-3) for assistance.

Unknown column 'xxx' in field list

The SQL statement contains a field name that does not exist in the mapping table.

This error usually occurs if you add a new field to the data table after you create the mapping table but do not add the new field to the mapping table.

The mapping table must contain all fields that are required for the query. If you add a new field to the data table, you must also add the new field to the mapping table. For more information, see Update the attribute columns of a mapping table.

Error message

Description

Solution

Search engine: search timeout, please retry

The query accessed a search index and timed out.

Check whether the query feature that you use requires you to configure predefined rules in the search index.

For example, to use the `ORDER BY` feature, you can configure presorting for the index when you create a search index. If the presorting method of the existing search index does not meet your requirements, you can use the dynamically modify the schema feature to modify the presorting method.