Bug #21076 error in a sum of floats : more numbers forward the "," that I wait...

• Submitted: 15 Jul 2006 13:45
• Modified: 15 Jul 2006 14:19
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22
• OS: Linux (debian)
• CPU Architecture: Any

[15 Jul 2006 13:45] [ name withheld ]

Description:
this is a sum of some floats numbers...
Je fais la somme de nombre flotants,

each float is a price
chaque float est un prix

and each float have just two numbers forward the "," like this preg : "/^[0-9]+,[0-9]{0,2}$/"
et chaque float n'a que deux nombres après la virgule, comme cette preg : "/^[0-9]+,[0-9]{0,2}$/"

mysql> SELECT prix FROM passage_liste WHERE prix%1!=0;
+-------+
| prix  |
+-------+
|  40.5 |
|  14.5 |
[...]
|  89.5 |
|  30.6 |
+-------+
69 rows in set (0.00 sec)

but this sum of floats is not like I wait... there are more than two numbers forward the ,...
mysql> SELECT SUM(prix) FROM passage_liste;
+-----------------+
| SUM(prix)       |
+-----------------+
| 10751.900011063 |
+-----------------+

How to repeat:
http://coucou747.hopto.org/paste_uploads.php?id=88&type=colore

mysql> SHOW CREATE table passage_liste
    -> ;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| passage_liste | CREATE TABLE `passage_liste` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_client` int(10) unsigned NOT NULL,
  `id_collaborateur` int(10) unsigned NOT NULL,
  `description` text,
  `date_de_passage` date default NULL,
  `prix` float default NULL,
  `prix_produits` float default NULL,
  `type_paiment` enum('carte bleu','cheque','espece','moneo') default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(prix) FROM passage_liste;
+-----------------+
| SUM(prix)       |
+-----------------+
| 10751.900011063 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT prix FROM passage_liste WHERE prix%1!=0;
+-------+
| prix  |
+-------+
|  40.5 |
|  14.5 |
|  86.5 |
|  12.4 |
|  24.8 |
| 114.5 |
|  40.5 |
|  62.5 |
|  19.5 |
|  66.5 |
|  62.5 |
|  91.5 |
|  84.5 |
| 125.5 |
| 118.5 |
|  62.5 |
|  62.5 |
|  26.5 |
|  12.4 |
|  41.1 |
| 108.6 |
|  62.5 |
|  59.4 |
|  48.5 |
|  29.2 |
|  43.4 |
|  38.5 |
|  26.1 |
|  55.4 |
|  83.3 |
|  12.4 |
|  66.5 |
|  61.5 |
|  78.5 |
|  53.8 |
|  22.5 |
|  32.5 |
|  36.5 |
|  79.5 |
|  95.5 |
|  33.4 |
|  94.2 |
|  57.5 |
|  61.5 |
| 100.5 |
|  52.5 |
|  91.4 |
|  83.5 |
|  57.5 |
|  66.5 |
|  62.5 |
|  34.9 |
|  81.5 |
|  36.5 |
|  26.5 |
|  85.5 |
|  77.4 |
|  26.5 |
|  36.5 |
|  43.5 |
|  36.5 |
|  31.5 |
|  43.4 |
|  67.5 |
|  43.4 |
|  51.4 |
|  52.5 |
|  89.5 |
|  30.6 |
+-------+
69 rows in set (0.00 sec)

mysql> SELECT SUM(prix) FROM passage_liste WHERE prix%1!=0;
+-----------------+
| SUM(prix)       |
+-----------------+
| 3919.9000110626 |
+-----------------+
1 row in set (0.01 sec)

Suggested fix:
mysql> SELECT version()
    -> ;
+---------------------+
| version()           |
+---------------------+
| 5.0.22-Debian_3-log |
+---------------------+
1 row in set (0.00 sec)

[15 Jul 2006 14:19] MySQL Verification Team

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.