[VI] Syntax and functions for using vectors

[roylyseng]

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.

We may have independent WL for this as they could be implemented even now on trunk.

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.

[jlopusza-oracle]

Vector index product	SQL syntax for ANN SELECT-time fraction / leaves / accuracy
Google Cloud SQL for MySQL / ScaNN	SELECT approx_distance(emb, @q, 'distance_measure=cosine,num_leaves_to_search=3') AS dist FROM docs ORDER BY dist LIMIT 10;
Google Spanner / ScaNN	SELECT DocId FROM Documents ORDER BY APPROX_EUCLIDEAN_DISTANCE(@q, DocEmbedding, options => JSON '{"num_leaves_to_search": 10}') LIMIT 100;
BigQuery / VECTOR_SEARCH IVF or TreeAH	SELECT * FROM VECTOR_SEARCH(TABLE docs, 'emb', TABLE queries, top_k => 10, distance_type => 'COSINE', options => '{"fraction_lists_to_search": 0.005}');
AlloyDB for PostgreSQL / ScaNN	BEGIN; SET LOCAL scann.num_leaves_to_search = 2; SELECT * FROM docs ORDER BY emb <=> @q LIMIT 10; COMMIT; SET LOCAL scann.satisfy_limit = 'relaxed_order'; SET LOCAL scann.max_pct_leaves_to_search = 15;
PostgreSQL pgvector / IVFFlat	BEGIN; SET LOCAL ivfflat.probes = 10; SELECT * FROM items ORDER BY embedding <-> @q LIMIT 10; COMMIT;
PostgreSQL pgvector / HNSW	BEGIN; SET LOCAL hnsw.ef_search = 100; SELECT * FROM items ORDER BY embedding <=> @q LIMIT 10; COMMIT;
SingleStore / IVF or HNSW vector index	IVF: SELECT ..., EUCLIDEAN_DISTANCE(v, @q) AS distance FROM docs ORDER BY distance SEARCH_OPTIONS '{"nprobe":20,"k":100}' ASC LIMIT 10; HNSW: ... SEARCH_OPTIONS '{"ef":100}' ...
ClickHouse / vector_similarity HNSW	WITH [...] AS q SELECT * FROM docs ORDER BY L2Distance(vec, q) LIMIT 10 SETTINGS hnsw_candidate_list_size_for_search = 512;
Oracle AI Vector Search / HNSW	SELECT ... FROM docs ORDER BY VECTOR_DISTANCE(emb, :q, COSINE) FETCH APPROXIMATE FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90; ... WITH TARGET ACCURACY PARAMETERS (efsearch 500);
Oracle AI Vector Search / IVF	SELECT ... FROM docs ORDER BY VECTOR_DISTANCE(emb, :q, COSINE) FETCH APPROXIMATE FIRST 10 ROWS ONLY WITH TARGET ACCURACY PARAMETERS (NEIGHBOR PARTITION PROBES 10);
SQL Server / Azure SQL DiskANN	SELECT TOP (10) WITH APPROXIMATE ... FROM VECTOR_SEARCH(TABLE = dbo.docs, COLUMN = emb, SIMILAR_TO = @q, METRIC = 'cosine') AS r ORDER BY r.distance; No exposed fraction/leaves/accuracy knob in SELECT syntax.
DuckDB VSS / HNSW	SET hnsw_ef_search = 100; SELECT * FROM docs ORDER BY array_distance(vec, @q) LIMIT 10;
Databricks SQL / AI Search	SELECT * FROM vector_search(index => 'main.db.my_index', query_vector => ARRAY(...), query_type => 'ANN', num_results => 10); No exposed fraction/leaves/accuracy knob in SQL.
MariaDB Vector / modified HNSW	SELECT id FROM docs ORDER BY VEC_DISTANCE_COSINE(embedding, @q) LIMIT 10; No exposed fraction/leaves/accuracy knob in SELECT syntax.
TiDB Vector Search / HNSW	SELECT * FROM docs ORDER BY VEC_COSINE_DISTANCE(embedding, '[1,2,3]') LIMIT 10; No exposed fraction/leaves/accuracy knob in SELECT syntax.

References:

• https://docs.cloud.google.com/sql/docs/mysql/search-filter-vector-embeddings?utm_source=chatgpt.com "Search and filter with vector embeddings | Cloud SQL for MySQL | Google Cloud Documentation"
• https://docs.cloud.google.com/spanner/docs/find-approximate-nearest-neighbors "Find approximate nearest neighbors (ANN) and query vector embeddings | Spanner | Google Cloud Documentation"
• https://docs.cloud.google.com/bigquery/docs/vector-search?utm_source=chatgpt.com "Search embeddings with vector search | BigQuery | Google Cloud Documentation"
• https://docs.cloud.google.com/alloydb/omni/kubernetes/current/docs/ai/perform-vector-search?utm_source=chatgpt.com "Perform vector search in AlloyDB Omni | AlloyDB Omni using the container orchestrator: Current (18.3.0) | Google Cloud Documentation"
• https://github.com/pgvector/pgvector/blob/master/README.md?utm_source=chatgpt.com "pgvector/README.md at master · pgvector/pgvector · GitHub"
• https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing/ "Vector Indexing · SingleStore Self-Managed Documentation"
• https://clickhouse.com/docs/engines/table-engines/mergetree-family/annindexes "Exact and Approximate Vector Search - ClickHouse Documentation"
• https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/approximate-search-using-hnsw.html?utm_source=chatgpt.com "Approximate Search Using HNSW"
• https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/approximate-search-using-ivf.html?utm_source=chatgpt.com "Approximate Search Using IVF"
• https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql?view=sql-server-ver17 "VECTOR_SEARCH (Transact-SQL) - SQL Server | Microsoft Learn"
• https://github.com/duckdb/duckdb-web/blob/main/docs/current/core_extensions/vss.md?utm_source=chatgpt.com "duckdb-web/docs/current/core_extensions/vss.md at main · duckdb/duckdb-web · GitHub"
• https://docs.databricks.com/aws/en/sql/language-manual/functions/vector_search?utm_source=chatgpt.com "vector_search function | Databricks on AWS"
• https://docs.pingcap.com/ai/vector-search-index/?utm_source=chatgpt.com "Vector Search Index | TiDB Docs"