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

### `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``FLOAT64`
OUTPUT`INT64``NUMERIC``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

### `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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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`

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

### `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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``FLOAT64`

### `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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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`. 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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
`INT64``FLOAT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
OUTPUT`INT64``NUMERIC``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``FLOAT64`
`INT64``INT64``NUMERIC``FLOAT64`
`NUMERIC``NUMERIC``NUMERIC``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``FLOAT64`
OUTPUT`INT64``NUMERIC``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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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``FLOAT64`
OUTPUT`FLOAT64``NUMERIC``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" }]