GoogleSQL for Spanner supports mathematical functions. All mathematical functions have the following behaviors:

• They return `NULL` if any of the input parameters is `NULL`.
• They return `NaN` if any of the arguments is `NaN`.

### Categories

Category Functions
Trigonometric `ACOS`   `ACOSH`   `ASIN`   `ASINH`   `ATAN`   `ATAN2`   `ATANH`   `COS`   `COSH`   `SIN`   `SINH`   `TAN`   `TANH`
Exponential and
logarithmic
`EXP`   `LN`   `LOG`   `LOG10`
Rounding and
truncation
`CEIL`   `CEILING`   `FLOOR`   `ROUND`   `TRUNC`
Power and
root
`POW`   `POWER`   `SQRT`
Sign `ABS`   `SIGN`
Distance `APPROX_DOT_PRODUCT`   `APPROX_COSINE_DISTANCE`   `APPROX_EUCLIDEAN_DISTANCE`   `DOT_PRODUCT`   `COSINE_DISTANCE`   `EUCLIDEAN_DISTANCE`
Comparison `GREATEST`   `LEAST`
Arithmetic and error handling `DIV`   `IEEE_DIVIDE`   `IS_INF`   `IS_NAN`   `MOD`   `SAFE_ADD`   `SAFE_DIVIDE`   `SAFE_MULTIPLY`   `SAFE_NEGATE`   `SAFE_SUBTRACT`

### Function list

Name Summary
`ABS` Computes the absolute value of `X`.
`ACOS` Computes the inverse cosine of `X`.
`ACOSH` Computes the inverse hyperbolic cosine of `X`.
`APPROX_COSINE_DISTANCE` Computes the approximate cosine distance between two vectors.
`APPROX_DOT_PRODUCT` Computes the approximate dot product of two vectors.
`APPROX_EUCLIDEAN_DISTANCE` Computes the approximate Euclidean distance between two vectors.
`ASIN` Computes the inverse sine of `X`.
`ASINH` Computes the inverse hyperbolic sine of `X`.
`ATAN` Computes the inverse tangent of `X`.
`ATAN2` Computes the inverse tangent of `X/Y`, using the signs of `X` and `Y` to determine the quadrant.
`ATANH` Computes the inverse hyperbolic tangent of `X`.
`CEIL` Gets the smallest integral value that is not less than `X`.
`CEILING` Synonym of `CEIL`.
`COS` Computes the cosine of `X`.
`COSH` Computes the hyperbolic cosine of `X`.
`COSINE_DISTANCE` Computes the cosine distance between two vectors.
`DIV` Divides integer `X` by integer `Y`.
`DOT_PRODUCT` Computes the dot product of two vectors.
`EXP` Computes `e` to the power of `X`.
`EUCLIDEAN_DISTANCE` Computes the Euclidean distance between two vectors.
`FLOOR` Gets the largest integral value that is not greater than `X`.
`GREATEST` Gets the greatest value among `X1,...,XN`.
`IEEE_DIVIDE` Divides `X` by `Y`, but does not generate errors for division by zero or overflow.
`IS_INF` Checks if `X` is positive or negative infinity.
`IS_NAN` Checks if `X` is a `NaN` value.
`LEAST` Gets the least value among `X1,...,XN`.
`LN` Computes the natural logarithm of `X`.
`LOG` Computes the natural logarithm of `X` or the logarithm of `X` to base `Y`.
`LOG10` Computes the natural logarithm of `X` to base 10.
`MOD` Gets the remainder of the division of `X` by `Y`.
`POW` Produces the value of `X` raised to the power of `Y`.
`POWER` Synonym of `POW`.
`ROUND` Rounds `X` to the nearest integer or rounds `X` to `N` decimal places after the decimal point.
`SAFE_ADD` Equivalent to the addition operator (`X + Y`), but returns `NULL` if overflow occurs.
`SAFE_DIVIDE` Equivalent to the division operator (`X / Y`), but returns `NULL` if an error occurs.
`SAFE_MULTIPLY` Equivalent to the multiplication operator (`X * Y`), but returns `NULL` if overflow occurs.
`SAFE_NEGATE` Equivalent to the unary minus operator (`-X`), but returns `NULL` if overflow occurs.
`SAFE_SUBTRACT` Equivalent to the subtraction operator (`X - Y`), but returns `NULL` if overflow occurs.
`SIGN` Produces -1 , 0, or +1 for negative, zero, and positive arguments respectively.
`SIN` Computes the sine of `X`.
`SINH` Computes the hyperbolic sine of `X`.
`SQRT` Computes the square root of `X`.
`TAN` Computes the tangent of `X`.
`TANH` Computes the hyperbolic tangent of `X`.
`TRUNC` Rounds a number like `ROUND(X)` or `ROUND(X, N)`, but always rounds towards zero and never overflows.

