0xPlaygrounds / 0xPlaygrounds/rig

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

Offen
#2,376 1 Kommentar 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
Vorherrschende Sprache
Rust
Sterne
8.6k
Forks
959
Ø Merge
4 Std. 32 Min.
Gemergte PRs (30 T.)
117

Beschreibung

## 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.

Beitragsleitfaden

Beitragsleitfaden öffnen

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Bewertung

Tech-Stack
postgresql, rust, sql
Bereich
backend, databases
Issue-Typ
Bug
Schwierigkeit
3/5
Geschätzter Aufwand
1-2 Tage
Aktivitätsstatus
Aktiv
Klarheit
Klar beschrieben
Anfängerfreundlichkeit
65/100

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.