Arithmetic operators¶

Arithmetic operators are used to generate numeric output from one or more input expressions.

The input expressions must be numeric (fixed-point or floating point), except in the following cases:

The unary operator + can take a number string, but this causes the string to be implicitly converted to its corresponding numeric value.
The binary operator - can be applied to DATE expressions.

List of arithmetic operators¶

Operator	Syntax	Description
`+` (unary)	`+a`	Returns `a`, which will cause implicit conversion of `a` to a numeric value. If `a` is a string, but the string cannot be converted to a numeric value, an error is returned.
`+`	`a + b`	Adds two numeric expressions (`a` and `b`).
`-` (unary)	`-a`	Negates the input numeric expression.
`-`	`a - b`	Subtracts one numeric expression (`b`) from another (`a`).
`-`	`a - b`	Subtracts one date expression (`b`) from another (`a`). The result is an integer number of days. Subtraction is the only arithmetic operation allowed on DATE expressions.
`*`	`a * b`	Multiplies two numeric expressions (`a` and `b`).
`/`	`a / b`	Divides one numeric expression (`a`) by another (`b`). For functions that return 0 when dividing by 0 or NULL, see DIV0 and DIV0NULL.
`%`	`a % b`	Computes the modulo of numeric expression `a` per `b`. See also MOD.

Scale and precision in arithmetic operations¶

The scale and precision of the output of an arithmetic operation depends on the scale and precision of the input(s).

This section describes the calculations Snowflake uses to preserve scale and precision in the numeric output generated by various arithmetic operations (multiplication, division, etc.). The following descriptions are used in this section:

Leading digits:

Number of digits (L) to the left of the decimal point in a numeric value.

Scale:

Number of digits (S) to the right of the decimal point in a numeric value.

Precision:

Total number of digits (P) in a numeric value, calculated as the sum of its leading digits and scale (i.e. P = L + S). Note that precision in Snowflake is always limited to 38.

Also:

Fixed-point data types (NUMBER, DECIMAL, etc.) utilize precision and scale. For example, for the DECIMAL(8,2) data type, precision is 8, scale is 2, and leading digits is 6.
Floating point data types (FLOAT, DOUBLE, REAL, etc.) utilize 8-byte doubles.

For outputs, note that these are maximum number of digits; the actual number of digits for any given output might be less.

Multiplication¶

When performing multiplication:

The number of leading digits in the output is the sum of the leading digits in both inputs.
Snowflake minimizes potential overflow (due to chained multiplication) by adding the number of digits in the scale of both inputs, up to a maximum threshold of 12 digits, unless either of the inputs has a scale larger than 12, in which case the larger input scale is used as the output scale.

In other words, assuming a multiplication operation with two inputs (L1.S1 and L2.S2), the maximum number of digits in the output are calculated as follows:

Leading digits:

L = L1 + L2

Scale:

S = min(S1 + S2, max(S1, S2, 12))

Precision:

P = L + S

Note

Snowflake performs integer multiplication for numeric values, so intermediate results might cause some overflow; however, the final output will not overflow.

Examples¶

select 10.01 n1, 1.1 n2, n1 * n2;

+-------+-----+---------+
|    N1 |  N2 | N1 * N2 |
|-------+-----+---------|
| 10.01 | 1.1 |  11.011 |
+-------+-----+---------+

select 10.001 n1, .001 n2, n1 * n2;

+--------+-------+----------+
|     I1 |    I2 |  I1 * I2 |
|--------+-------+----------|
| 10.001 | 0.001 | 0.010001 |
+--------+-------+----------+

select .1 n1, .0000000000001 n2, n1 * n2;

+-----+-----------------+-----------------+
|  N1 |              N2 |         N1 * N2 |
|-----+-----------------+-----------------|
| 0.1 | 0.0000000000001 | 0.0000000000000 |
+-----+-----------------+-----------------+

Copy

Division¶

When performing division:

The leading digits for the output is the sum of the leading digits of the numerator and the scale of the denominator.
Snowflake minimizes potential overflow in the output (due to chained division) and loss of scale by adding 6 digits to the scale of the numerator, up to a maximum threshold of 12 digits, unless the scale of the numerator is larger than 12, in which case the numerator scale is used as the output scale.

In other words, assuming a division operation with numerator L1.S1 and denominator L2.S2, the maximum number of digits in the output are calculated as follows:

Leading digits:

L = L1 + S2

Scale:

S = max(S1, min(S1 + 6, 12))

Precision:

P = L + S

If the result of the division operation exceeds the output scale, Snowflake rounds the output (rather than truncating the output).

Note

Similar to multiplication, intermediate division results might cause some overflow; however, the final output will not overflow.

Examples¶

select 2 n1, 7 n2, n1 / n2;

+----+----+----------+
| N1 | N2 |  N1 / N2 |
|----+----+----------|
|  2 |  7 | 0.285714 |
+----+----+----------+

select 10.1 n1, 2.1 n2, n1 / n2;

+------+-----+-----------+
|   N1 |  N2 |   N1 / N2 |
|------+-----+-----------|
| 10.1 | 2.1 | 4.8095238 |
+------+-----+-----------+

select 10.001 n1, .001 n2, n1 / n2;

+--------+-------+-----------------+
|     N1 |    N2 |         N1 / N2 |
|--------+-------+-----------------|
| 10.001 | 0.001 | 10001.000000000 |
+--------+-------+-----------------+

select .1 n1, .0000000000001 n2, n1 / n2;

+-----+-----------------+-----------------------+
|  N1 |              N2 |               N1 / N2 |
|-----+-----------------+-----------------------|
| 0.1 | 0.0000000000001 | 1000000000000.0000000 |
+-----+-----------------+-----------------------+

Copy

Addition and subtraction¶

For addition or subtraction:

The leading digits for the output is the largest number of leading digits of the inputs plus 1 (to preserve carried values).
The scale for the output is the largest scale of the inputs.

In other words, assuming an addition or subtraction operation has two inputs (L1.S1 and L2.S2), the maximum number of digits in the output are calculated as follows:

Leading digits:

L = max(L1, L2) + 1

Scale:

S = max(S1, S2)

Precision:

P = L + S

Other N-ary operations¶

For all other arithmetic operations with more than one numeric input, such as modulo (a % b or MOD):

The leading digits for the output is the largest number of leading digits of the inputs.
The scale for the output is the largest scale of the inputs.

In other words, assuming an n-ary operation with inputs L1.S1, L2.S2, etc., the maximum number of digits in the output are calculated as follows:

Leading digits:

L = max(L1, L2, ...)

Scale:

S = max(S1, S2, ...)

Precision:

P = L + S

Unary operations¶

Unary arithmetic operations have the same output precision and scale as the input precision and scale, except for ROUND, which allows explicitly specifying the output scale.

Bitwise operations¶

The list of supported bitwise arithmetic operations is available at Conditional expression functions.

Note:

For numeric values, bitwise operations only operate on the leading digits in the input. The output always has a scale of zero.
For binary bitwise operations, the output has the same number of leading digits as the maximum leading digits in the input.