### `ABS`

``````ABS(X)
``````

Description

Computes absolute value. Returns an error if the argument is an integer and the output value cannot be represented as the same type; this happens only for the largest negative input value, which has no positive representation.

X ABS(X)
25 25
-25 25
`+inf` `+inf`
`-inf` `+inf`

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`INT64``NUMERIC``FLOAT32``FLOAT64`

### `ACOS`

``````ACOS(X)
``````

Description

Computes the principal value of the inverse cosine of X. The return value is in the range [0,π]. Generates an error if X is a value outside of the range [-1, 1].

If X is `NUMERIC` then, the output is `FLOAT64`.

X ACOS(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### `ACOSH`

``````ACOSH(X)
``````

Description

Computes the inverse hyperbolic cosine of X. Generates an error if X is a value less than 1.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ACOSH(X)
`+inf` `+inf`
`-inf` `NaN`
`NaN` `NaN`
X < 1 Error

### `APPROX_COSINE_DISTANCE`

``````APPROX_COSINE_DISTANCE(vector1, vector2, options=>value)
``````

Description

Computes the approximate cosine distance between two vectors.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.
• `options`: A named argument with a value that represents a Spanner-specific optimization. `value` must be the following:

• `JSON'{"num_leaves_to_search": INT}'`

This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. For this argument, we recommend using a number that is 1% the total number of leaves defined in the `CREATE VECTOR INDEX` statement. The number of leaves to search is defined by the `num_leaves_to_search` option for both 2-level and 3-level trees.

If an unsupported option is provided, an error is produced.

Details

`APPROX_COSINE_DISTANCE` approximates the `COSINE_DISTANCE` between the given vectors. Approximation typically occurs when using specific indexing strategies that precompute clustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such as `WHERE s.id = 42` to your query. However, that might lead to poor recall problems because the `WHERE` filter happens after internal limits are applied. To mitigate this issue, you can increase the value of the `num_of_leaves_to_search` option.

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can't be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`. If a zero vector is encountered, an error is produced.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Limitations

• The function can only be used to sort vectors in a table with an `ORDER BY` clause.
• The function output must be the only ordering key in the `ORDER BY` clause.
• The `ORDER BY` clause must be followed by a `LIMIT` clause.
• One of the function arguments must directly reference an embedding column, and the other must be a constant expression, such as a query parameter reference.
• You can't use the function in the following ways:

• In a `WHERE`, `ON`, or `GROUP BY` clause.

• In a `SELECT` clause unless it is for ordering results in a later `ORDER BY` clause.

• As the input of another expression.

• In your query, you must specify a vector index by using the `force_index` query hint.

Return type

`FLOAT64`

Examples

In the following example, vectors are used to compute the approximate cosine distance:

In the following example, up to 1000 leaves in the vector index are searched to produce the approximate nearest two vectors using cosine distance:

``````SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=Singer_vector_index} AS s
ORDER BY APPROX_COSINE_DISTANCE(@queryVector, s.embedding, options=>JSON'{"num_leaves_to_search": 1000}')
LIMIT 2;

/*-----------+------------*
| FirstName | LastName   |
+-----------+------------+
| Marc      | Richards   |
| Catalina  | Smith      |
*-----------+------------*/
``````

### `APPROX_DOT_PRODUCT`

``````APPROX_DOT_PRODUCT(vector1, vector2, options=>value)
``````

Description

Computes the approximate dot product of two vectors.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.
• `options`: A named argument with a value that represents a Spanner-specific optimization. `value` must be the following:

• `JSON'{"num_leaves_to_search": INT}'`

