Common log query and analysis errors

If you have an issue with a query and analysis statement, use Copilot to troubleshoot. You can also use SQL optimization. For more information, see Use AI to generate query and analysis statements (Copilot).

On the query and analysis page of the Log Service console, an SQL query to find the daily top 10 client IP addresses by access volume returns Log entries: 0, and the timeline chart displays no data points. Copilot then automatically intervenes, provides diagnostic suggestions, and corrects the query statement. The corrected query then returns the correct analysis result. This animation demonstrates the complete troubleshooting flow with Copilot.

Log Service also supports intelligent query through the Agent Skill feature. For more information, see Use SLS Query Skill for intelligent log query and analysis.

Error description​

The specified Logstore does not exist in the current region and Project.

Causes​

• The Logstore has been deleted.

• The Logstore may be in a different Project.

• The Logstore may be in a different region.

Solution

Verify that the Logstore exists in the specified Project and region.

Error description​

SQL queries require the target logstore to have at least one configured index.

Cause​

The target logstore lacks an index configuration.

Solution

Enable index configuration for at least one column in the target logstore.

Error description​

This error occurs when the number of concurrent queries in your Project exceeds the quota.

Explanation

The SQL concurrency quota in Log Service is isolated at the Project level. When you submit SQL requests within a Project, each running query consumes a quota slot, which is released after the query completes. The SQL concurrency quota for a Project is 15 for queries in normal SQL mode and 100 for queries in enhanced SQL mode.

Causes​

• The number of concurrent requests is too high.

• Individual SQL queries have high execution latency.

• Your application's retry logic causes excessive retries after a request fails.

Solution

• Reduce the number of concurrent requests.

• Optimize your SQL queries to reduce execution latency.

• Implement retry logic with a random wait time. This helps prevent repeated, inefficient retries that increase the concurrent request load.

Error description​

An alias for an index field in the Logstore you are querying is causing a conflict. As a result, SQL cannot determine which column to analyze.

Cause​

A column name in the Logstore is exactly the same as the alias of another column.

Solution

Check the index key columns of the target Logstore for naming conflicts.

Error description​

You do not have the required permission to query the logstore.

Cause​

Your current identity does not have the required permissions for the logstore.

Solution

Check your RAM permissions and grant the log:GetLogStoreLogs permission to your current identity. The authorization policy must include the following action and resource:

action: log:GetLogStoreLogs, resource: acs:log:<region>:<uid>:project/<project>/logstore/<logstore>

Error description​

When using nested subqueries in SQL, you must specify the table name in the innermost subquery.

Explanation​

To simplify single-table queries, Simple Log Service uses the current logstore as the default table. For example, if your current logstore is named test, the following three queries are equivalent:

However, when running complex queries with subqueries, Simple Log Service cannot infer the target table for each subquery. Therefore, you must manually specify the FROM clause in each one.

Solution​

• To query the current logstore, use FROM log.

• Alternatively, specify the target logstore by name.

Error description​

This error indicates that the value and list items in an IN clause must have the same data type.

Cause​

This error is caused by a data type mismatch between the value and the list items in the IN operator. For example, the value is a varchar, while the list items are integers.

Solution​

To resolve this, ensure that the value and list items in the IN operator share the same data type. You can use the CAST or CONVERT function to convert the values to a matching data type before running the query. As a best practice, use the same data type for the corresponding column when writing logs to prevent similar errors.

Problem​

This error occurs when you pass a parameter of the wrong data type to an SQL function.

Cause​

• The function expects a string parameter, but you provided a bigint parameter.

• This type of error can occur with different functions, such as url_decode or regexp_like. The cause is always a mismatch between the data type the function expects and the one you provide.

Solution​

To resolve this issue, convert the input parameter to a string before passing it to the url_decode function. You can use the CAST or CONVERT function to convert the bigint parameter to a string. If the parameter is a literal, enclose it in quotation marks to treat it as a string. For example:

SELECT url_decode(CAST(bigint_param AS varchar(20))) -- Use CAST to convert a bigint parameter to a string.
SELECT url_decode('123456789') -- If the parameter is a literal, enclose it in quotation marks to treat it as a string.

Error description​

The target of the repeat operator is not specified.

Cause​

The regular expression engine throws this error when a repeat operator in your regular expression is missing a target. Repeat operators, such as *, +, or ?, must apply to a preceding character or group. For example, in the valid expression (a)*, the * operator applies to the group (a). However, in invalid patterns like *a or ()*, the operator has no target, so the engine does not know what to repeat.

Solution​

Review your regular expression to ensure that every repeat operator immediately follows a valid character or group. Correct the pattern by providing a target or removing the misplaced operator.

Error description​

NULL values are not allowed on the probe side of a semi join.

Cause​

This error occurs because a row in the probe table or a value from a subquery returned a NULL value, which prevents the semi join from executing correctly.

Solution​

