Numerical calculation returns string with DBI/Mysql

[Ken Bloom]

When I run the following query in DBI/Mysql, I'm finding that DBI is
returning the rho column as a string (rather than a float), thus forcing me
to convert it to a float with to_f. (I lose precision because the string
only returns the result to three decimal places). Any idea why DBI/Mysql is
returning the result of a numerical calculation as a string?

I'm running
mysql-5.0.34_alpha20070101-r61
mysql-ruby-2.7.2
ruby-dbi-0.0.21-r2
ruby-1.8.5_p2
on Gentoo.

dbh.execute("select childcounts.name name, childcounts.count/
parentcounts.count rho from parentcounts, childcounts where
parentcounts.left_key=childcounts.parent_left")

 

[Robert Dober]

When I run the following query in DBI/Mysql, I'm finding that DBI is
returning the rho column as a string (rather than a float), thus forcing me
to convert it to a float with to_f. (I lose precision because the string
only returns the result to three decimal places). Any idea why DBI/Mysql is
returning the result of a numerical calculation as a string?

I'm running
mysql-5.0.34_alpha20070101-r61
mysql-ruby-2.7.2
ruby-dbi-0.0.21-r2
ruby-1.8.5_p2
on Gentoo.

dbh.execute("select childcounts.name name, childcounts.count/
parentcounts.count rho from parentcounts, childcounts where
parentcounts.left_key=childcounts.parent_left")

Hi Ken
if this is an issue it is a MySQL issue I guess, concerning ruby, do
you have a compelling reason not to write

dbh.execute("select childcounts.name name, childcounts.count whatever,
parentcounts.count rho from parentcounts, childcounts where
parentcounts.left_key=childcounts.parent_left")

and let the gem do the arithmetic? (performance maybe)

Cheers
Robert

 

[Ken Bloom]

Hi Ken
if this is an issue it is a MySQL issue I guess, concerning ruby, do
you have a compelling reason not to write

dbh.execute("select childcounts.name name, childcounts.count whatever,
parentcounts.count rho from parentcounts, childcounts where
parentcounts.left_key=childcounts.parent_left")

and let the gem do the arithmetic? (performance maybe)

Apparently extracting all data as strings is an important part of the MySQL
C API (see http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html), so
I just have to live with that way of doing things.

Someone should look through the DBI coercion code to make sure that they're
coercing data back to a float correctly, because this is a case that's
getting missed. Either DBI or DBD::MySQL is responsible for the coercion --
I'm not entirely sure which though.

I can get better precision by adding 0.000000000 to the quotient in the SQL
statement (so childcounts.count/parentcounts.count becomes childcounts.count/
parentcounts.count+0.000000000) because that forces the returned string to
be longer. It would be nice if I could get the data in the native
representation though, have high precision automatically, and not need
kludges like this.

Who can I email directly about this bug?

--Ken