Are you sure that the difference is not in how the shim between python and the sqlite3 library works?

It would appear to me that in one case the shim is executing the first step during the call to .execute, causing .execute to throw an error (which is what would happen with an INSERT statement returning no rows) when trying to step the prepared statement. Consequently no cursor is returned.

In the other case, the shim is merely preparing the statement and not performing any step at all until the cursor is returned and subsequently stepped by the .fetchall method.

Have you tried issuing the commands using the CLI for the version(s) of sqlite3 in question to determine what error sqlite3 (the sqlite3 library, not the shim) is actually returning?

The author of the quoted comment is the author of the shim, and has ruled out the his package being responsible. He has examined the error codes sqlite has returned to it. He believes the problem is in sqlite and has asked me to report it here.

I went to the shim package author first.

Here is what happens with those two versions of the SQLite3 library.

SQLite version 3.36.0 2021-04-19 22:56:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma foreign_keys=1;
sqlite>
sqlite> create table users (id integer not null primary key, username text);
sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text);
sqlite> .param init
sqlite> .param set ?1 'u1'
sqlite> insert into users (username) values (?);
sqlite> .param set ?1 1
sqlite> .param set ?2 't1'
sqlite> insert into tweets (user_id, message) values (?, ?);
sqlite> .param set ?1 2
sqlite> .param set ?2 'tx'
sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message;
┌────┬─────────┬─────────┐
│ id │ user_id │ message │
├────┼─────────┼─────────┤
│ 2  │ 2       │ tx      │
└────┴─────────┴─────────┘
Error: FOREIGN KEY constraint failed
sqlite> ^Z


SQLite version 3.39.0 2022-03-13 04:15:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma foreign_keys=1;
sqlite>
sqlite> create table users (id integer not null primary key, username text);
sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text);
sqlite> .param init
sqlite> .param set ?1 'u1'
sqlite> insert into users (username) values (?);
sqlite> .param set ?1 1
sqlite> .param set ?2 't1'
sqlite> insert into tweets (user_id, message) values (?, ?);
sqlite> .param set ?1 2
sqlite> .param set ?2 'tx'
sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message;
Runtime error: FOREIGN KEY constraint failed

So it would appear that the bug in 3.35.5 which "returns" results despite the error has been fixed.

Clearly no one actually bothered to look at what the underlying SQLite3 library was doing.

Richard may know at what version this fix was made (I do not use RETURNING because the entire concept is based on an inherently flawed design methodology).

Note that the version numbers are different because my private build includes other enhancements and does not always perfectly track the official fossil repository.

That is to say more clearly that version 3.35.5 executes the RETURNING and returns data AND THEN thows an error after the fact (thus implying that the last returned row was an error); whereas, the current version throws the error and DOES NOT return the failing row.

The later behaviour (which does not return failed rows) is more sensical than returning data which is not representative of the statement result.

More clearly demonstrated:

SQLite version 3.36.0 2021-04-19 22:56:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode list
sqlite> create table users (id integer not null primary key, username text);
sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text);
sqlite> .param init
sqlite> .param set ?1 'u1'
sqlite> insert into users (username) values (?);
sqlite> .param set ?1 1
sqlite> .param set ?2 't1'
sqlite> insert into tweets (user_id, message) values (?, ?);
sqlite> .param set ?1 2
sqlite> .param set ?2 'tx'
sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message;
id|user_id|message
2|2|tx
Error: FOREIGN KEY constraint failed
sqlite>
sqlite> insert into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message;
id|user_id|message
2|1|crap
3|2|crap
4|3|crap
Error: FOREIGN KEY constraint failed
sqlite> insert or ignore into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message;
id|user_id|message
2|1|crap
3|2|crap
4|3|crap
Error: FOREIGN KEY constraint failed
sqlite>

SQLite version 3.39.0 2022-03-13 04:15:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode list
sqlite> create table users (id integer not null primary key, username text);
sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text);
sqlite> .param init
sqlite> .param set ?1 'u1'
sqlite> insert into users (username) values (?);
sqlite> .param set ?1 1
sqlite> .param set ?2 't1'
sqlite> insert into tweets (user_id, message) values (?, ?);
sqlite> .param set ?1 2
sqlite> .param set ?2 'tx'
sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message;
Runtime error: FOREIGN KEY constraint failed
sqlite>
sqlite> insert into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message;
Runtime error: FOREIGN KEY constraint failed
sqlite> insert or ignore into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message;
Runtime error: FOREIGN KEY constraint failed
sqlite>

