A subtle potential bug in most Rails applications
The ActiveRecord component in Rails offers a convenient and powerful interface between the set-oriented world of relational databases and the object-oriented world of Ruby programs. However, there's a potential bug lurking in many (if not most) Rails applications due to a subtle implication of the fact that sets, and hence database result sets, and not ordered.
Take a simple ActiveRecord call such as Post.first. Ask Rails developers what this does, and most will say that it returns the first row from the posts table. And, most of the time for small to medium size tables, on most database engines, it does. But thats purely a coincidence, because SQL does not define the order of rows in an SQL result set—database engines are free to return rows in an order that is convenient for them unless an explicit order by clause is used. But the SQL generated by ActiveRecord for this query is select `posts`.* from `posts` limit 1.
When talking about select statements, the Mysql reference says: You may have noticed in the preceding examples that the result rows are displayed in no particular order. The Oracle documentation says Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order. And PostgreSQL says If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.
So that innocent select statement is just returning a row at the whim of the database engine. It could be the first. It could be the 42nd. It could be any row. The same applies to queries using limit and offset, often used to paginate results. Call Post.limit(10).offset(10) and ActiveRecord executes select `posts`.* from `posts` limit 10 offset 10. Again, there's no ordering applied, and no guarantee that the same rows will be returned given the same query.
Does this actually affect us? Not often. In fact, probably you're never seen it happen. I have seen the results of a query change when using Oracle. As a table filled, Oracle decided to reorganize an index. As a result, paginating through a set of orders suddenly stopped displaying orders in date order. Adding an explicit order by fixed it.
The moral? Well, first, this isn't a big deal. But, whenever you use finders that assume an ordering in a result set, make sure you make the order explicit—add an order() call to the ARel chain. If you want first() to be compatible with last(), add order("id") to the call to first() (because, somewhat inconsistently, last() currently does add an order by id clause). If you want your paginated result sets to be consistent, make sure you order them (perhaps by id, or by created_at).
