Mysql multiplication between decimal type values loses accuracy

Question

Mysql multiplication between decimal type values loses accuracy

Asked 7 years, 4 months ago

Viewed 230 times

2

In my application I need precision for numerical values of 12 decimal places. I created the following test:

CREATE TABLE TESTE (
     VALOR1 DECIMAL(16,12),
     VALOR2 DECIMAL(16,12),
     VALOR3 DECIMAL(16,12) )

INSERT INTO TESTE (VALOR1, VALOR2)
VALUES ( 0.0001445312, 128)


UPDATE TESTE SET VALOR3 = VALOR1 * VALOR2


select valor3 from teste

valor3
0.0185

The correct value would be 0.0184999936, Mysql rounds to 4 decimal places.

How to achieve the correct result of 0.0184999936 without rounding?

It’s working for me: http://sqlfiddle.com/#! 9/3d02e0/1

– Maniero

2017/02/21 at 00:05
Mysql does not round up "on its own". Probably the problem is in the application you are using to access. I recommend testing with the official command line client, or with Mysql Workbench (or even Mysql Query Browser).

– Bacco

2017/02/21 at 00:09
I am using Mysql Workbenck to test in a Windows 7 OS.

– dluisbr

2017/02/21 at 00:12
@dluisbr test with VALOR1 * VALOR2 * 10000 to see if it is display problem (if more houses appear). The test you put in the question is exactly the real situation of DB?

– Bacco

2017/02/21 at 00:18
I tested with the official command line client and keeps rounding to 0.0185. It may be some BD or OS configuration?

– dluisbr

2017/02/21 at 00:19
I did the same here with Mysql from a server in production and there was no rounding up. What version of Mysql? Are you sure that what you put in the question is exactly the test you’re doing?

– Bacco

2017/02/21 at 00:21
Remembering that teste and TESTE are different tables in principle, which is an error of the example given in the question - to be portable the solution, must write everything with equal Casing (OS interferes with it). If you’re using a case sensitive environment, make sure you don’t have two tables with a test name.

– Bacco

2017/02/21 at 00:23
https://i.stack.Imgur.com/qxa1h.png

– Bacco

2017/02/21 at 00:27
Have you tried UPDATE TESTE SET VALOR3 = CAST((VALOR1 * VALOR2) AS DECIMAL(16,12)); ?

– Maurivan

2017/02/21 at 01:36
Bacco the one reported in the above question is exactly as I ran in the bank, in my environment is not case sensitive.

– dluisbr

2017/02/21 at 14:15
Maurivan, I ran your suggestion earlier before posting the question and keep rounding up.

– dluisbr

2017/02/21 at 14:16
The version of Mysql I am using is 5.5.36 Community Server (GPL).

– dluisbr

2017/02/21 at 14:19
Problem solved.

– dluisbr

2017/02/22 at 14:18
I uninstalled Mysql and installed the latest version 5.7.17 and redid the test, described above, worked as expected.

– dluisbr

2017/02/22 at 14:21

Show 9 more comments

No answers

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.