Simple Log Service:Common errors when querying and analyzing logs

Error description​

The logstore that you want to query does not exist in the current region and project.

Causes​

• The logstore has been deleted.

• The logstore does not exist and may be in another project.

• The logstore does not exist and may be in another region.

Solution

Make sure the logstore that you want to query exists.

Error description​

You are using SQL syntax, but the logstore has no indexes configured. SQL requires at least one index to be configured.

Cause​

The logstore that you want to query has no indexes configured.

Solution

Check the logstore that you want to query and make sure that index configurations are enabled for at least one column.

Error description​

There is a conflict between the alias of the index field in the logstore that you want to query. SQL cannot determine the specific column you want to analyze.

Explanation

The concurrency quota of Simple Log Service SQL is isolated at the user project level. Multiple SQL requests in the same project are submitted to the Simple Log Service server. When an SQL is being executed, it occupies an SQL concurrency quota. When the SQL execution is completed, the allocated SQL concurrency quota is returned. The SQL concurrency quota of a user project is 15 for executing normal SQL mode and 150 for executing enhanced SQL mode.

Causes​

• The number of concurrent requests is too high.

• The latency of a single SQL request is high.

• The retry logic of SQL requests in your business code causes many loop retries.

Solution

• Reduce the number of requests.

• Optimize SQL to reduce the execution latency of a single SQL.

• Add random wait time to the retry logic to avoid invalid repeated loop retries, which increases additional concurrent request pressure.

Error description​

There is a conflict between the alias of the index field in the logstore that you want to query. SQL cannot determine the specific column you want to analyze.

Cause​

A column name in the logstore is exactly the same as the alias of a column.

Solution

Check the index key columns of the logstore that you want to query and ensure that there are no naming conflicts.

Error description​

You do not have permission to access the logstore under your current identity.

Cause​

The logstore is not authorized to your current identity.

Solution

Check the RAM permissions and grant read permissions on the logstore to your current identity. Authorization resource description:

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

Error description​

You are using nested subqueries in SQL. Please specify the table name in the innermost subquery.

Explanation

To simplify user queries, Simple Log Service specifies the current logstore as the FROM table by default when performing single-table queries. Suppose your current logstore name is test, then the following three query statements are synonymous:

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

Solution​

• If you want to query the current logstore, you can directly use the constant string "FROM log" to specify the FROM table as the current logstore.

• Alternatively, you can explicitly specify the name of the target logstore you want to query.

Error description​

There is a syntax error in the SQL statement. The value and list items in the IN clause must be of the same data type: varchar.

Cause​

When using the IN operator for queries, the provided value and list items have inconsistent data types. For example, the value is of varchar type, and the list items are of integer type.

Solution​

Ensure that the value and list items in the IN operator have the same data type. You can use the CAST or CONVERT function to convert data types, or convert both the value and list items to the same data type before querying. We recommend using the same data type for the corresponding column fields when writing logs to avoid similar errors.

Error description​

There is a syntax error in the SQL statement. The input parameter type is incorrect when using the SQL function.

Causes​

• The function requires a string type parameter, but the input parameter type is bigint.

• This type of error may occur in different functions, not necessarily url_decode, but also regexp_like. The cause of the error is the same: the input parameter type is incorrect when using the SQL function.

Solution​

Convert the input parameter to a string type before passing it to the url_decode function. You can use the CAST or CONVERT function to convert the bigint type parameter to a string type parameter, or directly enclose the parameter in quotation marks when calling the function to convert it to a string type. For example:

SELECT url_decode(CAST(bigint_param AS varchar(20))) -- Use the CAST function to convert the bigint type parameter to a string typeSELECT url_decode('123456789') -- If the parameter is a literal, you can directly enclose the parameter to convert it to

Error description​

The target of the repeat operator is not specified.

Cause​

This may be an error in a regular expression, indicating that the target of the repeat operator is not specified. The repeat operator "()" is used to match zero or more occurrences of the preceding character or group, but it requires a target to apply the repetition. For example, "(a) *" means zero or more occurrences of the letter "a". If no target is specified, such as in "() *", the regular expression engine will not know how to apply the repeat operator, resulting in this error.

Solution​

Check whether the repeat operator "()" in the regular expression has a correct target and make the necessary corrections.

Error description​

NULL values are not allowed on the probe side of the SemiJoin.

Cause​

In a SemiJoin, non-NULL values cannot appear on the probe side. Otherwise, the SemiJoin cannot be executed correctly. This may be because a row record in the probe table or a subquery returned a NULL value during query runtime.

Solution​

