SQLite Forum

Crash when a RETURNING clause refers to a table in UPDATE FROM
Login
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.