Bug #8287 ROUND PROBLEM IN INSERT SELECT STATEMENT

• Submitted: 3 Feb 2005 15:12
• Modified: 18 Nov 2005 16:07
• Reporter: nicolas tricart
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: *
• OS: 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)