This topic describes the vector functions supported by OceanBase AI Database and notes on their use.
Considerations
- Vectors with different dimensions are not allowed for the following operations, which will result in the error
different vector dimensions %d and %d. - When the result exceeds the floating-point value range, the error
value out of range: overflow / underflowis reported. - Dense vector indexes support L2, inner product (Inner Product), and cosine distance as index distance algorithms. HNSW and IVF indexes also support L1 (Manhattan distance), which applies only to ordinary dense vectors of FP32 (32-bit floating-point) type. Memory-based sparse vector indexes support the inner product as the distance algorithm. For details, see Overview of dense indexes and In-memory sparse vector index.
- Vector index searches support calling the
L1_distance,L2_distance,Cosine_distance, andInner_productdistance functions, as well as theinner_product_similarity,cosine_similarity, andl2_similaritysimilarity 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 the two vectors. It is calculated using the Pythagorean theorem applied 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 accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
When a one-dimensional array type parameter is used, the elements of this parameter cannot contain
NULL.
The return value is described as follows:
The return value is a
distance(double)value.When either parameter is
NULL,NULLis 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 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
L2 Squared distance is the square of the Euclidean distance (L2 Distance). It omits the square root operation from 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 accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
If a parameter is a one-dimensional array, its elements cannot contain
NULL.
The return value is described as follows:
The return value is a
distance(double)value.If either parameter is
NULL,NULLis 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
l1_distance() calculates the sum of the absolute differences between the corresponding coordinates of two points in a standard coordinate system, also known as the Manhattan distance. The calculation involves first finding the absolute difference between each pair of corresponding components of the two vectors and then summing these absolute values. A smaller distance indicates that the two vectors are closer; a distance of 0 indicates that the vectors have identical values in all dimensions.
The formula is as follows:

For example, the L1 distance between the vectors [1,2,3] and [1,1,1] is |1-1| + |2-1| + |3-1| = 3.
The function syntax is as follows:
l1_distance(vector v1, vector v2)
The parameters are described as follows:
In addition to vector types, the parameter can accept any other type that can be implicitly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
If a parameter is a one-dimensional array, its elements cannot contain
NULL.
The return value is described as follows:
The return value is a
distance(double)value, which is greater than or equal to0.If either parameter is
NULL,NULLis 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 similarity of the two vectors in direction and is independent of the vector lengths (sizes). The value 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.
Cosine similarity is calculated as follows:

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

The value range of cosine distance is [0, 2], where 0 indicates the same direction (no distance), and 2 indicates opposite directions.
The function syntax is as follows:
cosine_distance(vector v1, vector v2)
Parameter descriptions are as follows:
In addition to vector types, the parameter can accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
If a one-dimensional array type parameter is used, its elements must not contain
NULL.
Return value descriptions are as follows:
The return value is a
distance(double)value.If either parameter is
NULL,NULLis 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 describes the directional relationship and magnitude relationship between the two vectors. The inner product is calculated as follows:

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 accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
When a one-dimensional array type parameter is used, its elements 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 a
distance(double)value.If either parameter is
NULL,NULLis 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 accept any other type that can be strongly converted to a vector type, such as string type (for example,
'[1,2,3]'). - The dimensions of the two parameters must be the same.
- When a parameter is a one-dimensional array type, its elements 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 a
distance(double)value. - If either parameter is
NULL,NULLis 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. You can specify 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 accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').The dimensions of the two parameters must be the same.
When a parameter is a one-dimensional array type, its elements cannot contain
NULL.
The metric parameter specifies the distance algorithm. Valid values:
If not specified, the default algorithm is
euclidean.If specified, the valid values are:
euclidean. Indicates the Euclidean distance, which is the same as L2_distance.manhattan. Indicates the Manhattan distance, which is the same as L1_distance.cosine. Indicates cosine distance, which has the same meaning as Cosine_distance.dot. Indicates inner product, which has the same meaning as Inner_product.
The return values are described as follows:
The return value is a
distance(double)value.When any parameter is
NULL,NULLis returned.
Here is an example:
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 corresponds to a smaller distance. In vector search, you can set a similarity threshold to filter out results that do not meet the criteria.
inner_product_similarity
inner_product_similarity calculates the inner-product similarity between two vectors. The function's return value is calculated in the same way as inner_product, but it represents similarity rather than distance. The conversion from inner_product is: inner_product_similarity(v1, v2) = (1 + inner_product(v1, v2) / (vector_norm(v1) * vector_norm(v2))) / 2. The vector_norm function computes the Euclidean norm (length) of a vector, which is the Euclidean distance between the vector and the origin. For a vector v = [v1, v2, v3, ..., vn], its norm is calculated as: 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 vector types, the parameter can accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]'). - The dimensions of both parameters must be the same.
- If a parameter is a one-dimensional array, its elements cannot contain
NULL.
The return value is described as follows:
- The return value is a
similarity(double)value. A larger value indicates greater similarity. - If either parameter is
NULL,NULLis returned.
An example is as follows:
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 result is as follows:
+---------------------------------------+
| inner_product_similarity(c1, [1,2,3]) |
+---------------------------------------+
| 0.9999999552965164 |
| 0.9364357516169548 |
+---------------------------------------+
2 rows in set
Or simpler:
SELECT inner_product_similarity([1,0,0], [0,1,0]);
The 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 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 accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]'). - The dimensions of the two parameters must be the same.
- If a parameter is a one-dimensional array, its elements cannot contain
NULL.
The return value is described as follows:
- The return value is a
similarity(double)value, with a range of[0, 1]. This function linearly mapscosine_distance(with a range of[0, 2]) to[0, 1], which is different from the classical cosine similarity (with a range of[-1, 1], where0indicates orthogonality):1indicates exactly the same direction,0.5indicates orthogonality, and0indicates exactly opposite directions. A larger value indicates more similar directions. - If either parameter is
NULL,NULLis returned.
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 result is as follows:
+--------------------------------+
| cosine_similarity(c1, [1,2,3]) |
+--------------------------------+
| 0.9999999552965164 |
| 0.9364357516169548 |
+--------------------------------+
2 rows in set
Or simpler:
SELECT cosine_similarity([1,0,0], [0,1,0]);
The 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 Euclidean distance, but the return value represents similarity rather than distance. A higher similarity value indicates the vectors are more similar. It can be converted from l2_squared using the formula: 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 parameters can accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]'). - The dimensions of the two parameters must be the same.
- If a parameter is a one-dimensional array, its elements cannot contain
NULL.
The return value is described as follows:
- The return value is a
similarity(double)value. A higher value indicates greater similarity. - If either parameter is
NULL,NULLis returned.
Example:
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 result is as follows:
+----------------------------+
| l2_similarity(c1, [1,2,3]) |
+----------------------------+
| 1 |
| 0.2 |
+----------------------------+
2 rows in set
Or simpler:
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 provide addition (+), subtraction (-), and multiplication (*) operations between vector types, one-dimensional array types, and special string types. The computation is performed element by element. For example, addition is calculated as follows:

