MySQL Bugs: #11283: cast as decimal

Bug #11283	cast as decimal
Submitted:	13 Jun 2005 10:13	Modified:	24 Jun 2005 17:37
Reporter:	Yoshiaki Tajika (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.7-beta-nightly-20050521	OS:	Linux (linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
cast(int_column as decimal) returns incorrect result.

How to repeat:
drop table if exists t1;
create table t1(c1 varchar(6))
engine=myisam default charset=latin1;
insert into t1 values('1');
select cast(c1 as decimal) from t1; -- (a)
select cast('1' as decimal); -- (b)

Then,
(a) returned 0.00. Odd result.
(b) returned 1.00. It's OK.

I expect that (a) also returns 1.00.

This bug was analyzed by Alexander Ivanov.
Here's what he found out:

The cause of the bug is incorrect behavior of the Field_longstr::val_decimal() function.
Details are as follows.
Field_longstr is the base class for Field_string, Field_varstring, and Field_blob classes.
The Field_longstr::val_decimal() function behaves correctly only in case of Field_string class. As to other two classes they require specific implementation of this function.

Alexander also prepared a fix:
The original val_decimal() function is moved from the Field_longstr class to the Field_string class.
Two newly implemented val_decimal() functions are added to the Field_varstring and Field_blob classes, respectively.

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/26028

ChangeSet
  1.1945 05/06/15 08:27:33 igor@rurik.mysql.com +4 -0
  cast.result, cast.test:
    Added test case for bug #11283.
  field.h, field.cc:
    Fixed bug #11283: wrong conversion from varchar to decimal.
    Added methods Field_string::val_decimal,
    Field_varstring::val_decimal, Field_blob::val_decimal.
    They are not inherited from the base class Field_longstr
    anymore.

The fix will appear in 5.0.8.

CAST section of refman updated, fix documented in 5.0.8 changelog:

<listitem><para><literal>CAST( ... AS DECIMAL) didn't work for strings. (Bug #11283)</literal></para></listitem>