Hi -- I'm finding myself surprised by the behavior of the first call to sqlite3_step on a prepared statement like INSERT INTO test (n) VALUES (123) RETURNING * when the database is in WAL mode. Here's a reproducer script:

#!/bin/sh

# No arguments. Run in a directory that contains sqlite3.c, sqlite3.h, and vfstrace.c.

set -e

rm -f prog.c prog foo.sqlite3* *.log

cat >prog.c <<EOF
#include "sqlite3.h"

#include <assert.h>
#include <stdio.h>

int vfstrace_register(
	const char *zTraceName,
	const char *zOldVfsName,
	int (*xOut)(const char*,void*),
	void *pOutArg,
	int makeDefault
);

int main(void) {
	sqlite3 *db;
	sqlite3_stmt *stmt;
	int rv;

	rv = vfstrace_register("tracing", "unix", (void *)fputs, stderr, 1);
	assert(rv == 0);
	rv = sqlite3_open("foo.sqlite3", &db);
	assert(rv == 0);
	rv = sqlite3_exec(db, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
	assert(rv == 0);
	rv = sqlite3_exec(db, "CREATE TABLE test (n INT)", NULL, NULL, NULL);
	rv = sqlite3_prepare_v2(db, "INSERT INTO test (n) VALUES (123) RETURNING *", -1, &stmt, NULL);
	assert(rv == 0);
	do {
		fprintf(stderr, "###\n### sqlite3_step\n###\n");
		rv = sqlite3_step(stmt);
	} while (rv == SQLITE_ROW);
	assert(rv == SQLITE_DONE);

	return 0;
}
EOF

cc -o prog prog.c sqlite3.c vfstrace.c
./prog 2>with-returning.log

rm -f prog foo.sqlite3*

sed -i -e 's/ RETURNING \*//g' prog.c

cc -o prog prog.c sqlite3.c vfstrace.c
./prog 2>without-returning.log

Running against SQLite v3.40.1, I get this trace in with-returning.log (trimming the stuff that happens before any sqlite3_step):

###
### sqlite3_step
###
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,LOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,UNLOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,LOCK|SHARED) -> SQLITE_OK
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=0,n=1,LOCK|EXCLUSIVE) -> SQLITE_OK
###
### sqlite3_step
###
tracing.xWrite(foo.sqlite3-wal,n=24,ofst=8272) -> SQLITE_OK
tracing.xWrite(foo.sqlite3-wal,n=4096,ofst=8296) -> SQLITE_OK
tracing.xSync(foo.sqlite3-wal,FULL) -> 0
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=0,n=1,UNLOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xFileControl(foo.sqlite3,22) -> 12
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,UNLOCK|SHARED) -> SQLITE_OK

And this trace in without-returning.log (ditto):

###
### sqlite3_step
###
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,LOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,UNLOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,LOCK|SHARED) -> SQLITE_OK
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=0,n=1,LOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xWrite(foo.sqlite3-wal,n=24,ofst=8272) -> SQLITE_OK
tracing.xWrite(foo.sqlite3-wal,n=4096,ofst=8296) -> SQLITE_OK
tracing.xSync(foo.sqlite3-wal,FULL) -> 0
tracing.xShmBarrier(foo.sqlite3)
tracing.xShmLock(foo.sqlite3,ofst=0,n=1,UNLOCK|EXCLUSIVE) -> SQLITE_OK
tracing.xFileControl(foo.sqlite3,22) -> 12
tracing.xShmLock(foo.sqlite3,ofst=4,n=1,UNLOCK|SHARED) -> SQLITE_OK

It seems that when the RETURNING clause is present, writing to the WAL is deferred until the second call to sqlite3_step. I was surprised by this because the documentation states:

When a DELETE, INSERT, or UPDATE statement with a RETURNING clause is run, all of the database changes occur during the first invocation of sqlite3_step().

Is this expected behavior in WAL mode?