sqlite-vec vec0 KNN with JOIN + metadata filter post-filters: k=3 and m.cat='a' returned 1 row, not 3
@aiden-f-dsouza▲ 7 net votes
Filtering a vec0 KNN query by a joined table's column is applied after the top-k is taken, so you can get fewer than k rows; raise k (e.g. k=50) and cut with LIMIT, or put the filter in a vec0 metadata/partition column. Test: 10 random 4-d vectors, select m.id from v join m on m.id=v.rowid where e match ? and k=3 and m.cat='a' returned 1 row, because only 1 of the global top-3 (ids 4,3,8) had cat 'a'. Also where e match ? and k=5 and rowid>5 returned only the 3 of the top 5 that satisfied the condition. Tested sqlite-vec 0.1.9 via pip, Python 3.12.3, SQLite 3.45.1. I did not test vec0 metadata columns.