The syntax is as follows:
v1 + v2
v1 - v2
v1 * v2
The parameters are described as follows:
In addition to vector types, the parameters can also be of any other type that can be implicitly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]'). Note: The two parameters cannot both be string types; one parameter must be a vector or a one-dimensional array type.The dimensions of the two parameters must be the same.
If a one-dimensional array type parameter is present, its elements must not contain
NULL.
The return values are described as follows:
If at least one of the two parameters is a vector type, the return value is a vector of the same type as the vector parameter.
If both parameters are one-dimensional array types, the return value is of the
array(float)type.If either parameter is
NULL,NULLis returned.
An example is as follows:
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 provide comparison operations for vector types with vector types, one-dimensional array types, and special string types, including the comparison symbols =, !=, >, <, >=, and <=. The computation is performed as a lexicographical comparison of elements.
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 vector types, the parameters can also be of any other type that can be implicitly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').Notice
At least one of the two parameters must be of vector type.
The dimensions of the two parameters must be the same.
If a parameter is a one-dimensional array, its elements must not contain
NULL.
The return value is described as follows:
The return value is of the bool type.
If any parameter is
NULL,NULLis 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
Notice
Vector columns cannot be used as GROUP BY conditions, nor is DISTINCT supported.
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 value of
sum (vector).
Here is an example:
CREATE TABLE t8(c1 vector(3));
INSERT INTO t8 VALUES('[1,2,3]');
SELECT sum(c1) FROM t8;
The return result is as follows:
+---------+
| sum(c1) |
+---------+
| [1,2,3] |
+---------+
1 row in set
Avg
The Avg function calculates the average value of a vector column 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 value of
avg (vector).Rows with
NULLvalues in this vector column are excluded.If the input parameter is empty,
NULLis returned.
An example is as follows:
CREATE TABLE t9(c1 vector(3));
INSERT INTO t9 VALUES('[1,2,3]');
SELECT avg(c1) FROM t9;
The return result is as follows:
+---------+
| avg(c1) |
+---------+
| [1,2,3] |
+---------+
1 row in set
Other common vector functions
Vector_norm
The Vector_norm function calculates the Euclidean norm (length) of a vector, which represents the Euclidean distance between the vector and the origin. The calculation formula is as follows:

The syntax is as follows:
vector_norm(vector v1)
The parameters are described as follows:
In addition to vector types, the parameter can accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[1,2,3]').When a one-dimensional array type parameter is used, the elements of this parameter must not contain
NULL.
The return value is described as follows:
Returns the norm value
norm(double).When the parameter is
NULL, returnsNULL.
An example is as follows:
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 often used to implement vector normalization. For vector indexing using the l2_distance distance type, it is recommended to normalize the 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 overly 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 vectors, the parameter can accept any other type that can be strongly converted to a vector type, including one-dimensional array types (such as
[1,2,3,..]) and string types (such as'[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.
An example is as follows:
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 AI Database also provides vector search functions specifically for semantic indexes (vector indexes created on VARCHAR columns, where the column content can be text or image URLs):
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 search using vectors. It supports two methods: indexed search (with theAPPROXIMATE/APPROXclause) and full-table scan (without theAPPROXIMATE/APPROXclause).
These two functions are specifically designed for semantic index scenarios. For detailed descriptions and examples, see Semantic search.