Check the query plan to determine which table returned a NULL value. If a subquery is involved, ensure that the result set returned by the subquery does not contain NULL values. If NULL values in an external table cause the issue, consider using INNER JOIN instead of SemiJoin, or use the COALESCE or ISNULL function to handle NULL values.

Error description​

Array subscript out of bounds.

Cause​

You are trying to access an index position that is out of the array's range. For example, accessing a negative index or an index beyond the array's length. This may be due to incorrect logic in SQL or data entry errors.

Solution​

In SQL, array index positions start from 1. Check the valid length of the array in SQL, then check the reference of the array index position, and ensure that the array subscript is not out of range.

Error description​

The expression "fields" is not of type ROW.

Cause​

When using the ROW type, the expression "fields" does not meet the requirements of the ROW type. This may be because the parameters do not meet the requirements when using the ROW function.

Solution​

Check whether the parameters of the ROW function are correct and whether all the fields in the parameters exist and meet the requirements. If the parameters are correct but the result is still not of type ROW, you can try using the CAST function to convert it to type ROW.

Error description​

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

Cause​

The delimiter appears more than once or not at all in a key-value pair in the input, so that the system cannot parse the key-value pair.

Solution​

Check whether the format of the key-value pair in the input is correct. Ensure that there is only one delimiter in each key-value pair and that there are correct keys and values on both sides of it.

Error description​

The regular expression cannot access the specified group.

Cause​

The regular expression matched 0 groups, making it impossible to access the first group. This may occur if grouping syntax is used in the regular expression but no groups are defined. In this case, the group cannot be accessed because it does not exist.

Solution​

Check the grouping syntax in the regular expression and ensure that at least one group is defined in the pattern. You can use parentheses () to define groups. For example, to match an email address in a string and separate the username and domain name into groups, use the following regular expression: (w+)@(w+.w+). In this example, there are two groups, so you can use group(1) and group(2) to access their values. If grouping is not needed, use non-capturing groups, such as (?:w+)@(?:w+.w+). If the issue persists, use an online regex checker for debugging and verify the pattern before using it in SQL.

Error description​

The ts_compare function must group by the timestamp type.

Cause​

When using the ts_compare function in SQL, the column used in the group by clause may be a non-timestamp type, such as numeric.

Solution​

Ensure that the column type used in the group by clause for the ts_compare function is the correct timestamp type. You can use functions such as from_unixtime to convert integer timestamps to the timestamp type.

Error description​

The timestamp is out of the specified time range.

Cause​

The SQL statement uses a timestamp that is out of the specified time range, possibly due to data entry errors or data type mismatches.

Solution​

Check whether the timestamp is correct. If the issue is caused by a data type mismatch, try using relevant data type conversion functions to convert the timestamp to the correct data type.

Error description​

ROW comparison is not supported for fields with NULL values.

Cause​

The SQL statement uses a ROW type comparison operation that contains NULL value elements, such as using the = or != operator for comparison.

Solution​

Before performing row comparison, you must handle the ROW that contains NULL elements. Use operators like IS NULL or IS NOT NULL to filter out NULL values, or use the COALESCE function to handle NULL values. Handle rows containing NULL elements during the log data writing and processing process to avoid the above errors.

Error description​

This usually occurs when you perform a foreign table join query using OSS and fail to access the OSS bucket: the specified key does not exist.

Cause​

The specified object does not exist in the OSS bucket you are accessing. It may have been deleted or never existed. This may be because you specified the wrong OSS bucket endpoint or the wrong object key.

Solutions​

• Check the OSS bucket and the name of the object key to be accessed to ensure they are correct.

• Go to OSS to confirm whether the specified object key exists in the specified OSS bucket.

Error description​

The maximum number of rows for pagination cannot exceed 1,000,000.

Cause​

Simple Log Service SQL limits the maximum output rows to 1,000,000. The pagination read request you are making exceeds this limit.

Solutions​

• Use the LIMIT clause to limit the maximum number of rows for pagination to no more than 1,000,000.

• Narrow the query range to limit the maximum number of rows for pagination to no more than 1,000,000.

• Use the Scheduled SQL service to perform SQL aggregation analysis in windows periodically, and then perform secondary aggregation on the aggregation results.

Error description​

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

Cause​

This usually occurs because you are trying to perform arithmetic or comparison operations between variables of different types, and the system cannot automatically determine which type of operator should be used.

Solution​

Add explicit type casts to tell the system which type of operator to use.

For example, if you are trying to add a string type variable and an integer type variable, you can use the CAST function to convert the string type variable to an integer type, and then perform the addition operation. Example code:

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

Here, we use the CAST function to convert the string type variable '10' to an integer type, and then add it to the integer type variable 5. This avoids the above error.

