0xPlaygrounds / 0xPlaygrounds/rig
fix(rig-postgres): `member` filters, single-condition filters and thresholds generate invalid SQL
- Lenguaje dominante
- Rust
- Estrellas
- 8.6k
- Forks
- 959
- Merge medio
- 4 h 32 min
- PR fusionados (30 d)
- 117
Descripción
## 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.
Guía de contribución
Línea de trabajo
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.
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- postgresql, rust, sql
- Área
- backend, databases
- Tipo de issue
- Error
- Dificultad
- 3/5
- Tiempo estimado
- 1-2 días
- Estado de actividad
- Activo
- Claridad
- Bien especificado
- Aptitud para principiantes
- 65/100