Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Microsoft.Data.Sqlite issue with multiple Blob colums #32747

Closed
francopettinari opened this issue Jan 8, 2024 · 3 comments
Closed

Microsoft.Data.Sqlite issue with multiple Blob colums #32747

francopettinari opened this issue Jan 8, 2024 · 3 comments
Labels
area-adonet-sqlite closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution customer-reported type-bug
Milestone

Comments

@francopettinari
Copy link
Contributor

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

SqliteBlobIssue.zip

@francopettinari
Copy link
Contributor Author

I forked the project and fixed the issue in SqliteDataRecord.GetStream(int ordinal).
My fix is back compatible and now supports multiple blobs in join statements.
Would it be useful to create a PR?

@ajcvickers
Copy link
Contributor

@francopettinari That would be great, thanks!

@francopettinari
Copy link
Contributor Author

francopettinari commented Jan 10, 2024

@ajcvickers
PR created.
I fixed on main branch, but it should also be ported to branch 8.0.0
Should I port also to branch 8.0.0? How is the workflow?
Sorry for stupid question, I'm new to code contributions.

@ajcvickers ajcvickers added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution labels Jan 27, 2024
@ajcvickers ajcvickers added this to the 9.0.0 milestone Jan 27, 2024
@ajcvickers ajcvickers modified the milestones: 9.0.0, 9.0.0-preview1 Jan 31, 2024
@AndriySvyryd AndriySvyryd removed closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution labels Feb 2, 2024
@AndriySvyryd AndriySvyryd reopened this Feb 2, 2024
@AndriySvyryd AndriySvyryd removed this from the 9.0.0-preview1 milestone Feb 2, 2024
ajcvickers pushed a commit that referenced this issue Feb 16, 2024
…32747) and following Assertion failure in SQLite following Blob column PR (Issue #32944) (#33111)

* blob fix in case of join with multiple tables and related rowid

* test case for issue #32770

* assert readded

* fix for

Assertion failure in SQLite following Blob column PR
@ajcvickers ajcvickers added this to the 9.0.0 milestone Feb 16, 2024
@ajcvickers ajcvickers added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution labels Feb 16, 2024
@ajcvickers ajcvickers modified the milestones: 9.0.0, 9.0.0-preview2 Feb 29, 2024
@roji roji modified the milestones: 9.0.0-preview2, 9.0.0 Oct 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-adonet-sqlite closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. community-contribution customer-reported type-bug
Projects
None yet
Development

No branches or pull requests

4 participants