SQLite Forum

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);
}

```