[VI] Syntax and functions for using vectors

This defines the DML part of the syntax for using vector indexes. DDL syntax is a separate chapter.

The definition is partially based on existing MySQL functionality and partially on the upcoming SQL standard IWD 9075-2:20XX, as of 2026-07-06.

Existing functions (Community)

STRING_TO_VECTOR converts a string into vector form.

VECTOR_TO_STRING converts a vector back to a string.

VECTOR_DIM returns the number of dimensions of a vector.

New functions (In HW)

VECTOR_DISTANCE ( vector1, vector2, metric ).

"vector1" and "vector2" are two vector expressions.

"metric" is a metric for how to calculate the vector distance. The standard defines these values: EUCLIDEAN, EUCLIDEAN_SQUARED, MANHATTAN, COSINE, DOT, HAMMING. We don't expect to implement all metrics in first implementation

Return value is a DOUBLE.

VECTOR_NORM ( vector, metric )

This function calculates the distance from the supplied vector to the zero vector.

Only the metrics EUCLIDEAN and MANHATTAN are relevant for this function.

Synonyms for compliance with SQL standard

VECTOR ( string [, dimensions [, coord type ] ] )

This is a possible synonym for STRING_TO_VECTOR. "dimensions" and "coord type" are currently irrelevant for MySQL.

VECTOR_SERIALIZE

This is a possible synonym for VECTOR_TO_STRING

VECTOR_DIMENSION_COUNT

This is a possible synonym for VECTOR_DIM.

Optional arithmetic and aggregate functions

These functions are mostly based on Oracle DB implementation.

Addition

Given two vectors A=(a1, a2, a3) and B=(b1, b2, b3), C = A + B is computed as C = (a1+b1, a2+b2, a3+b3).

Subtraction

Given two vectors A=(a1, a2, a3) and B=(b1, b2, b3), C = A - B is computed as C = (a1-b1, a2-b2, a3-b3).

Multiplication

Given two vectors A=(a1, a2, a3) and B=(b1, b2, b3), the Hadamard product AB is computed as AB = (a1b1, a2b2, a3*b3).

AVG

The average of a vector is calculated as a new vector, where each element is calculated as the average value of the corresponding elements of the input vectors.

SUM

The sum of a vector is calculated as a new vector, where each element is calculated as the sum of the corresponding elements of the input vectors.

JSON compatibility functions

We may consider to explore how JSON functions work together with VECTOR data. This is currently TBD.

Q : What about vectors with different length?
A : probably reject.

Implementation notes

The optimizer should be enhanced to access the index when

• A (sub)query against a single table is specified, and
• ORDER BY references VECTOR_DISTANCE as the primary order expression, and
• "vector1" references an indexed vector column, and
• "vector2" is a const-for-execution vector expression, and
• the specified "metric" is compliant with the vector index, and
• LIMIT is specified for the query, and
• a cost-based decision to use the index over a table scan is made, and
• no WHERE clause nor QUALIFY clause is present in the query, and
• query is not aggregated

The optimizer's decision to use an index may be overridden by using INDEX hints.

We should implement a new iterator class similar to IndexDistanceScanIterator, which is used to speed up use of the SP_DISTANCE function, for kNN search on geometry data.

This iterator class is currently only enabled for the hypergraph optimizer, thus we should implement vector index scan for this optimizer. We anticipate this optimizer to be enabled as default relatively soon. In case this does not happen, we may consider implementing index selection also for the old optimizer, however this will be a larger effort.

Start by reading the existing IndexDistanceScanIterator and the hypergraph optimizer path, which the issue identifies as the model and target for vector index scans. Trace how vector functions, supported metrics, LIMIT, ORDER BY, and index hints are represented, then define completion as the requested vector syntax and functions working with an optimizer-selected vector index scan under the listed conditions.