:first and :all giving different first records

I’m trying to track down a strange test error. This project has Rails
2.1.0 frozen in vendor/rails . On my MacBook Pro, the problem boils
down to this:

(rdb:1) Invoice.first(:order=>‘id desc’)

#<Invoice id: 3, type: “Invoice”, name: “Invoice 1 for Job 1”, memo:
nil, created_at: “2007-09-03 00:00:00”, updated_at: “2009-03-30
16:49:48”, est_inv_number: “1-03”, status: “free”, qb_txn_id: nil,
per_quote: false, bill_to: nil, printed_at: nil, emailed_at: nil,
faxed_at: nil, invoiceable_type: “Job”, invoiceable_id: 1, qb_sent_at:
nil, company_id: 850010094, itemize_tax_markup: false,
bill_to_customer_id: nil, qb_class_override: nil, proposal_loaded:
false, proposal_date: nil, proposal_re: nil, proposal_attn: nil,
proposal_body: nil, proposal_include: nil, proposal_exclude: nil,
proposal_sig: nil, date: “2007-09-03”>

(rdb:1) Invoice.find(:all, :order=>‘id desc’).first

#<Invoice
id: 9, type: “Invoice”, name: “Estimate 1 for Job 1”, memo: nil,
created_at: “2009-03-30 16:52:48”, updated_at: “2009-03-30 16:52:48”,
est_inv_number: “1-05”, status: “free”, qb_txn_id: nil, per_quote:
false, bill_to: nil, printed_at: nil, emailed_at: nil, faxed_at: nil,
invoiceable_type: “Job”, invoiceable_id: 1, qb_sent_at: nil,
company_id: 850010094, itemize_tax_markup: false, bill_to_customer_id:
nil, qb_class_override: nil, proposal_loaded: false, proposal_date:
nil, proposal_re: nil, proposal_attn: nil, proposal_body: nil,
proposal_include: nil, proposal_exclude: nil, proposal_sig: nil, date:
“2009-03-30”>

I’m in the debugger in the middle of a test run. I have another
Mac, running the same version of the code, checked out from the same
repository, on which this works fine. The SQL queries produced for
these two lines is identical on both machines:

Invoice Load (0.000516) SELECT * FROM est_invs WHERE ( (est_invs.type = ‘Invoice’ ) ) ORDER BY id desc LIMIT 1

and

Invoice Load (0.000536) SELECT * FROM est_invs WHERE ( (est_invs.type = ‘Invoice’ ) ) ORDER BY id desc

Here’s another way of looking at the same weirdness:

(rdb:1) Invoice.first.id
3
(rdb:1) Invoice.last.id

3
(rdb:1) Invoice.all.map(&:id)
[3, 4, 5, 6, 9]
(rdb:1) Invoice.all.first.id

3

(rdb:1) Invoice.all.last.id
9

On my other Mac, running the same code, same test, etc., it looks like this (which is how it should look):

(rdb:1) Invoice.first.id
3
(rdb:1) Invoice.last.id

9
(rdb:1) Invoice.all.map(&:id)
[3, 4, 5, 6, 9]
(rdb:1) Invoice.all.first.id
3
(rdb:1) Invoice.all.last.id

9

I can’t figure out what’s going on. I’ve been staring at this off and
on for days, and I’m out of ideas. Anyone have any good ones?

Ryan,

I had something similar on Rails 2.2.2 recently. In my case,
Object.first was returning nil but Object.all.first was working. I
banged my head for a bit then decided it wasn't important to "solve"
it at this time as the client just needed the project done, so I left
it as Object.all.first, which isn't as efficient, but still works.

I'd be interested if you figured this out. It seems so arbitrary.

One other thing that I would try if I were you: can you connect
directly to MySQL and try running the queries there by hand and see
what they return? That may help track down whether it's even a Rails
issue at all. I use CocoaSQL on my mac which makes it really easy to
copy-n-paste the SQL queries from the log/debugger and try them.

If it works fine in SQL directly, then you've ruled that out and can
focus on Rails, AR, caching and the like. Try it from script/console
as well to rule out web server (which probably isn't related anyway,
but it's always good rule things out).

If it doesn't work right in SQL, then you've ruled out Rails, and can
focus on what is screwed up with MySQL.

Best of luck! Post your response when you figure it out. :slight_smile:

-Danimal

If it doesn't work right in SQL, then you've ruled out Rails, and can
focus on what is screwed up with MySQL.

It's a mysql bug: see http://bugs.mysql.com/bug.php?id=37830 and
http://lists.mysql.com/commits/49739

Fred

Thank you! That was it. You’re the man! :slight_smile:

I went ahead and jumped from MySQL 5.0.x to 5.1.32, so no doubt I will soon have new adventures to relate.