When floats don’t float
I had the pleasure/pain of discovering first hand the issue of interpretation when it comes to data type intent. The Rails migration feature is one of the better features. To be able to quickly outline the schema of a table and produce it in a database from a rake task is the definition of love for a developer. It goes like this - I want to use my language (Ruby) and I want to create this table in the fewest amount of keystrokes possible:
create_table "student" do |table| table.string :name table.integer :zip table.float :gpa end
Now from the command line I run rake db:migrate and blamo - table made. No SQL statements. And I can create this table on any database I choose. SQLite, MySQL, Oracle, Postgres and unfortunately SQL Server.
Except when the world crashes down around you and your float values are being returned as integers. Yep. The student that only got 0.59 GPA (rough year with no electives) is now getting a 0 GPA because your database driver says so.
What?
That’s right. The database driver says so. In this case Oracle JDBC. Well it’s not all Oracle’s fault. They had a lot of help from IBM and WebSphere. Here’s how this all panned out.
- I performed the migration to an Oracle database using the regular Ruby OCI8 driver.
- Fire up the Rails app inside a WebSphere server using JRuby and connecting to the database with JNDI Oracle JDBC driver.
- Created a few records with the correct float values (0.24, 1.2, etc)
- Look at records in DB and see 0.24, 1.2 etc.
- Look at records from Rails app and see 0, 0 etc.
- Profit?
Hmm… That is rough. The app works. I know it. It worked all day long on my machine. Ha. Famous last words.
So after days of debugging (yes I said days) I finally found the problem. It was all in the interpretation. Ruby OCI8 read my table.float :gpa statement and said:
“oh - he wants a NUMBER column named ‘gpa’”
Well OK that works. Oracle defaults that way anyways. Oracle will recognize that as a field that can store 1000 or 1000.10. No problem. And the ActiveRecord JDBC adapter will receive that schema and say:
“oh - this column is a NUMBER with no precision or scale so we’ll default to decimal like Oracle does”
The problem occurs when you ask WebSphere to provide you the Oracle JDBC driver through JNDI and let WebSphere wrap it with it’s helpers. The irony. See the DataStoreHelpers will tell the ActiveRecord JDBC adapter that the column is actually a NUMBER(22) with a scale of 0. What’s wrong with that you say? This is what’s wrong (from activerecord-jdbc-adapter/lib/jdbc_adapter/jdbc_oracle.rb):
def simplified_type(field_type) case field_type when /^number\(1\)$/i : :boolean when /char/i : :string when /float|double/i : :float when /int/i : :integer when /num|dec|real/i : @scale == 0 ? :integer : :decimal when /date|time/i : :datetime when /clob/i : :text when /blob/i : :binary end end
Yikes. If the scale is 0 the ActiveRecord JDBC adapter says it’s an integer. Rightly so. So now a value of 0.24 is 0. Shit. Why would WebSphere Oracle DataStoreHelpers do this? If the data type is NUMBER with no precision and no scale, why would it create a precision of 22 and a scale of 0? Is that interpretation?
And the final kick in the pants is - if you run the migrations using JRuby and the ActiveRecord JDBC adapter - it puts the data type as FLOAT(63). Ouch.
I don’t know how to end this post. There is no morale. I just wanted to share my pain.