Skip to content

fix(core): keep the source_hash join out of the vec0 KNN scan on older SQLite - #2107

Merged
phernandez merged 2 commits into
mainfrom
fix/2065-vec0-old-sqlite
Nov 12, 2026
Merged

phernandez merged 2 commits into
mainfrom
fix/2065-vec0-old-sqlite

Conversation

@phernandez

Copy link
Copy Markdown
Member

Summary

Fixes #2065. On SQLite 3.37.2 (Ubuntu 22.04's system SQLite, which the reporter's Python links against), every sqlite-vec KNN query fails with:

An illegal WHERE constraint was provided on a vec0 auxiliary column in a KNN query.

Cause. _candidates builds vector_matches AS MATERIALIZED (... FROM search_vector_embeddings WHERE ... embedding MATCH ... AND k = ...) and joins it to search_vector_chunks on c.source_hash = vector_matches.source_hash. SQLite before 3.38 pushes that join term into the vec0 scan despite MATERIALIZED. source_hash is a vec0 auxiliary column (+source_hash), and vec0 rejects constraints on auxiliary columns in a KNN query. Newer SQLite keeps the term outside the scan, which is why it never reproduced on 3.45 or later.

Fix. c.source_hash = +vector_matches.source_hash. The unary plus makes the term unusable as a constraint on the vec0 column, so it stays in the join.

I ran the exact query shape with sqlite-vec 0.1.9 on two SQLite versions:

SQLite 3.37.2 (ubuntu:22.04) SQLite 3.53.1
current join the vec0 auxiliary-column error correct rows
unary plus correct rows, stale-hash chunk excluded correct rows, stale-hash chunk excluded

CI's SQLite is too new to push the term down, so tests/repository/test_sqlite_vec_index.py checks that the generated SQL keeps the unary plus. A comment in the code explains why it's there.

Also: generated frontmatter columns were never detected

SQLiteFts._entity_column_names read PRAGMA table_info(entity), which leaves out generated columns. On a migrated SQLite database, tags_json, frontmatter_status and frontmatter_type (added by d7e8f9a0b1c2) were therefore never found, and every status, type and tags filter fell back to json_extract(entity.entity_metadata, ...). Same results, but without the indexes. It's now PRAGMA table_xinfo.

This also explains the reporter's "missing tags_json": the diagnostic I asked them to run used table_info too, so their schema is fine.

Unit-test databases come from create_all and have no generated columns, so the generated-column filter branch had never run on SQLite. The new test in tests/repository/test_metadata_filters_edge_cases.py adds the columns with the migration's own DDL and checks status, type, tag-contains and tag-null filters through that branch. It fails without the table_xinfo change.

Verification

  • just fast-check: passes
  • just fast-test (benchmarks excluded): 9476 passed, 95 skipped

🤖 Generated with Claude Code

https://claude.ai/code/session_01CMYVL1rjJwNnrvxniz1dDD

…r SQLite

SQLite before 3.38 pushes the `c.source_hash = vector_matches.source_hash`
join term into the vec0 scan despite AS MATERIALIZED. vec0 rejects any
constraint on an auxiliary column in a KNN query, so every vector query
failed with "An illegal WHERE constraint was provided on a vec0 auxiliary
column in a KNN query" (#2065). A unary plus on the CTE column keeps the
term out of the scan.

Also detect the generated frontmatter columns with PRAGMA table_xinfo.
table_info omits generated columns, so SQLite never found tags_json,
frontmatter_status or frontmatter_type and every status, type and tags
filter fell back to json_extract.

Fixes #2065

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01CMYVL1rjJwNnrvxniz1dDD
Signed-off-by: phernandez <paul@basicmachines.co>
@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Nov 12, 2026 •

Copy link
Copy Markdown
Codex Review Summary: ✅ Completed

This comment shows the latest Codex review activity on this pull request.

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-11-12T20:41:09.994411Z fb2e81f Manual request
ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

@chatgpt-codex-connector chatgpt-codex-connector Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: a5abf22259

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

The tags_json generated column holds json_extract's result, which is
plain text for a scalar such as tags: "". json_each() raised
"malformed JSON" on it once table_xinfo found the column. Tag-contains
filters now walk entity_metadata at the tags path, as before.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01CMYVL1rjJwNnrvxniz1dDD
Signed-off-by: phernandez <paul@basicmachines.co>
@phernandez

Copy link
Copy Markdown
Member Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. 🎉

Reviewed commit: fb2e81f70b

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

@phernandez
phernandez merged commit 1cc3c27 into main Nov 12, 2026
53 checks passed
@phernandez
phernandez deleted the fix/2065-vec0-old-sqlite branch November 12, 2026 20:58
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

[BUG] bm_write (MCP) fails with "An illegal WHERE constraint was provided on a vec0 auxiliary column in a KNN query" when semantic search is disabled

1 participant