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

```sql
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**:

```sql
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**:

```sql
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**:

```sql
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 |

## Related articles

* **Stages**: [`alter`](../stages/alter), [`fields`](../stages/fields), [`limit`](../stages/limit)
* **Functions**: [`subtract()`](subtract), [`safe_add()`](safe_add), [`safe_negate()`](safe_negate), [`coalesce()`](coalesce)
* **Datasets**: [`xdr_data`](https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction)