Sqlite - Functions are not executed or result to NULL on INSERT

[jkporter]

SqliteBugDemo.zip

File a bug

I created a table that had columns with a default value of "(unixepoch('subsec'))". When performing INSERTs excluding these columns initially, they were causing an error because the columns were also marked as NOT NULL and after relaxing that restriction, I just had NULLs in the respective columns. I modified my INSERT include the function "unixepoch('subsec')" directly as part of the VALUES however this still resulted in NULLs.

No issues executing the SQL statements with DataGrip IDE client.

Include your code

SqliteBugDemo.zip

Sample C

using Microsoft.Data.Sqlite;
using var connection = new SqliteConnection("Data Source=demo.sqlite;Mode=ReadWriteCreate");

using var createTableCommand = connection.CreateCommand();
createTableCommand.CommandText = """
                          create table if not exists main.test
                          (
                              id         INTEGER                            not null
                                  constraint program_pk
                                      primary key,
                              _created   REAL default (unixepoch('subsec')) not null,
                              _modified  REAL default (unixepoch('subsec')) not null
                          )
                              strict;
                          """;

connection.Open();
createTableCommand.ExecuteNonQuery();

try
{
    using var command = connection.CreateCommand();
    command.CommandText = """
                      INSERT INTO main.test (id)
                      VALUES ($id)
                      """;
    command.Parameters.Add(new SqliteParameter("$id", 1));
    Console.WriteLine(command.CommandText);
    command.ExecuteNonQuery();
    Console.WriteLine("Succeed");
}
catch (Exception e)
{
    Console.WriteLine("Failed");
    Console.WriteLine(e);

}

Console.WriteLine();

try
{
    using var command = connection.CreateCommand();
    command.CommandText = """
                          INSERT INTO main.test (id, _created, _modified)
                          VALUES ($id, unixepoch('subsec'), unixepoch('subsec'))
                          """;
    command.Parameters.Add(new SqliteParameter("$id", 2));
    Console.WriteLine(command.CommandText);
    command.ExecuteNonQuery();
    Console.WriteLine("Succeed");
}
catch (Exception e)
{
    Console.WriteLine("Failed");
    Console.WriteLine(e);
}

Stack Trace

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: test._created'.
   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 Program.<Main>$(String[] args) in C:\Users\me\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 29

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: test._created'.
   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 Program.<Main>$(String[] args) in C:\Users\me\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 50

Include version information

Microsoft.Data.Sqlite version: 8.08 Target framework: NET 8.0 Operating system: Windows 11

[roji]

I can't repro the problem. Your code above doesn't run as-is - it's always better to provide an actual, verified, minimal and runnable console program that shows the problem - that saves everyone time.

Please try the below, and tweak it to make it fail in the same way you're seeing.

Attempted repro

```c# using var connection = new SqliteConnection(@"Data Source=demo.sqlite"); connection.Open(); using (var createTableCommand = new SqliteCommand { Connection = connection }) { createTableCommand.CommandText = """ drop table if exists main.programs; create table main.programs ( id INTEGER not null constraint program_pk primary key, type TEXT, created_at TEXT not null, updated_at TEXT not null, tokens TEXT, _created REAL default (unixepoch('subsec')), /* Removed not null to evade error */ _modified REAL default (unixepoch('subsec')), /* Removed not null to evade error */ _last_retrieved REAL ) strict; """; createTableCommand.ExecuteNonQuery(); } using (var insertProgramCommand = connection.CreateCommand()) { var idParameter = insertProgramCommand.CreateParameter(); idParameter.ParameterName = "$id"; insertProgramCommand.Parameters.Add(idParameter); var typeParameter = insertProgramCommand.CreateParameter(); typeParameter.ParameterName = "$type"; insertProgramCommand.Parameters.Add(typeParameter); var createdAtParameter = insertProgramCommand.CreateParameter(); createdAtParameter.ParameterName = "$created_at"; insertProgramCommand.Parameters.Add(createdAtParameter); var updatedAtParameter = insertProgramCommand.CreateParameter(); updatedAtParameter.ParameterName = "$updated_at"; insertProgramCommand.Parameters.Add(updatedAtParameter); var tokensParameter = insertProgramCommand.CreateParameter(); tokensParameter.ParameterName = "$tokens"; insertProgramCommand.Parameters.Add(tokensParameter); var _lastRetrivedParameter = insertProgramCommand.CreateParameter(); _lastRetrivedParameter.ParameterName = "$last_retrieved"; insertProgramCommand.Parameters.Add(_lastRetrivedParameter); _lastRetrivedParameter.Value = (DateTimeOffset.Now - DateTimeOffset.UnixEpoch).TotalSeconds; idParameter.Value = 1; typeParameter.Value = "Type"; createdAtParameter.Value = DateTimeOffset.UtcNow; updatedAtParameter.Value = DateTimeOffset.UtcNow; tokensParameter.Value = "[]"; insertProgramCommand.CommandText = """ INSERT INTO programs (id, type, created_at, updated_at, tokens, _created, _modified, _last_retrieved) VALUES ($id, $type, $created_at, $updated_at, $tokens, unixepoch('subsec'), unixepoch('subsec'), $last_retrieved); """; insertProgramCommand.ExecuteNonQuery(); idParameter.Value = 2; insertProgramCommand.CommandText = """ INSERT INTO programs (id, type, created_at, updated_at, tokens, _last_retrieved) VALUES ($id, $type, $created_at, $updated_at, $tokens, $last_retrieved); """; insertProgramCommand.ExecuteNonQuery(); } using (var selectCommand = new SqliteCommand { Connection = connection, CommandText = "SELECT id, _created, _modified FROM programs" }) using (var reader = selectCommand.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"Id: {reader.GetInt32(0)}, created={reader.GetString(1)}, modified={reader.GetString(2)}"); } } ```

[jkporter]

@roji I've updated my original comment and provided a project attached. SqliteBugDemo.zip

[roji]

Stripped-down repro:

using var connection = new SqliteConnection("Data Source=demo.sqlite;Mode=ReadWriteCreate");
connection.Open();

using var createTableCommand = connection.CreateCommand();
createTableCommand.CommandText = """
DROP TABLE IF EXISTS main.test;
CREATE TABLE main.test
(
    id INTEGER NOT NULL CONSTRAINT program_pk PRIMARY KEY,
    _created   REAL DEFAULT (unixepoch('subsec')) NOT NULL,
    _modified  REAL DEFAULT (unixepoch('subsec')) NOT NULL
) STRICT;
""";
createTableCommand.ExecuteNonQuery();

using var command = connection.CreateCommand();
command.CommandText = "INSERT INTO main.test (id) VALUES (8)";
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
Console.WriteLine("Succeed");

Note that these same SQLs work fine when executed directly against Sqlite, outside of .NET.

[cincuranet]

The problem is that Microsoft.Data.Sqlite v8.0.8 uses SQLitePCLRaw.bundle_e_sqlite3 version 2.1.6, which has SQLite 3.41.2 and that version seems to be "ignoring" the default clause (without NOT NULL it inserts NULL value). Manually upgrading to 2.1.7 and hence 3.44.0 solves the problem.

As a workaround now, you can explicitly reference it using <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.7" /> (or even 2.1.9 which is latest at the moment).

I'll make sure the next version of Microsoft.Data.Sqlite has newer/fixed version.

[cincuranet]

Result:

• For EF Core 8.0 we'll keep the version on 2.1.7. Changing the version, specifically the downstream version of SQLite seems to be too risky, especially given it is easy to reference newer versions as show above.
• For EF Core 9.0 we'll have newer version (very likely 2.1.10), hence the fix will be there.