SingleStore DB

VECTOR_SUB

Returns a vector blob from two input vectors by subtracting the second vector from the first vector.

Syntax
VECTOR_SUB(vector_expression, vector_expression)
Arguments
  • vector_expression: An expression that evaluates to a vector. The vector must be encoded as a blob containing packed single-precision or double-precision floating-point numbers in little-endian byte order.

Return Type

A blob containing packed single-precision floating-point numbers in little-endian byte order.

Remarks

A vector can be of any length, but both input vectors must be the same length and the blob lengths must be divisible by 4 bytes (or 8 bytes, depending on the datatype specified).

You can specify the datatype of the vector elements in which this operation is performed on the vector by adding a suffix to the function. Omitting the suffix from the function is equivalent to suffixing it with _F32. All operations are done using the specified datatype. The following table lists the suffixes and their datatype.

Suffix

Datatype

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

Examples

Example: SELECT Using VECTOR_SUB() on Existing Rows

The following example executes VECTOR_SUB() on a row containing two vectors. The HEX() built-in function is also used to return a readable form of the binary output.

Create a table with two BLOB-typed columns:

CREATE TABLE t (a BLOB, b BLOB);

Using the JSON_ARRAY_PACK() built-in function to easily insert properly formatted vectors, insert a row with each vector in a different column:

INSERT INTO t VALUES (JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));

To demonstrate the contents of the table, use the HEX() built-in function to return a readable form of the binary data:

SELECT HEX(t.a), HEX(t.b) FROM t;
****
+--------------------------+--------------------------+
| HEX(t.a)                 | HEX(t.b)                 |
+--------------------------+--------------------------+
| 3333333FCDCC4C3E9A99D93F | 0000803F0000003F00000040 |
+--------------------------+--------------------------+

Query the table using the VECTOR_SUB() function in a SELECT statement:

SELECT HEX(VECTOR_SUB(t.a, t.b)) FROM t;
****
+---------------------------+
| HEX(VECTOR_SUB(t.a, t.b)) |
+---------------------------+
| 9A9999BE9A9999BE989999BE  |
+---------------------------+

You can also view the vector blob in JSON format using the JSON_ARRAY_UNPACK() function:

SELECT JSON_ARRAY_UNPACK(VECTOR_SUB(t.a, t.b)) FROM t;
****
+------------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUB(t.a, t.b))  |
+------------------------------------------+
| [-0.300000012,-0.300000012,-0.299999952] |
+------------------------------------------+

Example: VECTOR_SUB() with JSON_ARRAY_PACK()

The following example uses JSON_ARRAY_PACK() as input parameters to the VECTOR_SUB() built-in function. The HEX() built-in function is also used to return a readable form of the binary output:

SELECT HEX(VECTOR_SUB(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')));
+-----------------------------------------------------------------------------------------+
| HEX(VECTOR_SUB(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'))) |
+-----------------------------------------------------------------------------------------+
| 9A99993E9A99993E9899993E                                                                |
+-----------------------------------------------------------------------------------------+
1 row in set (0.11 sec)

Example: Typecasting the Datatype Using Suffixes

To perform this operation on vectors specified in a format other than the default 32-bit floating point number, specify a suffix with the VECTOR_SUB() function. For example,

SELECT JSON_ARRAY_UNPACK_I32(VECTOR_SUB_I32(t.b, t.a)) FROM t;
****
+-------------------------------------------------+
| JSON_ARRAY_UNPACK_I32(VECTOR_SUB_I32(t.b, t.a)) |
+-------------------------------------------------+
| [5033165,11744051,2516582]                      |
+-------------------------------------------------+