Re: [sqlite3-ruby] Why does sqlite3-ruby convert decimal(12,2) to float?

[Aaron Patterson]

SQLite3 has no concept of precise types like pg (for example).  There are only 4 types (regardless of what you specify for the column type):

  http://www.sqlite.org/datatype3.html

On Friday, May 31, 2013, Hadmut Danisch wrote:

Hi,

I was just tracking a nasty bug in Ruby on Rails. I have a Rails app that worked under heavy use for about 5 years without problems under Rails2 and with a sqlite3 database. The app deals with currency values stored in the sqlite3 database as decimal(12,2) NOT NULL . And it contains a plausibility check which adds some values and checks the sum (something like is a+b+c=d+e+f). It worked for years. The SQL column type decimal(12,2) is converted to the Ruby type BigDecimal.

After upgrading to RoR 3 the app started to fail for some values, and debugging revealed that sometimes values are read from the database incorrectly. E.g. the database contains  9.05 , but in RoR this becomes the BigDecimal 9.050000000000001 , which causes the sums to be incorrect.

The reason is that sqlite3-ruby delivers the column as a float, and RoR then ( after require 'bigdecimal' and 'bigdecimal/util' ) uses the method to_d to convert it to BigDecimal. Since sqlite3-ruby delivers the column as Float, this becomes

9.05.to_d  which is 9.050000000000001  instead of 9.05


So why does sqlite3-ruby deliver a precise column type like decimal(12,2) as the unprecise type Float, thus forcing rounding errors?


regards
Hadmut

--
You received this message because you are subscribed to the Google Groups "sqlite3-ruby" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlite3-ruby...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Aaron Patterson
http://tenderlovemaking.com/