Bug #11708 Implicit/explicit conversion/cast from REAL to DECIMAL fail for decimal part
Submitted: 3 Jul 2005 7:55 Modified: 7 Jul 2005 16:59
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7 OS:Windows (Win XP Professional SP2)
Assigned to: Alexey Botchkov CPU Architecture:Any

[3 Jul 2005 7:55] Roland Bouman
Description:
Explicit:
When a floating point value is converted to a decimal type, either using CONVERT or CAST, the actual value returned has the expected number of decimal digits, but all decimal digits are zero.

Implicit:
When a floating point value is returned from a function declared as RETURNS DECIMAL(M,D), the actual value returned has the expected number of decimal digits, but all decimal digits are zero. 
However, when inserting a floating point value in a DECIMAL(M,D) database column, the decimal digits are retained (and rounded) as expected.

How to repeat:
mysql> use test
Database changed
mysql> create function f() returns decimal(2,3) return ln(14000);
Query OK, 0 rows affected (0.13 sec)

mysql> select ln(14000) c1
    -> ,      convert(ln(14000),decimal(2,3)) c2
    -> ,      cast(ln(14000) as decimal(2,3)) c3
    -> ,      f() c4
    -> ;
+-----------------+-------+-------+-------+
| c1              | c2    | c3    | c4    |
+-----------------+-------+-------+-------+
| 9.5468126085974 | 9.000 | 9.000 | 9.000 |
+-----------------+-------+-------+-------+
1 row in set (0.00 sec)

The expected result of c2,c3 and c4 is 9.547, not 9.000

Suggested fix:
Hounour the decimal part when implicitly or explictly casting to decimal in all cases, not just when inserting into database columns
[7 Jul 2005 15:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26771
[7 Jul 2005 16:59] Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html