.Net: Bug: SQLite Error 1: 'ambiguous column name:' when including VectorStoreRecordKey in VectorSearchOptions.Filter

[philipag]

Describe the bug
When using column names marked as [VectorStoreRecordData(IsFilterable = true)] in VectorSearchOptions.Filter, the query runs correctly.
However, using the column name marked as [VectorStoreRecordKey] in VectorSearchOptions.Filter, the query throws exception "SQLite Error 1: 'ambiguous column name: StartUTC'"

To Reproduce
Add a filter for the column marked [VectorStoreRecordKey]. Since that same column exists in both the vec_TestTable and TestTable, the data for both columns cannot be returned.

Expected behavior
The query should explicitly list the vec_TestTable column names to retrieve and should omit the [VectorStoreRecordKey] column since it will be included in the primary TestTable columns.

Platform

• Microsoft.SemanticKernel.Connectors.Sqlite v1.46.0-preview

Additional context
Normal DBContext logging shows only normal context queries. Queries run by VectorizedSearchAsync() don't appear in those logs and I could not find a way to enable logging in semantic search so that I could actually see the exact query that is failing. It would have been very useful to see the failing semantic query.

[adamsitnik]

Hi @philipag

I've failed to reproduce the issue. Could you please modify the repro app below to fail?

using Microsoft.Extensions.VectorData;
using Microsoft.SemanticKernel.Connectors.SqliteVec;
using Xunit;

namespace SqliteIntegrationTests;
public class Repro
{
    [Fact]
    public async Task CanUseKeyForFiltering()
    {
        string filePath = Path.GetTempFileName();
        string connectionString = $"Data Source={filePath}";

        using SqliteCollection<string, SimpleRecord> collection = new(connectionString, "test");
        await collection.EnsureCollectionExistsAsync();

        const string SearchedId = "first";
        await collection.UpsertAsync(new SimpleRecord
        {
            Id = SearchedId,
            Text = "Hello",
            Number = 42,
            Floats = new float[] { 1.0f, 2.0f, 3.0f }
        });

        await collection.UpsertAsync(new SimpleRecord
        {
            Id = "second",
            Text = "World",
            Number = 43,
            Floats = new float[] { 4.0f, 5.0f, 6.0f }
        });

        var fromGetAsync = await collection.GetAsync(r => r.Id == SearchedId, top: 3).SingleAsync();
        Assert.Equal(SearchedId, fromGetAsync.Id);

        var fromVectorSearch = await collection.SearchAsync(new ReadOnlyMemory<float>([1.0f, 2.0f, 3.0f]), top: 3, options:
            new VectorSearchOptions<SimpleRecord>()
            {
                Filter = r => r.Id == SearchedId
            }).SingleAsync();
        Assert.Equal(SearchedId, fromVectorSearch.Record.Id);
    }

    public sealed class SimpleRecord
    {
        [VectorStoreKey]
        public string Id { get; set; } = default!;

        [VectorStoreData]
        public string? Text { get; set; }

        [VectorStoreData]
        public int Number { get; set; }

        [VectorStoreVector(Dimensions: 3)]
        public ReadOnlyMemory<float> Floats { get; set; }
    }
}