Floating point imprecision in activerecord-jdbc-adapter?

[Justin Coyne]

I've got an application where I run a query and get the expected result of 5.8 from the database when I run the program under MRI.  When I switch to jruby (and jdbcmysql-adapter) I'm getting the result 5.80000019073486.

Here's the code for MRI:

SPEC = { :username => 'test', :password => 'test', :database=>'test', :host=>'localhost'}
require 'test/unit'
require 'rubygems'
require 'active_record'  ## For AR::ConnectionNotEstablished
require "active_record/connection_adapters/mysql_adapter"

class TestPrecision < Test::Unit::TestCase

  def test_correct_precision
    connection = ActiveRecord::Base.send("mysql_connection", SPEC)
    assert_equal "5.8", connection.select_rows("SELECT density from samples WHERE name = 'br1991a'")[0][0]
    assert_equal "float", connection.raw_connection.query("SHOW FIELDS FROM samples").all_hashes.select{|field| field["Field"] == 'density'}.first["Type"]
  end
end

And here's the same for jruby:

SPEC = { :username => 'test', :password => 'test', :database=>'test', :host=>'localhost'}
require 'test/unit'
require 'rubygems'
require 'active_record'  ## For AR::ConnectionNotEstablished

gem 'activerecord-jdbcmysql-adapter'
require 'active_record/connection_adapters/jdbcmysql_adapter.rb'

class TestPrecision < Test::Unit::TestCase

  def test_jdbc_precision
    connection = ActiveRecord::Base.send("mysql_connection", SPEC)
    assert_equal 5.8, connection.select_rows("SELECT density from samples WHERE name = 'br1991a'")[0][0]
  end
end


The MRI test passes but the output of the jruby test is:

  1) Failure:
test_jdbc_precision(TestPrecision) [problem_jruby.rb:16]:
<5.8> expected but was
<5.80000019073486>.

1 tests, 1 assertions, 1 failures, 0 errors


Does anybody know what's going on?

-Justin

[Uwe Kubosch]

How do you populate the table before running the test?  Can you show us
this?

On Mon, 2009-07-06 at 15:54 -0500, Justin Coyne wrote:

> I've got an application where I run a query and get the expected
> result of 5.8 from the database when I run the program under MRI.
> When I switch to jruby (and jdbcmysql-adapter) I'm getting the result
> 5.80000019073486.
>
> Here's the code for MRI:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
> require "active_record/connection_adapters/mysql_adapter"
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_correct_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal "5.8", connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>     assert_equal "float", connection.raw_connection.query("SHOW FIELDS
> FROM samples").all_hashes.select{|field| field["Field"] ==
> 'density'}.first["Type"]
>   end
> end
>
> And here's the same for jruby:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
>
> gem 'activerecord-jdbcmysql-adapter'
> require 'active_record/connection_adapters/jdbcmysql_adapter.rb'
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_jdbc_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal 5.8, connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>   end
> end
>
>
> The MRI test passes but the output of the jruby test is:
>
>   1) Failure:
> test_jdbc_precision(TestPrecision) [problem_jruby.rb:16]:
> <5.8> expected but was
> <5.80000019073486>.
>
> 1 tests, 1 assertions, 1 failures, 0 errors
>
>
> Does anybody know what's going on?
>
> -Justin
>
>

--

With kind regards,
Uwe Kubosch
Kubosch Consulting
Norway
http://kubosch.no/

[Justin Coyne]

I'm just going into a new empty database and running the following sql.

create table samples
( id int(11) primary key auto_increment,
  name varchar(10) not null,
  density float not null);

insert into samples (name, density) values ('br1991a', 5.8);

Sorry for not including this earlier.

-Justin

On Tue, Jul 7, 2009 at 1:41 AM, Uwe Kubosch <uwe@...> wrote:

How do you populate the table before running the test?  Can you show us
this?

