MySQL Bugs: #16172: DECIMAL data type processed incorrectly

Bug #16172	DECIMAL data type processed incorrectly
Submitted:	4 Jan 2006 4:05	Modified:	17 Aug 2006 11:34
Reporter:	Stan Segers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.19-BK, 5.0.15-nt	OS:	Linux (Linux, WinXP SP2)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The DECIMAL data type is processed incorrectly when;
A.
Presicion of the DECIMAL data type is too small to hold the value.
Returns the origional value (albeit rounded based on the scale), not an error.
B.
Scale of the DECIMAL data type is 0.
Returns 2 digits right of the decimal seperator (whereas it should return none).

How to repeat:
select cast(143.481 as decimal(4,1)); -- Okay
select cast(143.481 as decimal(4,0)); -- Not Okay, should return 143
select cast(143.481 as decimal(2,1)); -- Not Okay, should return error
select cast(143.481 as decimal(1,2)); -- Not Okay, should return error

Thank you for a problem report. Here are the results I've got from 5.0.19-BK on Linux:

mysql> select cast(143.481 as decimal(4,1)); -- Okay
+-------------------------------+
| cast(143.481 as decimal(4,1)) |
+-------------------------------+
|                         143.5 |
+-------------------------------+
1 row in set (0.03 sec)

mysql> select cast(143.481 as decimal(4,0)); -- Not Okay, should return 143
+-------------------------------+
| cast(143.481 as decimal(4,0)) |
+-------------------------------+
|                        143.48 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select cast(143.481 as decimal(2,1)); -- Not Okay, should return error
+-------------------------------+
| cast(143.481 as decimal(2,1)) |
+-------------------------------+
|                         143.5 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select cast(143.481 as decimal(1,2)); -- Not Okay, should return error
+-------------------------------+
| cast(143.481 as decimal(1,2)) |
+-------------------------------+
|                        143.48 |
+-------------------------------+
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

I think, this results should be at least documented, because inserting the same data into, say, DECIMAL(1,2) column will give different results. But manual (http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html) does not mention the ability to cast to not simply DECIMAL, but DECIMAL(X,Y) at all.

What I wanted to show, is that the datatype isn't checked properly (too late) or alternativeky the cast function is flawed. On top of that, it' s also inconsistent in behaviour. The decimal(4,1) in the example is handled as expected (precision is truncated), but that doesn't happen when casting decimal(4,0).

Even worse with the decimal(2,1), where 143.48 would be forced to lose scale and thus should return an error.
Finally decimal(1,2) is simply an error; should not be able to exists.

Unfortunatly the first time it gets properly checked, is when the storage engine is toched.

create table ttt 
 (dc1 decimal(4,1), 
  dc2 decimal(4,0), 
  dc3 decimal(2,1), 
  dc4 decimal(1,2)
 );

Returns the error correctly.
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D
(column 'dc4').

After creating the table without column dc4, the insert operation

insert ttt (dc1, dc2, dc3) values (134.48, 134.48, 134.48);
select * from ttt;

Returns an error and select does not produce any records.
ERROR 1264 (22003): Out of range value adjusted for column 'dc3' at row 1

Finally

insert ttt (dc1, dc2) values (134.48, 134.48);
select * from ttt;

Produces two warnings on insert (the truncated precision) and a single record in the table.

Query OK, 1 row affected, 2 warnings (0.02 sec)
+-------+------+------+
| dc1   | dc2  | dc3  |
+-------+------+------+
| 134.5 |  134 | NULL |
+-------+------+------+
1 row in set (0.00 sec)

So once the storage is touched, things are checked properly, but that is too late (a procedure could have spend 15 minutes already, calculating an illegal value for storage).

Bug #17418 is marked as a duplicate of this one.

Bug #17051 was marked as a duplicate of this bug.

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

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

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

ChangeSet@1.2271, 2006-07-31 15:06:54+05:00, gluh@mysql.com +9 -0
  Bug#16172 DECIMAL data type processed incorrectly
  issue an 'overflow warning' if result value is bigger than max possible value

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

ChangeSet@1.2226, 2006-07-31 16:41:46+05:00, gluh@mysql.com +4 -0
  Bug#16172 DECIMAL data type processed incorrectly
  issue an error in case of DECIMAL(M,N) if N > M

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

ChangeSet@1.2232, 2006-08-08 14:40:07+05:00, gluh@mysql.com +4 -0
  Bug#16172 DECIMAL data type processed incorrectly
  issue an error in case of DECIMAL(M,N) if N > M

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

ChangeSet@1.2266, 2006-08-08 16:03:42+05:00, gluh@mysql.com +9 -0
  Bug#16172 DECIMAL data type processed incorrectly
  issue an 'overflow warning' if result value is bigger than max possible value

Pushed to 5.0.25

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in 5.0.25 changelog.