Microsoft.Data.Sqlite: GetStream reads another row, or throws, when the INTEGER primary key is not the rowid

[Laurianti]

Bug description

GetStream, GetBytes, GetChars and GetTextReader treat a selected INTEGER primary key column as the rowid whenever it is the only key column of its table. SQLite does not make it an alias for the rowid in two cases:

• INTEGER PRIMARY KEY DESC: "it does not become an alias for the rowid" (CREATE TABLE). The blob is opened with the key value as the rowid, so the data of another row is returned, without an error.
• WITHOUT ROWID tables: "The incremental blob I/O mechanism does not work for WITHOUT ROWID tables" (WITHOUT ROWID). Opening the blob throws cannot open table without rowid.

In both cases there is no rowid to stream from, so the value should be read into memory, as it is for a composite primary key (#23554).

Your code

using System.Text;
using Microsoft.Data.Sqlite;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

using (var create = connection.CreateCommand())
{
    create.CommandText = """
        CREATE TABLE d (Id INTEGER PRIMARY KEY DESC, Data BLOB);
        INSERT INTO d VALUES (2, X'6F6E65'); -- 'one', rowid 1
        INSERT INTO d VALUES (1, X'74776F'); -- 'two', rowid 2
        CREATE TABLE wr (Id INTEGER PRIMARY KEY, Data BLOB) WITHOUT ROWID;
        INSERT INTO wr VALUES (2, X'6F6E65');
        """;
    create.ExecuteNonQuery();
}

foreach (var table in new[] { "d", "wr" })
{
    using var command = connection.CreateCommand();
    command.CommandText = $"SELECT Id, Data FROM {table} WHERE Id = 2";
    using var reader = command.ExecuteReader();
    reader.Read();

    Console.WriteLine($"{table} GetValue:  {Encoding.ASCII.GetString((byte[])reader.GetValue(1))}");
    using var stream = reader.GetStream(1);
    var data = new MemoryStream();
    stream.CopyTo(data);
    Console.WriteLine($"{table} GetStream: {Encoding.ASCII.GetString(data.ToArray())}");
}

Output:

d GetValue:  one
d GetStream: two
wr GetValue:  one
Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'cannot open table without rowid: wr'.

Stack traces

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'cannot open table without rowid: wr'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteBlob..ctor(SqliteConnection connection, String databaseName, String tableName, String columnName, Int64 rowid, Boolean readOnly)
   at Microsoft.Data.Sqlite.SqliteDataRecord.GetStream(Int32 ordinal)
   at Microsoft.Data.Sqlite.SqliteDataReader.GetStream(Int32 ordinal)
   at Program.<Main>$(String[] args) in /src/Program.cs:line 27

Microsoft.Data.Sqlite version

10.0.12 (SQLite 3.53.3), same code on main at e943aeb

Target framework

.NET 10.0

Operating system

Linux (mcr.microsoft.com/dotnet/sdk:10.0)

Planned fix

SQLite creates an index for a primary key (origin = 'pk' in pragma_index_list) only when the key is not an alias for the rowid: with INTEGER PRIMARY KEY DESC, in WITHOUT ROWID tables and for composite keys, but not for INTEGER PRIMARY KEY. In SqliteDataRecord.GetStream, the query that counts the key columns would count them only when that index does not exist:

SELECT COUNT(*) FROM pragma_table_info($table) WHERE pk != 0
AND NOT EXISTS (SELECT 1 FROM pragma_index_list($table) WHERE origin = 'pk');

Both cases then return a MemoryStream with the row's own data. I have this change ready with a test next to GetStream_works_when_composite_pk, covering both cases; the Microsoft.Data.Sqlite.Tests and Microsoft.Data.Sqlite.sqlite3mc.Tests suites pass. I can open a PR for it.