Float as ID's? Can not query on them

rubyonrails-talk
1

I have set my float id’s. I have a Rails 3.0 app and using a MySQL database. It seemed to work fine on a Postgres database.

Here is my problem:

irb(main):033:0> Patient.first

Patient Load (1.3ms) SELECT patients.* FROM patients LIMIT 1

=> #<Patient pid: 29312000.0, created_at: “2014-04-25 19:05:10”, updated_at: “2014-04-25 19:05:10”, race: “WHITE”, ethnicity: “NOT HISPANIC”, age: 47>

irb(main):034:0> Patient.find_by_pid(Patient.first.pid)

Patient Load (1.1ms) SELECT patients.* FROM patients LIMIT 1

Patient Load (346.1ms) SELECT patients.* FROM patients WHERE patients.pid = 29312000.0 LIMIT 1

=> nil

Basically, I can not use where I have used Float as a part of an Active Record Query. Another similar problem on another model.

irb(main):035:0> Lab.first

Lab Load (1.3ms) SELECT labs.* FROM labs LIMIT 1

=> #<Lab id: 1, patient_id: 29312000.0, code_id: 1, lab_date: “2010-11-29”, value: 51, age: 47, created_at: “2014-04-25 19:05:10”, updated_at: “2014-04-25 19:05:10”, difference: 12, report_id: 2432930000.0>

irb(main):036:0> Lab.find_by_patient_id(Lab.first.patient_id)

Lab Load (1.2ms) SELECT labs.* FROM labs LIMIT 1

Lab Load (341.4ms) SELECT labs.* FROM labs WHERE labs.patient_id = 29312000.0 LIMIT 1

2

I have set my float id's. I have a Rails 3.0 app and using a MySQL database. It seemed to work fine on a Postgres database.

Here is my problem:

irb(main):033:0> Patient.first   Patient Load (1.3ms) SELECT `patients`.* FROM `patients` LIMIT 1 => #<Patient pid: 29312000.0, created_at: "2014-04-25 19:05:10", updated_at: "2014-04-25 19:05:10", race: "WHITE", ethnicity: "NOT HISPANIC", age: 47> irb(main):034:0> Patient.find_by_pid(Patient.first.pid)   Patient Load (1.1ms) SELECT `patients`.* FROM `patients` LIMIT 1   Patient Load (346.1ms) SELECT `patients`.* FROM `patients` WHERE `patients`.`pid` = 29312000.0 LIMIT 1 => nil

Basically, I can not use where I have used Float as a part of an Active Record Query. Another similar problem on another model.

irb(main):035:0> Lab.first   Lab Load (1.3ms) SELECT `labs`.* FROM `labs` LIMIT 1 => #<Lab id: 1, patient_id: 29312000.0, code_id: 1, lab_date: "2010-11-29", value: 51, age: 47, created_at: "2014-04-25 19:05:10", updated_at: "2014-04-25 19:05:10", difference: 12, report_id: 2432930000.0> irb(main):036:0> Lab.find_by_patient_id(Lab.first.patient_id)   Lab Load (1.2ms) SELECT `labs`.* FROM `labs` LIMIT 1   Lab Load (341.4ms) SELECT `labs`.* FROM `labs` WHERE `labs`.`patient_id` = 29312000.0 LIMIT 1

I believe this is correct behavior. A float is not precise for any definition of that term. An integer or decimal is precise, because you define at what point you stop caring about non-integer value differences (0 or more decimal digits). Above all, an ID must be precise -- there can be only one, as it were.

Walter

3

A float is precise for many values, in particular any integer < 2^52 - 1 for 64-bit float values.

So the 3 most pertinent questions to OP are:

- Why the heck would you use a float as an id???

- What range of ids do you expect to use: min, max, and whether or not with fractional parts? (And if you will be using fractions, do you understand the rounding issues well enough to guarantee that for a given id, you will always provide the same float value?)

- Float is 64-bit on PostgreSQL. Is it 64 bit or 32 bit on MySQL? (Because if it's only 32 bit, then 29,312,000 is well out of the range which can be represented exactly.)

4

One of the fundamental rules of floating point numbers is that you should never normally test for exact equality between them, even if you believe they have been set to the same value. For example you cannot guarantee that the value in the db is held to the same precision as that in memory, so even writing it to the db and reading it back can change its value.

In addition it is nearly always a bad idea to override the default Rails id scheme, unless you have a very good reason. Let Rails set the id and have another field for your float value (if it really needs to be a float), but even then you will not be able to test for exact equality. Perhaps you can use a scaled integer type instead.

Colin

5

Thanks. That is helpful.

What if I added a column that was an integer and copied the float to that column after converting it to an integer. I do not think any of them are out range. I kept as float as I took an outside dataset and imported it into this Rails app.

6

Why? If they're integers, make the column integer. If not, figure out why they're floats and what you can do to handle the data.

7

Another approach that I have not heard mentioned…

If you really need to store floats then look into fixed precision mathematics which will allow you to store them as integers (provided you can accept a more limited total range) while retaining the precision of the floating point number. It will also solve your select problems.