Bug #29415 cast as decimal does allow out of range specification
Submitted: 28 Jun 2007 9:57 Modified: 9 Jul 2007 15:20
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.20 5.0.44 OS:Any (FreeBSD, Linux)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: cast, decimal

[28 Jun 2007 9:57] Martin Friebe
From: http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

> The maximum number of digits (M) for DECIMAL is 65.

This is enforced for "create" and "alter" table. Using the type "decimal" inside "cast()" any number can be specified. The range is not enforced. 

How to repeat:

drop table if exists t1;
create table t1 (a decimal(66,10)); # fails => expected

create table t1 (a decimal(65,10));
alter table t1 modify a decimal(66,10); # fails => expected

select cast(1 as decimal(66,10)); # no error => should fail

select cast(1 as decimal(99999,999)); # no error => should fail

| cast(1 as decimal(99999,999))                                              |
| 1.000000000000000000000000000000000000000000000000000000000000000000000000 |
1 row in set (0.00 sec)

73 digits without the dot

Suggested fix:
decimal(m,d) should always enorce the maximum allowed parameters

with regards to the last result. If mysql handles internally a higher precission, maybe it is worth documenting this?
[28 Jun 2007 10:56] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Jul 2007 17:10] Igor Babaev
For some queries if the scale for decimal in a cast operation exceeds DECIMAL_MAX_SCALE=30 the bug can lead to an assertion abort:

Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (3,30), (1,10), (2,10);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa;
ERROR 2013 (HY000): Lost connection to MySQL server during query

having on the server side:

Version: '5.0.46-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1257165920 (LWP 19113)]
mysqld: field.cc:2290: Field_new_decimal::Field_new_decimal(uint32, bool, const char*, st_table*, uint8, bool): Assertion `(precision <= ((9 * 9) - 8*2)) && (dec <= 30)' failed.

Program received signal SIGABRT, Aborted.
[7 Jul 2007 17:11] Igor Babaev
See also the report for bug #29417.
[7 Jul 2007 18:50] 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:


ChangeSet@1.2521, 2007-07-07 10:33:02-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #29415.
  The cast operation ignored the cases when the precision and/or the scale exceeded
  the limits, 65 and 30 respectively. No errors were reported in these cases.
  For some queries this may lead to an assertion abort.
  Fixed by throwing errors for such cases.
[8 Jul 2007 17:29] Bugs System
Pushed into 5.1.21-beta
[8 Jul 2007 17:31] Bugs System
Pushed into 5.0.46
[9 Jul 2007 15:20] Paul DuBois
Noted in 5.0.46, 5.1.21 changelogs.

For CAST(expr AS decimal(M,D)), the limits of 65 and 30 on the
precision (M) and scale (D) were not enforced.