Float as ID's? Can not query on them

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 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

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.)

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

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.

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.

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.