SEARCH ... USING VECTOR() cannot be used as a derived table or IN-predicate subquery

[mkhairi]

Version / build tested against

origin/main @ 7bd4d24

Deployment mode

Origin — single node (local)

Engine(s) involved

Vector

Summary

SEARCH ... USING VECTOR(...) is only accepted as a top-level statement. It cannot be used as a derived table (SELECT * FROM (SEARCH ...) s) or inside an IN (...) predicate — both fail in the SQL parser — so a k-NN result cannot be joined or filtered in the same statement. Every hybrid vector-plus-relational query therefore needs two round trips, and any relational filter has to be applied after the k-NN cut, which silently changes the result set size a caller asked for.

Steps to reproduce

DROP COLLECTION IF EXISTS vec_probe;
CREATE COLLECTION vec_probe;
CREATE VECTOR INDEX idx_vec_probe ON vec_probe (embedding) METRIC COSINE DIM 3;
INSERT INTO vec_probe (id, title, embedding) VALUES
  ('a1','one',ARRAY[0.1,0.2,0.3]), ('a2','two',ARRAY[0.4,0.5,0.6]);

SEARCH vec_probe USING VECTOR(embedding, ARRAY[0.1,0.2,0.3], 2);
--  id | _surrogate |      distance
--  a1 | 210        | 0.0
--  a2 | 211        | 0.27000001072883606      (2 rows)   <- correct

SELECT * FROM (SEARCH vec_probe USING VECTOR(embedding, ARRAY[0.1,0.2,0.3], 2)) s;
-- ERROR:  parse error: sql parser error: Expected: joined table, found: USING at Line: 1, Column: 29

SELECT id FROM vec_probe WHERE id IN (SEARCH vec_probe USING VECTOR(embedding, ARRAY[0.1,0.2,0.3], 2));
-- ERROR:  parse error: sql parser error: Expected: ), found: vec_probe at Line: 1, Column: 42

Expected behavior

A SEARCH result is a relation, so it composes: usable as a derived table, joinable against the base collection or another collection, and usable as the right-hand side of IN (...) when it projects a single column.

Actual behavior

Both forms fail in the parser before planning. The k-NN result can only be materialized client-side and correlated with a second query, which means post-filtering happens outside the engine: SEARCH ... k = 10 followed by a client-side filter can return far fewer than 10 usable rows with no way to ask the engine for "10 after filtering".

What actually happened? (check all that are true)

• Acknowledged/committed data was lost, corrupted, or silently wrong
• The server crashed, hung, or failed to start
• A security or isolation boundary was crossed
• Core functionality is broken with no acceptable workaround
• A workaround exists (rewrite the query, avoid one path, etc.)

Proposed severity

SEV-3 — Medium: feature wrong, but operational and a workaround exists

Reproducibility

Always — every attempt

Last known-good version / commit (if a regression)

(blank — never worked)

Environment & logs

Linux x86_64, release build from source (nodedb 0.4.0, git commit: 7bd4d24b6). Single local node, fresh data directory. Errors are client-visible parse errors; no server log output.

Files (best guess): SEARCH is dispatched as a DSL statement before the SQL parser sees a relation, so the grammar has no production placing it in a FROM item or subquery position.

Before submitting

• I searched existing issues and this is not a duplicate.
• I reproduced this on a released tag or a current main build (not a stale local branch).
• This is not a security vulnerability (those go to a private advisory).