SQLite Forum

Timeline
Login

11 forum posts by user wisej12

2022-01-06
07:38 Reply: Very obscure bug involving FTS5, triggers, RETURNING, and DBCONFIG_DEFENSIVE (artifact: 5ff49f6094 user: wisej12)

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.

2021-07-19
04:39 Post: Very obscure bug involving FTS5, triggers, RETURNING, and DBCONFIG_DEFENSIVE (artifact: 21127c1160 user: wisej12)

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

2021-05-17
20:52 Reply: Status of wal2 branch (artifact: 2cb5926cd6 user: wisej12)

I'd like to add my name to the list of people who are interested in this feature becoming official. It would solve many of the problems of SQLite I struggle to work around on a daily basis, when working with real-time systems.

2021-05-14
17:19 Reply: Discovered bug in xBestIndex of virtual tables (artifact: bb67018abd user: wisej12)

I just tested all four examples with the new check-in, and everything seems to be working as expected now. All four examples now report two constraints: foo, bar (in that order).

I did notice that if you change bar = foo to foo = bar (shown below), you get three constraints: foo, foo, bar (in that order). But I think that's completely fine.

SELECT * FROM vtab WHERE foo = ? AND foo = bar

Thanks for fixing this!

14:55 Reply: sqlite3_stmt_readonly is dependent on the current database (artifact: c8379336b1 user: wisej12)

Thank you! If this causes issues with legacy applications, we can always consider sqlite3_stmt_readonly_v2(). Either way, I'm happy this has been updated. Thanks :)

10:27 Edit reply: Discovered bug in xBestIndex of virtual tables (artifact: 89400b4bba user: wisej12)

That's not true. The constraint in example #2 is not related to the JOIN at all. Both foo and bar are columns of the same table.

In fact, if you remove foo = ?, then example #2 generates zero constraints. So it's clearly doing some transitive logic here. It knows that bar = ? based on foo = ? AND bar = foo. I think that's very clever and desirable behavior, but we don't get the same behavior in example #4 unfortunately.

10:21 Reply: Discovered bug in xBestIndex of virtual tables (artifact: 8a9cadf457 user: wisej12)

That's not true. The constraint in example #2 is not related to the JOIN at all. Both foo and bar are columns of the same table.

2021-05-13
23:20 Reply: sqlite3_stmt_readonly is dependent on the current database (artifact: c552316d2d user: wisej12)

I think returning either FALSE or MAYBE for CREATE TABLE statements is desirable behavior. At least, it's important for people to be able to detect these situations as "potentially mutating the database" instead of incorrectly assuming "will not mutate the database".

I suggest implementing sqlite3_stmt_readonly_v2() to maintain legacy compatibility.

16:59 Reply: Discovered bug in xBestIndex of virtual tables (artifact: 3a6abacbc0 user: wisej12)

Exactly, that's why I included example #2. There's clearly inconsistent behavior here.

16:54 Reply: sqlite3_stmt_readonly is dependent on the current database (artifact: 7838771d01 user: wisej12)

I agree, if it would be nice if there were a generic API for this. Considering that statements created by sqlite3_prepare_v3 transparently re-prepare themselves when the schema changes, it would be nice to have APIs that classify statements consistently, regardless of re-prepares.

04:27 Post: Discovered bug in xBestIndex of virtual tables (artifact: 830d37b928 user: wisej12)

I've discovered that in some circumstances, xBestIndex won't report all constraints on a hidden column. Specifically, it happens when that column is constrained to another hidden column, but only when the query does not involve JOINs. Let's look at a series of examples. For all examples, I'll be using the following virtual table declaration:

CREATE TABLE vtab(foo HIDDEN, bar HIDDEN, x, y);

First, let's look at some examples that behave correctly.

Example 1:

In this example, xBestIndex reports two constraints: "foo", "bar" (in that order). This is the correct behavior.

SELECT vtab.* FROM vtab, other_table WHERE foo = ? AND bar = ?;

Example 2:

In this example, xBestIndex reports three constraints: "foo", "bar", "foo" (in that order). Now, I'm not sure if that really makes sense, considering there's only two constraints in the WHERE clause, but I suppose you could argue that foo = bar can be reversed to imply a third constraint. I'll just give the benefit of the doubt and say this isn't a bug.

SELECT vtab.* FROM vtab, other_table WHERE foo = ? AND bar = foo;

Example 3:

This example is similar to #1, except we removed other_table. This one still behaves correctly. xBestIndex reports two constraints: "foo", "bar" (in that order).

SELECT * FROM vtab WHERE foo = ? AND bar = ?;

Example 4:

This example is similar to #2, except we removed other_table. This is the case that causes the bug. You would except xBestIndex to report at least two constraints (or maybe three, like in example #2), but instead, only one constraint is reported, which is on "foo". xBestIndex does not report any constraints on "bar".

SELECT * FROM vtab WHERE foo = ? AND bar = foo;

It would be great if case #4 could be fixed. Thank you!