VECTOR_MUL

Example 1 - Vector Type

The example below multiplies two vectors stored as VECTORs.

First create a table of vectors of length 4 using the VECTOR data type and insert data into that table.

CREATE TABLE vectors (id int, vec VECTOR(4) not null);

INSERT INTO vectors VALUES (1, '[0.45, 0.55, 0.495, 0.5]');
INSERT INTO vectors VALUES (2, '[0.1, 0.8, 0.2, 0.555]');
INSERT INTO vectors VALUES (3, '[-0.5, -0.03, -0.1, 0.86]');
INSERT INTO vectors VALUES (4, '[0.5, 0.3, 0.807, 0.1]');

The following SQL multiplies each of those vectors by the vector '[0.1,0.1,0.1,0.1]'.

SET @qv = '[0.1,0.1,0.1,0.1]':>VECTOR(4);

SELECT id, VECTOR_MUL(vec, @qv)
FROM vectors
ORDER BY id;

+------+-----------------------------------------------------------+
| id   | VECTOR_MUL(vec, @qv)                                      |
+------+-----------------------------------------------------------+
|    1 | [0.0449999981,0.0550000034,0.0494999997,0.0500000007]     |
|    2 | [0.0100000007,0.0800000057,0.0200000014,0.0555000007]     |
|    3 | [-0.0500000007,-0.00300000003,-0.0100000007,0.0860000029] |
|    4 | [0.0500000007,0.0300000012,0.0807000026,0.0100000007]     |
+------+-----------------------------------------------------------+

Example 2 - Vector Type and BLOB Type

Inputs to VECTOR_MUL can also be BLOBs. If one input is of type VECTOR and the other of type BLOB, VECTOR_MUL will automatically convert the BLOB to the type of the VECTOR argument.

The SQL below shows an example where VECTOR_MUL is used with a vector that is encoded as a BLOB using JSON_ARRAY_PACK. VECTOR_MUL will automatically convert this BLOB to the type of, vec, the left-hand argument.

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

SELECT id, VECTOR_MUL(vec, @qv)
FROM vectors
ORDER BY id; 

+------+-----------------------------------------------------------+
| id   | VECTOR_MUL(vec, @qv)                                      |
+------+-----------------------------------------------------------+
|    1 | [0.0449999981,0.0550000034,0.0494999997,0.0500000007]     |
|    2 | [0.0100000007,0.0800000057,0.0200000014,0.0555000007]     |
|    3 | [-0.0500000007,-0.00300000003,-0.0100000007,0.0860000029] |
|    4 | [0.0500000007,0.0300000012,0.0807000026,0.0100000007]     |
+------+-----------------------------------------------------------+

The results from Example 1 and Example 2 are the same. The difference between the queries is whether the query vector is a VECTOR or a BLOB.

Example 3 - JSON String Argument and I16 Element Type

This example shows how to use VECTOR_MUL with a VECTOR with an elementType of 16-bit integer (I16) and how to use a JSON string as input to VECTOR_MUL.

First create a table with a VECTOR attribute of length 3 and element type I16 and insert data into that table.

CREATE TABLE vectors_i16(id INT, vec VECTOR(3, I16));

INSERT INTO vectors_i16 VALUES(1, '[1, 2, 3]');
INSERT INTO vectors_i16 VALUES(2, '[4, 5, 6]');
INSERT INTO vectors_i16 VALUES(3, '[1, 4, 8]');

The following example shows the use of VECTOR_MUL with arguments that are both vectors stored as BLOBs.

The following SQL multiples each of the vectors in the vectors_i16 table by the vector '[1,2,3]'.

In this example, the JSON string '[1,2,3]' is used as input to the VECTOR_MUL function. VECTOR_MUL will detect that the second argument, vec, is of type VECTOR of length 3 and with element type 16-bit integer and will automatically convert the JSON string to a vector of that type.

SELECT id, vec, VECTOR_MUL(vec, '[1,2,3]')
FROM vectors_i16
ORDER BY id;

+------+---------+----------------------------+
| id   | vec     | VECTOR_MUL(vec, '[1,2,3]') |
+------+---------+----------------------------+
|    1 | [1,2,3] | [1,4,9]                    |
|    2 | [4,5,6] | [4,10,18]                  |
|    3 | [1,4,8] | [1,8,24]                   |
+------+---------+----------------------------+

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 |
+------+----------------------------------+

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.

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