On Mon, 2009-07-06 at 15:54 -0500, Justin Coyne wrote:
> I've got an application where I run a query and get the expected
> result of 5.8 from the database when I run the program under MRI.
> When I switch to jruby (and jdbcmysql-adapter) I'm getting the result
> 5.80000019073486.
>
> Here's the code for MRI:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
> require "active_record/connection_adapters/mysql_adapter"
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_correct_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal "5.8", connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>     assert_equal "float", connection.raw_connection.query("SHOW FIELDS
> FROM samples").all_hashes.select{|field| field["Field"] ==
> 'density'}.first["Type"]
>   end
> end
>
> And here's the same for jruby:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
>
> gem 'activerecord-jdbcmysql-adapter'
> require 'active_record/connection_adapters/jdbcmysql_adapter.rb'
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_jdbc_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal 5.8, connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>   end
> end
>
>
> The MRI test passes but the output of the jruby test is:
>
>   1) Failure:
> test_jdbc_precision(TestPrecision) [problem_jruby.rb:16]:
> <5.8> expected but was
> <5.80000019073486>.
>
> 1 tests, 1 assertions, 1 failures, 0 errors
>
>
> Does anybody know what's going on?
>
> -Justin
>
>

--
With kind regards,
Uwe Kubosch
Kubosch Consulting
Norway
http://kubosch.no/

[Justin Coyne]

Okay, here's the real bug. It's not in jdbc at all. It appears to be deeper in the guts of jruby.

jruby -e "require 'java';  puts JRuby.runtime.newFloat(java.lang.Float.new(5.8)).to_s()"

5.80000019073486

-Justin

On Tue, Jul 7, 2009 at 9:38 AM, Justin Coyne <digger250@...> wrote:

I'm just going into a new empty database and running the following sql.

create table samples
( id int(11) primary key auto_increment,
  name varchar(10) not null,
  density float not null);

insert into samples (name, density) values ('br1991a', 5.8);

Sorry for not including this earlier.

-Justin

On Tue, Jul 7, 2009 at 1:41 AM, Uwe Kubosch <uwe@...> wrote:

How do you populate the table before running the test?  Can you show us
this?

On Mon, 2009-07-06 at 15:54 -0500, Justin Coyne wrote:
> I've got an application where I run a query and get the expected
> result of 5.8 from the database when I run the program under MRI.
> When I switch to jruby (and jdbcmysql-adapter) I'm getting the result
> 5.80000019073486.
>
> Here's the code for MRI:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
> require "active_record/connection_adapters/mysql_adapter"
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_correct_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal "5.8", connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>     assert_equal "float", connection.raw_connection.query("SHOW FIELDS
> FROM samples").all_hashes.select{|field| field["Field"] ==
> 'density'}.first["Type"]
>   end
> end
>
> And here's the same for jruby:
>
> SPEC = { :username => 'test', :password =>
> 'test', :database=>'test', :host=>'localhost'}
> require 'test/unit'
> require 'rubygems'
> require 'active_record'  ## For AR::ConnectionNotEstablished
>
> gem 'activerecord-jdbcmysql-adapter'
> require 'active_record/connection_adapters/jdbcmysql_adapter.rb'
>
> class TestPrecision < Test::Unit::TestCase
>
>   def test_jdbc_precision
>     connection = ActiveRecord::Base.send("mysql_connection", SPEC)
>     assert_equal 5.8, connection.select_rows("SELECT density from
> samples WHERE name = 'br1991a'")[0][0]
>   end
> end
>
>
> The MRI test passes but the output of the jruby test is:
>
>   1) Failure:
> test_jdbc_precision(TestPrecision) [problem_jruby.rb:16]:
> <5.8> expected but was
> <5.80000019073486>.
>
> 1 tests, 1 assertions, 1 failures, 0 errors
>
>
> Does anybody know what's going on?
>
> -Justin
>
>