Check the query plan to identify which table returns a NULL value. If a subquery is involved, ensure that its result set does not contain NULL values. If an external table is the source of the NULL values, consider replacing the semi join with an INNER JOIN or using the COALESCE or ISNULL functions to handle them.

Error description​

Array subscript out of bounds.

Cause​

You attempted to access an index outside the valid range of the array, for example, a negative index or an index that exceeds the array length. This issue is typically caused by incorrect logic in your SQL statement or invalid input data.

Solution​

In SQL, array indexes start at 1. Check the array length in your SQL statement, and ensure that each index you reference is within the valid range.

Error description​

The expression 'fields' is not a ROW type.

Cause​

This error indicates that the 'fields' expression is not a valid ROW type. This can occur if you pass invalid parameters to the ROW function.

Solution​

Check the parameters of the ROW function to ensure all specified fields exist and are valid. If the parameters are correct but the expression is still not a ROW type, use the CAST function to convert it to a ROW type.

Error description​

The key-value delimiter must appear exactly once in each entry.

Cause​

The system cannot parse a key-value pair if its delimiter appears more than once or is missing.

Solution​

Check your input to ensure each key-value pair uses exactly one delimiter to separate the key from the value.

Error description​

The regular expression cannot access the specified group.

Cause​

The regular expression matched 0 groups, making it impossible to access a specific group. This happens if you use grouping syntax in a regular expression without defining any capturing groups.

Solution​

Check the grouping syntax in your regular expression and ensure that at least one capturing group is defined in the pattern. You can use parentheses () to define a capturing group. For example, to match an email address in a string and capture the username and domain name as separate groups, use the regular expression (\w+)@(\w+\.\w+). This pattern creates two groups. You can then access their matched values by referencing group(1) and group(2). If you need to group parts of a pattern without capturing the matched text, use a non-capturing group, such as (?:\w+)@(?:\w+\.\w+). If the issue persists, use an online regex checker to debug and validate the pattern before adding it to your SQL query.

Error description

The ts_compare function requires a group by clause on a column of the timestamp type.

Cause

This error occurs when you use the ts_compare function in an SQL query, but the column in the group by clause is not of the timestamp type, such as a numeric type.

Solution

Ensure the column in the group by clause is of the timestamp type when using the ts_compare function. For example, use the from_unixtime function to convert an integer timestamp to the timestamp type.

Error description​

The timestamp is outside the specified time range.

Cause​

The SQL statement contains an out-of-range timestamp, possibly due to data entry errors or a data type mismatch.

Solution​

Verify that the timestamp is correct. If a data type mismatch is the cause, use a data type conversion function to cast the timestamp to the correct data type.

Error description​

ROW comparison is not supported for fields that contain NULL elements.

Cause​

Your SQL statement uses a comparison operator, such as = or !=, on a ROW-type field that contains a NULL element. In SQL, any comparison involving a NULL value evaluates to UNKNOWN, which causes the operation to fail.

Solution​

Before performing a row comparison, you must handle NULL elements in the ROW-type field. You can filter them out using operators like IS NULL or IS NOT NULL, or replace them with a default value using the COALESCE function. As a best practice, handle NULL values earlier in your data pipeline, such as during data ingestion or processing, to prevent this error.

Description​

This error occurs during a foreign table join query when a specified object key does not exist in the target OSS bucket.

Cause​

The specified object does not exist in the OSS bucket. This can happen if the object was deleted, never existed, or if you provided an incorrect bucket endpoint or object key.

Solutions​

• Verify the OSS bucket name and object key.

• Use the OSS console to confirm that the object key exists in the bucket.

Description​

The maximum number of rows for a paginated query is 1,000,000.

Cause​

Simple Log Service SQL limits the number of output rows to 1,000,000. This error occurs because your request exceeds this limit.

Solutions​

• Use the LIMIT clause to restrict the query to 1,000,000 rows or fewer.

• Narrow the query range to return 1,000,000 rows or fewer.

• Use the Scheduled SQL service to perform periodic SQL aggregation analysis in windows. Then, perform a secondary aggregation on the results.

Error description​

Could not choose the best candidate operator. Explicit type casts must be added.

Cause​

This error usually occurs when you perform an arithmetic or comparison operation on variables of different data types, and the system cannot automatically determine which operator to use.

Solution​

Add an explicit type cast to specify which operator to use.

For example, to add a string and an integer, use the CAST function to convert the string to an integer before the addition:

SELECT CAST('10' AS INTEGER) + 5;

In this example, the CAST function prevents the error by converting the string '10' to an integer before adding it to 5.

Error description​

The specified function does not exist.

Causes​

The specified function is not found in SLS SQL. This error occurs if:

• You use a function specific to a database vendor that is not supported by SLS SQL.

• You misspell the function name.

Solution​

Verify that the function name is spelled correctly and is supported by SLS SQL.

Error description​

