Question about Errors within Transaction

(1) By Yu Liang (LY1598773890) on 2022-08-29 21:19:04 [source]

We encounter an unexpected behavior from the following query:

.testctrl optimization 0x2;
CREATE TABLE v0 ( c1 INT );
BEGIN;
INSERT INTO v0 ( c1 ) VALUES ( 0 ),  ( nth_value ( 0, -1) OVER ( ) );
SELECT * FROM v0;
-- Expected: (empty). Actual: {0}.

The INSERT statement above returns the following error:

Runtime error: second argument to nth_value must be a positive integer

However, upon failure, the INSERT saves one row of intermediate result to the table, and thus the SELECT statement returns {0}. This is not expected from us. We expect all the changes done by INSERT statement reverted when it encounter errors.

We read the documents from Section 3 of this link. The document does mention some transaction error handling strategies, specifically for a couple of listed errors. But the document doesn't mention the error case Runtime error we are facing here. We are not sure whether the current output is correct or not.

Thank you.

(2) By Richard Hipp (drh) on 2022-08-29 23:47:59 in reply to 1 [link] [source]

This problem comes about because the SQLite byte-code generator fails to consider that a function might return an error, causing the statement to abort. Here is a simpler manifestation of the same problem:

CREATE TABLE t1(x);
BEGIN;
INSERT INTO t1 VALUES(0), (json('xyzzy'));
SELECT * FROM t1;

As before, the "0" value is inserted. But then the subsequent json() function throws an error (because 'xyzzy' is not valid JSON) and the "0" is not rolled back.

When generating bytecode, if a DML statement modifies two or more rows and an abort might happen on the second or subsequent row, and the conflict resolution strategy is ABORT (which is the default), then that means that the statement must make use of a secondary statement journal, so that it can roll back just that one statement and not the whole transaction. But a statement journal is not free - there is a performance cost for creating and populating one. So, as an optimization, SQLite tries to avoid using a statement journal whenever it can.

The rule is this: If the statement

Makes more than one change to the database file (two or more rows change), and
The statement might abort, and
The statement is run as part of a larger transaction

Then if all three of those things are true, a statement journal has to be created for the statement, so that it can be rolled back if the abort does occur. You can see whether or not the statement journal is created by looking at the OP_Transaction opcode near the end of the bytecode.

There are lots of things that can cause an abort. SQLite is failing to recognize that some SQL functions can raise an error. This is probably because, years ago, none of the built-in functions in SQLite would ever raise errors - they would just return NULL if something went wrong. But more recently, we have added functions that can fail, and that has resulted in this bug. Probably the bug has existed since the very beginning of SQLite for application-defined functions that have always been able to raise an error.

The simple and immediate fix for this will be to say that any use of a function in the SQL statement means that the statement might abort. But that will have performance impact, because most SQL functions in SQLite cannot abort. They usually just return a NULL if something goes wrong. So after the initial fix, I'll have to go back in and add a new option to function implementations to indicate whether or not each function can abort, and only start the statement transaction in the case of those functions. This new optimization will be a lot of work, and a lot of testing.

(3) By Richard Hipp (drh) on 2022-08-30 00:12:14 in reply to 1 [link] [source]

A preliminary fix is now on trunk.

(4) By Yu Liang (LY1598773890) on 2022-08-30 00:21:56 in reply to 3 [link] [source]

Thank you Richard for the detailed information and the initial fix.

The initial fix commit: 5c95ae6c is tested working as expected. We will also keep track of the follow up optimizations.