MySQL Bugs: #27957: cast as decimal does not check overflow, also inconsistent with group, subselect

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

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.

Thank you for the bug report.

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.

Bad data bug, upgrading to P2.

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

Pushed into 5.1.19-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.19 changelogs.