0xPlaygrounds / 0xPlaygrounds/rig

fix(rig-postgres): `member` filters, single-condition filters and thresholds generate invalid SQL

Abierto
#2,376 1 comentario 0 reacciones 0 asignados Ver en GitHub
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

Abrir la 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

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.