(thus implying that the last returned row was an error)

Is incorrect.

It would appear that version 3.35.5 returns all the rows that were sought to be inserted, and eventually (after returning the data) throws an error and aborts the statement, leaving none of the returned rows having been inserted.

The current version, however, only returns data for successfully inserted rows. Since the statement was aborted due to a referential integrity violation, nothing is inserted and hence nothing is returned.

This was fixed here: https://www.sqlite.org/src/info/a818ba2ed635b91e
As a result of this bug report: https://sqlite.org/forum/forumpost/793beaf322

The bug fix would have appeared in release version 3.37.1 and later.

I puzzled over your post #1 for a while, trying to understand the complaint. I think it can be reduced to this: The following input to the sqlite3 shell produces a different error between versions 3.35.5 and 3.38.0 : pragma foreign_keys=1; drop table if exists tweets; drop table if exists users; create table users (id integer not null primary key, username text); create table tweets (id integer not null primary key, user_id integer not null references users(id), message text); insert into users (username) values ('u1'); .print Extraneous (to the complaint) insert: insert into tweets (user_id, message) values (1,'t1'); .print Destined-to-fail insert with returning: insert into tweets (user_id, message) values (2, 'tx') returning id, user_id, message;

I was (and remain) dubious that this is a SQLite "regression", because the form of an error is not generally guaranteed. The docs state the likely cause for various return error codes, but they do not, as a rule, promise that specific error codes are returned for specific errors.

However, because your report, and that discussion you linked, are vague as to what is actually happening at the SQLite API level, I decided to see for myself what happens. So I pulled source for 3.35.5 and built it, then ran the above code against that and a current version. They produce logically identical errors, differing only in formatting, saying "Error: FOREIGN KEY constraint failed".

Maybe I have misunderstood your complaint. Can you please weed out issues that are extraneous to the difference you are seeing, (such as inserts that succeed and do not affect subsequent failure)? And can you please say whether it is plain insert response or insert with returning clause response, or both, that has appeared, in your usage, to yield different error results? Finally, if we must get into what that Python shim is doing, can you please coax it into revealing the error code returned from the SQLite API as was done in some of that linked discussion?

Finally, I must raise another issue here. The SQLite dev team takes much care to verify that the SQLite library does what its docs say it will do. If not for recognition that human failure can be a factor, responsibility for your trouble might be ruled out from that quarter on the basis that a set of tests verify the documented operation of the returning clause. We often reject bug reports that involve layers not under the SQLite project's control when they have not been reduced to misbehavior at the SQLite API level. I am not yet invoking that as policy because it can be taken on (a degree of) faith that the Python adapter is presenting the SQL shown to the API, doing prepares right, and doing something semi-sensible with the prepare() and step() returns. So, those error codes are most of what is missing from a legitimate bug report against SQLite. That said, it would be much preferrable if we can deal with this in terms of what the SQLite CLI shell does in response to a short input script. We cannot be as confident as "the author of the shim" about ruling out responsibility.

You will note that the library returns a different error code depending whether or not there is a RETURNING clause. Without a returning clause the specific error code is returned. With a returning clause a generic SQLITE_ERROR is returned with the specific error only reflected in the message (but not the code).

SQLite version 3.39.0 2022-03-13 04:15:48
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode list
sqlite> create table users (id integer not null primary key, username text);
sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text);
sqlite> .param init
sqlite> .param set ?1 'u1'
sqlite> insert into users (username) values (?);
sqlite> .param set ?1 1
sqlite> .param set ?2 't1'
sqlite> insert into tweets (user_id, message) values (?, ?);
sqlite> .param set ?1 2
sqlite> .param set ?2 'tx'
sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message;
Runtime error: FOREIGN KEY constraint failed
sqlite> insert into tweets (user_id,message) values (?, ?);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite>

Since the CLI is not displaying the error code and the extended error code, this is easier to see with APSW (which does return exactly what the sqlite3 library returns):

