Wrong ErrorCode reported by SqliteException

[Giorgi]

When using sqlite3_limit to limit length of string or blob (SQLITE_LIMIT_LENGTH from https://www.sqlite.org/c3ref/c_limit_attached.html) and running an insert statement with value larger than the limit, the SqliteException has wrong SqliteErrorCode and SqliteExtendedErrorCode

Include your code

var connection = new SqliteConnection("Data Source=sqlite_test.db;");

connection.Open();

var command = connection.CreateCommand();
command.CommandText = @"CREATE TABLE ""Products"" (
          ""Id"" INTEGER NOT NULL CONSTRAINT ""PK_Products"" PRIMARY KEY AUTOINCREMENT,
          ""Name"" TEXT NOT NULL
      );";
command.ExecuteNonQuery();

var limit = SetLimit(connection.Handle, 0, 10);

command.CommandText = @"INSERT INTO ""Products"" (""Name"")  VALUES (@p0);";
command.Parameters.Add("@p0", SqliteType.Text);
command.Parameters[0].Value = new string('A', 15);

try
{
    command.ExecuteReader();
}
catch (SqliteException ex)
{
    Console.WriteLine($"Sqlite error: {ex.SqliteErrorCode} {ex.SqliteExtendedErrorCode}");
}
finally
{
    connection.Close();
}

[DllImport("e_sqlite3", CallingConvention = CallingConvention.Cdecl, EntryPoint = "sqlite3_limit")]
static extern int SetLimit(sqlite3 db, int id, int newVal);

This prints 19 and 1299 (SQLITE_CONSTRAINT and SQLITE_CONSTRAINT_NOTNULL respectively) instead of printing 18 and 18 (SQLITE_TOOBIG)

If I put the value directly in the SQL statement the correct error code is reported:

command.CommandText = @"INSERT INTO ""Products"" (""Name"") VALUES ('AAAAAAAAAAAAAAA');";

Also, when using EF Core and SQLite together the behavior in EF Core 3.1.1 and Microsoft.Data.SQLite was that the insert statement was failing with the correct error code (SQLITE_TOOBIG). I have a unit test that verifies that behavior: https://github.com/Giorgi/EntityFramework.Exceptions/blob/main/EntityFramework.Exceptions.Tests/SqliteTests.cs#L24 but the test fails when I upgrade to EF Core 6. Did the interaction between EF Core and SQLite provider change so drasticly between these versions that result in the new behavior? For example, did the EF Core 3 not use parameterized insert statements or is there a change in the way parameters are passed to the SQLite driver?

Finally, the SQLite shell works as expected:

Version information

Microsoft.Data.Sqlite version: 6.0.3
Target framework: .Net 6
Operating system: Windows

[Giorgi]

Also, if I explicitly set the parameter size to zero I get the SQLITE_TOOBIG error:

command.CommandText = @"INSERT INTO ""Products"" (""Name"")  VALUES (@p0);";
command.Parameters.Add("@p0", SqliteType.Text, 0);
command.Parameters[0].Value = new string('A', 15);

[Giorgi]

The error code is SQLITE_CONSTRAINT and SQLITE_CONSTRAINT_NOTNULL in 3.1.3 version of the provider but when using EF Core, the error code is SQLITE_TOOBIG so something must have changed in the way EF Core and Microsoft.Data.SQLite interact.

[ajcvickers]

@Giorgi Are you using the same version of the native SQLite library and the PCLraw package in both cases? Or are these also changing with EF version?

[Giorgi]

@ajcvickers I use the version that the Microsoft.Data.SQLite pulls as a dependency but I'm pretty sure that this isn't caused by SQLite because when I tried to reproduce it with plain ADO.NET with the version of Microsoft.Data.SQLite that is used by my test I couldn't get it to throw SqliteException with ErrorCode equal to SQLITE_TOOBIG. The only case when I can make SQLite throw SQLITE_TOOBIG is when I set the parameter size to zero in the second comment.

To summarize, the behavior does not change when using plain ADO.NET between the old version and new version of Microsoft.Data.SQLite. The behavior only changes depending how I set parameter or when I upgrade between EF Core versions

[Giorgi]

I tried doing running the same ado.net code with System.Data.SQLite.Core and I get string or blob too big error even if I do not set the parameter size explicitly:

var connection = new SQLiteConnection("Data Source=sqlite_test.db;");

connection.Open();

var command = connection.CreateCommand();
command.CommandText = @"CREATE TABLE ""Products"" (
          ""Id"" INTEGER NOT NULL CONSTRAINT ""PK_Products"" PRIMARY KEY AUTOINCREMENT,
          ""Name"" TEXT NOT NULL
      );";
command.ExecuteNonQuery();

connection.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_LENGTH, 10);

command.CommandText = @"INSERT INTO ""Products"" (""Name"")  VALUES (@p0);";
command.Parameters.Add("@p0", DbType.String);
command.Parameters[0].Value = new string('A', 15);

try
{
	command.ExecuteReader();
}
catch (SQLiteException ex)
{
	Console.WriteLine($"Sqlite error: {ex.ErrorCode} ");
}
finally
{
	connection.Close();
}

This results in SQLite error: 18

If I do the same with Microsoft.Data.Sqlite.Core I get SQLITE_CONSTRAINT and SQLITE_CONSTRAINT_NOTNULL

[Giorgi]

@bricelam Any update on this bug?

[bricelam]

I haven't looked into it yet. I'm focusing on my 7.0 enhancements right now. Will shift my focus to bugs closer to the end of the release.