What disrupts the inherit logic order of autoincrement that leads to illogical ordering of ids in the absence of an ORDER clause?

On occasion I come across a database table on a project where when no sorting is applied (ORDER clause) that the ids are not in their inherit logical order.

1
178
3
144
2

Under the presumption that no one went out of their way and started manually changing the id column values for any reason and that the id column is set to autoincrement what would lead to this scenario and should whatever that cause/those causes should be an issue to be addressed for security or data integrity purposes?

I’ll just elaborate on @a_horse_with_no_name ‘s comment. A table (relation) is a set of rows (tuples). By definition, a set is unordered. Asking what the next row is without specifying what you mean by next (ORDER BY), is like asking what colour joy has (well when it comes to power tools everyone knows that it is green and black ;-).

Assume that no rows reside in memory and that another connection reads id=186 into memory. Now your query asks for:

select id from T limit 1;

What would you consider the most sensible thing for the DBMS to do, return 186 from memory, or read a bunch of rows from disk to return the lowest id?

What’s the benefit of treat a table as unordered one might ask? The optimizer tries to execute the query as efficient as possible. It can re-arrange the query in a number of ways, as long as it does not violate the semantics of the query. If it would have to take the ordering into consideration, it would severely cripple the freedom to choose the most efficient plan for the query.

• DELETE might leave a ‘hole’ in the data; a subsequent INSERT may fill in that hole with the next AUTO_INCREMENT id.
• UPDATE can change the length of a record, thereby perhaps necessitating putting it somewhere else.
• REPLACE deletes a row(s) and inserts a row, thereby creating a new id.
• GROUP BY (or other constructs) may pick a different ordering.
• A JOIN may pick either table to ‘start’ with.
• A ROLLBACK'd transaction may lose ids.
• MyISAM, especially after a bunch of deletes/updates/inserts, is very likely to provide rows in an unpredictable order. (InnoDB stores the data in PRIMARY KEY order, so it is usually predictable.)
• etc, etc.

If you want specifics, please provide SHOW CREATE TABLE and the full SELECT statement.