DELETE FROM … RETURNING
(1) By Simon Slavin (slavin) on 2021-09-14 14:48:32 [link]
The page <https://sqlite.org/lang_delete.html> includes the following: > The ORDER BY clause on a DELETE statement is used only to determine which rows fall within the LIMIT. The order in which rows are deleted is arbitrary and is not influenced by the ORDER BY clause. This means that if there is a RETURNING clause, the rows returned by the statement probably will not be in the order specified by the ORDER BY clause. (The same thing happens with UPDATE). Someone told me, without even thinking about it, that that was a bug. I wanted to hear opinions. I understand why SQLite does it. I don't need someone to explain the programming. Just whether an intelligent programmer would consider this a bug or not.
(2) By anonymous on 2021-09-14 15:33:16 in reply to 1 [link]
No. The <code>RETURNING</code> extension is inspired by PostgreSQL, which does not have <code>ORDER BY</code> and <code>LIMIT</code> for <code>UPDATE</code> and <code>DELETE</code> statements. Thus, there is no expected order for the returned rows.
(3) By Larry Brasfield (larrybr) on 2021-09-14 16:00:34 in reply to 1 [link]
> ... whether an intelligent programmer would consider this a bug ... Given that the behavior is clearly documented, it's not a bug. A perhaps more interesting question is: Should delete ordering be supported?
(4) By Ryan Smith (cuz) on 2021-09-14 18:47:24 in reply to 3 [link]
No, neither should UPDATE ordering. And (to your previous question): No, calling it a bug is not a smart conclusion - hope that wasn't you or a loved one! :) ORDER BY is an output modifier not a relational method. Once you dictate in which order the engine should update or delete entries, you remove from it any optimization opportunity, or possibility thereof. You attempt to interfere with the engine process and no longer simply the veracity of the data or statements. You neuter the QP - undermining the very utility of an RDBMS engine. Further, I would always want the feedback to be in order of actual deletes, but have space for people needing a different order, however that can easily be done. What about stipulating the order of UPDATEs or DELETEs? Well, IF that is really really needed, you can easily achieve that by simply stating exactly which item to UPDATE or DELETE, and then the next, rinse, repeat, in any order you want it to happen, whether writing an SQL script or doing it in your own code. I can't see a situation in which you would want to UPDATE or DELETE whole blobs of records, but then also needing to fine-grain control exactly which of them go first, I mean, not unless you are using a severely broken relational DB schema. As to the other reply specifying it MUST be so because of Postgres - Well, its outcome might agree with mine, but it's an argument from authority fallacy to start with, even though a really good authority in this case. I feel looking into WHY Postgres did it, and holding up that point would be a better motivated argument.
(5) By anonymous on 2021-09-14 19:58:41 in reply to 4 [link]
> I can't see a situation in which you would want to UPDATE or DELETE whole blobs of records, but then also needing to fine-grain control exactly which of them go first, I mean, not unless you are using a severely broken relational DB schema. In the case of DELETE, it is unlikely that the order really needs to be controlled, except for optimization (which the database engine should do itself), and possibly virtual tables in some cases (although ideally there should be some way for a virtual table itself to handle such deletion optimizations, somehow). In the case of UPDATE, updating records in the wrong order can result in conflicts (including rowid conflicts and primary key conflicts), so there will need to be some way to avoid these conflicts (although again the database engine should do this by itself, but previous messages I have seen suggest that it doesn't (or at least didn't used to do)). (This will also need to be handled somehow for virtual tables, too.)
(6) By Ryan Smith (cuz) on 2021-09-14 21:15:50 in reply to 5 [link]
You are correct about UPDATEs needing to be passable as they are updated - i.e. there is no way to defer constraint checking till the end of a transaction, so every record update needs to succeed as if it was the only update in the query, yet also not rely on the full completed state of the transaction to check its immediate constraints. That also means that your update order within a single statement, if it was possible, will still not solve this problem. I think the people who advocate for this are maybe more concerned with triggers and FK actions happening in predetermined order on UPDATE and DELETE, but for that it's best to rely only on yourself and post those transactions/queries in the exact order you want them executed. No engine quirk or different DB engine's habit can ever mess with it then.
(7) By SeverKetor on 2021-09-14 23:31:36 in reply to 3
I can dream up some uses where something like that would be nice to have. Maybe you have a returning clause on a delete query and want the output ordered by a column alphabetically or something. It wouldn't delete the rows in that order (or at least, it would not specifically try to); just change how you see the returned rows. Given that you can just sort the output yourself outside of SQLite it's hardly important, but occasionally it would be nice.
(8) By Harald Hanche-Olsen (hanche) on 2021-09-15 06:53:29 in reply to 7 [link]
If we could use a DELETE statement as a CTE¹, you could sort it thus: ``` WITH deleted AS (DELETE FROM foo WHERE condition RETURNING stuff) SELECT * FROM deleted ORDER BY something; ``` ¹ This capability is on my wishlist, but I am not holding my breath waiting for it. ;-)
(9) By SeverKetor on 2022-05-30 04:59:36 in reply to 7 [link]
Nearly 9 months later I just realized I unintentionally came up with a way of doing this 11 months before I posted about it here. I have a view including an `ORDER BY` clause with an `INSTEAD OF DELETE` trigger on the view. My program deletes everything from the view and returns all the info for me, originally done because it was simpler to re-use a view than try to delete from the real table and return the right info. Basically every day for the last 19.5 months I've been relying on being able to order the results of a returning clause but never properly noticed I had done so. I guess if someone wanted to do this elsewhere (for some reason), they could create a view on their table as just `SELECT * FROM t ORDER BY c`, create an `INSTEAD OF` trigger for it, then use the view whenever they want their returning clause to be ordered in the way the view is. This has the downsides of being a little convoluted, slower, less convenient, and requiring as many views as possibly needed orders, but it works. I don't know, maybe someone will find it useful.