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: Sergey Glukhov CPU Architecture:Any

[4 Jan 2006 4:05] Stan Segers
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
[11 Jan 2006 18:27] Valeriy Kravchuk
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.
[12 Jan 2006 22:26] Stan Segers
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).
[15 Feb 2006 12:01] Valeriy Kravchuk
Bug #17418 is marked as a duplicate of this one.
[13 Apr 2006 0:58] Jim Winstead
Bug #17051 was marked as a duplicate of this bug.
[5 Jul 2006 13:34] 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/8763
[6 Jul 2006 11: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/8820
[31 Jul 2006 9:52] 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/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
[31 Jul 2006 11:26] 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/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
[8 Aug 2006 9:24] 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/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
[8 Aug 2006 10:48] 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/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
[16 Aug 2006 20:54] Reggie Burnett
Pushed to 5.0.25
[17 Aug 2006 11:34] Jon Stephens
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.