Bug #17043 Casting trimmed string to decimal loses precision
Submitted: 2 Feb 2006 15:10 Modified: 20 Apr 2006 12:30
Reporter: Ian Terrell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19-BK, 5.0, 5.1 OS:Linux (Linux, Windows XP SP2)
Assigned to: Jim Winstead CPU Architecture:Any

[2 Feb 2006 15:10] Ian Terrell
Description:
Casting a whitespace trimmed string to a decimal loses all precision after the decimal point.

Casting seems to work.  
The queries
     select cast('20.06' as decimal(19,2));
     select cast('  20.06 ' as decimal(19,2));
each return 20.06

Trimming seems to work.
The queries
     select rtrim('  20.06 ');
     select ltrim('  20.06 ');
     select rtrim(ltrim('  20.06 '));
return '  20.06', '20.06 ', and '20.06'

But, combining the two don't work.
The queries
     select cast(rtrim('  20.06 ') as decimal(19,2));
     select cast(ltrim('  20.06 ') as decimal(19,2));
     select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
all return 20.00

I would expect the queries above to return 20.06 instead.

Since you can cast properly without trimming this is a minor issue, but it's still unexpected behavior.

How to repeat:
Run the following script to see the symptoms:

-- working casts
select cast('20.06' as decimal(19,2));
select cast('  20.06 ' as decimal(19,2));

-- working trims
select rtrim('  20.06 ');
select ltrim('  20.06 ');
select rtrim(ltrim('  20.06 '));

-- nonworking combinations
select cast(rtrim('  20.06 ') as decimal(19,2));
select cast(ltrim('  20.06 ') as decimal(19,2));
select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
[2 Feb 2006 15:20] Valeriy Kravchuk
Thank you for a bug report. Verified as described on 5.0.19-BK on Linux:

mysql> select cast(rtrim('  20.06 ') as decimal(19,2));
 +------------------------------------------+
| cast(rtrim('  20.06 ') as decimal(19,2)) |
+------------------------------------------+
|                                    20.00 |
+------------------------------------------+
1 row in set (0.01 sec)

mysql>      select cast(ltrim('  20.06 ') as decimal(19,2));
+------------------------------------------+
| cast(ltrim('  20.06 ') as decimal(19,2)) |
+------------------------------------------+
|                                    20.00 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>      select cast(rtrim(ltrim('  20.06 ')) as decimal(19,2));
+-------------------------------------------------+
| cast(rtrim(ltrim('  20.06 ')) as decimal(19,2)) |
+-------------------------------------------------+
|                                           20.00 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>      select cast('  20.06 ' as decimal(19,2));
+-----------------------------------+
| cast('  20.06 ' as decimal(19,2)) |
+-----------------------------------+
|                             20.06 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)
[14 Mar 2006 9:59] 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/commits/3815
[20 Apr 2006 0:17] Jim Winstead
Fixed in 5.0.21 and will be fixed in 5.1.10. (Not pushed to the 5.1 tree yet, but will be merged up before 5.1.10 is released.)
[20 Apr 2006 12:30] Paul Dubois
Noted in 5.0.21, 5.1.10 changelogs.

Casting a string to <literal>DECIMAL</literal> worked, but
casting a trimmed string (using <literal>LTRIM()</literal> or
<literal>RTRIM()</literal>) resulted in loss of decimal
digits. (Bug #17043)