Comparison operators

Log Service supports the following comparison operators.

Important If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Operator	Syntax	Description	SQL	SPL
Relational operators	x < y	Returns true if x is less than y.	√	√
	x > y	Returns true if x is greater than y.	√	√
	x <= y	Returns true if x is less than or equal to y.	√	√
	x >= y	Returns true if x is greater than or equal to y.	√	√
	x = y	Returns true if x is equal to y.	√	√
	x <> y	Returns true if x is not equal to y.	√	√
	x != y	Returns true if x is not equal to y.	√	√
ALL operator	x relational operator ALL(subquery)	Returns true when x satisfies all conditions.	√	×
ANY operator	x relational operator ANY(subquery)	Returns true if x meets any of the conditions.	√	×
BETWEEN operator	x BETWEEN y AND z	Returns true if x is between y and z, inclusive.	√	√
DISTINCT operator	x IS DISTINCT FROM y	Returns true if x is not equal to y. Unlike the <> operator, this operator returns false if both x and y are NULL.	√	×
DISTINCT operator	x IS NOT DISTINCT FROM y	Returns true if x is equal to y. Unlike the = operator, this operator returns true if both x and y are NULL.	√	×
LIKE operator	x LIKE pattern [escape 'escape_character']	Checks if a string matches the specified pattern. This operation is case-sensitive.	√	√
SOME operator	x relational operator SOME(subquery)	Returns true if x satisfies any condition.	√	×
GREATEST operator	GREATEST(x, y...)	Find the maximum value of x and y.	√	×
LEAST operator	LEAST(x, y...)	Find the minimum value of x and y.	√	×
NULL operator	x IS NULL	Returns true if x is NULL.	√	√
NULL operator	x IS NOT NULL	Returns true if x is not NULL.	√	√

Relational operators

Relational operators compare the values of x and y. An operator returns true if its condition is met.

Syntax

Syntax	Description
x < y	Returns true if x is less than y.
x > y	Returns true if x is greater than y.
x <= y	Returns true if x is less than or equal to y.
x >= y	Returns true if x is greater than or equal to y.
x = y	Returns true if x is equal to y.
x <> y	Returns true if x is not equal to y.
x != y	Returns true if x is not equal to y.

Parameters

Parameter	Description
x	An expression of any comparable data type.
y	An expression of any comparable data type.

Return value type

boolean
Examples
- Example 1: Query logs from the previous day.
  - Query statement
```
* |
SELECT
  *
FROM  log
WHERE
  __time__ < to_unixtime(current_date)
  AND __time__ > to_unixtime(date_add('day', -1, current_date))
```
  - Query results: The query returns multiple log fields, including body_bytes_sent, client_ip, host, http_user_agent, http_x_forwarded_for, instance_id, instance_name, network_type, owner_id, and referer. These fields provide details for each access record, such as the request body size, client IP address, accessed domain name, User-Agent, instance ID, instance name, and network type.
- Example 2: To find customers whose phone number location differs from their access IP location, an e-commerce company queries the mobile and client_ip fields in its access logs.
  - Sample field values
```
mobile:1881111****
client_ip:192.168.2.0
```
  - Query statement
```
* |
SELECT
  mobile,
  client_ip,
  count(*) AS PV
WHERE
  mobile_city(mobile) != ip_to_city(client_ip)
  AND ip_to_city(client_ip) != ''
GROUP BY
  client_ip,
  mobile
ORDER BY
  PV DESC
```
  - Query results: The query returns two records. The sensitive information in the mobile and client_ip columns is masked. The corresponding PV values are 13 and 12.

ALL operator

The ALL operator compares a value, x, to all values in a subquery's result set. It returns true if the comparison is true for every value in the set.

Syntax
```
x relational operator ALL(subquery)
```

Parameters

Parameter	Description
x	A value of any comparable data type.
comparison operator	<, >, <=, >=, =, <>, != Important The ALL operator must immediately follow a basic operator (<, >, <=, >=, =, <>, !=).
subquery	An SQL subquery.

