PragDave

Be careful using default_scope and order()

In a recent post, I talked about how the Rails first and limit/offset modifiers to queries are not specified to return a particular result—databases are free to determine the order of rows in a result set if the query doesn't contain an explicit order by clause, so the actual row(s) returned by these methods may not be determined.

Some folks responded (in comments, tweets, and emails) by suggesting that you can easily solve this problem by adding a default_scope call at the top of models that you use first and offset on. Ignoring the fact that this really isn't a solution (both the names first and offset imply an ordering which they don't deliver, and the fix should be in them); ignoring that, the suggestion might lead to another unexpected problem.

Lets start with a simple ActiveRecord model:

class Wish
end

Executing Wish.limit(10).offset(10).to_sql returns the SQL

SELECT  `wishes`.* FROM `wishes`  LIMIT 10 OFFSET 10

Noticing the lack of an order by clause, you add a default scope:

class Wish
  default_scope order(:id)
end

Now when we execute the query, the SQL contains an order by clause:

SELECT  `wishes`.* FROM `wishes` ORDER BY id LIMIT 10 OFFSET 10

Cool. Problem fixed.

However, the user also wants a list of the 3 most recent wishes, so you code

Wish.order("created_at desc").limit(3)

Imagine your chagrin when you look at the SQL that gets run and see

SELECT  `wishes`.* FROM `wishes` ORDER BY id, created_at desc LIMIT 3

Because the id column is unique, it totally determines the ordering—the created_at desc part of the query has no effect.

Once you add a default_scope with an order clause to a model, all subsequent finders on that model will have that order as their primary ordering. If you want some other order, you'll need to remember to add unscoped to the chain:

Wish.unscoped.order("created_at desc").limit(3)

And that's an accident waiting to happen.

 

 

 

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).