SQLite Forum

Problem with Sqlite DELETE documentation
Login

Problem with Sqlite DELETE documentation

(1) By doug (doug9forester) on 2020-06-26 14:10:27 [link] [source]

I was reading the Sqlite syntax diagram re Delete and found a paragraph which I think should be stricken. Here is what the text has to say about Delete. The last paragraph doesn't make sense in light of the first 3 paragraphs:

If a DELETE statement has a LIMIT clause, the maximum number of rows that will be deleted is found by evaluating the accompanying expression and casting it to an integer value. If the result of the evaluating the LIMIT clause cannot be losslessly converted to an integer value, it is an error. A negative LIMIT value is interpreted as "no limit". If the DELETE statement also has an OFFSET clause, then it is similarly evaluated and cast to an integer value. Again, it is an error if the value cannot be losslessly converted to an integer. If there is no OFFSET clause, or the calculated integer value is negative, the effective OFFSET value is zero.
If the DELETE statement has an ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are sorted according to the ORDER BY. The first M rows, where M is the value found by evaluating the OFFSET clause expression, are skipped, and the following N, where N is the value of the LIMIT expression, are deleted. If there are less than N rows remaining after taking the OFFSET clause into account, or if the LIMIT clause evaluated to a negative value, then all remaining rows are deleted.
If the DELETE statement has no ORDER BY clause, then all rows that would be deleted in the absence of the LIMIT clause are assembled in an arbitrary order before applying the LIMIT and OFFSET clauses to determine the subset that are actually deleted.
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.

(2) By David Raymond (dvdraymond) on 2020-06-26 14:22:14 in reply to 1 [source]

The last line is saying that the order the individual records are deleted is not defined.

So if you have a table with ids of 1, 2, 3...

Then "delete from tbl order by id limit 5;" will delete id's 1 through 5. The last paragraph is saying that it might delete those individual rows in the order 3, 5, 1, 4, 2.

So the ORDER BY was used to determine which rows will be deleted, but it doesn't actually say:
delete row 1...
then delete row 2...
then delete row 3...
etc.

(3) By doug (doug9forester) on 2020-06-26 14:37:33 in reply to 2 [link] [source]

I understand the arbitrary order in which selected-for-delete rows are actually deleted. The last sentence of the paragraph doesn't include the phrase "selected-for-delete row". Adding that changes dramatically the meaning of the sentence. I suggest this change: ` The order in which rows which are selected for delete are deleted is arbitrary and is not influenced by the ORDER BY clause.

(4) By Larry Brasfield (LarryBrasfield) on 2020-06-26 15:19:21 in reply to 3 [link] [source]

I am unable to see that your change adds any information to the original set of sentences, or that it resolves any ambiguity, or even clarifies the set. Your changes create redundancy. That is almost harmless, except that it dilutes the simpler fact conveyed by the original sentence. In my view, that is detrimental, as it is a disfavor to those who read its preceding sentences carefully, forcing them to read more words than were necessary. It could even trigger a useless reread by those who wonder if some subtly new information is conveyed by the later sentence. (The natural mental question is: Is this merely repetition?)

Personally, I find repetition in documentation to be annoying, and it makes me think the author could not bother to tighten it up.

If you had a suggested change for the whole set of sentences that reduces word count and better conveys the concept you apparently missed, without degrading clarity of the other concepts, then it might be attractive.

(5) By Simon Slavin (slavin) on 2020-06-27 11:49:35 in reply to 1 [link] [source]

The order in which rows are deleted is arbitrary and is not influenced by the ORDER BY clause.

I understand the confusion given by the text of that sentence. It looks like it is talking about the selection process which decides which rows will be deleted. But actually it's talking about something that doesn't matter: the order in which the selected rows are deleted. That doesn't matter because it's done inside a transaction.

Removing that paragraph will remove the confusion without introducing any ambiguity which matters.

(6) By Keith Medcalf (kmedcalf) on 2020-06-27 12:06:33 in reply to 5 [link] [source]

Incorrect.

This clause is extremely important. It means that a statement like:

DELETE FROM t0 ORDER BY something;
is processed exactly the same as
DELETE FROM t0;

and that the "ORDER BY" is irrelevant to the order of deletion. If it had no meaning then it would not be included.

In other words, stating that "the colour that the gun is painted has no effect on the range or velocity of the projectile" means precisely and exactly what it says: that a pink gun is no more accurate and discharges bullets at the same velocity as a gun painted lavender despite any "wishing" to the contrary. It says nothing however as to whether the pink or the lavender gun may go better with your ensemble. Or whether the components of the pink paint are heavier than the components of the lavender paint.

(7) By Larry Brasfield (LarryBrasfield) on 2020-06-27 12:20:30 in reply to 5 [link] [source]

(Several points, all relating to

The order in which rows are deleted is arbitrary and is not influenced by the ORDER BY clause.

and assertions about that.)

(1) It is probably false that the deletion order "is not influenced" by the order clause. More likely, it is so influenced but "is not controlled by the ORDER BY clause." In other words, the order is not guaranteed.

(2) The sentence quoted says nothing about the selection process, so I cannot see how it could look like it does.

(3) Sometimes, deletion order would matter. Consider, for example, a table representing a directed graph with self references enforced by foreign key constraints. (And I have not heard of such constraints being suspended within transactions.)

(4) Removal of that quoted sentence would cause one fact, not explained elsewhere in the section, to not be explained at all. It addresses (inaccurately) the fact that the deletion order is not guaranteed by use of the ORDER BY clause. The preceding text goes only to what rows belong to the deleted set, possibly affected by an ORDER BY clause. Set membership is independent of member ordering.

(5) Because it is an expected error for readers to assume that an ORDER BY clause will effect a time ordering (as it does for SELECTs under the stepping regime), the quoted sentence clarifies the fact that no such effect should be relied upon.

(8.1) By luuk on 2020-06-27 15:35:53 edited from 8.0 in reply to 5 [link] [source]

The ORDER BY needs to be given when you want correct records to be deleted.

Example:

CREATE TABLE test(i INTEGER PRIMARY KEY);

INSERT INTO test VALUES (1),(2),(3),(4),(5);

To delete all records:

DELETE FROM test;

But this produces an error (which is OK):

DELETE FROM test ORDER BY i;

But suppose we want to delete two records?

The only way to know WHICH records are deleted is to specify a WHERE clause like:

DELETE FROM TEST WHERE i IN(1,2);

Or, to specify an 'ORDER BY' and a 'LIMIT'

To delete the first two records:

DELETE FROM TEST ORDER BY i ASC LIMIT 2;

To delete the last two records:

DELETE FROM TEST ORDER BY i DESC LIMIT 2;

But these last two lines produce this ERROR: Error: near "ORDER": syntax error (Windows 10, SQLite 3.32.3 )

But the reason for this error is explained in the docs: If SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax of the DELETE statement is extended by the addition of optional ORDER BY and LIMIT clauses (https://www.sqlite.org/lang_delete.html)