Float as ID's? Can not query on them

Rails
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

On Apr 27, 2014, at 1:38 AM, tekram wrote:

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

On Apr 27, 2014, at 6:53 AM, Walter Lee D. [email protected]
wrote:

I believe this is correct behavior. A float is not precise for any definition of
that term.

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

–
Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice

4

On 27 April 2014 06:38, tekram [email protected] wrote:

“2014-04-25 19:05:10”, race: “WHITE”, ethnicity: “NOT HISPANIC”, age: 47>

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

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

On Apr 27, 2014, at 9:28 PM, tekram [email protected] wrote:

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.

–
Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice

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.