This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. For this argument, we recommend using a number that is 1% the total number of leaves defined in the `CREATE VECTOR INDEX` statement. The number of leaves to search is defined by the `num_leaves_to_search` option for both 2-level and 3-level trees.

If an unsupported option is provided, an error is produced.

Details

`APPROX_DOT_PRODUCT` approximates the `DOT_PRODUCT` between two vectors. Approximation typically occurs when using specific indexing strategies that precompute clustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such as `WHERE s.id = 42` to your query. However, that might lead to poor recall problems because the `WHERE` filter happens after internal limits are applied. To mitigate this issue, you can increase the value of the `num_of_leaves_to_search` option.

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `INT64`
• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Limitations

• The function can only be used to sort vectors in a table with an `ORDER BY` clause.
• The function output must be the only ordering key in the `ORDER BY` clause.
• The `ORDER BY` clause must be followed by a `LIMIT` clause.
• One of the function arguments must directly reference an embedding column, and the other must be a constant expression, such as a query parameter reference.
• You can't use the function in the following ways:

• In a `WHERE`, `ON`, or `GROUP BY` clause.

• In a `SELECT` clause unless it is for ordering results in a later `ORDER BY` clause.

• As the input of another expression.

• In your query, you must specify a vector index by using the `force_index` query hint.

Return type

`FLOAT64`

Examples

In the following example, up to 1000 leaves in the vector index are searched to produce the approximate nearest two vectors using dot product distance:

``````SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=Singer_vector_index} AS s
ORDER BY APPROX_DOT_PRODUCT(@queryVector, s.embedding, options=>JSON'{"num_leaves_to_search": 1000}')
LIMIT 2;

/*-----------+------------*
| FirstName | LastName   |
+-----------+------------+
| Marc      | Richards   |
| Catalina  | Smith      |
*-----------+------------*/
``````

### `APPROX_EUCLIDEAN_DISTANCE`

``````APPROX_EUCLIDEAN_DISTANCE(vector1, vector2, options=>value)
``````

Description

Computes the approximate Euclidean distance between two vectors.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.
• `options`: A named argument with a value that represents a Spanner-specific optimization. `value` must be the following:

• `JSON'{"num_leaves_to_search": INT}'`

This option specifies the approximate nearest neighbors (ANN) algorithm configuration used in your query. The total number of leaves is specified when you create your vector index. For this argument, we recommend using a number that is 1% the total number of leaves defined in the `CREATE VECTOR INDEX` statement. The number of leaves to search is defined by the `num_leaves_to_search` option for both 2-level and 3-level trees.

If an unsupported option is provided, an error is produced.

Details

`APPROX_EUCLIDEAN_DISTANCE` approximates the `EUCLIDEAN_DISTANCE` between two vectors. Approximation typically occurs when using specific indexing strategies that precompute clustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such as `WHERE s.id = 42` to your query. However, that might lead to poor recall problems because the `WHERE` filter happens after internal limits are applied. To mitigate this issue, you can increase the value of the `num_of_leaves_to_search` option.

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Limitations

• The function can only be used to sort vectors in a table with an `ORDER BY` clause.
• The function output must be the only ordering key in the `ORDER BY` clause.
• The `ORDER BY` clause must be followed by a `LIMIT` clause.
• One of the function arguments must directly reference an embedding column, and the other must be a constant expression, such as a query parameter reference.
• You can't use the function in the following ways:

• In a `WHERE`, `ON`, or `GROUP BY` clause.

• In a `SELECT` clause unless it is for ordering results in a later `ORDER BY` clause.

• As the input of another expression.

• In your query, you must specify a vector index by using the `force_index` query hint.

Return type

`FLOAT64`

Examples

In the following example, vectors are used to compute the approximate Euclidean distance:

In the following example, up to 1000 leaves in the vector index are searched to produce the approximate nearest two vectors using Euclidean distance:

``````SELECT FirstName, LastName
FROM Singers@{FORCE_INDEX=Singer_vector_index} AS s
ORDER BY APPROX_EUCLIDEAN_DISTANCE(@queryVector, 0.1], s.embedding, options=>JSON'{"num_leaves_to_search": 1000}')
LIMIT 2;

/*-----------+------------*
| FirstName | LastName   |
+-----------+------------+
| Marc      | Richards   |
| Catalina  | Smith      |
*-----------+------------*/
``````

