A testcase causing Assertion `!pTrigger' failed

(1) By Jingzhou Fu (fuboat) on 2022-02-28 15:48:47 [source]

System Information:

compile-time options: CC=clang-12 ./configure --enable-debug
sqlite_source_id: 2022-02-28 13:38:28 f2f0426035d4e0334be000a3eb62bbd7d61fdab7c2ef9ba13cfdf6482396dd13
output: sqlite3: sqlite3.c:125760: void sqlite3Insert(Parse *, SrcList *, Select *, IdList *, int, Upsert *): Assertion `!pTrigger' failed.

PoC:

CREATE TABLE temp.sqlsim8 ( sqlsim10 , sqlsim9 , sqlsim11 );
CREATE TABLE sqlsim12(sqlsim13,sqlsim14,sqlsim15);
INSERT INTO sqlsim8 SELECT * FROM sqlsim12 RETURNING *;

(2) By Richard Hipp (drh) on 2022-02-28 17:04:08 in reply to 1 [link] [source]

Thanks for the bug report.

Simplified test case:

CREATE TABLE t1(a);
INSERT INTO t1(a) VALUES(1);
CREATE TEMP TABLE t2(b);
INSERT INTO t2 SELECT * FROM t1 RETURNING *;

The problem is fixed on trunk as of check-in 1d3760a517b8bd2a. The bug causes no serious harm (no memory errors) for release builds. The only adverse effect is that the RETURNING clause is ignored. The assertion fault only happens in debug builds. Because this bug causes no serious harm for release builds, there are no plans to do a patch release. You can work around the bug by recoding the INSERT statement like this:

INSERT INTO t2 SELECT * FROM t1 WHERE true RETURNING *;

Analysis

SQLite has an optimization (which we call the "xfer optimization") for statements like:

INSERT INTO table1 SELECT * FROM table2;

That optimization helps VACUUM to run much faster. It is important. But the xfer optimization is subject to many restrictions. Among those restrictions is that it does not work with a RETURNING clause. SQLite has always detected that restriction and bypassed the xfer optimization ever since RETURNING was introduced in version 3.35.0. Except, if the destination table is a TEMP table, then the detection of the RETURNING clause was incorrect. The resulting INSERT statement still runs and still transfers all the data from table2 into table1, but because the xfer optimization is incapable of processing the RETURNING clause, it behaves as if the RETURNING clause did not exist.

If you add a "WHERE true" clause onto the SELECT statement, that also disables the xfer optimization, which is how the work-around gets the RETURNING clause going again.

(3) By ddevienne on 2022-02-28 17:46:35 in reply to 2 [link] [source]

Hi Richard. Is the xfer optimization the reason why the query plan is empty below?
Could such optimization be made visible in the explain query plan output?

C:\Users\ddevienne>sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t1(a);
sqlite> INSERT INTO t1(a) VALUES(1);
sqlite> CREATE TEMP TABLE t2(b);
sqlite> explain query plan INSERT INTO t2 SELECT * FROM t1 RETURNING *;
sqlite> explain query plan INSERT INTO t2 SELECT * FROM t1 WHERE true RETURNING *;
QUERY PLAN
`--SCAN t1