Does ActiveRecord#first method always return record with minimal ID?

Question

Env: Rails 4.2.4, Postgres 9.4.1.0

Is there a guarantee that ActiveRecord#first method will always return a record with minimal ID and ActiveRecord#last - with maximum ID?

I can see from Rails console that for these 2 methods appropriate ORDER ASC/DESC is added to generated SQL. But an author of another SO thread Rails with Postgres data is returned out of order tells that first method returned NOT first record...

ActiveRecord first:

2.2.3 :001 > Account.first
Account Load (1.3ms) SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1

ActiveRecord last:

2.2.3 :002 > Account.last
Account Load (0.8ms) SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" DESC LIMIT 1

==========

ADDED LATER:

So, I did my own investigation (based on D-side answer) and the Answer is NO. Generally speaking the only guarantee is that first method will return first record from a collection. It may as a side effect add ORDER BY PRIMARY_KEY condition to SQL, but it depends on either records were already loaded into cache/memory or not.

Here's methods extraction from Rails 4.2.4: /activerecord/lib/active_record/relation/finder_methods.rb

# Find the first record (or first N records if a parameter is supplied).
# If no order is defined it will order by primary key.
# ---> NO, IT IS NOT. <--- This comment is WRONG.

def first(limit = nil)
  if limit
    find_nth_with_limit(offset_index, limit)
  else
    find_nth(0, offset_index) # <---- When we get there - `find_nth_with_limit` method will be triggered (and will add `ORDER BY`) only when its `loaded?` is false
  end
end


def find_nth(index, offset)
  if loaded?
    @records[index] # <--- Here's the `problem` where record is just returned by index, no `ORDER BY` is applied to SQL

  else
    offset += index
    @offsets[offset] ||= find_nth_with_limit(offset, 1).first
  end
end

Here's a few examples to be clear:

Account.first #  True, records are ordered by ID

a = Account.where('free_days > 1') # False, No ordering
a.first # False, no ordering, record simply returned by @records[index]

Account.where('free_days > 1').first # True, Ordered by ID

a = Account.all # False, No ordering    
a.first # False, no ordering, record simply returned by @records[index]

Account.all.first # True,  Ordered by ID

Now examples with has-many relationship:

Account has_many AccountStatuses, AccountStatus belongs_to Account

a = Account.first
a.account_statuses # No ordering

a.account_statuses.first
# Here is a tricky part: sometimes it returns @record[index] entry, sometimes it may add ORDER BY ID (if records were not loaded before)

Here is my conclusion: Treat method first as returning a first record from already loaded collection (which may be loaded in any order, i.e. unordered). And if I want to be sure that first method will return record with minimal ID - then a collection upon which I apply first method should be appropriately ordered before.

And Rails documentation about first method is just wrong and need to be rewritten. http://guides.rubyonrails.org/active_record_querying.html

1.1.3 first

The first method finds the first record ordered by the primary key. <--- No, it is not!

Answer 1

If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

http://www.postgresql.org/docs/9.4/static/queries-order.html (emphasis mine)

So ActiveRecord actually adds ordering by primary key, whichever that is, to keep the result deterministic. Relevant source code is easy to find using pry, but here are extracts from Rails 4.2.4:

# show-source Thing.all.first
def first(limit = nil)
  if limit
    find_nth_with_limit(offset_index, limit)
  else
    find_nth(0, offset_index)
  end
end

# show-source Thing.all.find_nth
def find_nth(index, offset)
  if loaded?
    @records[index]
  else
    offset += index
    @offsets[offset] ||= find_nth_with_limit(offset, 1).first
  end
end

# show-source Thing.all.find_nth_with_limit    
def find_nth_with_limit(offset, limit)
  relation = if order_values.empty? && primary_key
               order(arel_table[primary_key].asc) # <-- ATTENTION
             else
               self
             end

  relation = relation.offset(offset) unless offset.zero?
  relation.limit(limit).to_a
end

Answer 2

it may change depending of your Database engine, it returns always the minimal ID in mysql with first method but it does not works the same for postgresql, I had several issues with this when I was a nobai, my app was working as expected in local with mysql, but everything was messed up when deployed to heroku with postgresql, so for avoid issues with postgresql always order your records by id before the query:

Account.order(:id).first

The above ensures minimal ID for mysql, postgresql and any other database engine as you can see in the query:

SELECT  `accounts`.* FROM `accounts`  ORDER BY `accounts`.`id` ASC LIMIT 1