SQLite Forum

How should RETURNING work with a cascading delete?
I've wished for a way to find out the effects of CASCADE many times,  
but across the board, in any table, not just for self-referential FKs.

So I agree with others, that **direct effets** should be returned only,  
and that *same-table CASCASE side-effets* are out-of-bounds,  
for compatibility with PostgreSQL (and likely other RDBMSs).

Regarding your point 1.2, I guess that implies that the CASCADE is applied  
*depth-first* per-deleted-row, rather than accumulated for *post-processing*  
(kinda like a *breath-first delete*). That's unfortunate, and of course an  
implementation detail I guess. Could be a nasty *gotcha* indeed.

Still, compatibility matters more IMHO.  
The doc could just mention that pathological case perhaps.

BTW, option #1 would violate what [changes][1] returns, no?  
So that clearly points to option#2 IMHO, for internal SQLite consistency.

Also, what would be allowed after `RETURNING`? Any select-clause *expr*?  
Can I `RETURNING COUNT(*)` to *emulate* `sqlite3_changes()` for example?

[1]: https://sqlite.org/c3ref/changes.html