SQLite Forum

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

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

(1) By Joshua Wise (wisej12) on 2021-07-19 04:39:30 [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
  • 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);
}

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

Thanks for reporting this. Should now be fixed here:

https://sqlite.org/src/info/c8601d83fbecf84c

Dan.

(3) By Joshua Wise (wisej12) on 2022-01-06 07:38:22 in reply to 2 [link] [source]

I noticed that this fix was included in version 3.37.0. I've been testing it, and it does seem to work properly in the test case I demonstrated. However, I discovered that the bug still occurs when all of the following conditions are met:

  • the sqlite3 connection is in a transaction (autocommit mode is off)
  • the sqlite3 connection has never successfully inserted a row into the virtual table (directly, or via a trigger)
  • all of the conditions described in the original post are met

To clarify the second point above, I've verified that it doesn't matter if the virtual table is empty or not. Even after running DELETE * FROM b, the bug won't occur if the sqlite3 connection had previously inserted a row into the virtual table. Also, it seems to be an issue with the sqlite3 connection, rather than the database itself, because even if some other connection successfully inserted a row into the virtual table, the bug will still occur for a fresh sqlite3 connection.

You can reproduce this case by simply modifying the original test case to run within a transaction.

(4) By Joshua Wise (wisej12) on 2022-02-14 16:51:38 in reply to 2 [link] [source]

Any updates on the status of this bug?