### `ASIN`

``````ASIN(X)
``````

Description

Computes the principal value of the inverse sine of X. The return value is in the range [-π/2,π/2]. Generates an error if X is outside of the range [-1, 1].

If X is `NUMERIC` then, the output is `FLOAT64`.

X ASIN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### `ASINH`

``````ASINH(X)
``````

Description

Computes the inverse hyperbolic sine of X. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ASINH(X)
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

### `ATAN`

``````ATAN(X)
``````

Description

Computes the principal value of the inverse tangent of X. The return value is in the range [-π/2,π/2]. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X ATAN(X)
`+inf` π/2
`-inf` -π/2
`NaN` `NaN`

### `ATAN2`

``````ATAN2(X, Y)
``````

Description

Calculates the principal value of the inverse tangent of X/Y using the signs of the two arguments to determine the quadrant. The return value is in the range [-π,π].

If Y is `NUMERIC` then, the output is `FLOAT64`.

X Y ATAN2(X, Y)
`NaN` Any value `NaN`
Any value `NaN` `NaN`
0.0 0.0 0.0
Positive Finite value `-inf` π
Negative Finite value `-inf`
Finite value `+inf` 0.0
`+inf` Finite value π/2
`-inf` Finite value -π/2
`+inf` `-inf` ¾π
`-inf` `-inf` -¾π
`+inf` `+inf` π/4
`-inf` `+inf` -π/4

### `ATANH`

``````ATANH(X)
``````

Description

Computes the inverse hyperbolic tangent of X. Generates an error if X is outside of the range (-1, 1).

If X is `NUMERIC` then, the output is `FLOAT64`.

