Microsoft.Data.Sqlite issue with multiple Blob colums #32747
Description
Microsoft.Data.Sqlite.Core issue with multiple Blob colums
Microsoft.Data.Sqlite.Core is not able to manage joined queries where
two blob fields belong from two different tables.
in the attached example:
command.CommandText = @"SELECT
A.ID as AID,
A.DESCRIPTION as ADESC,
A.VALUE as AVALUE,
B.ID as BID,
B.DESCRIPTION as BDESC,
B.VALUE as BVALUE
FROM
A JOIN B
ON B.FATHER_ID=A.ID ";
var reader = command.ExecuteReader();
while(reader.Read()) {
Console.WriteLine($"A.ID={reader.GetInt32(0)} A.DESC={reader.GetString(1)} B.ID={reader.GetInt32(2)} B.DESC={reader.GetString(3)}");
long abuffLength = reader.GetBytes(2, 0, null, 0, 0); //this line will force to cache rowid=1 from table A
long bbuffLength = reader.GetBytes(5,0,null,0,0); //this line will fail because rowid=1 for table B is invalid
}The first line caches a rowid that is not usable for the second line.
Attached you can find a demo project that is able to reproduce the issue.
Stack trace
Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such rowid: 1'.
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.SqliteDataRecord.GetBytes(Int32 ordinal, Int64 dataOffset, Byte[] buffer, Int32 bufferOffset, Int32 length)
at Microsoft.Data.Sqlite.SqliteDataReader.GetBytes(Int32 ordinal, Int64 dataOffset, Byte[] buffer, Int32 bufferOffset, Int32 length)
at Test.Program.Main(String[] args) in C:\Users\Franco\source\repos\SqliteBlobIssue\Program.cs:line 65
Version information
Microsoft.Data.Sqlite version: HEAD, 8.0.0
Microsoft.Data.Sqlite.Core version: HEAD, 8.0.0
Target framework: NET 8.0
Operating system: Windows, Android, iOS