Why is my decimal slightly bigger after coming out of the database?

Question

I am a bit confused about a 2-digit decimal gaining an extra 0.000000000000000001 (or so) after going into and out of a database.

This is what I've done: (Rails 3.2.8)

Created a migration:

class CreateItems < ActiveRecord::Migration
  def change
    create_table :items do |t|
      t.column :price, :decimal, :precision => 16, :scale => 2
    end
  end
end

Created a model:

class Item < ActiveRecord::Base
end

Then:

$ rails c
>> i = Item.new
>> i.price = 9.46
>> i.save
>> Item.first.price
=> #<BigDecimal:46b3768,'0.9460000000 000001E1',27(45)>

It's a SQLite database, and it all looks ok in there:

$ rails db
>> select * from items;
1|9.46

Note that the only number I've noticed this happening with is 9.46. Where has the extra 0.00000000000001 come from?

Edit I understand that floating point representations of some numbers are not possible without small errors. But why does Item.first.price not equal BigDecimal.new('9.46')? Is SQLite storing a float as opposed to an integer and a number of times it should be divided by 10 (that's what I'd expect from a decimal column)? Or is there some gotcha in ActiveRecord I'm not aware of with retrieving the value from the DB? See below:

$ rails c
>> decimal = BigDecimal.new('9.46')
>> Item.first.price == decimal
=> false

Floating point does stuff like that. Read this: http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html — Qsario, Oct 09 '12 at 22:05
I suppose my assumption that the Ruby BigDecimal type didn't use floating point arithmetic was false (which I probably should have been able to work out on my own, actually) — hdgarrood, Oct 09 '12 at 22:21

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

9.46 is not exactly representable as an IEEE double-precision floating point number. This has a great many consequences, but means here that you're getting the value that is exactly representable and which is closest to what you put in.

If it's vital you get '9.46' out, store it in a column with TEXT affinity (and be aware that you're storing a string then, not a “number”).

edited May 23 '17 at 12:12

Community

1
1

answered Oct 09 '12 at 22:07

Donal Fellows

133,037
18
149
215

I also hear on the grapevine that SQLite4 may have decimal arithmetic to lessen the effect of this class of problem, but it won't affect this _exact_ one, as that's caused by the fact that it takes an infinite number of binary floating point digits to represent 9.46 exactly (just as it takes an infinite number of decimal digits to represent ⅓ exactly). – Donal Fellows Oct 09 '12 at 22:13
Does SQLite3 not have decimal columns then? – hdgarrood Oct 09 '12 at 22:18
I'm unaccepting as I've now realised what the question I want to ask is and edited accordingly; I hope that's not bad SE etiquette. – hdgarrood Oct 09 '12 at 22:50

score 1 · Answer 2 · answered Oct 09 '12 at 22:08

I would recommend reading this article: What Every Computer Scientist Should Know About Floating Arithmetic

The tl;dr version of the article...

Squeezing infinitely many real numbers into a finite number of bits requires an approximate representation. Although there are infinitely many integers, in most programs the result of integer computations can be stored in 32 bits. In contrast, given any fixed number of bits, most calculations with real numbers will produce quantities that cannot be exactly represented using that many bits. Therefore the result of a floating-point calculation must often be rounded in order to fit back into its finite representation. This rounding error is the characteristic feature of floating-point computation.

Seen links to that a few times; i suppose it's time I actually do read it. Thanks — hdgarrood, Oct 09 '12 at 22:24

score 1 · Accepted Answer · edited Oct 09 '12 at 22:43

1

I am not very familiar with ruby but İnstead of using

i.price = 9.46

Try using

i.price = BigDecimal.new('9.46')

edited Oct 09 '12 at 22:43

mu is too short

426,620
70
833
800

answered Oct 09 '12 at 22:08

ali köksal

1,217
1
12
19

But the float 9.46 will surely have been converted to a decimal when it was saved to the database? – hdgarrood Oct 09 '12 at 22:16
Yes, but since the conversion will be from a float value, it will not be exactly 9.46 – ali köksal Oct 09 '12 at 22:27
Surely it's not possible to store 9.460000000000001 in the price column in the DB, which has been defined to hold two digits? – hdgarrood Oct 09 '12 at 22:29
That really depends on the datastore implementation. – ali köksal Oct 09 '12 at 22:38
Using `i.price = BigDecimal.new('9.46')` has the same effect as `i.price = 9.46` if the object is saved and then retrieved from the DB – hdgarrood Oct 09 '12 at 22:53
Then. Somewhere along the way, in sqlite or ruby driver that value is converted to a float and back to bigdecimal. – ali köksal Oct 09 '12 at 23:20
Yes -- I now realise, that's my question. Where, and why? – hdgarrood Oct 09 '12 at 23:21
[https://rails.lighthouseapp.com/projects/8994/tickets/4871-bigdecimal-comparison-failure-in-sqlite3_test-on-192-head] says sqlite3 stores bigdecimals as floating numbers. – ali köksal Oct 09 '12 at 23:28
Ah yes, thanks, that's what I was looking for. Just tested the same with mysql and it does what I'd expect it to as well. Cheers! – hdgarrood Oct 09 '12 at 23:32
I could not read how you solved this? I see the link to the ticket says it has been resolved but I am experiencing the same problem right now. – Jepzen May 19 '14 at 12:05
none worked with me. Suggestions are most welcome. I tried in using BigDecimal.new('9.46') in rails c. It's truncating. db is sqlite3, rails 4+ – anubhav16 Jun 27 '15 at 05:41

Why is my decimal slightly bigger after coming out of the database?

3 Answers3