MySQL Bugs: #24096: Please provide third parameter ROUND_METHOD to round() or round

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:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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()
   http://java.sun.com/j2se/1.4.2/docs/api/java/math/BigDecimal.html
i.e.

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

Alternatively or additionally a function just for this mode would be sufficient, too.

Thank you for a reasonable feature request.

What do you mean, depending on the input data ?

Sergei, with "depending on the input data" I mean what is described here:
http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html

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 |
+------------+--------------+

bye,

-christian-