Scalar Vector Query

A scalar vector query feeds the query vector of a Top-K l2_distance search from a scalar subquery; with an IVF-FLAT or HNSW index on the column, MatrixOne answers it with an approximate-nearest-neighbor scan instead of a full table scan.

Description

A vector Top-K query usually takes the query vector as a literal, for example:

SELECT id FROM items ORDER BY l2_distance(v, '[1,0,0]') LIMIT 2;

When the query vector is stored in another table, the query vector column can be provided by a scalar subquery that returns at most one row:

SELECT id FROM items
ORDER BY l2_distance(v, (SELECT v FROM query_vectors WHERE id = 'q'))
LIMIT 2;

If the searched column has an IVF-FLAT or HNSW vector index, MatrixOne rewrites this shape into an approximate-nearest-neighbor (ANN) scan, the same plan used for literal query vectors. EXPLAIN shows the ivf_search node for an IVF-FLAT index (Scalar Vector Query appears in the plan output). When the scalar subquery returns no row, or returns NULL, no ANN scan happens and the original relational ordering applies, so a missing provider falls back to a correct non-index result rather than an error.

The rewrite holds under the same option surface as the literal form:

  • The system variable probe_limit limits how many IVF-FLAT lists are probed, exactly as in a literal-vector search.

  • BY RANK WITH OPTION 'mode=pre' keeps the ANN scan; BY RANK WITH OPTION 'mode=force' bypasses it, matching IVF Vector Query with Rank Options semantics.

  • The provider subquery can appear inside a CTE or a derived table; each occurrence is rewritten independently, and two occurrences with different filters do not interfere with each other.

  • The whole statement can be prepared and executed with PREPARE/EXECUTE; rebinding the provider key, the limit, or a NULL provider behaves the same as running the query again.

  • When the provider table is qualified with a snapshot, for example (SELECT v FROM query_vectors {snapshot='snapshot_name'} WHERE id = 1), the provider row is read as of the snapshot time, so the ANN search stays temporally consistent with the qualification: mixed current/snapshot providers in one statement are not collapsed.

Not every shape is rewritten. A provider subquery that can return more than one row is deliberately left as-is and still raises the scalar-subquery cardinality error. A scalar subquery over the same table being searched (SELECT ... FROM items ref WHERE ref.id = ...) participates in the rewrite provided it returns at most one row.

Syntax

SELECT ... FROM indexed_table
ORDER BY l2_distance(vector_column, (SELECT query_vector FROM provider WHERE ...))
LIMIT k [OFFSET n];

Arguments

  • vector_column is a vector column with an IVF-FLAT or HNSW index.

  • The provider subquery must return at most one vector. A multi-row result retains the ordinary scalar-subquery error.

  • k is the Top-K limit; OFFSET is applied to the ordered results.

Examples

All statements below can be run consecutively on a fresh MatrixOne instance. The IVF-FLAT part uses experimental_ivf_index and probe_limit, and the HNSW part uses experimental_hnsw_index.

DROP DATABASE IF EXISTS scalar_vector_demo;
CREATE DATABASE scalar_vector_demo;
USE scalar_vector_demo;
SET experimental_ivf_index = 1;
SET probe_limit = 1;
CREATE TABLE items(id VARCHAR(32) PRIMARY KEY, v VECF64(3) NOT NULL);
INSERT INTO items VALUES ('a','[0,0,0]'),('b','[1,0,0]'),('c','[3,0,0]');
CREATE INDEX ix USING IVFFLAT ON items(v) LISTS=1 OP_TYPE 'vector_l2_ops';
CREATE TABLE query_vectors(id VARCHAR(32) PRIMARY KEY, v VECF64(3));
INSERT INTO query_vectors VALUES ('q','[1,0,0]'),('n',NULL);

The two queries below return the same two nearest rows; with the index, the second one uses the ANN scan. EXPLAIN shows Scalar Vector Query and the ivf_search node:

EXPLAIN SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2;
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2;
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 1 OFFSET 1;
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2 BY RANK WITH OPTION 'mode=pre';
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2 BY RANK WITH OPTION 'mode=force';

Neither a missing provider row nor a NULL provider vector takes the ANN path; the result is the ordinary ordered result over the fallback relation, including with OFFSET:

