CODEWITHSUNDEEP
mysqlruby-on-railsrubydoubleprecision
Luana Maia
Luana Maia

Reputation: 1

Migrate changing column from float to double changing higher values in ruby on rails

I'm trying to make a migrate that changes the column type from float to double, because the float type rounds off some values in the database, such as:

posted value: 23337840
value in database with column type float: 23337800

posted value: 23337870
value in database with column type float: 3337900

When I run the migrate to double these values are corrected to the correct one, with due precision, however values like 1 trillion are wrong.

value in column in float type: 1000000000000
value in column in double type: 999999995904

Note: When a new insert is made in the database with the column in double type with a value of 1 trillion, the value is correct. The error only occurs when a value is migrated from float to double.

Does anyone know why this occurs since the double should accept larger values? And what way can it be done so that these values already in the database remain correct?

Upvotes: 0

Views: 657

Answers (1)

Stefan
Stefan

Reputation: 114188

A MySQL FLOAT database column stores (by default) 32-bit single precision floats. These values have limited precision which becomes most obvious for very small or very large numbers. For example, these are the available 32-bit floating point values closest to 1,000,000,000,000: (with their respective difference)

 999999799296.0  # -200,704
 999999864832.0  # -135,168
 999999930368.0  #  -69,632
 999999995904.0  #   -4,096
1000000061440.0  #  +61,440
1000000126976.0  # +126,976
1000000192512.0  # +192,512

If you have a value in-between, it will be converted to the closest float available. Attempting to store the integer 1,000,000,000,000 as a 32-bit float will actually store 999,999,995,904.0 because that's the closest value available, "only" off by 4,096.

The values FLOAT can represent is a subset of the values DOUBLE can represent. Which means that converting a column from FLOAT to DOUBLE is a lossless operation. There's no further precision loss, its numeric value won't change. However, there's no way to reconstruct the original value from the 32-bit float, i.e. converting 999,999,995,904.0 from FLOAT to DOUBLE will still be 999,999,995,904.0.

Storing new values into a DOUBLE column do benefit from the higher precision. That's why you can have 1,000,000,000,000.0 in your DOUBLE column.

Note that floats in Ruby are 64 bit nowadays, i.e. Ruby's Float corresponds to MySQL's DOUBLE.

Upvotes: 1

Related Questions