Return value type
Boolean type.
Examples

This example checks if all requests for instance 'i-01' have a status code of 200.
- Sample log
```
instance_id:i-01
status:200
```
- Query and analysis statement
```
* | select 200 = ALL(select status where instance_id='i-01')
```
- The query and analysis results show _col0 as false. This means not all logs for instance 'i-01' have a status of 200.

ANY operator

The ANY operator compares a value x to a set of values returned by a subquery. It returns true if the comparison is true for at least one value in the set.

Syntax
```
x relational operator ANY(subquery)
```

Parameters

Parameter	Description
x	An expression of any comparable data type.
relational operator	<, >, <=, >=, =, <>, or != Important A relational operator must precede the `ANY` operator.
subquery	A subquery that returns a single column of values. The column's data type must match the data type of the expression `x`.

Return value type

boolean
Examples

Check if any request for instance i-01 has a status code of 200.
- Sample fields
```
instance_id:i-01
status:200
```
- Query statement
```
* | SELECT 200 = ANY(SELECT status WHERE instance_id='i-01')
```
- The query returns the _col0 column with a value of true, indicating the condition is met.

BETWEEN operator

BETWEEN is used to determine whether x is between y and z. If it is, it returns true. The range between y and z is a closed interval.

Syntax
```
x BETWEEN y AND z
```

Parameters

Parameter	Description
x	An expression of any comparable data type.
y	An expression of any comparable data type.
z	An expression of any comparable data type.

Important

The data types of x, y, and z must be the same.
If the value of x, y, or z is null, the result is null.

Return value type

Boolean
Examples
- Example 1: Check if the value of the status field is within the range [200, 299].
  - Query statement
```
* | SELECT status BETWEEN 200 AND 299
```
  - Query and analysis results: The query returns three rows in the _col0 column, and the value in each row is true. This indicates that the status field value for each corresponding log is between 200 and 299.
- Example 2: Count the logs where the value of the status field is not within the range [200, 299].
  - Query statement
```
* | SELECT count(*) AS count FROM log WHERE status NOT BETWEEN 200 AND 299
```
  - Query and analysis results: The query returns a count of 250.

DISTINCT operator

The DISTINCT operator compares two values, x and y, and correctly handles NULL values.

Syntax
- IS DISTINCT FROM returns true if x is not equal to y.
```
x IS DISTINCT FROM y
```
- IS NOT DISTINCT FROM returns true if x is equal to y.
```
x IS NOT DISTINCT FROM y
```

Parameters

Parameter	Description
x	An expression of a comparable data type.
y	An expression of a comparable data type.

Unlike the basic operators (= and <>), the DISTINCT operator correctly handles NULL values.

x	y	x = y	x <> y	x IS DISTINCT FROM y	x IS NOT DISTINCT FROM y
1	1	true	false	false	true
1	2	false	true	true	false
1	null	null	null	true	false
null	null	null	null	false	true

Return value type

Boolean.
Examples

Compare 0 with null.
- Query statement
```
* | select 0 IS DISTINCT FROM null
```
- The query result is true, indicating that 0 and null are distinct.

LIKE operator

The LIKE operator checks if a string matches a specified character pattern. This operation is case-sensitive.

Syntax

x LIKE pattern [escape 'escape_character']

Parameters

Parameter	Description
x	An expression of any comparable data type.
pattern	The character pattern to search for, which can include strings and wildcard characters. The following wildcard characters are supported: Percent sign (%): Matches any string of zero or more characters. Underscore (_): Matches any single character.
escape_character	A character used to escape wildcard characters in the character pattern.

Note

The LIKE operator is primarily used for pattern matching queries on logs. For more information, see How to perform an exact match query.

Return value type

boolean
Examples
SQL
- Example 1: Queries logs where the value of the request_uri field ends with file-8 or file-6.
  - Sample field
    request_uri:/request/path-2/file-6
  - Query and analysis statement
    *|SELECT * WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'
  - Query and analysis results: This query returns log records where the request_uri field ends with file-8 or file-6. Each record includes fields such as remote_addr, remote_user, request_length, request_method, request_time, request_uri, scheme, server_protocol, slbid, status, and time_local.
