VECTOR_SUB
Returns a vector blob from two input vectors by subtracting the second vector from the first vector.
It is a scalar function.
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 the input blob length must be divisible by the packed vector element size (1, 2, 4 or 8 bytes, depending on the vector element).
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 |
---|---|
| 8-bit signed integer |
| 16-bit signed integer |
| 32-bit signed integer |
| 64-bit signed integer |
| 32-bit floating-point number (IEEE standard format) |
| 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 vsub_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 vsub_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(a), HEX(b) FROM vsub_t; **** +--------------------------+--------------------------+ | HEX(a) | HEX(b) | +--------------------------+--------------------------+ | 3333333FCDCC4C3E9A99D93F | 0000803F0000003F00000040 | +--------------------------+--------------------------+
Query the table using the VECTOR_SUB()
function in a SELECT
statement:
SELECT HEX(VECTOR_SUB(a, b)) FROM vsub_t; **** +---------------------------+ | HEX(VECTOR_SUB(a, b)) | +---------------------------+ | 9A9999BE9A9999BE989999BE | +---------------------------+
You can also view the vector blob in JSON format using the JSON_ARRAY_UNPACK()
function:
SELECT JSON_ARRAY_UNPACK(VECTOR_SUB(a, b)) FROM vsub_t; **** +------------------------------------------+ | JSON_ARRAY_UNPACK(VECTOR_SUB(a, 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(b, a)) FROM vsub_t; **** +---------------------------------------------+ | JSON_ARRAY_UNPACK_I32(VECTOR_SUB_I32(b, a)) | +---------------------------------------------+ | [5033165,11744051,2516582] | +---------------------------------------------+