I am using Postgres 9.1.2, Rails 3.2.1 and pg 0.12.2. Simple User model. For some bizarre reason when you do a select * from users; the first result is actually the record that was inserted second. The order for all other records is the fine. I have reproduced it a few times.
So if there are only 2 records a User.first and User.last give the same result and even with multiple records the record with user_id 2 is fetched on User.first.
Model.first results in a query without an ORDER BY clause:
SELECT "users".* FROM "users" LIMIT 1;
I don't know why I am getting this erratic behaviour but wouldn't it make sense to have first add an ORDER BY clause so that User.first generates:
SELECT "users".* FROM "users" ORDER BY "users"."id" LIMIT 1;
Unless you have an ORDER BY there isn't a defined order, so it can't be out of order. The order is intended to be whatever is convenient for the back end.
I was trying to see what the rationale behind that was. The fact that first gives you different results depending on the backend just doesn't seem right to me.
It doesn't need to seem "right" it only has to seem like the SQL standard. :) It is not ordered unless you declare an order. A table doesn't "have" an order. The very concept of an order is only attached to your request to retrieve some data.
One of the issues that the back end deals with, as an example, is that most tables have at least one variable width column. So the physical storage will have to get moved around when rows change. So imposing an order would slow a lot of things down for no reason, where it is faster for the db to return the physical order.
Yes, I understand all of that. But there is never any case where using first makes sense except in a console session and slowing things down there is a non issue. Also last uses an ORDER BY so the speed argument is bogus and it creates an inherent asymmetry between first and last.
You are right, sorry.
@ak2196 @rubypanther please try/review the pull request if you have the time. =D
Seems this fixed in master according to @tenderlove: #5153 (comment).
@rafaelfranca you can close this one too.
I don't think so. We still need to fix it in master. But when it was fixed we can closed it. I'll make sure that he guy will add this issue number in the commit message. Thanks.
Made `first` finder consistent among database engines by adding a
default order clause (fixes #5103)
Fixed by #5153