Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
Submitted: 19 Apr 2007 16:06 Modified: 15 May 2007 2:56
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.40 OS:Any
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: cast, decimal, overflow, qc

[19 Apr 2007 16:06] Martin Friebe
Description:
a DECIMAL(M,D)  can store an overall of M digits. Attempting to store a bigger number results in truncation.

Cast does truncate/round the precission, but does not check for numbers that are to big. This leads to inconsistent results, as numbers are cut off once a temp table is required:

#No cut off
 select cast(11.1234 as DECIMAL(3,2));
+-------------------------------+
| cast(11.1234 as DECIMAL(3,2)) |
+-------------------------------+
|                         11.12 |
+-------------------------------+

# cut off
select * from (select cast(11.1234 as DECIMAL(3,2))) t;
+-------------------------------+
| cast(11.1234 as DECIMAL(3,2)) |
+-------------------------------+
|                          9.99 |
+-------------------------------+

How to repeat:
select cast(11.1234 as DECIMAL(3,2));
select * from (select cast(11.1234 as DECIMAL(3,2))) t;

select cast(a as DECIMAL(3,2))
 from (select 11.1233 as a
  UNION select 11.1234
  UNION select 12.1234
 ) t;

select cast(a as DECIMAL(3,2)), count(*)
 from (select 11.1233 as a
  UNION select 11.1234
  UNION select 12.1234
 ) t group by 1;

Suggested fix:
Cast should always check for overflow.
[19 Apr 2007 16:25] Godofredo Miguel Solorzano
Thank you for the bug report.
[6 May 2007 14:17] 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/26187

ChangeSet@1.2474, 2007-05-06 18:17:24+05:00, holyfoot@mysql.com +4 -0
  bug #27957 (cast as decimal does not check overflow, also inconsistent with group, subselect)
  Item_decimal_typecast::val_decimal now checks the value if it's too
  big to fit the given precision/scale, truncates the value and issues
  a warning if the decimal gets off the range.
[6 May 2007 18:27] Trudy Pelzer
Bad data bug, upgrading to P2.
[9 May 2007 13:27] 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/26378

ChangeSet@1.2474, 2007-05-09 17:27:14+05:00, holyfoot@mysql.com +7 -0
  Bug #27957 cast as decimal does not check overflow, also inconsistent with group, subselect
  Missing check for overflow added to the Item_decimal_typecast::val_decimal
[13 May 2007 6:16] Bugs System
Pushed into 5.1.19-beta
[13 May 2007 6:19] Bugs System
Pushed into 5.0.42
[15 May 2007 2:56] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.