Why is Foo.first returning the last record?

Question

I have 2 records in Foo, with id's 1 and 2. Both created in that order. Bare in mind, in Postgres, records have no inherent order.

In Rails console. Foo.first and Foo.last returns the last record. I was under the impression that Foo.first would return the first record.

Here's the catch. The SQL queries look like:

SELECT "foos".* FROM "foos" LIMIT 1
SELECT "foos".* FROM "foos" ORDER BY "foos"."id" DESC LIMIT 1

The second query (Foo.last) has an ORDER BY DESC. So why doesn't AR have an ORDER BY ASC for .first? Whats the logic behind this? Seems a bit "inconsistent".

I can easily solve this by doing: Foo.order('id ASC').first instead. But looking for an explanation.

Answer 1

There isn't any logic to it, if there was any sense to first (or last for that matter), then it would raise an exception if you neglected to specify an explicit order either as an argument to first or as part of the current scope chain. Neither first nor last make any sense whatsoever in the context of a relational database unless there is an explicit ordering specified.

My guess is that whoever wrote first assumed that order by whatever_the_pk_is was implicit if there was no explicit order by. Then they probably did some experiments to empirically verify their assumption and it just happened to work as they expected with the particular tables and databases that they checked with (mini-rant: this is why you never ever assume unspecified behavior; if a particular behavior isn't explicitly specified, don't assume it even if the current implementation behaves that way or if empirical evidence suggests that it behaves that way).

If you trace through a simple M.first, you'll find that it does this:

limit(1).to_a[0]

No explicit ordering so you get whatever random ordering the database feels like using, that could be order by pk or it could be the table's block order on disk. If you trace through M.last, you'll get to find_last:

def find_last
  #...
        reverse_order.limit(1).to_a[0]
  #...
end

And reverse_order:

def reverse_order
  relation = clone
  relation.reverse_order_value = !relation.reverse_order_value
  relation
end

The @reverse_order_value instance variable isn't initialized so it will start out as nil and a ! will turn it into a true. And if you poke around for how @reverse_order_value is used, you'll get to reverse_sql_order:

def reverse_sql_order(order_query)
  order_query = ["#{quoted_table_name}.#{quoted_primary_key} ASC"] if order_query.empty?
  #...

and there's the author's invalid assumption about ordering laid bare for all to see. That line should probably be:

raise 'Specify an order you foolish person!' if order_query.empty?

I'd recommend that you always use .order(...).limit(1).first instead of first or last so that everything is nice and explicit; of course, if you wanted last you'd reverse the .order condition. Or you could always say .first(:order => :whatever) and .last(:order => :whatever) to again make everything explicit.