Mathematical functions

ZetaSQL 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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`

`IS_INF`

IS_INF(X)

Description

Returns TRUE if the value is positive or negative infinity.

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.

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

`IEEE_DIVIDE`

IEEE_DIVIDE(X, Y)

Description

Divides X by Y; this function never fails. Returns DOUBLE unless both X and Y are FLOAT, in which case it returns FLOAT. 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`

`RAND`

RAND()

Description

Generates a pseudo-random value of type DOUBLE in the range of [0, 1), inclusive of 0 and exclusive of 1.

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`POWER`

POWER(X, Y)

Description

Synonym of POW(X, Y).

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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

This function supports specifying collation.

Return Data Types

Data type of the input values.

`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

This function supports specifying collation.

Return Data Types

Data type of the input values.

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

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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`INT32`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`
`INT64`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`
`UINT32`	`INT64`	`INT64`	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`UINT64`	ERROR	ERROR	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`INT64`	`INT64`	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	ERROR	ERROR	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`INT32`	`INT64`	ERROR	ERROR	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`

`SAFE_ADD`

SAFE_ADD(X, Y)

Description

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

X	Y	SAFE_ADD(X, Y)
5	4	9

Return Data Type

INPUT	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`INT64`	`INT64`	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	ERROR	ERROR	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
`INT32`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`INT64`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT32`	`INT64`	`INT64`	`INT64`	`INT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`UINT64`	ERROR	ERROR	`INT64`	`INT64`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`
`FLOAT`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`
`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`INT32`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`
`INT64`	`INT64`	`INT64`	`INT64`	ERROR	`NUMERIC`	`BIGNUMERIC`
`UINT32`	`INT64`	`INT64`	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`UINT64`	ERROR	ERROR	`UINT64`	`UINT64`	`NUMERIC`	`BIGNUMERIC`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`NUMERIC`	`BIGNUMERIC`
`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`	`BIGNUMERIC`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

`CEILING`

CEILING(X)

Description

Synonym of CEIL(X)

`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	`INT32`	`INT64`	`UINT32`	`UINT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT`	`DOUBLE`
OUTPUT	`DOUBLE`	`DOUBLE`	`DOUBLE`	`DOUBLE`	`NUMERIC`	`BIGNUMERIC`	`DOUBLE`	`DOUBLE`

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

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

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

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.

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

`COT`

COT(X)

Description

Computes the cotangent for the angle of X, where X is specified in radians. X can be any data type that coerces to DOUBLE. Supports the SAFE. prefix.

X	COT(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
`0`	`Error`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT COT(1) AS a, SAFE.COT(0) AS b;

+---------------------+------+
| a                   | b    |
+---------------------+------+
| 0.64209261593433065 | NULL |
+---------------------+------+

`COTH`

COTH(X)

Description

Computes the hyperbolic cotangent for the angle of X, where X is specified in radians. X can be any data type that coerces to DOUBLE. Supports the SAFE. prefix.

X	COTH(X)
`+inf`	`1`
`-inf`	`-1`
`NaN`	`NaN`
`0`	`Error`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT COTH(1) AS a, SAFE.COTH(0) AS b;

+----------------+------+
| a              | b    |
+----------------+------+
| 1.313035285499 | NULL |
+----------------+------+

`CSC`

CSC(X)

Description

Computes the cosecant of the input angle, which is in radians. X can be any data type that coerces to DOUBLE. Supports the SAFE. prefix.

X	CSC(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
`0`	`Error`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT CSC(100) AS a, CSC(-1) AS b, SAFE.CSC(0) AS c;

+----------------+-----------------+------+
| a              | b               | c    |
+----------------+-----------------+------+
| -1.97485753142 | -1.188395105778 | NULL |
+----------------+-----------------+------+

`CSCH`

CSCH(X)

Description

Computes the hyperbolic cosecant of the input angle, which is in radians. X can be any data type that coerces to DOUBLE. Supports the SAFE. prefix.

X	CSCH(X)
`+inf`	`0`
`-inf`	`0`
`NaN`	`NaN`
`0`	`Error`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT CSCH(0.5) AS a, CSCH(-2) AS b, SAFE.CSCH(0) AS c;

+----------------+----------------+------+
| a              | b              | c    |
+----------------+----------------+------+
| 1.919034751334 | -0.27572056477 | NULL |
+----------------+----------------+------+

`SEC`

SEC(X)

Description

Computes the secant for the angle of X, where X is specified in radians. X can be any data type that coerces to DOUBLE.

X	SEC(X)
`+inf`	`NaN`
`-inf`	`NaN`
`NaN`	`NaN`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT SEC(100) AS a, SEC(-1) AS b;

+----------------+---------------+
| a              | b             |
+----------------+---------------+
| 1.159663822905 | 1.85081571768 |
+----------------+---------------+

`SECH`

SECH(X)

Description

Computes the hyperbolic secant for the angle of X, where X is specified in radians. X can be any data type that coerces to DOUBLE. Never produces an error.

X	SECH(X)
`+inf`	`0`
`-inf`	`0`
`NaN`	`NaN`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT SECH(0.5) AS a, SECH(-2) AS b, SECH(100) AS c;

+----------------+----------------+---------------------+
| a              | b              | c                   |
+----------------+----------------+---------------------+
| 0.88681888397  | 0.265802228834 | 7.4401519520417E-44 |
+----------------+----------------+---------------------+

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

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

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

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.

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

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

X	TANH(X)
`+inf`	1.0
`-inf`	-1.0
`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.

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

