CODEWITHSUNDEEP
ruby-on-railsprecision
dontangg
dontangg

Reputation: 4809

Rails precision error

When I run this in my Rails application:

my_envelope.transactions.sum(:amount)

This SQL is shown in the log files:

SQL (0.3ms)  SELECT SUM("transactions"."amount") AS sum_id FROM "transactions" WHERE (envelope_id = 834498537)

And this value is returned:

<BigDecimal:1011be570,'0.2515999999 9999997E2',27(27)>

As you can see, the value is 25.159999. It should be 25.16. When I run the same SQL on the database myself, the correct value is returned.

I'm a little confused because I know that there are precision problems with Floats, but it is returning a BigDecimal. The SQL column type is decimal. I'm using using sqlite3 (3.6.17) and sqlite3-ruby (1.3.2). Any ideas?

Update 1

Here are the results when I run this directly using the SQLite3-ruby interface.

$ rails c test
Loading test environment (Rails 3.0.3)
irb(main):001:0> db = SQLite3::Database.new("db/test.sqlite3")
=> #<SQLite3::Database:0x5242020>
irb(main):002:0> db.execute("SELECT SUM(amount) FROM transactions WHERE envelope_id = 834498537")
=> [[25.159999999999997]]

The class of that number is Float. btw, the three numbers it sums are -40.25, 100, and -34.59.

Update 2

After more research, it turns out that this is just the way the sqlite3 works. It returns a double (same as Ruby Float) to sqlite3-ruby and sqlite3-ruby just passes it on to Rails as a Float. Then, Rails converts it to BigDecimal because the column type is decimal. Before Ruby 1.9, Ruby would round this number for us and we wouldn't see the problem.

Upvotes: 3

Views: 767

Answers (1)

noodl
noodl

Reputation: 17408

It's not an elegant solution but you can bypass the creation of the Float object by casting the value of your aggregate calculation to TEXT in the query. This "fixes" the rounding error. Please update this question if you find a better solution (such as by patching the sqlite3-ruby driver).

SELECT CAST(SUM(amount) AS TEXT) FROM transactions WHERE envelope_id = 834498537

By casting to a string you're allowing Active Record to call BigDecimal's constructor which requires a string and bypassing Float with its inaccurate ISO floating point issues.

By the way, I doubt it's a good idea to name your table transactions. That's bound to conflict with some other class name or database-specific keyword at some point.

Upvotes: 2

Related Questions