MySQL Bugs: #29415: cast as decimal does allow out of range specification

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:	None
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

Description:
From: http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

> DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
....
> 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?

Thank you for the report.

Verified as described.

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:

mysql> CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL);
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.

See also the report for bug #29417.

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

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.

Pushed into 5.1.21-beta

Pushed into 5.0.46

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.