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

• They returnNULL if any of the input parameters isNULL.
• They returnNaN if any of the arguments isNaN.

Categories

Function list

ABS

ABS(X)

Description

Computes absolute value. Returns an error if the argument is an integer and theoutput value can't be represented as the same type; this happens only for thelargest negative input value, which has no positive representation.

Return Data Type

ACOS

ACOS(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

ACOSH

ACOSH(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

APPROX_COSINE_DISTANCE

APPROX_COSINE_DISTANCE(vector1,vector2,options=>value)

Description

Computes the approximatecosine distance between twovectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

• options: A named argument with a value that represents aSpanner-specific optimization.value must be the following:

  • JSON'{"num_leaves_to_search": INT}'

  This option specifies the approximate nearest neighbors (ANN) algorithmconfiguration used in your query. The total number of leaves is specifiedwhen you create your vector index. For this argument, we recommend usinga number that's 1% the total number of leaves defined in theCREATE VECTOR INDEX statement. The number of leaves to search is definedby thenum_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 theCOSINE_DISTANCE between the given vectors. Approximationtypically occurs when using specific indexing strategies that precomputeclustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such asWHERE s.id = 42 to your query. However, thatmight lead to poor recall problems because theWHERE filter happens afterinternal limits are applied. To mitigate this issue, you can increase thevalue of thenum_of_leaves_to_search option.

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

  T can represent the following and must be the same for bothvectors:

  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can't be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, 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 isNULL.

• If a vector isNULL,NULL is returned.

Limitations

• The function can only be used to sort vectors in a table with anORDER BYclause.
• The function output must be the only ordering key in theORDER BY clause.
• TheORDER 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 parameterreference.

• You can't use the function in the following ways:

  • In aWHERE,ON, orGROUP BY clause.

  • In aSELECT clause unless it's for ordering results in a laterORDER BY clause.

  • As the input of another expression.

Return type

FLOAT64

Examples

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

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

SELECTFirstName,LastNameFROMSingers@{FORCE_INDEX=Singer_vector_index}ASsORDERBYAPPROX_COSINE_DISTANCE(@queryVector,s.embedding,options=>JSON'{"num_leaves_to_search": 1000}')LIMIT2;/*-----------+------------+ | FirstName | LastName   | +-----------+------------+ | Marc      | Richards   | | Catalina  | Smith      | +-----------+------------*/

APPROX_DOT_PRODUCT

APPROX_DOT_PRODUCT(vector1,vector2,options=>value)

Description

Computes the approximatedot product of two vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

• options: A named argument with a value that represents aSpanner-specific optimization.value must be the following:

  • JSON'{"num_leaves_to_search": INT}'

  This option specifies the approximate nearest neighbors (ANN) algorithmconfiguration used in your query. The total number of leaves is specifiedwhen you create your vector index. For this argument, we recommend usinga number that's 1% the total number of leaves defined in theCREATE VECTOR INDEX statement. The number of leaves to search is definedby thenum_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 theDOT_PRODUCT between twovectors. Approximation typically occurs when using specific indexing strategiesthat precompute clustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such asWHERE s.id = 42 to your query. However, thatmight lead to poor recall problems because theWHERE filter happens afterinternal limits are applied. To mitigate this issue, you can increase thevalue of thenum_of_leaves_to_search option.

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

  T can represent the following and must be the same for bothvectors:

  • INT64
  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0].

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Limitations

• The function can only be used to sort vectors in a table with anORDER BYclause.
• The function output must be the only ordering key in theORDER BY clause.
• TheORDER 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 parameterreference.

• You can't use the function in the following ways:

  • In aWHERE,ON, orGROUP BY clause.

  • In aSELECT clause unless it's for ordering results in a laterORDER BY clause.

  • As the input of another expression.

Return type

FLOAT64

Examples

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

SELECTFirstName,LastNameFROMSingers@{FORCE_INDEX=Singer_vector_index}ASsORDERBYAPPROX_DOT_PRODUCT(@queryVector,s.embedding,options=>JSON'{"num_leaves_to_search": 1000}')DESCLIMIT2;/*-----------+------------+ | FirstName | LastName   | +-----------+------------+ | Marc      | Richards   | | Catalina  | Smith      | +-----------+------------*/

APPROX_EUCLIDEAN_DISTANCE

APPROX_EUCLIDEAN_DISTANCE(vector1,vector2,options=>value)

Description

Computes the approximateEuclidean distance betweentwo vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

• options: A named argument with a value that represents aSpanner-specific optimization.value must be the following:

  • JSON'{"num_leaves_to_search": INT}'

  This option specifies the approximate nearest neighbors (ANN) algorithmconfiguration used in your query. The total number of leaves is specifiedwhen you create your vector index. For this argument, we recommend usinga number that's 1% the total number of leaves defined in theCREATE VECTOR INDEX statement. The number of leaves to search is definedby thenum_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 theEUCLIDEAN_DISTANCE between two vectors. Approximationtypically occurs when using specific indexing strategies that precomputeclustering.

Query results across invocations aren't guaranteed to repeat.

You can add a filter such asWHERE s.id = 42 to your query. However, thatmight lead to poor recall problems because theWHERE filter happens afterinternal limits are applied. To mitigate this issue, you can increase thevalue of thenum_of_leaves_to_search option.

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

  T can represent the following and must be the same for bothvectors:

  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0].

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Limitations

