SQLite Forum

Very obscure bug involving FTS5, triggers, RETURNING, and DBCONFIG_DEFENSIVE

Very obscure bug involving FTS5, triggers, RETURNING, and DBCONFIG_DEFENSIVE

(1) By Joshua Wise (wisej12) on 2021-07-19 04:39:30 [link] [source]

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

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

(2) By Dan Kennedy (dan) on 2021-07-19 14:19:23 in reply to 1 [source]

Thanks for reporting this. Should now be fixed here: