Float as ID's? Can not query on them

[tekram]

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

 

[Walter Lee Davis]

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

>
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/c3009ec3-2990-44ab-960e-caa75658b3b9%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Scott Ribe]

On Apr 27, 2014, at 6:53 AM, Walter Lee Davis <wa...@wdstudio.com> 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 Ribe
scott...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

[Colin Law]

On 27 April 2014 06:38, tekram <tashfee...@gmail.com> wrote:
> 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.

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

[tekram]

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. 

[Scott Ribe]

On Apr 27, 2014, at 9:28 PM, tekram <tashfee...@gmail.com> 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.

[DHoelzer]

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.