Efcore: [UWP] "SQL logic error" with large result sets and ORDER BY

[osolo]

On UWP apps, Sqlite queries meeting the criteria below throw the following exception:

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'SQL logic error'.'

Note that the same code, running on .NET Core but not in UWP has no such problem.

To Reproduce

The reproduce the crash, the following conditions must be met:

1. UWP app
2. Perform a SELECT that:
   2.1. Returns at least ~3MB worth of data
   2.2. Returns at least 2 rows
   2.3. Has an ORDER BY clause

It doesn't matter if it's a lot of small rows or a couple of huge rows, as long as you return at least 3MB of data.

Resulting exception:

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'SQL logic error'.'
This exception was originally thrown at this call stack:
Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int, SQLitePCL.sqlite3)
Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(System.Data.CommandBehavior)
App1.MainPage.RunTest(string) in MainPage.xaml.cs
App1.MainPage.MainPage() in MainPage.xaml.cs

I have attached a sample solution with crashing code. The solution has two projects (UWP and Desktop) that run the same exact code. If you run App1 you can see the crash.

SqlTest.zip

Additional context

Microsoft.Data.Sqlite version: 3.12
Target framework: UWP
Operating system: Windows

[ThE-TiGeR]

I have exactly the same problem. It happened to me when I switched from Microsoft.Data.Sqlite 2.2.6 to 3.1.2
If I remove the the ORDER BY clause it is not happening any more

Target framework: UWP
Operating system: Windows

[bricelam]

Does it repro using the version of SQLite that ships in the Windows SDK?

- <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.2" />
+ <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="3.1.2" />
- <PackageReference Include="Microsoft.Data.Sqlite" Version="3.1.2" />
+ <PackageReference Include="Microsoft.Data.Sqlite.Core" Version="3.1.2" />
+ <PackageReference Include="SQLitePCLRaw.bundle_winsqlite3" Version="2.0.2" />

[osolo]

@bricelam It does not repo (works correctly) if I change the packages as you specified.

[bricelam]

@ericsink This could be an issue with e_sqlite3 (or newer versions of SQLite) on UWP...

[ericsink]

WELL then.

I will investigate...

[Fernand-Delavy]

Test.zip
Hi,
My side it is not the ORDER BY create the problem, it is "VACUUM;"
run the Test App (UWP C# app) you will see the problem
i use VS 2019 16.4.5

[ajcvickers]

@Fernand-Delavy There was an issue with vacuum commands that was fixed for 3.0: #19313.

[ajcvickers]

Closing this as external. For now the workaround is to use the SQLite binary shipped with Windows.

[rneeft]

Ive made an repro scenario on github. https://github.com/rneeft/SqliteNetIssue. Im using the sqlite library but we think it might be an underlying assembly, that cause the issue.

steps

• Start debugging
• Click the button
• Verify that everything is working (Debugger stops at line 91)
• Stop debugging
• Upgrade sqlite-net-pcl to the latest version 1.7.335
• Start debugging
• Click the button
• Exception SQL logic error is thrown.

[bricelam]

@rneeft This may have been fixed in Microsoft.Data.Sqlite 3.1.4 by PR #20077. But if you're only using SQLite-net, you'll need to call sqlite3_win32_set_directory yourself.

[pedasmith]

FWIW: I had this problem when I was using SQLite 3.13; it's fixed with the update to 3.1.5. I didn't add any additional code (e.g., to call the sqlite2_win32_set_directory function) and I didn't switch to some other version of SQLite.

Original CSPROJ file:

    <PackageReference Include="Microsoft.EntityFrameworkCore">
      <Version>3.1.3</Version>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite">
      <Version>3.1.3</Version>
    </PackageReference>

I updated the two 3.1.3 projects to 3.1.5