.Net: [MEVD] Sqlite filtering behavior is problematic/incorrect

[roji]

In all of our providers, the (LINQ) filter is a pre-filter, taking effect before the vector similarity search. However, in SQLite this seems to be the opposite: if you ask do a filtered similarity search with top=1, it looks like this first gets the most similar record, and only then applies the filter (returning nothing if the single filter doesn't match).

I'm not sure if this is intentional/documented, but it certainly doesn't seem very useful - the point of filtering is usually to restrict which records get considered for similarity search (e.g. within a given category, tenant...). We should check if this behavior is because of our own connector or just the way sqlite_vec works, and consider what to do based on that.

@dmytrostruk assigning to you since I think you wrote the SQLite connector and are the most familiar with it. This technically isn't blocking for Build, but we shouldn't GA the connector before we understand what's going on (and we have plans to use SQLite as our demo/getting started connector...).

Full repro

public class Foo(SqliteFixture fixture) : IClassFixture<SqliteFixture>
{
    [Fact]
    public async Task SqliteIssue()
    {
        var collection = new SqliteVectorStoreRecordCollection<string, Record>("Data Source=/tmp/foo.sqlite", "foo");
        if (await collection.CollectionExistsAsync())
        {
            await collection.DeleteCollectionAsync();
        }

        await collection.CreateCollectionAsync();

        await collection.UpsertAsync(
        [
            new Record
            {
                Key = "1",
                Text = "foo",
                Vector = new ReadOnlyMemory<float>([1f, 1f, 1f])
            },
            new Record
            {
                Key = "2",
                Text = "bar",
                Vector = new ReadOnlyMemory<float>([10f, 20f, 35f])
            }
        ]);

        var results = await collection.VectorizedSearchAsync(new ReadOnlyMemory<float>([10f, 20f, 35f]), 1, new VectorSearchOptions<Record>
        {
            Filter = r => r.Text == "foo",
        }).ToListAsync();

        Assert.Single(results);
    }

    public class Record
    {
        [VectorStoreRecordKey]
        public string Key { get; set; }

        [VectorStoreRecordData(IsIndexed = true)]
        public string Text { get; set; }

        [VectorStoreRecordVector(Dimensions: 3)]
        public ReadOnlyMemory<float>? Vector { get; set; }
    }
}

[roji]

BTW this may be a matter of simply using subqueries to ensure that the WHERE operation occurs first, and the ORDER BY later. In SQL, WHERE is supposed to be evaluated first in any case (so no subquery should be needed), but sqlite_vec seems to be doing some odd things so something else may be going on.