MySQL Bugs: #17043: Casting trimmed string to decimal loses precision

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

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));

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)

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

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.)

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)