Bug #8287 ROUND PROBLEM IN INSERT SELECT STATEMENT
Submitted: 3 Feb 2005 15:12 Modified: 18 Nov 2005 16:07
Reporter: nicolas tricart Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:* OS:Microsoft Windows (windows)
Assigned to: Konstantin Osipov CPU Architecture:Any

[3 Feb 2005 15:12] nicolas tricart
Description:
I have one table (A) with a field in decimal (17,6) and an oher table (B) with a field in decimal (17,2).
When I do a INSERT SELECT statement from A to B, the field with 6 decimals is not round to 2 decimals in table B.

How to repeat:
CREATE TABLE `test_origine` (
  `ID` int(11) NOT NULL auto_increment,
  `MONTANT` decimal(17,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;
CREATE TABLE `test` (
  `ID` int(11) NOT NULL auto_increment,
  `MONTANT` decimal(17,2) NOT NULL default '0.00',
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;
INSERT test_origine (MONTANT) VALUES (1.111111);
INSERT test SELECT * FROM test_origine;
SELECT MONTANT FROM test;   => 1.111111

Suggested fix:
"SELECT MONTANT FROM test" should return 1.11 and not 1.111111 it is decimal(17,2).
[18 Nov 2005 16:07] Konstantin Osipov
This has been fixed in 5.0 release:

mysql> CREATE TABLE `test_origine` (
    ->   `ID` int(11) NOT NULL auto_increment,
    ->   `MONTANT` decimal(17,6) NOT NULL default '0.000000',
    ->   PRIMARY KEY  (`ID`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE `test` (
    ->   `ID` int(11) NOT NULL auto_increment,
    ->   `MONTANT` decimal(17,2) NOT NULL default '0.00',
    ->   PRIMARY KEY  (`ID`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT test_origine (MONTANT) VALUES (1.111111);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT test SELECT * FROM test_origine;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT MONTANT FROM test;
+---------+
| MONTANT |
+---------+
|    1.11 |
+---------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.17-valgrind-max-debug |
+---------------------------+
1 row in set (0.00 sec)