safe_subtract ↗

Use the safe_subtract() function to perform subtraction of two numeric values with overflow protection. Unlike the standard subtract() function, safe_subtract() returns null instead of raising an error when the result overflows.

Syntax

safe_subtract(<number1>, <number2>)

Parameters

Name Type Required Description
number1 integer, float Yes The number to subtract from (minuend).
number2 integer, float Yes The number to subtract (subtrahend).

Returns

Type: integer or float (matches input types)

Description: The safe_subtract() function returns the difference of the two input values (number1 - number2). If the result would overflow the numeric type, the function returns null instead of raising an error. If either input is null, the function returns null.

Usage notes

  • Input type: XQL doesn't support NaN or infinite values as input and these value types also can not be returned.
  • Overflow Protection: The primary advantage of safe_subtract() over subtract() is that it returns null instead of raising an error when the result exceeds the maximum or minimum value of the numeric type.
  • Null Handling: If either input expression is null, the function returns null.
  • Type Preservation: When both inputs are integers, the result is an integer. When either input is a float, the result is a float.
  • Common Use Cases: This function is used when working with potentially large numbers where overflow is a concern, such as calculating differences between large counters, timestamps represented as integers, or accumulated metrics.

Examples

Example 1: Safe subtraction of literal values

Goal: Perform safe subtraction on numeric literals, including cases that might overflow.

XQL code:

dataset = xdr_data
| limit 1
| alter result1 = safe_subtract(200, 100)
| alter result2 = safe_subtract(-9223372036854775808, 1)
| fields result1, result2

Explanation: safe_subtract(200, 100) returns 100 as expected. safe_subtract(-9223372036854775808, 1) would underflow the minimum 64-bit integer value, so it returns null instead of raising an error.

Output:

RESULT1 RESULT2
100 null

Example 2: Safe subtraction of field values

Goal: Safely subtract two numeric fields that might produce overflow.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter safe_diff = safe_subtract(event_id, numeric_value)
| fields event_id, numeric_value, safe_diff
| limit 3

Explanation: This query safely subtracts numeric_value from event_id for each record. If any combination would cause an overflow, the result is null rather than an error.

Output:

EVENT_ID NUMERIC_VALUE SAFE_DIFF
101 5.0 96.0
102 200.0 -98.0
103 50.0 53.0

Example 3: Use safe_subtract with coalesce for fallback

Goal: Perform safe subtraction and provide a fallback value when the result is null due to overflow.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| alter safe_result = safe_subtract(event_id, numeric_value)
| alter final_result = coalesce(safe_result, 0)
| fields event_id, numeric_value, safe_result, final_result
| limit 3

Explanation: This query uses safe_subtract() to subtract two fields, then applies coalesce() to replace any null results (from overflow or null inputs) with 0 as a fallback value.

Output:

EVENT_ID NUMERIC_VALUE SAFE_RESULT FINAL_RESULT
101 5.0 96.0 96.0
102 200.0 -98.0 -98.0
103 50.0 53.0 53.0