You attempted to use an array index of 0.

Cause​

Unlike many programming languages, SQL uses one-based indexing for arrays.

Solution​

Use an index of 1 to access the first element of an array.

Error description​

This error occurs when you access an array element with a non-positive integer index.

Cause​

Array indices in SQL are 1-based. You cannot use 0 or a negative number as an index.

Solution​

Update your query to use only positive integers for array indices.

Description​

All operands in the COALESCE function must have the same data type.

Cause​

The operands in the COALESCE function have mismatched data types. This error occurs when a boolean type is used with another data type, such as a number or string.

Solution​

Check the data type of each operand in the COALESCE function. If a mismatch is found, use the CAST function to convert the operands to a common data type.

Error description​

A scalar query cannot return multiple columns.

Cause​

The subquery selects more than one column.

Solution​

Ensure that the subquery returns only a single column or value. Modify the subquery or the main query. Alternatively, use a JOIN statement instead of a subquery.

Error description​

The GROUP BY clause cannot contain aggregate functions or window functions.

Cause​

This error occurs when you include an aggregate function or a window function in the GROUP BY clause.

Solution​

Ensure the GROUP BY clause contains only column names. Aggregate functions and window functions belong in the SELECT statement, not the GROUP BY clause. To group by the result of an aggregation, use its alias or numeric index in the GROUP BY clause, not the function expression itself. For example:

SELECT column1, column2, COUNT(column3) as count_column3
FROM table
GROUP BY column1, column2, 3

In this query, count_column3 is the alias for COUNT(column3), and 3 is the numeric index that refers to its position in the SELECT statement. Note that using a numeric index can make code harder to read and maintain. Using an alias is recommended for clarity.

Error description​

A WHERE clause cannot contain aggregate functions or window functions.

Cause​

This error occurs because the WHERE clause is evaluated before groups and aggregates are calculated. Therefore, the results from an aggregate or window function are not yet available for filtering.

Solution​

Use the WHERE clause to filter individual rows based on column values. To filter grouped data based on the result of an aggregate function, use the HAVING clause. The HAVING clause runs after data grouping and aggregation.

For example, use the following query:

SELECT column1, column2, COUNT(column3) AS count_column3
FROM table
GROUP BY column1, column2
HAVING count_column3 > 10

In this query, count_column3 is the alias for the result of the COUNT(column3) aggregate function. The HAVING clause correctly filters the grouped results based on this alias.

Error description​

The left operand of a LIKE expression must be a varchar, but a bigint was provided.

Cause​

The LIKE operator performs pattern matching on string data types. This error occurs because an attempt was made to apply it to a non-string data type (bigint).

Solution​

Use the CAST function to convert the bigint to a varchar.

SELECT * FROM table WHERE CAST(bigint_column AS varchar) LIKE 'pattern'

This statement first converts the bigint_column to a varchar, which enables the LIKE operator to perform a pattern match against the specified pattern.

Error description​

The left side of the logical expression must be a boolean type (actual: varchar).

Cause​

This error usually occurs when you try to use a logical expression where the right side of a relational operator, such as = or !=, is a boolean value (true or false), but the type on the left side is a non-boolean type, such as varchar or another type.

Solution​

Ensure the left side of the logical expression is a boolean type.

Error description​

The right side of the logical expression must be a boolean (actual: bigint).

Cause​

This error occurs when the variable on the right side of a logical expression is not a boolean.

Solution​

Ensure the value on the right side of the logical expression is a boolean.

error description​

The specified json_path is invalid.

cause​

This error occurs if you use a JSON function, such as json_extract, json_extract_scalar, or json_size, with an invalid json_path in an SQL statement.

solution​

• A json_path is normally specified in the format $.a.b. In this format, $ represents the root node of the current JSON object, and the period . is used to reference a nested node. However, if a field name in the JSON object contains special characters (such as ., a space , or -), for example, http.path, http path, or http-path, you need to use square brackets [] instead of a period . and enclose the field name in double quotes "". For example:

  * | SELECT json_extract_scalar(request, '$["X-Power-Open-App-Id"]')

• For more information, see JSON functions and FAQs about the query and analysis of JSON logs.

Error description​

A single query can contain a maximum of 10 distinct operations.

Cause​

Your SQL query uses more than 10 distinct operations.

Solution​

• Reduce the number of distinct operations in your SQL query to 10 or fewer.

• Use approx_distinct instead of distinct.

Error description​

The specified key does not exist in the map.

Cause​

This error occurs when a query attempts to access a key that does not exist in a map type column.

Solution​

• Verify that the specified key exists in the map data.

• To prevent a query from failing when a key might not exist, wrap the map access expression with the try function. This function returns NULL if the key is not found. For example:

  SELECT try(map['name']) -- Returns NULL if the 'name' key is not found.

Cause

This is an internal system error.

Solution

Refresh the page and rerun the query and analysis statement.