I'm using SQLite version 3.35.3. In an attempt to understand which columns a RETURNING clause can refer to, I tried to combine RETURNING with a FROM clause in an UPDATE statement:

CREATE TABLE a (id INTEGER NOT NULL PRIMARY KEY);
UPDATE a SET id = a.id + 1 FROM (SELECT * FROM a) AS old RETURNING old.*;

Running the update statement crashes sqlite.

Note the following perhaps simpler test case:

sqlite> create table x(a,b);
sqlite> create table y(c,d);
sqlite> update x set a=c from y where b=d returning *;

works just fine, but using a wildcard qualified table reference crashes:

sqlite> create table x(a,b);
sqlite> create table y(c,d);
sqlite> update x set a=c from y where b=d returning x.*;

sqlite> create table x(a,b);
sqlite> create table y(c,d);
sqlite> update x set a=c from y where b=d returning y.*;

Note that this gives an error:

sqlite> create table x(a,b);
sqlite> create table y(c,d);
sqlite> update x set a=c from y where b=d returning x.a, y.d;
Error: no such column: y.d

Does the returning clause permit returning values not from the updated table?

Thanks for the simplified test case.

Does the returning clause permit returning values not from the updated table?

One would think not. But PostgreSQL apparently does. It's unclear whether that makes any sense or is an implementation artifact.

Note that the first (working) case only returns columns a and b from table x.

It would appear that table qualifiers are not permitted and that the columns must exist in the updated table (though returning a correlated scalar subquery result seems to work). However only columns from the table being updated are visible to the correlate.

Any use of tablename.* in a RETURNING clause seems to cause the problem. Simplified test case:

CREATE TABLE t1(x);
INSERT INTO t1 VALUES(0) RETURNING t1.*;

There is a ticket at https://sqlite.org/src/tktview/132994c8b1063b. There will be a fix called version 3.35.4. Version 3.35.4 will also fix a problem in the new query planner optimization described as "8b" in the changelog. The optimizer problem was discovered earlier today.

If y'all could, please test out the tip of the branch-3.35 branch (this check-in) and verify that the problem reported above is fixed. If no new problems appear, the 257e16f7b34e6b6f check-in will get tagged as the 3.35.4 patch release.

Seems fine to me.

Might I also suggest documenting that only columns from the target table may be referenced in the returning clause (that is, the table that is being inserted into or updated and not other tables such as the additional tables listed in the from clause in the case of UPDATE ... FROM ...).

If that condition were to be lifted (in the case of an UPDATE ... FROM ...) then the change in restriction can be documented at that time.

It's unclear whether that makes any sense or is an implementation artifact.

I think that it doesn't really make sense, but what I think might make sense is to allow "old" and "new" in RETURNING like is allowed in triggers, so that a UPDATE can return the old and new values. For upsert, the old values would be null if there is no conflict, and perhaps "excluded" might also be allowed, for the value that would be inserted if there were no conflict.

The terms old.<colName> and new.<colName> are handled as one would hope in the RETURNING clause in v3.35+. Have you seen the "excluded" idea implemented somewhere?

Except that for an INSERT the contents of the old and new tables are the same, rather than the old values being NULL; and in particular, when an INSERT does an ON CONFLICT ... DO UPDATE the old and new values are not the pre-update and post-update values, but rather only the post-update values.

The values of old and new are correct, however, for UPDATE statements.

That is, for INSERT statements the columns in the RETURNING clause appear to be as-if from an AFTER INSERT trigger where old is merely an alias for new; and, in the case of an UPDATE statement, they are the old and new as would be seen in an AFTER UPDATE trigger.

I would have assumed that if the old alias was recognized in an INSERT ... RETURNING that old would reference the values before the INSERT (as in all null) or, if there was an ON CONFLICT ... DO UPDATE triggered, the values before the update was processed; and that the new values would represent the row as inserted or updated.

I think "handling old and new correctly" would mean throwing an error. I thought I had done that. Did I miss an error path?

FYI:

SQLite version 3.36.0 2021-03-30 02:12:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> insert into x values (1) returning old.*;
Error: RETURNING may not use "TABLE.*" wildcards
sqlite> insert into x values (1) returning old.rowid, new.rowid;
┌───────────┬───────────┐
│ old.rowid │ new.rowid │
├───────────┼───────────┤
│ 1         │ 1         │
└───────────┴───────────┘
sqlite> insert into x values (1) returning old.rowid, old.x, new.rowid, new.x, *;
┌───────────┬───────┬───────────┬───────┬───┐
│ old.rowid │ old.x │ new.rowid │ new.x │ x │
├───────────┼───────┼───────────┼───────┼───┤
│ 2         │ 1     │ 2         │ 1     │ 1 │
└───────────┴───────┴───────────┴───────┴───┘
sqlite> update x set x = 5 where rowid = 2 returning old.rowid, old.x, new.rowid, new.x, *;
┌───────────┬───────┬───────────┬───────┬───┐
│ old.rowid │ old.x │ new.rowid │ new.x │ x │
├───────────┼───────┼───────────┼───────┼───┤
│ 2         │ 1     │ 2         │ 5     │ 5 │
└───────────┴───────┴───────────┴───────┴───┘
sqlite> insert into x (rowid, x) values (1,10) on conflict (rowid) do update set x = excluded.x returning old.rowid, new.rowid, old.x, new.x, *;
┌───────────┬───────────┬───────┬───────┬────┐
│ old.rowid │ new.rowid │ old.x │ new.x │ x  │
├───────────┼───────────┼───────┼───────┼────┤
│ 1         │ 1         │ 10    │ 10    │ 10 │
└───────────┴───────────┴───────┴───────┴────┘
sqlite> select rowid, x from x;
┌───────┬────┐
│ rowid │ x  │
├───────┼────┤
│ 1     │ 10 │
│ 2     │ 5  │
└───────┴────┘
sqlite> update x set x=20 where rowid=1 returning old.x, new.x;
┌───────┬───────┐
│ old.x │ new.x │
├───────┼───────┤
│ 10    │ 20    │
└───────┴───────┘
sqlite>

