0xPlaygrounds / 0xPlaygrounds/rig
fix(rig-postgres): `member` filters, single-condition filters and thresholds generate invalid SQL
- Lingua principale
- Rust
- Stelle
- 8.6k
- Fork
- 959
- Merge medio
- 4h 32m
- PR unite (30g)
- 117
Descrizione
## Summary
`rig-postgres` generates SQL that Postgres rejects for any `VectorSearchRequest` that carries a `member` filter, a single-condition filter, or a `threshold`. When the threshold *would* have parsed, it compared in the wrong direction.
| # | Where (`crates/rig-postgres/src/lib.rs`) | Bug | Effect |
|---|---|---|---|
| 1 | `PgSearchFilter::member` | renders `id is in ($3, $4)` | `syntax error at or near "in"` — the unit test currently pins this string |
| 2 | `search_query` | threshold predicate `distance > $N` is placed in the inner `SELECT`'s `WHERE`, where `distance` is only a select-list alias | `column "distance" does not exist` for every `.threshold(..)` request |
| 3 | `search_query` | `>` on a pgvector *distance*, while `VectorSearchRequestBuilder::threshold` is documented as a **minimum similarity** | even if #2 parsed, it would keep the *least* similar rows |
| 4 | `search_query` | `"WHERE" + condition` with no separator | a single-condition filter renders `WHEREprice >= $3`; only compound filters (which start with `(`) ever parsed |
## Reproduction
```rust
let store = PostgresVectorStore::with_defaults(model, pool);
// 1: syntax error at or near "in"
store.top_n_ids(VectorSearchRequest::builder().query("q").samples(3)
.filter(PgSearchFilter::member("document->>'name'".into(), vec![json!("a"), json!("b")]))
.build()).await?;
// 2 + 3: column "distance" does not exist
store.top_n_ids(VectorSearchRequest::builder().query("q").samples(3).threshold(0.8).build()).await?;
// 4: syntax error at or near "WHEREprice"
store.top_n_ids(VectorSearchRequest::builder().query("q").samples(3)
.filter(PgSearchFilter::gte("price".into(), json!(5)))
.build()).await?;
```
Generated SQL on `main` for the three shapes (from `search_query`):
```
WHEREprice >= $3
WHEREdistance > $3
WHERE(distance > $3) AND ((kind = $4) AND (id is in ($5, $6)))
```
## Proposed fix (non-breaking)
- `member` renders `IN`.
- Apply the threshold as a minimum similarity on a per-operator expression that repeats the distance operator instead of naming the alias — `1 - (embedding <=> $1) >= $N` for cosine/jaccard, `-(embedding <#> $1) >= $N` for inner product, `-(embedding <-> $1) >= $N` for L2/L1/hamming — the same approach `rig-sqlite` takes. Returned scores stay raw distances in ascending order, so no public behavior changes for callers who never set a threshold.
- Separate `WHERE` from its first condition.
- Pin the generated SQL with unit tests (no database needed) and extend the Docker-backed integration test with a threshold and a `member` filter.
I have a PR ready for this.
Guida per i contributori
Apri la guida per i contributori
Direzione di ricerca
The issue is in `crates/rig-postgres/src/lib.rs`. Look at the `PgSearchFilter::member` method, the `search_query` function, and the threshold handling. The SQL generation logic needs fixing for member filters (use `IN`), single-condition filters (add space after `WHERE`), and thresholds (adjust operator and placement). Run the existing unit tests to see failures, then write new tests to verify the corrected SQL strings. Check the integration tests in the Docker-backed setup.
Scritto dal modello di indicizzazione a partire dal testo della issue.
Valutazione
- Stack tecnologico
- postgresql, rust, sql
- Ambito
- backend, databases
- Tipo di issue
- Bug
- Difficoltà
- 3/5
- Tempo stimato
- 1-2 giorni
- Stato di attività
- Attiva
- Chiarezza
- Specificata chiaramente
- Idoneità per principianti
- 65/100