- Example 2: Check if the value of the request_uri field ends with file-6.
  - Sample field
    request_uri:/request/path-2/file-6
  - Query and analysis statement
    * | SELECT request_uri LIKE '%file-6'
  - The query returns the _col0 column with a value of true. This indicates that the value of request_uri matches the %file-6 pattern.
SPL
- Example 1: Queries logs where the value of the request_uri field ends with file-8 or file-6.
  - Sample field
```
request_uri:/request/path-2/file-6
```
- SPL statement
```
*|WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'
```
- SPL results
The query returns all log entries where the value of the request_uri field ends with file-8 or file-6.
- Example 2: Check if the value of the request_uri field ends with file-6.
  - Sample field
```
request_uri:/request/path-2/file-6
```
- SPL statement
```
* | extend a = request_uri LIKE '%file-6'
```
- SPL results
The query results include a new field, a, with a value of true. This indicates that the value of the request_uri field in the log entry ends with file-6.

SOME operator

The SOME operator compares a value x to the set of values returned by a subquery. The operator returns true if the comparison is true for any value in the set.

Syntax
```
x relational operator SOME(subquery)
```

Parameters

Parameter	Description
x	An expression of any comparable data type.
relational operator	<, >, <=, >=, =, <>, != Important The `SOME` operator must immediately follow a relational operator (<, >, <=, >=, =, <>, or !=).
subquery	A subquery that returns a single column of values. The data type of the column must be the same as the data type of the expression x.

Return value type

Boolean type.
Examples

Check whether any request related to instance i-01 has a request time of less than 20 seconds.
- Sample fields
```
instance_id:i-01
request_time:16
```
- Query statement
```
* | SELECT 20 > SOME(SELECT request_time WHERE instance_id='i-01')
```
- Query results

GREATEST operator

The GREATEST operator returns the maximum value of x and y.

Note

The GREATEST operator is used for horizontal comparison, whereas the max function is used for vertical comparison.

Syntax
```
GREATEST(x, y...)
```

Parameters

Parameter	Description
x	An expression of a comparable data type.
y	An expression of a comparable data type.

Return value type

double
Examples

Compare the values of the request_time and status fields in the same row to find the largest value.
- Sample fields
```
request_time:38
status:200
```
- Search and analysis statement
```
* |  SELECT GREATEST(request_time,status)
```
- The query and analysis results include the _col0 column with a value of 200.0, which is the larger of the request_time (38) and status (200).

LEAST operator

The LEAST operator returns the minimum value of x and y.

Note

The LEAST operator is used for horizontal comparison, while the min function is used for vertical comparison.

Syntax
```
LEAST(x, y...)
```

Parameters

Parameter	Description
x	The value to compare. It can be any comparable data type.
y	The value to compare. It can be any comparable data type.

Return value type

double
Examples

Compare the request_time field value and the status field value in the same row to obtain the minimum value.
- Sample fields
```
request_time:77
status:200
```
- Query and analysis statement
```
* |  SELECT LEAST(request_time,status)
```
- Query and analysis results
  
  The query returns 77.

Null operator

The null operator checks whether x is null.

Syntax
- IS NULL: Returns true if the expression is null.
```
x IS NULL
```
- IS NOT NULL: Returns true if the expression is not null.
```
x IS NOT NULL
```

Parameters

Parameter	Description
x	An expression of any comparable data type.

Return value type

Boolean type.
Examples
- Example 1: Check whether the value of the status field is null.
  - Query statement
```
* | select status IS NULL
```
  - Query and analysis results: The query returns the _col0 column. Both rows contain the value false, indicating that the status field is not null.
- Example 2: Count log entries where the status field is not null.
  - Query statement
```
* | SELECT count(*) AS count FROM log WHERE status IS NOT NULL
```
  - Query and analysis results: The query returns a count of 1340.