Rounded numbers returned as '0.999999999992345' sometimes

Question

I have a report that should return something along the lines of

SELECT brand, ROUND(SUM(count * price) / SUM(count), 2) 
    WHERE ... GROUP BY brand, ...; 

The problem is, I sometimes get 9990.32999999999992345 in my perl code instead of 9990.33 which direct SQL request returns.

The number starts looking that way right after fetchrow_hashref, if it ever does. The same number can come in 'good' or 'bad' form in different queries, but always the same way in any specific query.

How can I track this down?

Answer 1

Read all about floating point accuracy problems here: http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

Answer 2

As mellamokb said, you have to round your floating-point numbers. More importantly, count and price probably means that you are calculating the price of something. As this page explains for the FLOAT and DOUBLE datatype, calculations are approximate while for DECIMAL they are exact. For your particular example, the chance is low that will give problems but not if you do a lot of calculations with your price. The usual rule is to always use exact datatypes for calculating prices.

Answer 3

Always round floating point numbers when displaying them on the screen. And do it as the final step as it is displayed. Any intermediate operation has the potential to cause problems like this.

Answer 4

I can think of a couple of causes of this, but first:

Does it make any difference to put a CONCAT( '', ... ) around your ROUND? What version of perl are you using? What does perl -V:nvtype report?

Answer 5

33/100 is a periodic number in binary just like 1/3 is a periodic number in decimal.

$ perl -e'printf "%.20f\n", 0.33'
0.33000000000000001554

Therefore, it would take infinite storage to store it as a floating point number. To avoid the problem, you'll need to store the number as a string, either early (in the query before it's a float) or late (by rounding).

Answer 6

It's an issue inherent with floating point numbers. It's a design feature, not a flaw.

Make sure the value returned from the database is not a floating point value, but a string or decimal. (If the data types of `price` and `count` are both DECIMAL, then the resulting expression should be DECIMAL.

If either of those is a floating point, then you can convert to DECIMAL...

SELECT brand, CONVERT( SUM(count * price) / SUM(count), DECIMAL(18,2) ) 
    WHERE ... GROUP BY brand, ...; 

Or convert to a string

SELECT brand, CONVERT(CONVERT( SUM(count * price) / SUM(count), DECIMAL(18,2)),CHAR)
    WHERE ... GROUP BY brand, ...; 

You can let the conversion to DECIMAL do the rounding for you. If you return a DECIMAL or VARHCAR to Perl, that should avoid floating point issues.

More generally, to handle representation (rounding) of floating point in Perl, you can format using the sprintf function, e.g.

my $rounded_val = sprintf(%.2f, $float_val);