X ATANH(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`
X < -1 Error
X > 1 Error

### `CEIL`

``````CEIL(X)
``````

Description

Returns the smallest integral value that is not less than X.

X CEIL(X)
2.0 2.0
2.3 3.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `CEILING`

``````CEILING(X)
``````

Description

Synonym of CEIL(X)

### `COS`

``````COS(X)
``````

Description

Computes the cosine of X where X is specified in radians. Never fails.

X COS(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### `COSH`

``````COSH(X)
``````

Description

Computes the hyperbolic cosine of X where X is specified in radians. Generates an error if overflow occurs.

If X is `NUMERIC` then, the output is `FLOAT64`.

X COSH(X)
`+inf` `+inf`
`-inf` `+inf`
`NaN` `NaN`

### `COSINE_DISTANCE`

``````COSINE_DISTANCE(vector1, vector2)
``````

Description

Computes the cosine distance between two vectors.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.

Details

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can't be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`. If a zero vector is encountered, an error is produced.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Return type

`FLOAT64`

Examples

In the following example,vectors are used to compute the cosine distance:

``````SELECT COSINE_DISTANCE([1.0, 2.0], [3.0, 4.0]) AS results;

/*----------*
| results  |
+----------+
| 0.016130 |
*----------*/
``````

The ordering of numeric values in a vector doesn't impact the results produced by this function. For example these queries produce the same results even though the numeric values in each vector is in a different order:

``````SELECT COSINE_DISTANCE([1.0, 2.0], [3.0, 4.0]) AS results;
``````
``````SELECT COSINE_DISTANCE([2.0, 1.0], [4.0, 3.0]) AS results;
``````
`````` /*----------*
| results  |
+----------+
| 0.016130 |
*----------*/
``````

In the following example, the function can't compute cosine distance against the first vector, which is a zero vector:

``````-- ERROR
SELECT COSINE_DISTANCE([0.0, 0.0], [3.0, 4.0]) AS results;
``````

Both vectors must have the same dimensions. If not, an error is produced. In the following example, the first vector has two dimensions and the second vector has three:

``````-- ERROR
SELECT COSINE_DISTANCE([9.0, 7.0], [8.0, 4.0, 5.0]) AS results;
``````

### `DIV`

``````DIV(X, Y)
``````

Description

Returns the result of integer division of X by Y. Division by zero returns an error. Division by -1 may overflow. If both inputs are `NUMERIC` and the result is overflow, then it returns a `numeric overflow` error.

X Y DIV(X, Y)
20 4 5
12 -7 -1
20 3 6
0 20 0
20 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUT`INT64``NUMERIC`
`INT64``INT64``NUMERIC`
`NUMERIC``NUMERIC``NUMERIC`

### `DOT_PRODUCT`

``````DOT_PRODUCT(vector1, vector2)
``````

Description

Computes the dot product of two vectors. The dot product is computed by summing the product of corresponding vector elements.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.

Details

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `INT64`
• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Return type

`FLOAT64`

Examples

``````SELECT DOT_PRODUCT([100], [200]) AS results

/*---------*
| results |
+---------+
| 20000   |
*---------*/
``````
``````SELECT DOT_PRODUCT([100, 10], [200, 6]) AS results

/*---------*
| results |
+---------+
| 20060   |
*---------*/
``````
``````SELECT DOT_PRODUCT([100, 10, 1], [200, 6, 2]) AS results

/*---------*
| results |
+---------+
| 20062   |
*---------*/
``````
``````SELECT DOT_PRODUCT([], []) AS results

/*---------*
| results |
+---------+
| 0       |
*---------*/
``````

### `EXP`

``````EXP(X)
``````

Description

Computes e to the power of X, also called the natural exponential function. If the result underflows, this function returns a zero. Generates an error if the result overflows.

X EXP(X)
0.0 1.0
`+inf` `+inf`
`-inf` 0.0

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `EUCLIDEAN_DISTANCE`

``````EUCLIDEAN_DISTANCE(vector1, vector2)
``````

Description

Computes the Euclidean distance between two vectors.

Definitions

• `vector1`: A vector that is represented by an `ARRAY<T>` value.
• `vector2`: A vector that is represented by an `ARRAY<T>` value.

Details

• `ARRAY<T>` can be used to represent a vector. Each zero-based index in this array represents a dimension. The value for each element in this array represents a magnitude.

`T` can represent the following and must be the same for both vectors:

• `FLOAT32`
• `FLOAT64`

In the following example vector, there are four dimensions. The magnitude is `10.0` for dimension `0`, `55.0` for dimension `1`, `40.0` for dimension `2`, and `34.0` for dimension `3`:

``````[10.0, 55.0, 40.0, 34.0]
``````
• Both vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of `0`, such as `[]` or `[0.0, 0.0]`.

• An error is produced if a magnitude in a vector is `NULL`.

• If a vector is `NULL`, `NULL` is returned.

Return type

`FLOAT64`

Examples

In the following example, vectors are used to compute the Euclidean distance:

``````SELECT EUCLIDEAN_DISTANCE([1.0, 2.0], [3.0, 4.0]) AS results;

/*----------*
| results  |
+----------+
| 2.828    |
*----------*/
``````

The ordering of magnitudes in a vector doesn't impact the results produced by this function. For example these queries produce the same results even though the magnitudes in each vector is in a different order:

``````SELECT EUCLIDEAN_DISTANCE([1.0, 2.0], [3.0, 4.0]);
``````
``````SELECT EUCLIDEAN_DISTANCE([2.0, 1.0], [4.0, 3.0]);
``````
`````` /*----------*
| results  |
+----------+
| 2.828    |
*----------*/
``````

Both vectors must have the same dimensions. If not, an error is produced. In the following example, the first vector has two dimensions and the second vector has three:

``````-- ERROR
SELECT EUCLIDEAN_DISTANCE([9.0, 7.0], [8.0, 4.0, 5.0]) AS results;
``````

### `FLOOR`

``````FLOOR(X)
``````

Description

Returns the largest integral value that is not greater than X.

X FLOOR(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -3.0
-2.8 -3.0
-2.5 -3.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `GREATEST`

``````GREATEST(X1,...,XN)
``````

Description

Returns the greatest value among `X1,...,XN`. If any argument is `NULL`, returns `NULL`. Otherwise, in the case of floating-point arguments, if any argument is `NaN`, returns `NaN`. In all other cases, returns the value among `X1,...,XN` that has the greatest value according to the ordering used by the `ORDER BY` clause. The arguments `X1, ..., XN` must be coercible to a common supertype, and the supertype must support ordering.

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

Return Data Types

Data type of the input values.

### `IEEE_DIVIDE`

``````IEEE_DIVIDE(X, Y)
``````

Description

Divides X by Y; this function never fails. Returns `FLOAT64` unless both X and Y are `FLOAT32`, in which case it returns `FLOAT32`. Unlike the division operator (/), this function does not generate errors for division by zero or overflow.

X Y IEEE_DIVIDE(X, Y)
20.0 4.0 5.0
0.0 25.0 0.0
25.0 0.0 `+inf`
-25.0 0.0 `-inf`
0.0 0.0 `NaN`
0.0 `NaN` `NaN`
`NaN` 0.0 `NaN`
`+inf` `+inf` `NaN`
`-inf` `-inf` `NaN`

### `IS_INF`

``````IS_INF(X)
``````

Description

Returns `TRUE` if the value is positive or negative infinity.

Returns `FALSE` for `NUMERIC` inputs since `NUMERIC` cannot be `INF`.

X IS_INF(X)
`+inf` `TRUE`
`-inf` `TRUE`
25 `FALSE`

### `IS_NAN`

``````IS_NAN(X)
``````

Description

Returns `TRUE` if the value is a `NaN` value.

Returns `FALSE` for `NUMERIC` inputs since `NUMERIC` cannot be `NaN`.

X IS_NAN(X)
`NaN` `TRUE`
25 `FALSE`

### `LEAST`

``````LEAST(X1,...,XN)
``````

Description

Returns the least value among `X1,...,XN`. If any argument is `NULL`, returns `NULL`. Otherwise, in the case of floating-point arguments, if any argument is `NaN`, returns `NaN`. In all other cases, returns the value among `X1,...,XN` that has the least value according to the ordering used by the `ORDER BY` clause. The arguments `X1, ..., XN` must be coercible to a common supertype, and the supertype must support ordering.

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

Return Data Types

Data type of the input values.

### `LN`

``````LN(X)
``````

Description

Computes the natural logarithm of X. Generates an error if X is less than or equal to zero.

X LN(X)
1.0 0.0
`+inf` `+inf`
`X < 0` Error

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `LOG`

``````LOG(X [, Y])
``````

Description

If only X is present, `LOG` is a synonym of `LN`. If Y is also present, `LOG` computes the logarithm of X to base Y.

X Y LOG(X, Y)
100.0 10.0 2.0
`-inf` Any value `NaN`
Any value `+inf` `NaN`
`+inf` 0.0 < Y < 1.0 `-inf`
`+inf` Y > 1.0 `+inf`
X <= 0 Any value Error
Any value Y <= 0 Error
Any value 1.0 Error

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `LOG10`

``````LOG10(X)
``````

Description

Similar to `LOG`, but computes logarithm to base 10.

X LOG10(X)
100.0 2.0
`-inf` `NaN`
`+inf` `+inf`
X <= 0 Error

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `MOD`

``````MOD(X, Y)
``````

Description

Modulo function: returns the remainder of the division of X by Y. Returned value has the same sign as X. An error is generated if Y is 0.

X Y MOD(X, Y)
25 12 1
25 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUT`INT64``NUMERIC`
`INT64``INT64``NUMERIC`
`NUMERIC``NUMERIC``NUMERIC`

### `POW`

``````POW(X, Y)
``````

Description

Returns the value of X raised to the power of Y. If the result underflows and is not representable, then the function returns a value of zero.

X Y POW(X, Y)
2.0 3.0 8.0
1.0 Any value including `NaN` 1.0
Any value including `NaN` 0 1.0
-1.0 `+inf` 1.0
-1.0 `-inf` 1.0
ABS(X) < 1 `-inf` `+inf`
ABS(X) > 1 `-inf` 0.0
ABS(X) < 1 `+inf` 0.0
ABS(X) > 1 `+inf` `+inf`
`-inf` Y < 0 0.0
`-inf` Y > 0 `-inf` if Y is an odd integer, `+inf` otherwise
`+inf` Y < 0 0
`+inf` Y > 0 `+inf`
Finite value < 0 Non-integer Error
0 Finite value < 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `POWER`

``````POWER(X, Y)
``````

Description

Synonym of `POW(X, Y)`.

### `ROUND`

``````ROUND(X [, N])
``````

Description

If only X is present, rounds X to the nearest integer. If N is present, rounds X to N decimal places after the decimal point. If N is negative, rounds off digits to the left of the decimal point. Rounds halfway cases away from zero. Generates an error if overflow occurs.

Expression Return Value
`ROUND(2.0)` 2.0
`ROUND(2.3)` 2.0
`ROUND(2.8)` 3.0
`ROUND(2.5)` 3.0
`ROUND(-2.3)` -2.0
`ROUND(-2.8)` -3.0
`ROUND(-2.5)` -3.0
`ROUND(0)` 0
`ROUND(+inf)` `+inf`
`ROUND(-inf)` `-inf`
`ROUND(NaN)` `NaN`
`ROUND(123.7, -1)` 120.0
`ROUND(1.235, 2)` 1.24

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `SAFE_ADD`

``````SAFE_ADD(X, Y)
``````

Description

Equivalent to the addition operator (`+`), but returns `NULL` if overflow occurs.

5 4 9

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `SAFE_DIVIDE`

``````SAFE_DIVIDE(X, Y)
``````

Description

Equivalent to the division operator (`X / Y`), but returns `NULL` if an error occurs, such as a division by zero error.

X Y SAFE_DIVIDE(X, Y)
20 4 5
0 20 `0`
20 0 `NULL`

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `SAFE_MULTIPLY`

``````SAFE_MULTIPLY(X, Y)
``````

Description

Equivalent to the multiplication operator (`*`), but returns `NULL` if overflow occurs.

X Y SAFE_MULTIPLY(X, Y)
20 4 80

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `SAFE_NEGATE`

``````SAFE_NEGATE(X)
``````

Description

Equivalent to the unary minus operator (`-`), but returns `NULL` if overflow occurs.

X SAFE_NEGATE(X)
+1 -1
-1 +1
0 0

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`INT64``NUMERIC``FLOAT32``FLOAT64`

### `SAFE_SUBTRACT`

``````SAFE_SUBTRACT(X, Y)
``````

Description

Returns the result of Y subtracted from X. Equivalent to the subtraction operator (`-`), but returns `NULL` if overflow occurs.

X Y SAFE_SUBTRACT(X, Y)
5 4 1

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``FLOAT64``FLOAT64`
`FLOAT32``FLOAT64``FLOAT64``FLOAT64``FLOAT64`
`FLOAT64``FLOAT64``FLOAT64``FLOAT64``FLOAT64`

### `SIGN`

``````SIGN(X)
``````

Description

Returns `-1`, `0`, or `+1` for negative, zero and positive arguments respectively. For floating point arguments, this function does not distinguish between positive and negative zero.

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`INT64``NUMERIC``FLOAT32``FLOAT64`

### `SIN`

``````SIN(X)
``````

Description

Computes the sine of X where X is specified in radians. Never fails.

X SIN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### `SINH`

``````SINH(X)
``````

Description

Computes the hyperbolic sine of X where X is specified in radians. Generates an error if overflow occurs.

If X is `NUMERIC` then, the output is `FLOAT64`.

X SINH(X)
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

### `SQRT`

``````SQRT(X)
``````

Description

Computes the square root of X. Generates an error if X is less than 0.

X SQRT(X)
`25.0` `5.0`
`+inf` `+inf`
`X < 0` Error

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`

### `TAN`

``````TAN(X)
``````

Description

Computes the tangent of X where X is specified in radians. Generates an error if overflow occurs.

X TAN(X)
`+inf` `NaN`
`-inf` `NaN`
`NaN` `NaN`

### `TANH`

``````TANH(X)
``````

Description

Computes the hyperbolic tangent of X where X is specified in radians. Does not fail.

If X is `NUMERIC` then, the output is `FLOAT64`.

X TANH(X)
`+inf` 1.0
`-inf` -1.0
`NaN` `NaN`

### `TRUNC`

``````TRUNC(X [, N])
``````

Description

If only X is present, `TRUNC` rounds X to the nearest integer whose absolute value is not greater than the absolute value of X. If N is also present, `TRUNC` behaves like `ROUND(X, N)`, but always rounds towards zero and never overflows.

X TRUNC(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
`+inf` `+inf`
`-inf` `-inf`
`NaN` `NaN`

Return Data Type

INPUT`INT64``NUMERIC``FLOAT32``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64``FLOAT64`
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]