This corresponds to checkin [3039bcaff9] on trunk.

I would think that

sqlite> insert into x (rowid, x) values (1,10) on conflict (rowid) do update set x = excluded.x returning old.rowid, new.rowid, old.x, new.x, *;
┌───────────┬───────────┬───────┬───────┬────┐
│ old.rowid │ new.rowid │ old.x │ new.x │ x  │
├───────────┼───────────┼───────┼───────┼────┤
│ 1         │ 1         │ 10    │ 10    │ 10 │
└───────────┴───────────┴───────┴───────┴────┘

above should return

sqlite> insert into x (rowid, x) values (1,10) on conflict (rowid) do update set x = excluded.x returning old.rowid, new.rowid, old.x, new.x, *;
┌───────────┬───────────┬───────┬───────┬────┐
│ old.rowid │ new.rowid │ old.x │ new.x │ x  │
├───────────┼───────────┼───────┼───────┼────┤
│ 1         │ 1         │ 1     │ 10    │ 10 │
└───────────┴───────────┴───────┴───────┴────┘

and that

sqlite> insert into x values (1) returning old.rowid, old.x, new.rowid, new.x, *;
┌───────────┬───────┬───────────┬───────┬───┐
│ old.rowid │ old.x │ new.rowid │ new.x │ x │
├───────────┼───────┼───────────┼───────┼───┤
│ 2         │ 1     │ 2         │ 1     │ 1 │
└───────────┴───────┴───────────┴───────┴───┘

sqlite> insert into x values (1) returning old.rowid, old.x, new.rowid, new.x, *;
┌───────────┬───────┬───────────┬───────┬───┐
│ old.rowid │ old.x │ new.rowid │ new.x │ x │
├───────────┼───────┼───────────┼───────┼───┤
│           │       │ 2         │ 1     │ 1 │
└───────────┴───────┴───────────┴───────┴───┘

Anyways, it doesn't seem to be documented. (Although, I think that its working old properly with upsert should be fixed before it is documented.)

There is a bug in all prior versions of SQLite 3.35 that allows nonsense table names in the RETURNING clause. RETURNING is currently only able to return columns from the single table being updated. For the case of an UPDATE FROM, the additional tables in the FROM clause cannot be referenced by RETURNING.

But SQLite fails to detect this problem. In fact, if it encounters a table name that it does not recognize, it quietly ignores it and just pulls the column off of the table being updated. Consider an example:

CREATE TABLE t1(a INT,b INT);
INSERT INTO t1(a,b) VALUES(1,2);
UPDATE t1 SET b=b+1 RETURNING nosuchtable.a, another.b;

Even though there are no tables named "nosuchtable" or "another", the table names in the RETURNING clause are silently ignored and the "a" and "b" columns of table "t1" are used. It works as if the clause has been "RETURNING a, b".

I will strive to fix this oversight in the next patch release.

Please see if you can break tip of trunk now.

Looks good, but for one more minor item:

UPDATE target aliases are recognized in the statement itself, but not in the RETURNING clause (or subqueries):

sqlite> update x as t set x = 1 where x = 1 returning *, (select y from y where x == x.x);
QUERY PLAN
|--SCAN t (~524288 rows)
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN y (~262144 rows)
┌───┬──────────────────────────────────┐
│ x │ (select y from y where x == x.x) │
├───┼──────────────────────────────────┤
│ 1 │ 10                               │
└───┴──────────────────────────────────┘
sqlite> update x as t set x = 1 where t.x = 1 returning *, (select y from y where x == x.x);
QUERY PLAN
|--SCAN t (~524288 rows)
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN y (~262144 rows)
┌───┬──────────────────────────────────┐
│ x │ (select y from y where x == x.x) │
├───┼──────────────────────────────────┤
│ 1 │ 10                               │
└───┴──────────────────────────────────┘
sqlite> update x as t set x = 1 where t.x = 1 returning *, t.x, (select y from y where x == x.x);
Error: no such column: t.x
sqlite> update x as t set x = 1 where t.x = 1 returning *, sin(radians(t.x)), (select y from y where x == x.x);
Error: no such column: t.x
sqlite> update x as t set x = 1 where t.x = 1 returning *, (select y from y where x == t.x);
Error: no such column: t.x
sqlite>

The RETURNING clause does not know about the alias name for the table being deleted, inserted, or updated. It only knows the canonical name of the table. This is a known limitation. I thought about mentioning it in the list of limitations on the RETURNING documentation page, but I couldn't find a way to describe it that didn't seem like it would just confuse people more.

This limitation might be relaxed in a future release. But a major refactoring will be required to accomplish that, so it would be inappropriate for a patch release.

The first two sentences are a perfect description of the limitation.

I have been unable to find any other examples of untoward behaviour.