--
With kind regards,
Uwe Kubosch
Kubosch Consulting
Norway
http://kubosch.no/

[Charles Oliver Nutter-4]

On Tue, Jul 7, 2009 at 2:26 PM, Justin Coyne<digger250@...> wrote:
> Okay, here's the real bug. It's not in jdbc at all. It appears to be deeper
> in the guts of jruby.
>
> jruby -e "require 'java';  puts
> JRuby.runtime.newFloat(java.lang.Float.new(5.8)).to_s()"
>
> 5.80000019073486

It looks like the conversion from single-precision float to
double-precision produces this effect.
java.lang.Float.new(5.8)doubleValue produces the same result.

Here's a longer demonstration:

import java.lang.reflect.*;

public class Floaty {
  public static void main(String[] args) {
    Float f = 5.8f;
    System.out.println(f);
    System.out.println(f.floatValue());
    System.out.println(f.doubleValue());
    System.out.println((double)f.floatValue());
    System.out.println(new Double(f.floatValue()).doubleValue());
    System.out.println((double)5.8f);
    System.out.println(new Double(5.8f));
    System.out.println(5.8);
    System.out.println(new Double(5.8));
    System.out.println(new Double("5.8"));
  }
}

And the output:

5.8
5.8
5.800000190734863
5.800000190734863
5.800000190734863
5.800000190734863
5.800000190734863
5.8
5.8
5.8

This starts to look like a "WONTFIX" or maybe a "CANTFIX" since it's
Java/JDK/JVM float-conversion behavior at play here. But if we're
getting floats out of the database somewhere in the JDBC adapters,
perhaps we should ask for Double or double instead?

- Charlie

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Justin Coyne]

Ad-hoc patch available on the ticket now:

http://kenai.com/jira/browse/ACTIVERECORD_JDBC-25

-Justin

On Tue, Jul 7, 2009 at 2:53 PM, Charles Oliver Nutter <headius@...> wrote:

On Tue, Jul 7, 2009 at 2:26 PM, Justin Coyne<digger250@...> wrote:
> Okay, here's the real bug. It's not in jdbc at all. It appears to be deeper
> in the guts of jruby.
>
> jruby -e "require 'java';  puts
> JRuby.runtime.newFloat(java.lang.Float.new(5.8)).to_s()"
>
> 5.80000019073486

It looks like the conversion from single-precision float to
double-precision produces this effect.
java.lang.Float.new(5.8)doubleValue produces the same result.

Here's a longer demonstration:

import java.lang.reflect.*;

public class Floaty {
 public static void main(String[] args) {
   Float f = 5.8f;
   System.out.println(f);
   System.out.println(f.floatValue());
   System.out.println(f.doubleValue());
   System.out.println((double)f.floatValue());
   System.out.println(new Double(f.floatValue()).doubleValue());
   System.out.println((double)5.8f);
   System.out.println(new Double(5.8f));
   System.out.println(5.8);
   System.out.println(new Double(5.8));
   System.out.println(new Double("5.8"));
 }
}

And the output:

5.8
5.8
5.800000190734863
5.800000190734863
5.800000190734863
5.800000190734863
5.800000190734863
5.8
5.8
5.8

This starts to look like a "WONTFIX" or maybe a "CANTFIX" since it's
Java/JDK/JVM float-conversion behavior at play here. But if we're
getting floats out of the database somewhere in the JDBC adapters,
perhaps we should ask for Double or double instead?

- Charlie

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email

[Uwe Kubosch]

On Tue, 2009-07-07 at 14:53 -0500, Charles Oliver Nutter wrote:

> On Tue, Jul 7, 2009 at 2:26 PM, Justin Coyne<digger250@...> wrote:
> > Okay, here's the real bug. It's not in jdbc at all. It appears to be deeper
> > in the guts of jruby.
> >
> > jruby -e "require 'java';  puts
> > JRuby.runtime.newFloat(java.lang.Float.new(5.8)).to_s()"
> >
> > 5.80000019073486
>
> It looks like the conversion from single-precision float to
> double-precision produces this effect.
> java.lang.Float.new(5.8)doubleValue produces the same result.

