Bug #61691 CAST('2.5' AS DECIMAL) rounds to 3
Submitted: 29 Jun 2011 14:59 Modified: 1 Jul 2011 3:36
Reporter: Denis TRUFFAUT Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.77 OS:Any
Assigned to: CPU Architecture:Any
Tags: CAST DECIMAL ROUND CEIL

[29 Jun 2011 14:59] Denis TRUFFAUT
Description:
CAST (2.5 AS DECIMAL) has currently the same behavior as ROUND OR CEIL, and there is no FLOAT casting available.

How to repeat:
Query :
-----------------------
SELECT CAST('2.5' AS DECIMAL), 
       CAST(2.5 AS DECIMAL), 
       CAST('2.5' AS DECIMAL(20,2)), 
       CAST(2.5 AS DECIMAL(20,2));

Current output :
-----------------------
3
3
2.5
2.5

Expected output :
-----------------------
2.5
2.5
2.5
2.5

Suggested fix:
How to fix :
-----------------------
DECIMAL is not ROUND or CEIL
DECIMAL must not round/ceil if size M,D is not specified

Please provide 
CAST (2.5 AS FLOAT) 
CAST (2.5 AS REAL)
CAST (2.5 AS DOUBLE)  

and bind 
CAST (2.5 AS DECIMAL)
CAST (2.5 AS NUMERIC) 
without specified size on it.

There is a lot of aliases, don't forget them :)
[29 Jun 2011 17:31] Valeriy Kravchuk
According to the manual, http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html, DECIMAL is equal to DECIMAL(10,0). Scale 0 means that there should be zero digits after the decimal point in the resulting value. So, 2.5 can become either 2 or 3, and why not 3 then? 

As for cast as FLOAT, there is no need to do so, as 2.5 is already FLOAT (see http://dev.mysql.com/doc/refman/5.0/en/number-syntax.html).

Where is the bug here?
[29 Jun 2011 18:53] Denis TRUFFAUT
Well, this unexpected cast does not only apply to floats, but also to strings with scientific notation or unknown floating part length, inside table fields, that you want to compare with float values inside MySQL (excluding a PHP cast).

Consider the following example :

CREATE TABLE test (
  myField VARCHAR(255)
);

INSERT INTO test VALUES 
('2.5'),
('2.50'),
('2.50000'),
('pouetpouetpouetpouet'),
('2.500'),
('02.5000000'),
('25E-1');

SELECT myField, 
       CAST(myField AS DECIMAL) AS myFieldCasted, 
       IF(CAST(myField AS DECIMAL) = 2.5, 'yes', 'no') AS 'isEqualTo2.5', 
       IF(CAST(myField AS DECIMAL(20,2)) = 2.5, 'yes', 'no') AS 'shouldBeEqualTo2.5' 
FROM test;

+----------------------+---------------+--------------+--------------------+
| myField              | myFieldCasted | isEqualTo2.5 | shouldBeEqualTo2.5 |
+----------------------+---------------+--------------+--------------------+
| 2.5                  |             3 | no           | yes                |
| 2.50                 |             3 | no           | yes                |
| 2.50000              |             3 | no           | yes                |
| pouetpouetpouetpouet |             0 | no           | no                 |
| 2.500                |             3 | no           | yes                |
| 02.5000000           |             3 | no           | yes                |
| 25E-1                |             3 | no           | yes                |
+----------------------+---------------+--------------+--------------------+

Assuming the floating part length is unknown, user *CANNOT* specify it when casting to decimal. He has no choice than using random and approximative numbers for integer and floating part (Not really a serious choice...), or use the default decimal cast hoping it will produce the expected intelligent and adaptative cast.

That's why the default behavior of decimal casting *SHOULD NOT* produce an integer. It *MUST* produce a floating value, or at least, a floating cast *SHOULD* be implemented.

Best regards,

Denis TRUFFAUT
[30 Jun 2011 13:19] Valeriy Kravchuk
Thank you for a nice and detailed example, but I still do not see a bug in these results:

mysql> SELECT myField, 
    ->        CAST(myField AS DECIMAL) AS myFieldCasted, 
    ->        IF(CAST(myField AS DECIMAL) = 2.5, 'yes', 'no') AS 'isEqualTo2.5', 
    ->        IF(CAST(myField AS DECIMAL(20,2)) = 2.5, 'yes', 'no') AS 'shouldBeEqualTo2.5' 
    -> FROM test;
+----------------------+---------------+--------------+--------------------+
| myField              | myFieldCasted | isEqualTo2.5 | shouldBeEqualTo2.5 |
+----------------------+---------------+--------------+--------------------+
| 2.5                  |             3 | no           | yes                |
| 2.50                 |             3 | no           | yes                |
| 2.50000              |             3 | no           | yes                |
| pouetpouetpouetpouet |             0 | no           | no                 |
| 2.500                |             3 | no           | yes                |
| 02.5000000           |             3 | no           | yes                |
| 25E-1                |             3 | no           | yes                |
+----------------------+---------------+--------------+--------------------+

taking documented definition of DECIMAL and default type conversion rules (string like '2.5' is converted to float in numeric context) into account. 

No matter how many digits you have in the string after '.', you should just set desired number of correct digits after the decimal point, N, in CAST('string' AS DECIMAL(M,N)) to get correct result.

If you want CAST(... AS DECIMAL) to work as CAST(... AS DECIMAL(M,N)) with some predefined values of M and N > 0, this will be a feature request, but as it is a change in documented behavior it can appear only in version 5.6+, even if accepted.

Do you want me to consider this as a feature request to be passed to product management?
[30 Jun 2011 14:42] Denis TRUFFAUT
Oh, yes please !
[30 Jun 2011 15:01] Valeriy Kravchuk
What exact M and N do you suggest to use by default for DECIMAL?
[30 Jun 2011 19:49] Denis TRUFFAUT
After several discussions with my team, I suggest a default DECIMAL(M,N) set to DECIMAL(10,5).