Microsoft.Data.SQLite: Can no longer insert empty Blob with 3.0.0-preview8

[T18970237136]

When using Microsoft.Data.SQLite version 3.0.0-preview8.19405.11, it seems we can no longer insert empty blobs/byte arrays using a parameter; instead a NULL value gets inserted.

Steps to reproduce

• Create a new .NET Core 3.0 console project on Windows using .NET Core SDK 3.0.100 - preview8.
• Add <PackageReference Include="Microsoft.Data.SQLite" Version="3.0.0-preview8.19405.11" />
• Add the following code in Program.cs:

using System;
using System.IO;
using Microsoft.Data.Sqlite;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            string dbFile = "mytestdb1.db";

            // Clean the file if it already exists
            if (File.Exists(dbFile))
                File.Delete(dbFile);

            var conSb = new SqliteConnectionStringBuilder();
            conSb.DataSource = dbFile;

            using (var con = new SqliteConnection(conSb.ConnectionString)) {
                con.Open();

                using (var cmd = con.CreateCommand()) {
                    cmd.CommandText = "CREATE TABLE Abc(MyValue BLOB NOT NULL);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO Abc (MyValue) VALUES (@P1);";
                    var p1 = cmd.Parameters.AddWithValue("@P1", new byte[0]);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

Expected behavior: No exception occurs; a row is inserted containing an empty blob.

Actual behavior:
Exception message: Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: Abc.Col2'.
Stack trace:

   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Test.Program.Main(String[] args) in C:\Users\developer4\Desktop\DebugMicrosoftSqlite\Program.cs:line 30

This problem doesn't occur when using version 2.2.6 of Microsoft.Data.SQLite. The problem also doesn't occur when using a byte array with length 1 or higher.

Further technical details

EF Core version: 3.0.0-preview8.19405.11
Database Provider: Microsoft.Data.SQLite
Operating system: Windows 10 Version 1903 x64
IDE: Visual Studio 2019 16.2.3

[ericsink]

This problem is easy to repro with just SQLitePCLRaw.

[ericsink]

Fix pending.

@bricelam Let me know what you want to do about release timing. I know you folks are probably in some level of code freeze for 3.0, but I can probably get a 2.0.1 with this fix pretty quickly.

[ericsink]

Hmmm. I spoke too soon. This might be trickier than I thought. :-( Still digging.

[ericsink]

See:

http://sqlite.1065341.n5.nabble.com/Setting-empty-blob-data-not-NULL-td40242.html

[ericsink]

OK, the code change for this fix was fairly localized.

Storing an empty blob in SQLite is an odd thing. Basically, inside the ISQLite3Provider implementation(s), if the span for the blob is zero-length, the fixed block will yield a null pointer from that span, which will get passed to sqlite3_bind_blob(), which will change the value to a SQL NULL. To avoid this, for the case when the span is zero-length, we need a non-null pointer (which SQLite apparently doesn't really use), so I contrive one, and explicitly pass zero for the length.

This change causes the new failing test case to pass, and appears to cause no other regressions (per my tests). I tend to think the fix is safe, but I'm at maybe 95% confidence, not 100%.

I'm not yet sure what SQLitePCLRaw 1.x (which did not use spans) did with a zero-length byte array. If it was inserting a zero-length blob, then this fix certainly seems like it should happen. Need to check that.

[ericsink]

SQLitePCLRaw 1.x does insert a zero-length blob. So yeah, confidence up to maybe 99% -- this fix seems like the right thing to do.

[bricelam]

Reminds me of aspnet/Microsoft.Data.Sqlite#203. I think we should take a fix. It doesn't seem urgent for our 3.0 release (the bar to get things in gets higher every day), so I'm fine waiting until 3.1 to update to a newer version of SQLitePCL.raw. Thoughts @ajcvickers?

[bricelam]

Whenever you're ready to publish a new package, customers can always just manually update to it to unblock themselves.

[ericsink]

Very good. I'll plan to publish 2.0.1 sometime in the next few days.