• The function can only be used to sort vectors in a table with anORDER BYclause.
• The function output must be the only ordering key in theORDER BY clause.
• TheORDER 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 parameterreference.

• You can't use the function in the following ways:

  • In aWHERE,ON, orGROUP BY clause.

  • In aSELECT clause unless it's for ordering results in a laterORDER BY clause.

  • As the input of another expression.

Return type

FLOAT64

Examples

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

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

SELECTFirstName,LastNameFROMSingers@{FORCE_INDEX=Singer_vector_index}ASsORDERBYAPPROX_EUCLIDEAN_DISTANCE(@queryVector,0.1],s.embedding,options=>JSON'{"num_leaves_to_search": 1000}')LIMIT2;/*-----------+------------+ | FirstName | LastName   | +-----------+------------+ | Marc      | Richards   | | Catalina  | Smith      | +-----------+------------*/

ASIN

ASIN(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

ASINH

ASINH(X)

Description

Computes the inverse hyperbolic sine of X. Doesn't fail.

If X isNUMERICthen, the output isFLOAT64.

ATAN

ATAN(X)

Description

Computes the principal value of the inverse tangent of X. The return value isin the range [-π/2,π/2]. Doesn't fail.

If X isNUMERICthen, the output isFLOAT64.

ATAN2

ATAN2(X,Y)

Description

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

If Y isNUMERICthen, the output isFLOAT64.

ATANH

ATANH(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

CEIL

CEIL(X)

Description

Returns the smallest integral value that isn't less than X.

Return Data Type

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.

COSH

COSH(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

COSINE_DISTANCE

COSINE_DISTANCE(vector1,vector2)

Description

Computes thecosine distance between two vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

Details

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

  T can represent the following and must be the same for bothvectors:

  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can't be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, 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 isNULL.

• If a vector isNULL,NULL is returned.

Return type

FLOAT64

Examples

In the following example,vectorsare used to compute the cosine distance:

SELECTCOSINE_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;/*----------+ | results  | +----------+ | 0.016130 | +----------*/

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

SELECTCOSINE_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;

SELECTCOSINE_DISTANCE([2.0,1.0],[4.0,3.0])ASresults;

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

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

-- ERRORSELECTCOSINE_DISTANCE([0.0,0.0],[3.0,4.0])ASresults;

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

-- ERRORSELECTCOSINE_DISTANCE([9.0,7.0],[8.0,4.0,5.0])ASresults;

DIV

DIV(X,Y)

Description

Returns the result of integer division of X by Y. Division by zero returnsan error. Division by -1 may overflow. Ifboth inputs areNUMERIC and the result is overflow,then it returns anumeric overflow error.

Return Data Type

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

DOT_PRODUCT

DOT_PRODUCT(vector1,vector2)

Description

Computes thedot product of two vectors. Thedot product is computed by summing the product of correspondingvector elements.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

Details

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

  T can represent the following and must be the same for bothvectors:

  • INT64
  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0].

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Return type

FLOAT64

Examples

SELECTDOT_PRODUCT([100],[200])ASresults/*---------+ | results | +---------+ | 20000   | +---------*/

SELECTDOT_PRODUCT([100,10],[200,6])ASresults/*---------+ | results | +---------+ | 20060   | +---------*/

SELECTDOT_PRODUCT([100,10,1],[200,6,2])ASresults/*---------+ | results | +---------+ | 20062   | +---------*/

SELECTDOT_PRODUCT([],[])ASresults/*---------+ | results | +---------+ | 0       | +---------*/

EXP

EXP(X)

Description

Computese to the power of X, also called the natural exponential function. Ifthe result underflows, this function returns a zero. Generates an error if theresult overflows.

Return Data Type

EUCLIDEAN_DISTANCE

EUCLIDEAN_DISTANCE(vector1,vector2)

Description

Computes theEuclidean distance between two vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value.
• vector2: A vector that's represented by anARRAY<T> value.

Details

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

  T can represent the following and must be the same for bothvectors:

  • FLOAT32
  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• Both vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0].

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Return type

FLOAT64

Examples

In the following example, vectorsare used to compute the Euclidean distance:

SELECTEUCLIDEAN_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;/*----------+ | results  | +----------+ | 2.828    | +----------*/

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

SELECTEUCLIDEAN_DISTANCE([1.0,2.0],[3.0,4.0]);

SELECTEUCLIDEAN_DISTANCE([2.0,1.0],[4.0,3.0]);

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

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

-- ERRORSELECTEUCLIDEAN_DISTANCE([9.0,7.0],[8.0,4.0,5.0])ASresults;

FLOOR

FLOOR(X)

Description

Returns the largest integral value that isn't greater than X.

Return Data Type

GREATEST

GREATEST(X1,...,XN)

Description

Returns the greatest value amongX1,...,XN. If any argument isNULL, returnsNULL. Otherwise, in the case of floating-point arguments, if any argument isNaN, returnsNaN. In all other cases, returns the value amongX1,...,XNthat has the greatest value according to the ordering used by theORDER BYclause. The argumentsX1, ..., XN must be coercible to a common supertype, andthe supertype must support ordering.

Return Data Types

Data type of the input values.

IEEE_DIVIDE

IEEE_DIVIDE(X,Y)

Description

Divides X by Y; this function never fails. ReturnsFLOAT64 unlessboth X and Y areFLOAT32, in which case it returnsFLOAT32. Unlike the division operator (/),this function doesn't generate errors for division by zero or overflow.

IS_INF

IS_INF(X)

Description

ReturnsTRUE if the value is positive or negative infinity.

ReturnsFALSE forNUMERICinputs sinceNUMERIC can't beINF.

IS_NAN

IS_NAN(X)

Description

ReturnsTRUE if the value is aNaN value.

ReturnsFALSE forNUMERIC inputs sinceNUMERIC can't beNaN.

LEAST

LEAST(X1,...,XN)

Description

Returns the least value amongX1,...,XN. If any argument isNULL, returnsNULL. Otherwise, in the case of floating-point arguments, if any argument isNaN, returnsNaN. In all other cases, returns the value amongX1,...,XNthat has the least value according to the ordering used by theORDER BYclause. The argumentsX1, ..., XN must be coercible to a common supertype, andthe supertype must support ordering.

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 orequal to zero.

Return Data Type

LOG

LOG(X[,Y])

Description

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

Return Data Type

LOG10

LOG10(X)

Description

Similar toLOG, but computes logarithm to base 10.

Return Data Type

MOD

MOD(X,Y)

Description

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

Return Data Type

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

POW

POW(X,Y)

Description

Returns the value of X raised to the power of Y. If the result underflows andisn't representable, then the function returns a value of zero.

Return Data Type

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

POWER

POWER(X,Y)

Description

Synonym ofPOW(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 casesaway from zero. Generates an error if overflow occurs.

Return Data Type

SAFE_ADD

SAFE_ADD(X,Y)

Description

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

Return Data Type

SAFE_DIVIDE

SAFE_DIVIDE(X,Y)

Description

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

Return Data Type

SAFE_MULTIPLY

SAFE_MULTIPLY(X,Y)

Description

Equivalent to the multiplication operator (*), but returnsNULL if overflow occurs.

Return Data Type

SAFE_NEGATE

SAFE_NEGATE(X)

Description

Equivalent to the unary minus operator (-), but returnsNULL if overflow occurs.

Return Data Type

SAFE_SUBTRACT

SAFE_SUBTRACT(X,Y)

Description

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

Return Data Type

SIGN

SIGN(X)

Description

Returns-1,0, or+1 for negative, zero and positive argumentsrespectively. For floating point arguments, this function doesn't distinguishbetween positive and negative zero.

Return Data Type

SIN

SIN(X)

Description

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

SINH

SINH(X)

Description

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

If X isNUMERICthen, the output isFLOAT64.

SQRT

SQRT(X)

Description

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

Return Data Type

TAN

TAN(X)

Description

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

TANH

TANH(X)

Description

Computes the hyperbolic tangent of X where X is specified in radians. Doesn'tfail.

If X isNUMERICthen, the output isFLOAT64.

TRUNC

TRUNC(X[,N])

Description

If only X is present,TRUNC rounds X to the nearest integer whose absolutevalue isn't greater than the absolute value of X. If N is also present,TRUNCbehaves likeROUND(X, N), but always rounds towards zero and never overflows.

Return Data Type