Use SQL functions

This topic describes the vector functions supported by OceanBase Database and notes on their use.

Considerations

• Operations on vectors with different dimensions return the error different vector dimensions %d and %d.
• When the result exceeds the floating-point value range, the error value out of range: overflow / underflow is returned.
• Dense vector indexes support L2, inner product, and cosine distance as index distance algorithms. For details, see HNSW series indexes and IVF series indexes. Sparse vector indexes support inner product and negative inner product as index distance algorithms.
• Vector index searches support calling the L2_distance, Cosine_distance, Inner_product, and Negative_inner_product distance functions, as well as the inner_product_similarity, cosine_similarity, and l2_similarity similarity functions described in this document.

Distance functions

Distance functions are used to calculate the distance between two vectors. The specific calculation method varies depending on the distance algorithm.

L2_distance

The Euclidean distance reflects the distance between the coordinates of the compared vectors—basically, the straight-line distance between two vectors. It is calculated by applying the Pythagorean theorem to the vector coordinates:

The function syntax is as follows:

l2_distance(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is used, the elements of this parameter cannot contain NULL.

The return value is described as follows:

• The return value is distance(double).

• When either parameter is NULL, NULL is returned.

An example is as follows:

CREATE TABLE t1(c1 vector(3));
INSERT INTO t1 VALUES('[1,2,3]');
SELECT l2_distance(c1, [1,2,3]), l2_distance([1,2,3],[1,1,1]), l2_distance('[1,1,1]','[1,2,3]') FROM t1;

The return result is as follows:

+--------------------------+------------------------------+----------------------------------+
| l2_distance(c1, [1,2,3]) | l2_distance([1,2,3],[1,1,1]) | l2_distance('[1,1,1]','[1,2,3]') |
+--------------------------+------------------------------+----------------------------------+
|                        0 |             2.23606797749979 |                 2.23606797749979 |
+--------------------------+------------------------------+----------------------------------+
1 row in set

L2_squared

The L2 squared distance is the square of the Euclidean distance (L2 Distance). It omits the square root operation in the Euclidean distance formula, thereby reducing the computational cost while maintaining the relative order of distances. The calculation method is as follows:

The syntax is as follows:

l2_squared(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is used, the elements of this parameter cannot contain NULL.

The return values are described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t1(c1 vector(3));
INSERT INTO t1 VALUES('[1,2,3]');
SELECT l2_squared(c1, [1,2,3]), l2_squared([1,2,3],[1,1,1]), l2_squared('[1,1,1]','[1,2,3]') FROM t1;

The return result is as follows:

+-------------------------+-----------------------------+---------------------------------+
| l2_squared(c1, [1,2,3]) | l2_squared([1,2,3],[1,1,1]) | l2_squared('[1,1,1]','[1,2,3]') |
+-------------------------+-----------------------------+---------------------------------+
|                       0 |                           5 |                               5 |
+-------------------------+-----------------------------+---------------------------------+
1 row in set

L1_distance

The Manhattan distance calculates the sum of the absolute differences between the corresponding coordinates of two points in a standard coordinate system. The formula is as follows:

The function syntax is as follows:

l1_distance(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• If a parameter is a single-level array, its elements cannot contain NULL.

The return values are described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t2(c1 vector(3));
INSERT INTO t2 VALUES('[1,2,3]');
INSERT INTO t2 VALUES('[1,1,1]');
SELECT l1_distance(c1, [1,2,3]) FROM t2;

The return result is as follows:

+--------------------------+
| l1_distance(c1, [1,2,3]) |
+--------------------------+
|                        0 |
|                        3 |
+--------------------------+
2 rows in set

Cosine_distance

Cosine similarity measures the angular difference between two vectors. It reflects the directional similarity of the two vectors and is independent of the vector lengths (sizes). The range of cosine similarity is [-1, 1], where 1 indicates the vectors are in exactly the same direction, 0 indicates orthogonality, and -1 indicates opposite directions.

The cosine similarity is calculated as follows:

Since a cosine similarity measure closer to 1 indicates greater similarity, the cosine distance (or cosine dissimilarity) is sometimes used as a way to measure distance between vectors. The cosine distance can be calculated by subtracting the cosine similarity from 1:

The range of cosine distance is [0, 2], where 0 indicates exactly the same direction (no distance), and 2 indicates exactly opposite directions.

The function syntax is as follows:

cosine_distance(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is present, its elements cannot contain NULL.

The return value is described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

Examples are as follows:

CREATE TABLE t3(c1 vector(3));
INSERT INTO t3 VALUES('[1,2,3]');
INSERT INTO t3 VALUES('[1,2,1]');
SELECT cosine_distance(c1, [1,2,3]) FROM t3;

+------------------------------+
| cosine_distance(c1, [1,2,3]) |
+------------------------------+
|                            0 |
|          0.12712843905603044 |
+------------------------------+
2 rows in set

Inner_product

The inner product, also known as the dot product or scalar product, represents a type of multiplication between two vectors. Geometrically, the inner product indicates the direction and magnitude relationship between the two vectors. The formula for calculating the inner product is:

The syntax is as follows:

inner_product(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is used, its elements cannot contain NULL.

• When using this function with sparse vectors, one parameter can be a string in sparse vector format, such as c2,'{1:2.4}'; both parameters being strings is not supported.

The return value is described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

A dense vector example is as follows:

CREATE TABLE t4(c1 vector(3));
INSERT INTO t4 VALUES('[1,2,3]');
INSERT INTO t4 VALUES('[1,2,1]');
SELECT inner_product(c1, [1,2,3]) FROM t4;

The return result is as follows:

+----------------------------+
| inner_product(c1, [1,2,3]) |
+----------------------------+
|                         14 |
|                          8 |
+----------------------------+
2 rows in set

A sparse vector example is as follows:

CREATE TABLE t4(c1 INT, c2 SPARSEVECTOR, c3 SPARSEVECTOR);
INSERT INTO t4 VALUES(1, '{1:1.1, 2:2.2}', '{1:2.4}');
INSERT INTO t4 VALUES(2, '{1:1.5, 3:3.6}', '{4:4.5}');
SELECT inner_product(c2,c3) FROM t4;

The return result is as follows:

+----------------------+
| inner_product(c2,c3) |
+----------------------+
|    2.640000104904175 |
|                    0 |
+----------------------+
2 rows in set

Negative_inner_product

Negative_inner_product calculates the negative inner product between two vectors, using the following formula:

The syntax is as follows:

negative_inner_product(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is used, the elements of this parameter cannot contain NULL.

• When using this function with sparse vectors, one of the parameters can be a string in sparse vector format, such as c2,'{1:2.4}'; both parameters being strings is not supported.

The return value is described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

A dense vector example is as follows:

CREATE TABLE t5(c1 vector(3));
INSERT INTO t5 VALUES('[1,2,3]');
INSERT INTO t5 VALUES('[1,2,1]');
SELECT negative_inner_product(c1, [1,2,3]) FROM t5;

The return result is as follows:

+-------------------------------------+
| negative_inner_product(c1, [1,2,3]) |
+-------------------------------------+
|                                 -14 |
|                                  -8 |
+-------------------------------------+
2 rows in set

A sparse vector example is as follows:

CREATE TABLE t5(c1 INT, c2 SPARSEVECTOR, c3 SPARSEVECTOR);
INSERT INTO t5 VALUES(1, '{1:1.1, 2:2.2}', '{1:2.4}');
INSERT INTO t5 VALUES(2, '{1:1.5, 3:3.6}', '{4:4.5}');
SELECT negative_inner_product(c2,c3) FROM t5;

The return result is as follows:

+-------------------------------+
| negative_inner_product(c2,c3) |
+-------------------------------+
|            -2.640000104904175 |
|                             0 |
+-------------------------------+
2 rows in set

Vector_distance

vector_distance calculates the distance between two vectors by specifying parameters to choose different distance algorithms.

The syntax is as follows:

vector_distance(vector v1, vector v2 [, string metric])

The vector v1/v2 parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• The dimensions of the two parameters must be the same.

• When a single-level array type parameter is used, its elements cannot contain NULL.

The metric parameter specifies the distance algorithm. Valid values are:

• If not specified, the default algorithm is euclidean.

• If specified, the valid values are:

  • euclidean. Indicates Euclidean distance, which has the same meaning as L2_distance.

  • manhattan. Indicates Manhattan distance, which has the same meaning as L1_distance.

  • cosine. Indicates cosine distance, which has the same meaning as Cosine_distance.

  • dot. Indicates dot product, which has the same meaning as Inner_product.

The return value is described as follows:

• The return value is distance(double).

• If either parameter is NULL, NULL is returned.

An example is as follows:

CREATE TABLE t5(c1 vector(3));
INSERT INTO t5 VALUES('[1,2,3]');
INSERT INTO t5 VALUES('[1,2,1]');
SELECT vector_distance(c1, [1,2,3], euclidean) FROM t5;

The return result is as follows:

+-----------------------------------------+
| vector_distance(c1, [1,2,3], euclidean) |
+-----------------------------------------+
|                                       0 |
|                                       2 |
+-----------------------------------------+
2 rows in set

Similarity functions

Similarity functions measure the degree of similarity between two vectors. A higher value indicates greater similarity. They are the inverse concept of distance functions: higher similarity means smaller distance. In vector search, you can set a similarity threshold to filter for results that meet the condition.

inner_product_similarity

inner_product_similarity calculates the dot-product similarity between two vectors. The function's return value is calculated in the same way as the dot-product distance function inner_product, but it represents similarity rather than distance. The conversion formula between inner_product and inner_product_similarity is: inner_product_similarity(v1, v2) = (1 + inner_product(v1, v2) / (vector_norm(v1) * vector_norm(v2))) / 2. The vector_norm function calculates the Euclidean norm (magnitude) of a vector, which is the Euclidean distance between the vector and the origin. For a vector v = [v1, v2, v3, ..., vn], the norm calculation formula is: vector_norm(v) = √(v1² + v2² + v3² + ... + vn²).

The syntax is as follows:

inner_product_similarity(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameters can be any types that can be cast to vectors, such as strings (for example, '[1,2,3]').
• The dimensions of the two parameters must be the same.
• When a parameter is a single-level array type, the elements of this parameter cannot contain NULL.

The return value is described as follows:

• The return value type is double. The result is the similarity value between the two vectors. A larger value indicates greater similarity.
• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t12(c1 vector(3));
INSERT INTO t12 VALUES('[1,2,3]');
INSERT INTO t12 VALUES('[1,2,1]');
SELECT inner_product_similarity(c1, [1,2,3]) FROM t12;

The return result is as follows:

+---------------------------------------+
| inner_product_similarity(c1, [1,2,3]) |
+---------------------------------------+
|                    0.9999999552965164 |
|                    0.9364357516169548 |
+---------------------------------------+
2 rows in set

Alternatively, run the following statement:

SELECT inner_product_similarity([1,0,0], [0,1,0]);

The return result is as follows:

+--------------------------------------------+
| inner_product_similarity([1,0,0], [0,1,0]) |
+--------------------------------------------+
|                                        0.5 |
+--------------------------------------------+
1 row in set

cosine_similarity

cosine_similarity calculates the cosine similarity between two vectors, reflecting their directional similarity and being independent of the vector lengths (sizes). The calculation formula is: cosine_similarity(v1, v2) = 1 - (cosine_distance(v1, v2) / 2).

The syntax is as follows:

cosine_similarity(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameters can be any types that can be cast to vectors, such as strings (for example, '[1,2,3]').
• The dimensions of the two parameters must be the same.
• When a parameter is a single-level array type, the elements of this parameter cannot contain NULL.

The return value is described as follows:

• The return value is of the double type and ranges from [0, 1]. This function linearly maps cosine_distance, which ranges from [0, 2], to [0, 1]. It differs from the classic cosine similarity, which ranges from [-1, 1] and is 0 for orthogonal vectors. A value of 1 indicates that the vectors point in the same direction, 0.5 indicates that they are orthogonal, and 0 indicates that they point in opposite directions. A larger value indicates more similar directions.
• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t13(c1 vector(3));
INSERT INTO t13 VALUES('[1,2,3]');
INSERT INTO t13 VALUES('[1,2,1]');
SELECT cosine_similarity(c1, [1,2,3]) FROM t13;

The return result is as follows:

+--------------------------------+
| cosine_similarity(c1, [1,2,3]) |
+--------------------------------+
|             0.9999999552965164 |
|             0.9364357516169548 |
+--------------------------------+
2 rows in set

Alternatively, run the following statement:

SELECT cosine_similarity([1,0,0], [0,1,0]);

The return result is as follows:

+-------------------------------------+
| cosine_similarity([1,0,0], [0,1,0]) |
+-------------------------------------+
|                                 0.5 |
+-------------------------------------+
1 row in set

l2_similarity

l2_similarity calculates the L2 similarity between two vectors. L2 similarity is based on the Euclidean distance, but its return value represents similarity rather than distance. A higher similarity value indicates that the two vectors are more similar. The conversion with l2_squared is: l2_similarity(v1, v2) = 1 / (1 + l2_squared(v1, v2)).

The syntax is as follows:

l2_similarity(vector v1, vector v2)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').
• The dimensions of the two parameters must be the same.
• When a single-level array type parameter is used, the elements of this parameter cannot contain NULL.

The return value is described as follows:

• The return value type is double, representing the similarity value between the two vectors. A larger value indicates greater similarity.
• If either parameter is NULL, NULL is returned.

An example is as follows:

CREATE TABLE t14(c1 vector(3));
INSERT INTO t14 VALUES('[1,2,3]');
INSERT INTO t14 VALUES('[1,2,1]');
SELECT l2_similarity(c1, [1,2,3]) FROM t14;

The return result is as follows:

+----------------------------+
| l2_similarity(c1, [1,2,3]) |
+----------------------------+
|                          1 |
|                        0.2 |
+----------------------------+
2 rows in set

Alternatively, run the following statement:

SELECT l2_similarity([1,0,0], [0,1,0]);

The return result is as follows:

+---------------------------------+
| l2_similarity([1,0,0], [0,1,0]) |
+---------------------------------+
|              0.3333333333333333 |
+---------------------------------+
1 row in set

Arithmetic functions

Arithmetic functions perform element-wise addition (+), subtraction (-), and multiplication (*) between two vectors, a vector and a single-level array or special string, two single-level arrays, or a single-level array and a special string. For example, addition is performed as follows:

The syntax is as follows:

v1 + v2
v1 - v2
v1 * v2

The parameters are described as follows:

• In addition to the vector type, the parameters can be any types that can be cast to vectors, such as strings (for example, '[1,2,3]'). Note: The two parameters cannot both be strings. If either parameter is a string, the other must be a vector.

• The dimensions of the two parameters must be the same.

• If a parameter is a single-level array, its elements cannot contain NULL.

The return value is described as follows:

• If at least one of the two parameters is of the vector type, the return value is the same vector type as the vector parameter.

• If neither parameter is a vector and both parameters are single-level arrays, the return value is of the array(float) type.

• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t6(c1 vector(3));
INSERT INTO t6 VALUES('[1,2,3]');
SELECT [1,2,3] + '[1.12,1000.0001, -1.2222]', c1 - [1,2,3] FROM t6;

The return result is as follows:

+---------------------------------------+--------------+
| [1,2,3] + '[1.12,1000.0001, -1.2222]' | c1 - [1,2,3] |
+---------------------------------------+--------------+
| [2.12,1002,1.7778]                    | [0,0,0]      |
+---------------------------------------+--------------+
1 row in set

Comparison functions

Comparison functions perform element-wise lexicographical comparisons between vectors, single-level arrays, and special strings. Supported operators are =, !=, >, <, >=, and <=.

The syntax is as follows:

v1 = v2
v1 != v2
v1 > v2
v1 < v2
v1 >= v2
v1 <= v2

The parameters are described as follows:

• In addition to the vector type, the parameters can be any types that can be cast to vectors, such as strings (for example, '[1,2,3]').

  Note

  One of the two parameters must be of the vector type.

• The dimensions of the two parameters must be the same.

• If a parameter is a single-level array, its elements cannot contain NULL.

The return value is described as follows:

• The return value is of the bool type.

• If either parameter is NULL, NULL is returned.

Here is an example:

CREATE TABLE t7(c1 vector(3));
INSERT INTO t7 VALUES('[1,2,3]');
SELECT c1 = '[1,2,3]' FROM t7;

The return result is as follows:

+----------------+
| c1 = '[1,2,3]' |
+----------------+
|              1 |
+----------------+
1 row in set

Aggregate functions

Sum

The Sum function calculates the sum of vector columns in a table by performing element-wise accumulation to obtain a sum vector.

The syntax is as follows:

sum(vector v1)

The parameters are described as follows:

• Only vector types are supported.

The return value is described as follows:

• Returns the vector sum.

An example is as follows:

CREATE TABLE t8(c1 vector(3));
INSERT INTO t8 VALUES('[1,2,3]'),('[2,3,4]'),('[3,4,5]');
SELECT sum(c1) FROM t8;

The return result is as follows:

+----------+
| sum(c1) |
+----------+
| [6,9,12] |
+----------+
1 row in set

Element-wise addition of multi-row vectors: For the first dimension, 1 + 2 + 3 = 6; for the second dimension, 2 + 3 + 4 = 9; for the third dimension, 3 + 4 + 5 = 12. Therefore, the sum is [6,9,12].

Avg

The Avg function calculates the average value of vector columns in a table.

The syntax is as follows:

avg(vector v1)

The parameters are described as follows:

• Only vector types are supported.

The return value is described as follows:

• Returns the vector average.

• Excludes NULL rows in the vector column.

• Returns NULL if the input parameter is empty.

Here is an example:

CREATE TABLE t9(c1 vector(3));
INSERT INTO t9 VALUES('[2,4,6]'),('[4,6,8]'),('[6,8,10]');
SELECT avg(c1) FROM t9;

The return result is as follows:

+---------+
| avg(c1) |
+---------+
| [4,6,8] |
+---------+
1 row in set

Calculate the element-wise average of multiple vectors: For the first dimension, (2 + 4 + 6) / 3 = 4; for the second dimension, (4 + 6 + 8) / 3 = 6; for the third dimension, (6 + 8 + 10) / 3 = 8. Therefore, the average value is [4,6,8].

Other common vector functions

Vector_norm

The Vector_norm function calculates the Euclidean norm (magnitude) of a vector, which represents the Euclidean distance between the vector and the origin. The calculation formula is:

The syntax is as follows:

vector_norm(vector v1)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

• If a single-level array type parameter is used, its elements must not contain NULL.

The return value is described as follows:

• Returns the norm(double) magnitude.

• Returns NULL if the parameter is NULL.

Here is an example:

CREATE TABLE t10(c1 vector(3));
INSERT INTO t10 VALUES('[1,2,3]');
SELECT vector_norm(c1),vector_norm([1,2,3]) FROM t10;

The return result is as follows:

+--------------------+----------------------+
| vector_norm(c1)    | vector_norm([1,2,3]) |
+--------------------+----------------------+
| 3.7416573867739413 |   3.7416573867739413 |
+--------------------+----------------------+
1 row in set

Vector normalization

Vector normalization is the process of standardizing the length (norm) of a vector to 1. The vector_norm function is commonly used to implement vector normalization.

For vector indexes that use the l2 distance type, it is recommended to normalize vectors to improve the accuracy of similarity search. After normalization, the norm of a vector is 1, and the range of the L2 distance is confined to [0, 2], thereby avoiding precision issues caused by excessively small similarity values.

For example, for the vector [3, 4]:

• Norm of the vector: vector_norm([3, 4]) = √(3² + 4²) = 5
• After normalization: [3, 4] / 5 = [0.6, 0.8]

The norm of the normalized vector is 1. Verification method:

SELECT vector_norm([0.6, 0.8]);

The return result is as follows:

+-------------------------+
| vector_norm([0.6, 0.8]) |
+-------------------------+
|       1.000000029802322 |
+-------------------------+
1 row in set

Vector_dims

The vector_dims function returns the dimensionality of a vector.

The syntax is as follows:

vector_dims(vector v1)

The parameters are described as follows:

• In addition to the vector type, the parameter can be any type that can be cast to a vector, such as a string (for example, '[1,2,3]').

The return value is described as follows:

• Returns the dimensionality value dims(int64).

• An error is reported if the parameter is NULL.

Example:

CREATE TABLE t11(c1 vector(3));
INSERT INTO t11 VALUES('[1,2,3]');
INSERT INTO t11 VALUES('[1,1,1]');
SELECT vector_dims(c1), vector_dims('[1,2,3]') FROM t11;

The return result is as follows:

+-----------------+------------------------+
| vector_dims(c1) | vector_dims('[1,2,3]') |
+-----------------+------------------------+
|               3 |                      3 |
|               3 |                      3 |
+-----------------+------------------------+
2 rows in set

Semantic index-related functions

In addition to the general vector functions mentioned above, OceanBase also provides vector search functions specifically for semantic indexes (vector indexes created on text columns):

• semantic_distance(column_name, 'query_content' [, 'query_type']): Performs vector search using raw text or an image URL. The system automatically calls the embedding model to complete the vectorization.
• semantic_vector_distance(column_name, query_vector): Performs a vector-based search. It supports two methods: index search (with the APPROXIMATE/APPROX clause) and full-table scan (without the APPROXIMATE/APPROX clause).

These two functions are specifically designed for semantic index scenarios. For detailed descriptions and examples, see Semantic index.