The gist of my question: is there a way to portably store and retrieve
Infinity in an ActiveRecord float column?
Following is an amusing saga, but it doesn’t really answer the question.
Step 1: As was mentioned in Infinity - Ruby - Ruby-Forum, you
can’t simply write a Ruby infinite value. Here are the results in Rails
3 with the sqlite 1.3.3 gem:
r = TestRec.create(:x => (1.0/0.0))
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column:
Infinity: INSERT INTO “test_recs” (“x”) VALUES (Infinity)
Step 2: So if storing infinity isn’t possible, how about a really big
value, like Float::MAX?
Whoa! x has changed from Float::MAX to Infinity. So it appears that if
you want to store Infinity in a DB column, you need to store Float::MAX
instead. At least for sqlite.
Naturally, I don’t trust this as a general solution. Can I assume it
will work this way across other DBs? If not, is there a generally
portable way to store Infinity?
The gist of my question: is there a way to portably store and retrieve
Infinity in an ActiveRecord float column?
Okay, first some clarifications:
Infinity is not value and cannot be represented by a float (lower case).
What is actually happening is that the Float class has been given the
ability to represent Infinity and -Infinity. This is a representation of
a concept (similar to nil) not a value. Databases are generally capable
of representing nil (or rather NULL), but have no means of representing
Infinity (AFAIK).
Regardless of Ruby’s somewhat odd implementation; (1.0 / 0.0) is not
equal to infinity. Anything divided by zero is undefined. Any real
number could represent the result, hence “undefined.” Some languages
will crash (or throw an exception) on such an operation. I suppose Ruby
implemented this in the Float class to provide a way for the language to
represent the abstract concept of Infinity.
This behavior is, AFAIK, unique to Ruby. So I suppose I’m saying that
there may not be a portable way to represent Infinity in relational
databases.