# Numerical calculation returns string with DBI/Mysql

**URL:** <https://www.ruby-forum.com/t/numerical-calculation-returns-string-with-dbi-mysql/86488>\
**Category:** Ruby\
**Created:** [February 22, 2007, 6:10pm UTC](https://www.ruby-forum.com/t/numerical-calculation-returns-string-with-dbi-mysql/86488 "2007-02-22T18:10:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [February 22, 2007, 6:10pm UTC](https://www.ruby-forum.com/t/numerical-calculation-returns-string-with-dbi-mysql/86488/1 "2007-02-22T18:10:22Z")

</div>

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”)

---

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [February 22, 2007, 7:18pm UTC](https://www.ruby-forum.com/t/numerical-calculation-returns-string-with-dbi-mysql/86488/2 "2007-02-22T19:18:29Z")

</div>

On 2/22/07, Ken B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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

---

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [February 22, 2007, 11:11pm UTC](https://www.ruby-forum.com/t/numerical-calculation-returns-string-with-dbi-mysql/86488/3 "2007-02-22T23:11:04Z")

</div>

On Fri, 23 Feb 2007 03:17:32 +0900, Robert D. wrote:

> On 2/22/07, Ken B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > 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  
> > parentcounts.count rho from parentcounts, childcounts where  
> > 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](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