Error description​

The function does not exist.

Causes​

The function you specified does not exist in the Simple Log Service SQL system.

• You used a specific function of a database vendor that is not a standard SQL function and is not provided in the Simple Log Service SQL system.

• You misspelled the function name.

Solution​

Check the function name and confirm that it is a valid function provided by Simple Log Service SQL.

Error description​

SQL array index positions start at 1.

Cause​

When using array index positions in SQL, you may have started counting from 0.

Solution​

SQL array index positions start counting from 1.

Error description​

Index positions start at 1.

Cause​

When using array index positions in SQL, you may have specified an invalid negative value or 0.

Solution​

SQL array index positions start counting from 1.

Error description​

All parameters in the COALESCE function must be of the same type.

Cause​

In the COALESCE function, operands must have the same data type. Otherwise, a data type error will occur. In this error, at least one operand is of Boolean type, while other operands have different data types, such as numbers or strings.

Solution​

Check each operand in the COALESCE function and ensure they have the same data type. If they are different, perform data type conversion to make them have the same data type. You can use the CAST function to perform data type conversion.

Error description​

Scalar queries do not support returning multiple columns.

Cause​

Multiple columns are selected in the subquery.

Solution​

Ensure that the subquery returns only one column or one value. You can modify the subquery or main query. You can also try using the JOIN statement instead of the subquery to retrieve the required data.

Error description​

The GROUP BY clause cannot contain aggregate or window functions.

Cause​

Aggregate or window functions are included in the GROUP BY clause.

Solution​

Include only column names in the GROUP BY clause, not aggregate or window functions. Aggregate and window functions should be used in the SELECT statement, not in the GROUP BY clause. If you need to use an aggregate function in the GROUP BY clause, use the alias of the column or the numeric index instead of the aggregate function. For example, use the following query:

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

In this query, count_column3 is the alias of COUNT(column3), and 3 is the position of COUNT(column3) in the SELECT statement. Note that using numeric indexes may make the code difficult to understand, so we recommend that you use column aliases.

Error description​

The WHERE clause cannot contain aggregate functions or window functions.

Cause​

Aggregate functions or window functions are included in the WHERE clause.

Solution​

Include only column names in the WHERE clause, not aggregate or window functions. These should be used in the SELECT statement, not in the WHERE clause. If you need to use an aggregate function in the WHERE clause, you can use the alias of the column instead.

For example, use the following query:

SELECT column1, column2, COUNT(column3) as count_column3
FROM table
WHERE count_column3 > 10

count_column3 is the alias of COUNT(column3), which represents the aggregate count result of column3.

Error description​

The left side of the LIKE expression must be of varchar type (actual: bigint).

Cause​

This error usually occurs when you try to use the LIKE operator to compare bigint with varchar. The LIKE operator requires both sides of the expression to have the same data type.

Solution​

You may need to use the CAST function to convert bigint to varchar.

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

This will convert bigint_column to varchar, allowing the LIKE operator to match it with the specified pattern.

Error description​

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

Cause​

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

Solution​

Check the value type on the left side of the logical expression and ensure it is boolean.

Error description​

The right side of the logical expression must be of the boolean type, which is actually bigint.

Cause​

This error usually occurs when you try to use a logical expression, and the variable type on the right side of the logical expression is not boolean.

Solution​

You need to check the value type on the right side of the logical expression and ensure it is boolean.

Error description​

Invalid JSON access path.

Cause​

When using JSON functions (such as json_extract, json_extract_scalar, json_size, etc.) in SQL to access the specified JSON path, an invalid path is specified.

Solution​

• Specify the json_path correctly, in the format $.a.b. Here, $ represents the root node of the current JSON object, and the period . refers to the node to be extracted (can be cascaded). However, when there are special characters (such as ., space , and -) in the fields of the JSON object, such as http.path, http path, and http-path, you must use square brackets [] instead of the 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 is limited to using a maximum of 10 distincts.

Cause​

You used more than 10 distinct calculations in SQL.

Solutions​

• Reduce the number of distincts used in SQL to below 10.

• Use approx_distinct instead of distinct.

Error description​

The specified key does not exist in the map.

Cause​

You used a map type in SQL and specified a key that does not exist.

Solutions​

• Check the map type data and confirm that the key you specified exists in the map.

• If the key does not exist in the map type data, use the try function to wrap the map access to ignore the error, for example:

  SELECT try(map['name']) -- map is a map type column. If the key named 'name' does not exist in the column, NULL is returned.

Cause

Internal system error.

Solution

Refresh the page and execute the query and analysis statement again.