SELECT id FROM items WHERE id='a' ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='missing')) LIMIT 2;
SELECT COUNT(*) FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='missing')) LIMIT 2) t;
SELECT id FROM items WHERE id='a' ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='n')) LIMIT 2;
SELECT COUNT(*) FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='n')) LIMIT 2) t;
SELECT COUNT(*) FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='n')) LIMIT 0) t;

Two CTE selectors each carry their own provider and their own filters; both run the ANN scan independently:

WITH a AS (SELECT CONCAT(id,'') AS id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2),
b AS (SELECT CONCAT(id,'') AS id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2)
SELECT a.id,b.id FROM a JOIN b ON a.id=b.id ORDER BY a.id;
WITH a AS (SELECT CONCAT(id,'') AS id FROM items WHERE id>'a' ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2),
b AS (SELECT CONCAT(id,'') AS id FROM items WHERE id<'c' ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='missing')) LIMIT 2)
SELECT a.id,b.id FROM a JOIN b ON a.id=b.id ORDER BY a.id;

A prepared statement rebinds the provider key and the limit on every EXECUTE, and a changed provider value is picked up without re-prepare:

PREPARE p FROM 'SELECT id FROM items WHERE id=''a'' ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=?)) LIMIT ?';
SET @id='q';
SET @k=0;
EXECUTE p USING @id,@k;
SET @k=2;
EXECUTE p USING @id,@k;
SET @id='n';
EXECUTE p USING @id,@k;
SET @id='missing';
EXECUTE p USING @id,@k;
SET @id='q';
EXECUTE p USING @id,@k;
SET @k=0;
EXECUTE p USING @id,@k;
DEALLOCATE PREPARE p;
PREPARE nested FROM 'SELECT * FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=?)) LIMIT 2) t LIMIT ? OFFSET 1';
SET @k=0;
EXECUTE nested USING @id,@k;
SET @k=5;
EXECUTE nested USING @id,@k;
UPDATE query_vectors SET v='[3,0,0]' WHERE id='q';
EXECUTE nested USING @id,@k;
DEALLOCATE PREPARE nested;

A provider subquery that can return more than one row is not rewritten; it fails with the usual scalar-subquery cardinality error:

-- Expected-Success: false
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM items ref WHERE ref.id IN ('a','b'))) LIMIT 2;
DROP DATABASE scalar_vector_demo;

The items ref shape above still goes through the rewrite whenever it returns at most one row, for example SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM items ref WHERE ref.id='b')) LIMIT 2;. The example above intentionally picks two rows to show the un-rewritten fallback. ref is only a correlation alias, not a reserved word.

HNSW

The same rewrite works for HNSW indexes. Enable the HNSW index, repeat the same query shapes with a VECF32 column, and the plan outputs the ANN node instead of ivf_search:

SET @saved_hnsw=@@experimental_hnsw_index;
SET experimental_hnsw_index=1;
CREATE DATABASE scalar_vector_demo;
USE scalar_vector_demo;
CREATE TABLE items(id BIGINT PRIMARY KEY, v VECF32(3) NOT NULL);
INSERT INTO items VALUES (1,'[0,0,0]'),(2,'[1,0,0]'),(3,'[3,0,0]');
CREATE INDEX ix USING HNSW ON items(v) OP_TYPE 'vector_l2_ops' M 4 EF_CONSTRUCTION 16 EF_SEARCH 16;
CREATE TABLE query_vectors(id BIGINT PRIMARY KEY, v VECF32(3));
INSERT INTO query_vectors VALUES (1,'[1,0,0]'),(2,'[3,0,0]'),(3,NULL);
EXPLAIN SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=1)) LIMIT 2;
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=1)) LIMIT 2;
SELECT id FROM items WHERE id=1 ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=99)) LIMIT 2;
SELECT COUNT(*) FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=99)) LIMIT 2) t;
SELECT COUNT(*) FROM (SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=3)) LIMIT 2) t;
PREPARE p FROM 'SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id=?)) LIMIT ?';
SET @id=1;
SET @k=2;
EXECUTE p USING @id,@k;
SET @id=2;
EXECUTE p USING @id,@k;
SET @k=0;
EXECUTE p USING @id,@k;
SET @k=2;
UPDATE query_vectors SET v='[0,0,0]' WHERE id=2;
EXECUTE p USING @id,@k;
DEALLOCATE PREPARE p;
SET experimental_hnsw_index=@saved_hnsw;
DROP DATABASE scalar_vector_demo;

See Also