Vector Functions and Operators
Vector functions
The following functions are designed specifically for Vector Data Types.
Vector Distance Functions:
| Function Name | Description |
|---|---|
| VEC_L2_DISTANCE | Calculates L2 distance (Euclidean distance) between two vectors |
| VEC_COSINE_DISTANCE | Calculates the cosine distance between two vectors |
| VEC_NEGATIVE_INNER_PRODUCT | Calculates the negative of the inner product between two vectors |
| VEC_L1_DISTANCE | Calculates L1 distance (Manhattan distance) between two vectors |
Other Vector Functions:
| Function Name | Description |
|---|---|
| VEC_DIMS | Returns the dimension of a vector |
| VEC_L2_NORM | Calculates the L2 norm (Euclidean norm) of a vector |
| VEC_FROM_TEXT | Converts a string into a vector |
| VEC_AS_TEXT | Converts a vector into a string |
Extended built-in functions and operators
The following built-in functions and operators are extended, supporting operating on Vector Data Types.
Arithmetic operators:
| Name | Description |
|---|---|
+ | Vector element-wise addition operator |
- | Vector element-wise subtraction operator |
For more information about how vector arithmetic works, see Vector Data Type | Arithmetic.
Aggregate (GROUP BY) functions:
| Name | Description |
|---|---|
COUNT() | Return a count of the number of rows returned |
COUNT(DISTINCT) | Return the count of a number of different values |
MAX() | Return the maximum value |
MIN() | Return the minimum value |
Comparison functions and operators:
| Name | Description |
|---|---|
BETWEEN ... AND ... | Check whether a value is within a range of values |
COALESCE() | Return the first non-NULL argument |
= | Equal operator |
<=> | NULL-safe equal to operator |
> | Greater than operator |
>= | Greater than or equal operator |
GREATEST() | Return the largest argument |
IN() | Check whether a value is within a set of values |
IS NULL | NULL value test |
ISNULL() | Test whether the argument is NULL |
LEAST() | Return the smallest argument |
< | Less than operator |
<= | Less than or equal operator |
NOT BETWEEN ... AND ... | Check whether a value is not within a range of values |
!=, <> | Not equal operator |
NOT IN() | Check whether a value is not within a set of values |
For more information about how vectors are compared, see Vector Data Type | Comparison.
Control flow functions:
| Name | Description |
|---|---|
CASE | Case operator |
IF() | If/else construct |
IFNULL() | Null if/else construct |
NULLIF() | Return NULL if expr1 = expr2 |
Cast functions:
| Name | Description |
|---|---|
CAST() | Cast a value as a certain type |
CONVERT() | Cast a value as a certain type |
For more information about how to use CAST(), see Vector Data Type | Cast.
Full references
VEC_L2_DISTANCE
VEC_L2_DISTANCE(vector1, vector2)
Calculates the L2 distance (Euclidean distance) between two vectors using the following formula:
The two vectors must have the same dimension. Otherwise an error is returned.
Examples:
[tidb]> select VEC_L2_DISTANCE('[0,3]', '[4,0]');
+-----------------------------------+
| VEC_L2_DISTANCE('[0,3]', '[4,0]') |
+-----------------------------------+
| 5 |
+-----------------------------------+
VEC_COSINE_DISTANCE
VEC_COSINE_DISTANCE(vector1, vector2)
Calculates the cosine distance between two vectors using the following formula:
The two vectors must have the same dimension. Otherwise an error is returned.
Examples:
[tidb]> select VEC_COSINE_DISTANCE('[1, 1]', '[-1, -1]');
+-------------------------------------------+
| VEC_COSINE_DISTANCE('[1, 1]', '[-1, -1]') |
+-------------------------------------------+
| 2 |
+-------------------------------------------+
VEC_NEGATIVE_INNER_PRODUCT
VEC_NEGATIVE_INNER_PRODUCT(vector1, vector2)
Calculates the distance by using the negative of the inner product between two vectors, using the following formula:
The two vectors must have the same dimension. Otherwise an error is returned.
Examples:
[tidb]> select VEC_NEGATIVE_INNER_PRODUCT('[1,2]', '[3,4]');
+----------------------------------------------+
| VEC_NEGATIVE_INNER_PRODUCT('[1,2]', '[3,4]') |
+----------------------------------------------+
| -11 |
+----------------------------------------------+
VEC_L1_DISTANCE
VEC_L1_DISTANCE(vector1, vector2)
Calculates the L1 distance (Manhattan distance) between two vectors using the following formula:
The two vectors must have the same dimension. Otherwise an error is returned.
Examples:
[tidb]> select VEC_L1_DISTANCE('[0,0]', '[3,4]');
+-----------------------------------+
| VEC_L1_DISTANCE('[0,0]', '[3,4]') |
+-----------------------------------+
| 7 |
+-----------------------------------+
VEC_DIMS
VEC_DIMS(vector)
Returns the dimension of a vector.
Examples:
[tidb]> select VEC_DIMS('[1,2,3]');
+---------------------+
| VEC_DIMS('[1,2,3]') |
+---------------------+
| 3 |
+---------------------+
[tidb]> select VEC_DIMS('[]');
+----------------+
| VEC_DIMS('[]') |
+----------------+
| 0 |
+----------------+
VEC_L2_NORM
VEC_L2_NORM(vector)
Calculates the L2 norm (Euclidean norm) of a vector using the following formula:
Examples:
[tidb]> select VEC_L2_NORM('[3,4]');
+----------------------+
| VEC_L2_NORM('[3,4]') |
+----------------------+
| 5 |
+----------------------+
VEC_FROM_TEXT
VEC_FROM_TEXT(string)
Converts a string into a vector.
Examples:
[tidb]> select VEC_FROM_TEXT('[1,2]') + VEC_FROM_TEXT('[3,4]');
+-------------------------------------------------+
| VEC_FROM_TEXT('[1,2]') + VEC_FROM_TEXT('[3,4]') |
+-------------------------------------------------+
| [4,6] |
+-------------------------------------------------+
VEC_AS_TEXT
VEC_AS_TEXT(vector)
Converts a vector into a string.
Examples:
[tidb]> select VEC_AS_TEXT('[1.000, 2.5]');
+-------------------------------+
| VEC_AS_TEXT('[1.000, 2.5]') |
+-------------------------------+
| [1,2.5] |
+-------------------------------+
MySQL compatibility
The vector functions and the extended usage of built-in functions and operators over vector data types are TiDB specific, and are not supported in MySQL.