Rounded numbers are returned as "0.999999999992345" sometimes

Question

I have a report that should return something row by row

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

The problem is that sometimes I get 9990.32999999999992345 in my perl code instead of 9990.33, which returns an SQL query.

The number starts to look the same right after fetchrow_hashref, if it ever will be. In different requests, the same amount can be in the form of “good” or “bad”, but always the same in any particular request.

How can I track this?

Answer 1

I can come up with a couple of reasons for this, but first:

Doesn't matter put CONCAT( '', ... ) around your ROUND? What version of Perl are you using? What is perl -V: nvtype report?

Answer 2

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

Answer 3

As mellamokb said, you need to round the floating point numbers. More importantly, count and price probably mean that you are calculating the price of something. Since this page explains the data type FLOAT and DOUBLE, the calculations are approximate, and for DECIMAL they are accurate. For your specific example, the probability is low, which will give problems, but not if you do a lot of calculations using price . The usual rule is to always use exact data types for pricing.

Answer 4

Always show floating point numbers when displaying them on the screen. And do it as a last step while it is displayed. Any intermediate operation can cause such problems.

Answer 5

33/100 is a periodic number in binary form, just as 1/3 is a periodic number in decimal value.

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

Therefore, to store it as a floating point number, endless storage is required. To avoid this problem, you need to save the number as a string, either early (in the request before the float), or later (rounding).

Answer 6

This is a problem with floating point numbers. This is a design feature, not a drawback.

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

If any of them is a floating point, you can convert it to DECIMAL ...

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

Or convert to string

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

You can let the DECIMAL conversion do rounding for you. If you return DECIMAL or VARHCAR to Perl, this should prevent floating point problems.

More generally, to handle a floating point representation (rounding) in Perl, you can format it using the sprintf function, for example.

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