Forum: Ruby on Rails Float as ID's? Can not query on them

F9264303ef2808a8b05ff3e63cbc9459?d=identicon&s=25 tekram (Guest)
on 2014-04-27 07:39
(Received via mailing list)
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*
A47e0a6beeb9d048ff054fc1c3a97418?d=identicon&s=25 Walter Davis (walterdavis)
on 2014-04-27 14:55
(Received via mailing list)
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
15eb13d662803f57bc4aea59704988b4?d=identicon&s=25 Scott Ribe (Guest)
on 2014-04-27 17:00
(Received via mailing list)
On Apr 27, 2014, at 6:53 AM, Walter Lee Davis <waltd@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_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
4c6bde00168d595053c09aac7e487f8e?d=identicon&s=25 Colin Law (Guest)
on 2014-04-27 17:03
(Received via mailing list)
On 27 April 2014 06:38, tekram <tashfeen.ekram@gmail.com> 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
F9264303ef2808a8b05ff3e63cbc9459?d=identicon&s=25 tekram (Guest)
on 2014-04-28 05:30
(Received via mailing list)
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.
15eb13d662803f57bc4aea59704988b4?d=identicon&s=25 Scott Ribe (Guest)
on 2014-04-28 06:34
(Received via mailing list)
On Apr 27, 2014, at 9:28 PM, tekram <tashfeen.ekram@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.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
E98f32db77fde25415752e21f78a41ee?d=identicon&s=25 DHoelzer (Guest)
on 2014-04-28 16:27
(Received via mailing list)
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.
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.