Solved

Precision Issue with MySQL numeric fields

Posted on 2012-04-04
12
392 Views
Last Modified: 2012-04-14
This is a bit of a weird one.

Before we go any further, I have tried switching the field to INT, FLOAT, DOUBLE and DECIMAL with the following results:

INT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
FLOAT - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
DOUBLE - Doesn't seem to allow me to specify 2 decimal places, keeps setting it to 0
DECIMAL - Removes all decimal places completely. Unacceptable for currency field.

So the problem is this.

I have a table which I store customer payments. When storing the payment data, the field value is in #.##  format.  Recently, just did a bulk update for all customers in the system to give a discount off a flat amount each.  When I queried the database for a SUM of this amount instead of :

0.50 +
0.50 +
0.50 +
= 1.50

It gave
0.50 +
0.50 +
0.50 +
= 1.501837782989874

Which makes absolutely ZERO sense whatsoever, until I do a bit more research. Some people have mentioned something about switching the field type to INT at 2 decimals ( http://bytes.com/topic/mysql/answers/862239-sum-increases-precision-decimal-point ) -- which is why I tried this to no avail.  Also found a really interesting doc on precision storage issues.  

The long and short, what is a quick and dirty way to fix this.  I do not need any more than 2 decimal places stored as this is a simple currency value.
0
Comment
Question by:microvb
  • 6
  • 6
12 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37806322
You need to declare decimal with the number of decimals you need, i.e. decimal(10,2) allows 8 digits to the left of the decimal and 2 to the right.  

What you descibed with adding floats is expected - that type doesn't do precision math.
0
 
LVL 3

Author Comment

by:microvb
ID: 37806400
@johanntagle

Could you please clarify where   "decimal(10,2)"  would go.  I have already tried setting the field to all of the above combinations with the results as stated above.  The field format is currently back to it's original state which is not Float --- it is Double.

None of the field types except "DECIMAL" which is whole number only and does not support decimals (ironic), will SUM without making up magical numbers from the kingdom of la-la land.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37808334
DECIMAL supports decimals, you just need to declare the column properly i.e.

create table mytable (col1 decimal(10,2));
0
 
LVL 3

Author Comment

by:microvb
ID: 37809033
@johanntagle

I manually adjusted the tables using ALTER table syntax, however when I do something like

SELECT (SUM(price) * 1.13) pricewithtax FROM tablename

The result is still a similar problem. The workaround I have found to this (which seems inneficient) is as follows:

SELECT FORMAT(CEIL((SUM(price)*1.13)*100)/100,2) pricewithtax FROM tablename

Is there a more native method for ensuring that the data type remains the same -- even when calculations are being performed as the work-around seems to really make the code not so user friendly.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37809129
You can use the convert or the cast function see http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
0
 
LVL 3

Author Comment

by:microvb
ID: 37809448
@johanntagle

I appreciate the concept, however I see no real difference in type-casting versus what I am doing as a work-around.  I am looking for a more natural solution as SUM, on a field which is alleged to be limited to 2 decimal places should not convert itself to something else when doing a calculation.

Example:

Given the following Data (CSV notation for ease of use here)
++++++++++++
ID  |   PRICE   |    
----------------------
0    |    0.50     |
1    |    1.25     |
---------------------

Structure:
ID:  INT, UNIQUE AUTOINCREMENT
PRICE:  DECIMAL(10,2)

TEST SCENARIO 1
Command:
SELECT SUM(PRICE) FROM `example`;

Result:
1.75

* As Expected

TEST SCENARIO 2
Command:
SELECT SUM(PRICE) * 1.13 FROM `example`;

Result:

* As Expected

==========================================

Large data set comprising of similar data in standard currency format (no more than 2 decimals) --- ends up returning the artifact of the decimal trailing on for up to 12 digits (on the right side of the decimal point)

Do you know of a way to ensure that when you SUM() with or without an inline calculation that no artifact digits appear without convoluting the SQL script -- aka, in a natural state ?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 24

Expert Comment

by:johanntagle
ID: 37809474
hmmm multiplying two 2-decimal numbers should result in at most 4 decimal places so that is indeed odd.  Can you post some sample data I can play with?
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37815157
FYI I cannot replicate your problem.   See below

mysql> create table test_decimal (a decimal(10,2));
Query OK, 0 rows affected (0.15 sec)

-- INSERT 10000 records
mysql> begin
    -> declare a int;
    -> declare b decimal(10,2);
    -> set a=0;
    -> set b=0.25;
    -> while a<10000 do
    -> insert into test_decimal values (b);
    -> set a=a+1;
    -> set b=b+0.25;
    -> end while;
    -> end;
    -> |

mysql> delimiter ;
mysql> call pop_decimal();
Query OK, 1 row affected (0.42 sec)

-- SHOW SAMPLE DATA
mysql> select * from test_decimal limit 10;
+------+
| a    |
+------+
| 0.25 |
| 0.50 |
| 0.75 |
| 1.00 |
| 1.25 |
| 1.50 |
| 1.75 |
| 2.00 |
| 2.25 |
| 2.50 |
+------+
10 rows in set (0.00 sec)


mysql> select sum(a) from test_decimal;
+-------------+
| sum(a)      |
+-------------+
| 12501250.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(a)*1.13 from test_decimal;
+---------------+
| sum(a)*1.13   |
+---------------+
| 14126412.5000 |
+---------------+
1 row in set (0.00 sec)

Open in new window

0
 
LVL 3

Assisted Solution

by:microvb
microvb earned 0 total points
ID: 37817697
@johanntagle

I too am unable to replicate the problem. I think it may be because I have stored calculated results from another table -- which in theory, switching the field type to DECIMAL(10,2) should truncate.  I am unable to reproduce on a fresh table -- if there were some way to check for the data stored that trails beyond the field type setting of 2 decimals, that would be great :)

Just being thorough for future users looking for a solution to correct the problem with an existing table, the following command will adjust the field correctly.

ALTER TABLE `tablename` CHANGE `fieldname` `fieldname` DECIMAL(10,2);

Open in new window


Thank you for your assistance. If you can think of a way to query for all records where the decimal fraction trails beyond 2 places, please add that here as it would be very helpful in locating this issue -- as it is rather obscure especially when automating and rounding SUM totals with calculations, this problem is all but invisible without deeper inspection of the data being returned.
0
 
LVL 3

Author Comment

by:microvb
ID: 37817707
I just noticed that you are experiencing something similar where the result being returned is NOT the same data type as you are SUM'ming on.

mysql> select sum(a)*1.13 from test_decimal;
+---------------+
| sum(a)*1.13   |
+---------------+
| 14126412.5000 |
+---------------+

Open in new window


Note that it returns 4 decimal positions when the field is actually 2 decimal points. Further, it appears to be 0 padded. Where data stored in the DECIMAL field is actually rounded up to the nearest (2) decimals as suggested by DECIMAL(10,2), the resulting SUM ignores this and appears to auto-cast into a different data type with padding.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37817866
That is because we multiplied two numbers that have two decimals each.  The resulting product expectedly has 4, even if they are all zeroes.  That's just the way it is.  when you store te result to a decimal(10,2) it will get truncated.  If you need the product to show only two decimals upon computation, then just use round() or truncate() functions.  See http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html
0
 
LVL 3

Author Closing Comment

by:microvb
ID: 37845992
My comment contains information relating to altering existing tables.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now