> This starts to look like a "WONTFIX" or maybe a "CANTFIX" since it's
> Java/JDK/JVM float-conversion behavior at play here. But if we're
> getting floats out of the database somewhere in the JDBC adapters,
> perhaps we should ask for Double or double instead?

When using Float/Double, you should always be prepared for small
variations during handling.

If you want to store and retrieve a decimal number without it being
subject to rounding etc, you should use the DECIMAL column type.  It
will be converted to a BigDecimal when loaded by ActiveRecord, and it
will not suffer any rounding/conversion changes.

--
With kind regards,
Uwe Kubosch
Kubosch Consulting
Norway
http://kubosch.no/

[Michael Campbell-4]

Uwe Kubosch wrote:

> When using Float/Double, you should always be prepared for small
> variations during handling.
>
> If you want to store and retrieve a decimal number without it being
> subject to rounding etc, you should use the DECIMAL column type.  It
> will be converted to a BigDecimal when loaded by ActiveRecord, and it
> will not suffer any rounding/conversion changes.

+1.  That MRI and JRuby do different things seems interesting, but beyond that
this is pretty standard post-COBOL-BCD numeric computing fare.

For the OP, I think most unit test frameworks provide some sort of
assertEquals(floatingpoint_value_1, floatingpoint_value_2, allowed_variance)
type of call.  If not, it's trivial to write.



---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Justin Coyne]

I'm going to assert that this test should pass:

  def test_jdbc_precision
    Sample.delete_all()
    assert_nil Sample.find_by_name('br1991a')
    density = 5.8
    Sample.new(:name=>'br1991a', :density=>density).save!
    sample = Sample.find_by_name('br1991a')
    assert_not_nil sample
    assert_equal density, sample.density
  end


Presently it doesn't:


  1) Failure:
test_jdbc_precision(TestPrecision) [problem_rails.rb:25]:
<5.8> expected but was
<5.80000019073486>.


This is because the activerecord-jdbc adapter is casting the value from the database to a float (32 bit) and then to a RubyFloat which is backed by a java double type.   To be consistent, activerecord-jdbc should just use the same representation that jruby uses internally (a double). 


-Justin

On Wed, Jul 8, 2009 at 12:12 PM, Michael Campbell <michael.campbell@...> wrote:

Uwe Kubosch wrote:

When using Float/Double, you should always be prepared for small
variations during handling.

If you want to store and retrieve a decimal number without it being
subject to rounding etc, you should use the DECIMAL column type.  It
will be converted to a BigDecimal when loaded by ActiveRecord, and it
will not suffer any rounding/conversion changes.

+1.  That MRI and JRuby do different things seems interesting, but beyond that this is pretty standard post-COBOL-BCD numeric computing fare.

For the OP, I think most unit test frameworks provide some sort of assertEquals(floatingpoint_value_1, floatingpoint_value_2, allowed_variance) type of call.  If not, it's trivial to write.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

  http://xircles.codehaus.org/manage_email

[Charles Oliver Nutter-4]

On Wed, Jul 8, 2009 at 3:16 PM, Justin Coyne<digger250@...> wrote:

>   1) Failure:
> test_jdbc_precision(TestPrecision) [problem_rails.rb:25]:
> <5.8> expected but was
> <5.80000019073486>.
>
>
> This is because the activerecord-jdbc adapter is casting the value from the
> database to a float (32 bit) and then to a RubyFloat which is backed by a
> java double type.   To be consistent, activerecord-jdbc should just use the
> same representation that jruby uses internally (a double).

Agreed...whenever possible the floating-point representation fed to
JRuby should be as high precision as possible, since we'll always
upcast to double.

- Charlie

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email