MySQL Bugs: #5673: Rounding problem in 4.0.21 inserting decimal value into a char field

Bug #5673	Rounding problem in 4.0.21 inserting decimal value into a char field
Submitted:	20 Sep 2004 19:49	Modified:	28 Apr 2005 9:09
Reporter:	Chad Truemper	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0	OS:	Any (Any)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
When a (10,2) decimal value from one table, is inserted into a char(21) field in another table, the value stored in the char(21) field of the second table has extra digits after the decimal point and is not rounded off.  MySQL 4.0.20 did round this off to two digits after the decimal point, but 4.0.21 no longer rounds the value off.  Please see the example in 'How to repeat' below. 

How to repeat:
CREATE TABLE products (
  StrippedProductID char(15) NOT NULL default '',
  ZLevelPrice decimal(10,2) default NULL,
  PRIMARY KEY (StrippedProductID)
 ) TYPE=InnoDB;
 
 CREATE TABLE updates (
  productid char(15) NOT NULL default '',
  zlevelprice char(21) default NULL,
  PRIMARY KEY (productid)
) TYPE=InnoDB;

INSERT INTO products VALUES ('002TRANS','49.99');
INSERT INTO products VALUES ('003TRANS','39.98');
INSERT INTO products VALUES ('004TRANS','31.18');

INSERT INTO updates SELECT * FROM products;

MySQL 4.0.21 Returns this:

mysql> select * from updates; 
+-----------+-----------------------+
| productid | zlevelprice           |
+-----------+-----------------------+
| 002TRANS  | 49.990000000000001989 |
| 003TRANS  | 39.979999999999996873 |
| 004TRANS  | 31.179999999999999715 |
+-----------+-----------------------+
3 rows in set (0.00 sec)

MySQL 4.0.20 and earlier returns the desired result which is:

mysql> select * from updates;
+-----------+-------------+
| productid | zlevelprice |
+-----------+-------------+
| 002TRANS  | 49.99       |
| 003TRANS  | 39.98       |
| 004TRANS  | 31.18       |
+-----------+-------------+
3 rows in set (0.01 sec)

I have further tested this case and appended the following to the insert_select.test file in my 
mysql-test suite. I will leave this for the developer to add and bk commit themselves along with a 
fix. 

Please see the note in the test comment. 

I can confirm that this test passes in 4.0.20 and fails in 4.0.21 and the current 4.0 development 
tree at the time of writing. This bug also exists in the current 4.1 development tree at the time of 
writing. I have not tested this in the 5.0 development tree.

-- Append to insert_select.test --

# Bug 5673 - Decimal of precision >= 1 INSERT..SELECTED into char
# field of size >= 17 results in bad strings in final field.
# table type does not appear to have any effect
CREATE TABLE t1 (
  StrippedProductID char(15) NOT NULL default '',
  ZLevelPrice decimal(5,1) default NULL,
  PRIMARY KEY (StrippedProductID)
 ) TYPE=MyISAM;

 CREATE TABLE t2 (
  productid char(15) NOT NULL default '',
  zlevelprice char(17) default NULL,
  PRIMARY KEY (productid)
) TYPE=MyISAM;

INSERT INTO t1 VALUES ('002TRANS','49.9');
INSERT INTO t1 VALUES ('003TRANS','39.9');
INSERT INTO t1 VALUES ('004TRANS','31.1');

INSERT INTO t2 SELECT * FROM t1;

SELECT * FROM t1;

SELECT * FROM t2;

DROP TABLE t1;

DROP TABLE t2;

-- the following should be appended to the insert_select.result file --
CREATE TABLE t1 (
StrippedProductID char(15) NOT NULL default '',
ZLevelPrice decimal(5,1) default NULL,
PRIMARY KEY (StrippedProductID)
) TYPE=MyISAM;
CREATE TABLE t2 (
productid char(15) NOT NULL default '',
zlevelprice char(17) default NULL,
PRIMARY KEY (productid)
) TYPE=MyISAM;
INSERT INTO t1 VALUES ('002TRANS','49.9');
INSERT INTO t1 VALUES ('003TRANS','39.9');
INSERT INTO t1 VALUES ('004TRANS','31.1');
INSERT INTO t2 SELECT * FROM t1;
SELECT * FROM t1;
StrippedProductID       ZLevelPrice
002TRANS        49.9
003TRANS        39.9
004TRANS        31.1
SELECT * FROM t2;       
productid       zlevelprice     
002TRANS        49.9    
003TRANS        39.9    
004TRANS        31.1    
DROP TABLE t1;
DROP TABLE t2;

Subject: Bug#5673 (floating point regression in 4.0): a possible way to fix
(scrum daily)

Also is present in later releases.

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/internals/24441

Thank you for your bug report. We can't fix this in 4.0 (the patch would be too big), the test case is added to 5.0 which doesn't expose this bug.