Home ROUND function in mySql giving strange result after using TRUNCATE
Reply: 1

ROUND function in mySql giving strange result after using TRUNCATE

sweetu514
1#
sweetu514 Published in 2018-01-13 05:57:16Z

I am using a stored procedure in mysql to set the following value to a double variable.

 DECLARE vVar DOUBLE(16,2) DEFAULT 0;

 SET vVar = ROUND(TRUNCATE((29.00 * 1.75/100),2));

Firing the above expression in mySql editor gives 1 (which is correct, since ROUND(0.50) would give 1). However, the value that gets stored in vVar is 0.

On the other hand, if I write

SET vVar = ROUND(TRUNCATE((29.00 * 1.75/100),4));

then the value that gets stored in vVar becomes 1. (which is also correct, since ROUND(0.5075) is 1).

Could someone please explain the strange behavior occurring here.

I had a doubt regarding the datatype of vVar, so I tried changing it from DOUBLE(16,2) to DECIMAL(16,2). But it did not make any difference.

Is there a way to get 1 from the first expression ?

 SET vVar = ROUND(TRUNCATE((29.00 * 1.75/100),2));

Any help would be appreciated.

Rick James
2#
Rick James Reply to 2018-01-13 20:08:12Z

Yuck!

Keep in mind that FLOAT and DOUBLE numbers are stored in binary, not decimal. This means that everything else you are doing in that expression needs to be converted between binary and decimal one or more times.

0.5075 cannot be represented exactly in DOUBLE -- with, or without the (16,2)

DOUBLE(16,2) adds an extra rounding, namely to 2 decimal places before storing into the binary DOUBLE.

My head spins.

I hope yours does, too.

Enough for may rambling... When I try variations on your SET, I get 1. I did not build a Stored Proc; I suspect something else is wrong in the SP. Perhaps you could build a complete test case for us to work with?? Also, what version of MySQL are you using -- in the unlikely case that something is broken in that version.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.332981 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO