One of my users has discovered an extremely obscure bug in SQLite. It only seems to occur when inserting a row into a table while the following conditions are met:
- the `INSERT` statement has a `RETURNING` clause
- `sqlite3_step()` is only called once, which means it never returns `SQLITE_DONE`
- the table being inserted into has a `BEFORE INSERT` or `AFTER INSERT` trigger which inserts a row into an FTS5 table
- `SQLITE_DBCONFIG_DEFENSIVE` is enabled
Unless _all_ of the previous conditions are satisfied, the bug cannot be replicated. For example, if `sqlite3_step()` is called twice on the `INSERT` statement, the bug does not occur. If the trigger inserts into a regular table, instead of an FTS5 table, the bug does not occur. If the `RETURNING` clause is removed, the bug does not occur. If `SQLITE_DBCONFIG_DEFENSIVE` is not enabled, the bug does not occur.
When the bug occurs, invoking `sqlite3_reset()` or `sqlite3_finalize()` on the `INSERT` statement results in an error (result code `1`), and any active transaction is forcefully rolled back.
The really strange thing is that if `sqlite3_step()` is called twice (which means the second one returns `SQLITE_DONE`), no error occurs and everything works as expected. However, I still think this is a bug because I'm supposed to be able to call `sqlite3_reset()` or `sqlite3_finalize()` at any point in the prepared statement's lifecycle.
Here is a minimal C program that reproduces the bug:
```
#include <stdio.h>
#include <assert.h>
#include <sqlite3.h>
void exec(sqlite3* db, const char* sql) {
int status = sqlite3_exec(db, sql, NULL, NULL, NULL);
assert(status == SQLITE_OK);
}
int test(sqlite3* db) {
sqlite3_stmt* stmt;
int status;
status = sqlite3_prepare_v2(db, "INSERT INTO a VALUES (1, 'foo') RETURNING id;", -1, &stmt, NULL);
assert(status == SQLITE_OK);
status = sqlite3_step(stmt);
assert(status == SQLITE_ROW);
// If you uncomment this, the bug will not occur.
// status = sqlite3_step(stmt);
// assert(status == SQLITE_DONE);
status = sqlite3_finalize(stmt);
printf("status: %d\n", status);
printf("errmsg: %s\n", sqlite3_errmsg(db));
return status;
}
int main(void) {
sqlite3* db;
int status;
status = sqlite3_open(":memory:", &db);
assert(status == SQLITE_OK);
status = sqlite3_db_config(db, SQLITE_DBCONFIG_DEFENSIVE, 1, NULL);
assert(status == SQLITE_OK);
exec(db, "CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);");
exec(db, "CREATE VIRTUAL TABLE b USING fts5(name);");
exec(db, "CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN INSERT INTO b (name) VALUES ('foo'); END;");
return test(db);
}
```