Python 3.9.10 (tags/v3.9.10:f2f3f53, Jan 17 2022, 15:14:21) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>>
>>> conn = apsw.Connection()
>>> conn.execute('pragma foreign_keys=1')
<newapsw.Cursor object at 0x0000020A664D98E0>
>>> conn.execute('create table users (id integer not null primary key, username text)')
<newapsw.Cursor object at 0x0000020A664D96A0>
>>> conn.execute('create table tweets (id integer not null primary key, user_id integer not null references users(id), message text)')
<newapsw.Cursor object at 0x0000020A664D98E0>
>>> conn.execute('insert into users (username) values (?)', ('u1',))
<newapsw.Cursor object at 0x0000020A6875C0A0>
>>> conn.execute('insert into tweets (user_id, message) values (?, ?)', (1, 't1'))
<newapsw.Cursor object at 0x0000020A6875C040>
>>>
>>> try:
...     conn.execute('insert into tweets (user_id, message) values (?, ?) returning id, user_id, message', (2, 'tx'))
... except Exception as exc:
...     print('got %s - %d/%d' % (exc, exc.result, exc.extendedresult))
...
got SQLError: FOREIGN KEY constraint failed - 1/1
>>> try:
...     conn.execute('insert into tweets (user_id, message) values (?, ?)', (2, 'tx'))
... except Exception as exc:
...     print('got %s - %d/%d' % (exc, exc.result, exc.extendedresult))
...
got ConstraintError: FOREIGN KEY constraint failed - 19/787
>>>

This should probably be fixed. Even with a RETURNING clause the correct error code should be returned. (though it should be noted that the generic SQLITE_ERROR is correct it is not particularly diagnostic)

NOTE I have monkeypatched APSW so that it understands conn.execute by creating a cursor on which to execute the statement and returning that cursor (and also conn.executescript which works similarly by creating a cursor on which to execute the statement batch and discards any returned results). On an as-distributed APSW you will need to replace all conn.execute with conn.cursor().execute to emulate what conn.execute does. the apsw.Connection is also monkeypatched with different options including that the default is '' if no filename is specified.

... This should probabky be fixed. Even with a RETURNING clause the correct error code should be returned.

Yes, I agree. Thanks for your patience in ferreting out the real issue here.

To clarify the issue a bit.

In sqlite 3.35, breaking a foreign-key constraint in an INSERT...RETURNING will result in the sqlite error code being set to SQLITE_CONSTRAINT

In sqlite 3.38, the same results in a generic SQLITE_ERROR code.

Fixed at check-in 3f9887d4a58cbfdb.

Note that the same also exists for DELETE as well:

Python 3.9.10 (tags/v3.9.10:f2f3f53, Jan 17 2022, 15:14:21) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> db = apsw.Connection()
>>> db.execute('create table x(id integer primary key);')
<newapsw.Cursor object at 0x000001B6299BD160>
>>> db.execute('create table y(id integer primary key, y integer not null references x(id));')
<newapsw.Cursor object at 0x000001B6299BD280>
>>> db.execute('insert into x values (1),(2),(3);')
<newapsw.Cursor object at 0x000001B6299BD0A0>
>>> db.execute('insert into y values (2,2);')
<newapsw.Cursor object at 0x000001B6299BD280>
>>> try:
...  db.execute('delete from x returning id')
... except Exception as exc:
...  print(exc, exc.result, exc.extendedresult)
...
SQLError: FOREIGN KEY constraint failed 1 1
>>>

it returns SQLIE_ERROR instead of SQLITE_CONSTRAINT. I suspect that UPDATE has the same problem (but have not looked).

Disregard that as it is fixed by the single patch ...

>>> import apsw
>>> db = apsw.Connection()
>>> db.execute('create table x(id integer primary key);')
<newapsw.Cursor object at 0x000002A5B15098E0>
>>> db.execute('create table y(id integer primary key, y integer not null references x(id));')
<newapsw.Cursor object at 0x000002A5B32DC040>
>>> db.execute('insert into x values (1),(2),(3);')
<newapsw.Cursor object at 0x000002A5B32DC0A0>
>>> db.execute('insert into y values (2,2);')
<newapsw.Cursor object at 0x000002A5B32DC040>
>>> try:
...  db.execute('delete from x')
... except Exception as exc:
...  print(exc, exc.result, exc.extendedresult)
...
ConstraintError: FOREIGN KEY constraint failed 19 787
>>> try:
...  db.execute('delete from x returning id')
... except Exception as exc:
...  print(exc, exc.result, exc.extendedresult)
...
ConstraintError: FOREIGN KEY constraint failed 19 787
>>> try:
...  db.execute('update y set y = 5')
... except Exception as exc:
...  print(exc, exc.result, exc.extendedresult)
...
ConstraintError: FOREIGN KEY constraint failed 19 787
>>> try:
...  db.execute('update y set y = 5 returning id')
... except Exception as exc:
...  print(exc, exc.result, exc.extendedresult)
...
ConstraintError: FOREIGN KEY constraint failed 19 787
>>>