Sign inTry Free

VECTOR_MUL

On this page

The VECTOR_MUL function multiples two vectors element by element and returns a vector which is the result of that multiplication.

Syntax

VECTOR_MUL(vector_expression, vector_expression)

Arguments

  • 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 more information on using suffixes with vectors encoded as BLOBs.

Remarks

  • The default format for vector element storage and processing is a 32-bit floating-point number (F32). When the inputs are BLOBs, the function assumes the inputs are vectors encoded as 32-bit floating-point numbers.

  • When a suffix is used, the function will interpret the inputs as vectors encoded as specified by the suffix. See Using Suffixes for Other Element Types with BLOBs for more information.

Using VECTOR_MUL with Vectors as Blobs

The following examples and descriptions show the use of VECTOR_MUL 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 multiplies the vectors in the vectors_b table by the vector '[0.1,0.1,0.1,0.1]' and uses JSON_ARRAY_UNPACK to see the results in JSON format.

SET @qv = JSON_ARRAY_PACK('[0.1,0.1,0.1,0.1]');


SELECT id, JSON_ARRAY_UNPACK(VECTOR_MUL(vec, @qv)) AS vector_mul
FROM vectors_b
ORDER BY id;
+------+-------------------------------------------------------+
| id   | vector_mul                                            |
+------+-------------------------------------------------------+
|    1 | [0.0100000007,0.0800000057,0.0200000014,0.0555000007] |
|    2 | [0.0449999981,0.0550000034,0.0494999997,0.0500000007] |
+------+-------------------------------------------------------+

JSON_ARRAY_UNPACK was used here to output the vectors in readable format because VECTOR_MUL returns a BLOB when its inputs are BLOBs.

The following query multiplies the vectors in the vectors_b table by the vector '[0.1,0.1,0.1,0.1]' 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_MUL(vec, @qv)) AS vector_mul
FROM vectors_b
ORDER BY id;
+------+----------------------------------+
| id   | vector_mul                       |
+------+----------------------------------+
|    1 | 0BD7233C0BD7A33D0BD7A33CF853633D |
|    2 | EB51383DAF47613D83C04A3DCDCC4C3D |
+------+----------------------------------+

Using Suffixes for Other Element Types with BLOBs

The default element type for vector storage and processing is 32-bit floating point (F32). However, other element types are supported.

You can specify the datatype of the vector elements to be used in the operation by adding a suffix to the function. All operations are done using the specified datatype. Omitting the suffix from the function is equivalent to suffixing it with _F32.

When using a suffix, the return type will be the type of the suffix.

The following table lists the suffixes and their data type.

Suffix

Data Type

_I8

8-bit signed integer

_I16

16-bit signed integer

_I32

32-bit signed integer

_I64

64-bit signed integer

_F32

32-bit floating-point number (IEEE standard format)

_F64

64-bit floating-point number (IEEE standard format)

Example 2 - BLOBs with 16-bit Integers

Below is an example of using JSON_ARRAY_PACK and VECTOR_MUL with 16-bit signed integers.

First create a table of vectors stored as 16-bit integers. Note the use of the _I16 suffix on JSON_ARRAY_PACK.

CREATE TABLE vectors_b_i (id int, vec BLOB not null);


INSERT INTO vectors_b_i VALUES (1, JSON_ARRAY_PACK_I16('[1, 3, 2, 5]')); 
INSERT INTO vectors_b_i VALUES(2, JSON_ARRAY_PACK_I16('[23, 4, 1, 8]'));

The following query multiplies the vectors in the vectors_b_itable by the vector '[2,2,2,2]'.

SET @qv = JSON_ARRAY_PACK_I16('[2, 2, 2, 2]');


SELECT JSON_ARRAY_UNPACK_I16(VECTOR_MUL_I16(@qv, vec)) AS vector_mul
FROM vectors_b_i;
+-------------+
| vector_mul  |
+-------------+
| [2,6,4,10]  |
| [46,8,2,16] |
+-------------+

The result is an array of 16-bit integers as indicated by the _I16 suffix.

Note

Be sure that the suffixes you use to pack the vector data match the suffixes you use to unpack the data and the suffixes you use on functions to process that data.

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).

Related Topics

Last modified: May 30, 2024

Was this article helpful?

On this page

Was this article helpful?