Arithmetic Operators

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

The input expressions must be numeric (both fixed-point and floating point numbers are allowed), except for the unary operator (+), which can take a number string, but this will cause the string to be implicitly converted to its corresponding numeric value.

In this Topic:

List of Arithmetic Operators

Operator

Syntax

Description

+

a + b

Adds two numeric expressions (a and b).

+ (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

Subtracts one numeric expression (b) from another (a).

- (unary)

-a

Negates the input numeric expression.

*

a * b

Multiplies two numeric expressions (a and b).

/

a / b

Divides one numeric expression (a) by another (b).

%

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 may 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 may 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 |
+-----+-----------------+-----------------+

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

In addition, Snowflake performs integer division with rounding.

Note

Similar to multiplication, intermediate division results may 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 |
+-----+-----------------+-----------------------+

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.