The VECTOR_SUB function subtracts the second vector from the first vector and returns a vector which is the result of that subtraction.


VECTOR_SUB(vector_expression, vector_expression)


  • vector_expression: An expression that evaluates to a vector. Vectors can be stored in SingleStore using the BLOB type (BLOB Types).

Return Type

If both arguments are BLOBs, then the return type will be by default a BLOB that contains a vector encoded as 32-bit floating point numbers (F32). Alternatively if a suffix is used, the return type will be a BLOB that contains a vector encoded using the type of the suffix.

See Using Suffixes for Other Element Types with BLOBs for information on using suffixes with vectors encoded as BLOBs.

Using VECTOR_SUB with Vectors as BLOBs

The following examples and descriptions show the use of VECTOR_ADD with arguments that are both vectors stored as BLOBs.

Example 1 - BLOB Arguments

Create a table with a column of type BLOB to store the vectors. The second column in this table, with column name vec and type BLOB, will store the vectors. This example demonstrates storing vector data using BLOBs, hence the column of type BLOB named vec.

Then use the JSON_ARRAY_PACK built-in function to easily insert properly formatted vectors.

CREATE TABLE vectors_b (id int, vec BLOB not null);
INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]')); 
INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));

The following query subtracts the vector '[0.1,0.1,0.1,0.1]' from the vectors in the vectors_b table and uses HEX to view the results in hexadecimal format.

SET @qv = JSON_ARRAY_PACK('[0.1,0.1,0.1,0.1]');
SELECT id, HEX(VECTOR_SUB(vec, @qv)) as vector_sub
FROM vectors_b
| id   | vector_sub                       |
|    1 | 000000003333333FCDCCCC3DC3F5E83E |
|    2 | 3333B33E6766E63E713DCA3ECDCCCC3E |

Formatting Binary Vector Data for BLOBs

When using the BLOB type for vector operations, vector data can be formatted using JSON_ARRAY_PACK. If your vector data is already in a packed binary format, you can load that data into the BLOBs. The data must be encoded as a BLOB containing packed numbers in little-endian byte order. Vectors stored as BLOBs can be of any length; however, the input blob length must be divisible by the size of the packed vector elements (1, 2, 4 , or 8 bytes, depending on the vector element).

Last modified: April 8, 2024

Was this article helpful?