`ATANH`

ATANH(X)

Description

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

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

`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 [-π,π].

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

`CBRT`

CBRT(X)

Description

Computes the cube root of X. X can be any data type that coerces to DOUBLE. Supports the SAFE. prefix.

X	CBRT(X)
`+inf`	`inf`
`-inf`	`-inf`
`NaN`	`NaN`
`0`	`0`
`NULL`	`NULL`

Return Data Type

DOUBLE

Example

SELECT CBRT(27) AS cube_root;

+--------------------+
| cube_root          |
+--------------------+
| 3.0000000000000004 |
+--------------------+

Files

mathematical_functions.md

Latest commit

History

mathematical_functions.md

File metadata and controls

Mathematical functions

ABS

SIGN

IS_INF

IS_NAN

IEEE_DIVIDE

RAND

SQRT

POW

POWER

EXP

LN

LOG

LOG10

GREATEST

LEAST

DIV

SAFE_DIVIDE

SAFE_MULTIPLY

SAFE_NEGATE

SAFE_ADD

SAFE_SUBTRACT

MOD

ROUND

TRUNC

CEIL

CEILING

FLOOR

COS

COSH

ACOS

ACOSH

COT

COTH

CSC

CSCH

SEC

SECH

SIN

SINH

ASIN

ASINH

TAN

TANH

ATAN

ATANH

ATAN2

CBRT

`ABS`

`SIGN`

`IS_INF`

`IS_NAN`

`IEEE_DIVIDE`

`RAND`

`SQRT`

`POW`

`POWER`

`EXP`

`LN`

`LOG`

`LOG10`

`GREATEST`

`LEAST`

`DIV`

`SAFE_DIVIDE`

`SAFE_MULTIPLY`

`SAFE_NEGATE`

`SAFE_ADD`

`SAFE_SUBTRACT`

`MOD`

`ROUND`

`TRUNC`

`CEIL`

`CEILING`

`FLOOR`

`COS`

`COSH`

`ACOS`

`ACOSH`

`COT`

`COTH`

`CSC`

`CSCH`

`SEC`

`SECH`

`SIN`

`SINH`

`ASIN`

`ASINH`

`TAN`

`TANH`

`ATAN`

`ATANH`

`ATAN2`

`CBRT`