Bug #24096 Please provide third parameter ROUND_METHOD to round() or round_common()
Submitted: 8 Nov 2006 17:48 Modified: 13 Nov 2006 8:49
Reporter: Christian Hammers (Silver Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[8 Nov 2006 17:48] Christian Hammers
Most people will want to use round() in what we call "commercial mode" or "round-half-up" mode. Sadly depending on the input data round() decides to
use "round-to-even" mode so to be sure people have to use ugly floor() constructs.

How to repeat:

Suggested fix:

Please add a third parameter to round which can be used to specify the rounding mode just like Java's setScale()

   SELECT round(fieldname, 2, "ROUND_HALF_UP") FROM tablename;

Alternatively or additionally a function just for this mode would be sufficient, too.
[10 Nov 2006 13:16] Valeriy Kravchuk
Thank you for a reasonable feature request.
[11 Nov 2006 15:20] Sergei Golubchik
What do you mean, depending on the input data ?
[13 Nov 2006 8:49] Christian Hammers
Sergei, with "depending on the input data" I mean what is described here:

As you can see at this example I can't even be sure what the result will be
if I have the same number but don't know for sure if the column type is
e.g. decimal or double...
It's something I don't want to have to remember or as people normally need
commercial ROUND_HALF_UP mode they should be able to specify it directly.

mysql> SELECT ROUND(2.5), ROUND(25E-1);
| ROUND(2.5) | ROUND(25E-1) |
| 3          |            2 |