MySQL Bugs: #55537: TRUNCATE() and ROUND() functions are inconsistent depending on (non)const params

Bug #55537	TRUNCATE() and ROUND() functions are inconsistent depending on (non)const params
Submitted:	25 Jul 2010 16:37	Modified:	26 Jul 2010 16:45
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.41-log, 4.1, 5.0, 5.1, 5.6.99	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ROUND(), TRUNCATE()

Description:
Calling on TRUNCATE() and ROUND() result with inconsistent and wrong (according to documentation) results, depending whether the 'D' parameter is constant or retreieved from row data,

How to repeat:
Sample data:

mysql> create table n (n int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into n values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

See how truncate(pi(), n) does not really truncate, Compare to the specific output of truncate(pi(), 3).
Same goes for round().

mysql> select n, pi(), round(pi(), n), truncate(pi(), n), round(pi(), 3), truncate(pi(), 3) from n;
+------+----------+----------------+-------------------+----------------+-------------------+
| n    | pi()     | round(pi(), n) | truncate(pi(), n) | round(pi(), 3) | truncate(pi(), 3) |
+------+----------+----------------+-------------------+----------------+-------------------+
|    1 | 3.141593 |       3.100000 |          3.100000 |          3.142 |             3.141 |
|    2 | 3.141593 |       3.140000 |          3.140000 |          3.142 |             3.141 |
|    3 | 3.141593 |       3.142000 |          3.141000 |          3.142 |             3.141 |
|    4 | 3.141593 |       3.141600 |          3.141500 |          3.142 |             3.141 |
|    5 | 3.141593 |       3.141590 |          3.141590 |          3.142 |             3.141 |
+------+----------+----------------+-------------------+----------------+-------------------+
5 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.41-log |
+------------+
1 row in set (0.00 sec)

Suggested fix:
truncate() should truncate; round should round() & eliminate trailing zeros.

I think the following test shows what happens more clearly:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 --column-
type test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select n, truncate(pi(), n), truncate(pi(),3) from n;
Field   1:  `n`
Catalog:    `def`
Database:   `test`
Table:      `n`
Org_table:  `n`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   0
Flags:      NUM

Field   2:  `truncate(pi(), n)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 8
Decimals:   6
Flags:      BINARY NUM

Field   3:  `truncate(pi(),3)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     20
Max_length: 5
Decimals:   3
Flags:      NOT_NULL BINARY NUM

+------+-------------------+------------------+
| n    | truncate(pi(), n) | truncate(pi(),3) |
+------+-------------------+------------------+
|    1 |          3.100000 |            3.141 |
|    2 |          3.140000 |            3.141 |
|    3 |          3.141000 |            3.141 |
|    4 |          3.141500 |            3.141 |
|    5 |          3.141590 |            3.141 |
+------+-------------------+------------------+
5 rows in set (0.00 sec)

Note decimals 6 vs 3 in columns metadata. 

I am not sure it is possible to get the same value for decimals in case of table column defined as INT, and I am not sure what is the bug here.

The bug is in that the parameter D is an INT, either where I provide it as const or as table column. They're both an INT. Why should it make for different results?

By the same reasoning, why should 
SELECT x+1 
be different from 
SELECT x+n FROM numbers WHERE n=1
(abstract example).

There is a function, and it receives an INT parameter. IT could be a constant, a user variable, a row value, the result of a stored function, the result of a UDF, what have you. It should act consistently.

I hope I'm clear on why this is a bug.
Regards

Thank you for the feedback.

Verified as described.

The bug report says results are "inconsistent" -- true..
The bug report says results are "wrong (according to
documentation)" -- I'm not so sure.
The MySQL Reference Manual says
"The return type is the same type as that of the first
argument (assuming that it is integer, double, or decimal)."
http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_round
In this sentence "type" means integer or double or
decimal, and nothing is said about precision scale etc.

We're talking about non-standard functions, and in
another DBMS I see that ROUND() can change such things.
So it's up to MySQL to decide how to behave in this case:
1. leave it alone, an inconsistency is not a bug
2  the number of post-decimal positions is always the
   same as D, the second argument
3. the number of post-decimal positions is always the
   same as the number in X, the first argument
I'd favour '3.', but as a feature request.

Hi Peter,

With regard to ROUND():
"...The return type is the same type as that of the first
argument (assuming that it is integer, double, or decimal)..."
>
OK, but in my examples above, the first argument was always PI(), unchanged. So why is this relevant?

"...and nothing is said about precision scale etc."
> 
Then how about TRUNCATE() ?
The docs say: "Returns the number X, truncated to D decimal places"
The examples show:
  mysql> SELECT TRUNCATE(-1.999,1);
          -> -1.9
This isn't a math theorem; I cannot follow math logic to the point of Q.E.D. But it is easy to deduce that TRUNCATE() removes digits -- not turn them into zeros. This is what a reasonable reader understands, and this is what the example shows.

I suppose we can rewrite the documentation so that the behavior is as expected; but really -- this is just a bug.

Best regards

>> With regard to ROUND():
>> "...The return type is the same type as that of the first
>> argument (assuming that it is integer, double, or decimal)..."
> OK, but in my examples above, the first argument was always PI(), unchanged. So why is
> this relevant?

Because PI() is a DOUBLE, and ROUND(PI(),...) is a DOUBLE.

>>"...and nothing is said about precision scale etc."
> 
> Then how about TRUNCATE() ?
> The docs say: "Returns the number X, truncated to D decimal places"
> The examples show:
> mysql> SELECT TRUNCATE(-1.999,1);
>          -> -1.9
> This isn't a math theorem; I cannot follow math logic to the point of Q.E.D. But it is
> easy to deduce that TRUNCATE() removes digits -- not turn them into zeros. This is what a
> reasonable reader understands, and this is what the example shows.

> I suppose we can rewrite the documentation so that the behavior is as expected; but
> really -- this is just a bug.

We're better off if we can predict an expression's
data type + precision + scale + length, in advance, and
consistently. Sometimes it's not easy to do so with
non-constant inputs, and we might come up with something
less than ideal. We do see that there's inconsistency.