CODEWITHSUNDEEP
ruby-on-railsrubysqlitefloating-pointbigdecimal
user331471
user331471

Reputation: 893

Rails is messing up BigDecimals by throwing in Floating Point Logic

I'm playing around with rails a bit and I have found something strange. For storing a money value I use the typical decimal data type which active record converts to BigDecimal. I considered this to be precise and I thought to avoid the odd behavior of floating point math. But when I store 99.99 to the db everything works fine, but when the records gets loaded by active record it loses precision and converts to something like 99.9899999999. This looks like a floating point issue.

I made some tests and found out that creating a BigDecimal like this b = BigDecimal.new("99.99") leads to a "clean" variable but building it this way b = BigDecimal.new(99.99) leads to the "unclean" version that I want to avoid.

I guess, that ActiveRecord reconstructs the BigDecimal with an intermediate float when loading the record from the database. This is not what I want and I would like to know if it can be avoided.

Ruby Version 1.9.3p0 Rails 3.2.9 Sqlite 3.7.9

Upvotes: 4

Views: 1914

Answers (2)

DigitalRoss
DigitalRoss

Reputation: 146053

Don't use floating point for monetary values

Yes, exactly, SQLite is messing up your BigDecimal values.

The fundamental problem is that the FP format cannot store most decimal fractions correctly.

I believe you have about four choices:

  1. Round everything to, say, two decimal places so that you never notice the slightly-off values.
  2. Store your BigDecimal values in SQLite with the TEXT or BLOB storage class.
  3. Use a different db that has some sort of decimal string support.
  4. Scale everything to integral values and use the INTEGER storage class.

The problem is that FP fractions are rational numbers of the form x/2n. But the decimal monetary amounts have fractions that are x/(2n * 5m). The representations just aren't compatible. For example, in 0.01 ... 0.99, only 0.25, 0.50, and 0.75 have exact binary representations.

Upvotes: 2

mu is too short
mu is too short

Reputation: 434585

Your problem is that you're using SQLite and SQLite doesn't have native support for numeric(m,n) data types. From the fine manual:

1.0 Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

  • NULL. The value is a NULL value.
  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • BLOB. The value is a blob of data, stored exactly as it was input.

Read further down that page to see how SQLite's type system works.

Your 99.99 may be BigDecimal.new('99.99') in your Ruby code but it is almost certainly the REAL value 99.99 (i.e. an eight byte IEEE floating point value) inside SQLite and there goes the neighborhood.

So switch to a better database in your development environment; in particular, develop on top of whatever database you're going to be deploying on.

Upvotes: 5

Related Questions