GoogleSQL for Spanner supports mathematical functions. All mathematical functions have the following behaviors:
 They return
NULL
if any of the input parameters isNULL
.  They return
NaN
if any of the arguments isNaN
.
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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.options
: A named argument with a value that represents a Spannerspecific 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 thenum_leaves_to_search
option for both 2level and 3level 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 zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 aLIMIT
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
, orGROUP BY
clause.In a
SELECT
clause unless it is for ordering results in a laterORDER 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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.options
: A named argument with a value that represents a Spannerspecific 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 thenum_leaves_to_search
option for both 2level and 3level 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 zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 aLIMIT
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
, orGROUP BY
clause.In a
SELECT
clause unless it is for ordering results in a laterORDER 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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.options
: A named argument with a value that represents a Spannerspecific 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 thenum_leaves_to_search
option for both 2level and 3level 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 zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 aLIMIT
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
, orGROUP BY
clause.In a
SELECT
clause unless it is for ordering results in a laterORDER 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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.
Details
ARRAY<T>
can be used to represent a vector. Each zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.
Details
ARRAY<T>
can be used to represent a vector. Each zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 anARRAY<T>
value.vector2
: A vector that is represented by anARRAY<T>
value.
Details
ARRAY<T>
can be used to represent a vector. Each zerobased 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 dimension0
,55.0
for dimension1
,40.0
for dimension2
, and34.0
for dimension3
:[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 floatingpoint 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 floatingpoint 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  Noninteger  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.
X  Y  SAFE_ADD(X, Y) 

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 