SQLite Forum

Crash when a RETURNING clause refers to a table in UPDATE FROM
Login

Crash when a RETURNING clause refers to a table in UPDATE FROM

(1) By Simon Binder (sbinder) on 2021-03-29 21:41:23 [link]

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.

(2) By Keith Medcalf (kmedcalf) on 2021-03-29 23:12:40 in reply to 1 [link]

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?

(3) By Larry Brasfield (larrybr) on 2021-03-29 23:38:31 in reply to 2

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.

(8) By anonymous on 2021-03-30 18:04:16 in reply to 3 [link]

> 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.

(9) By Larry Brasfield (larrybr) on 2021-03-30 21:46:17 in reply to 8 [link]

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?

(10) By Keith Medcalf (kmedcalf) on 2021-03-30 22:25:44 in reply to 9 [link]

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.

(14) By anonymous on 2021-03-31 03:34:25 in reply to 10 [link]

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.)

(11) By Richard Hipp (drh) on 2021-03-30 22:30:00 in reply to 9 [link]

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

(12) By Keith Medcalf (kmedcalf) on 2021-03-30 22:59:50 in reply to 11 [link]

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.

(13) By Keith Medcalf (kmedcalf) on 2021-03-30 23:08:05 in reply to 12 [link]

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 │
└───────────┴───────┴───────────┴───────┴───┘
```
above should return
```
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 │
└───────────┴───────┴───────────┴───────┴───┘
```

(15) By Richard Hipp (drh) on 2021-03-31 16:17:15 in reply to 8 [link]

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.

(16) By Keith Medcalf (kmedcalf) on 2021-03-31 21:54:52 in reply to 15 [link]

That seems to work such that as of [51d5c50b2f] on trunk new and old are no longer recognized -- only columns from the UPDATE/INSERT target.  But expressions and scalar correlates are still permitted.

```
SQLite version 3.36.0 2021-03-31 21:26:50
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> create table y(x,y);
sqlite> insert into x values (2);
sqlite> insert into y values (2,5);
sqlite> insert into x values (2) returning x, (select y from y where x.x == y.x);
┌───┬────────────────────────────────────┐
│ x │ (select y from y where x.x == y.x) │
├───┼────────────────────────────────────┤
│ 2 │ 5                                  │
└───┴────────────────────────────────────┘
sqlite> insert into x values (45) returning x, sin(radians(x));
┌────┬───────────────────┐
│ x  │  sin(radians(x))  │
├────┼───────────────────┤
│ 45 │ 0.707106781186547 │
└────┴───────────────────┘
```

I note that accessing the old/new tablenames in such a correlate results in crashes ... as shown below (except for INSERT where referencing old returns an appropriate error):

```
sqlite> insert into x values (2) returning x, (select y from y where old.x == y.x);
Error: no such column: old.x
sqlite> insert into x values (2) returning x, (select y from y where new.x == y.x);
*** CRASH ***
update x set x = 1 returning x, (select y from y where y.x == old.x);
*** CRASH ***
update x set x = 1 returning x, (select y from y where y.x == new.x);
*** CRASH ***
```

(17) By Richard Hipp (drh) on 2021-03-31 23:57:28 in reply to 16 [link]

Please see if you can break tip of trunk now.

(18) By Keith Medcalf (kmedcalf) on 2021-04-01 00:49:36 in reply to 17 [link]

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>
```

(19) By Richard Hipp (drh) on 2021-04-01 11:14:00 in reply to 18 [link]

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][1],
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.

[1]: https://sqlite.org/lang_returning.html#limitations_and_caveats

(20) By Keith Medcalf (kmedcalf) on 2021-04-01 19:59:46 in reply to 19 [link]

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

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

(4) By Keith Medcalf (kmedcalf) on 2021-03-30 00:12:46 in reply to 2 [link]

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.

(5) By Richard Hipp (drh) on 2021-03-30 00:54:59 in reply to 1 [link]

Any use of <i>tablename</i><b>.*</b> 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][1].
The optimizer problem was discovered earlier today.

[1]: https://www.sqlite.org/releaselog/3_35_3.html

(6) By Richard Hipp (drh) on 2021-03-30 02:02:21 in reply to 5 [link]

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

[1]: https://www.sqlite.org/src/timeline?r=branch-3.35
[2]: https://www.sqlite.org/src/info/branch-3.35

(7) By Keith Medcalf (kmedcalf) on 2021-03-30 03:12:57 in reply to 6 [link]

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.