Search vectors through an equi-join

Search indexed chunks while restricting results to known documents. This v4.2.6 workflow uses an inner equi-join and shows that one chunk can appear more than once when multiple document rows match it.

Create an IVF-FLAT index over the chunk vectors and a document table. Chunks c0 and c1 are nearest to [0,0], but their document_id has no matching document:

DROP DATABASE IF EXISTS vector_ivf_equi_join;
CREATE DATABASE vector_ivf_equi_join;
USE vector_ivf_equi_join;
SET @saved_experimental_ivf_index = @@experimental_ivf_index;
SET experimental_ivf_index = 1;
SET probe_limit = 1;
CREATE TABLE chunks (id VARCHAR(32) PRIMARY KEY, v VECF32(2) NOT NULL, document_id VARCHAR(32), category VARCHAR(32));
INSERT INTO chunks VALUES ('c0','[0,0]','u','x'),('c1','[0.01,0]','u','x'),('c2','[0.1,0]','d1','x'),('c3','[0.2,0]','d2','z'),('c4','[0.3,0]','d1','y'),('c5','[0.6,0]','d3','outside'),('c6','[0.15,0]',NULL,'null');
CREATE INDEX chunks_ivf USING IVFFLAT ON chunks(v) LISTS=1 OP_TYPE 'vector_l2_ops';
CREATE TABLE documents (row_id INT PRIMARY KEY, document_id VARCHAR(32), label VARCHAR(32), payload VARCHAR(32));
INSERT INTO documents VALUES (1,'d1','x','first'),(2,'d1','y','second'),(3,'d2','z','third'),(4,'d3','outside','fourth'),(5,NULL,'null','fifth');

Run the joined Top-K query. EXPLAIN contains ivf_search. The result IDs are c2, c2, c3: c2 appears twice because two document rows have document_id='d1'. The mode=force query checks the result with the exact path:

EXPLAIN SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3;
SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3;
SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3 BY RANK WITH OPTION 'mode=force';
SET experimental_ivf_index=@saved_experimental_ivf_index;
DROP DATABASE vector_ivf_equi_join;

The rewrite applies to inner equality joins and an equivalent IN membership query. A LEFT JOIN or non-equality join does not use this rewrite. IVF-FLAT is approximate; this small dataset verifies query behavior, not production recall or speed. See IVF Equi-join Vector Query for filters, index hints, and more query shapes. The MatrixOne regression test covers the source feature.