Bug #5610 19.99 problem
Submitted: 16 Sep 2004 15:16 Modified: 16 Sep 2004 16:33
Reporter: Paul Ardeleanu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version: OS:Linux (Suse 9.1)
Assigned to: CPU Architecture:Any

[16 Sep 2004 15:16] Paul Ardeleanu
Description:
Hi,
I get some confusing results when comparing the value: 19.99.
Here are my tests:

select 19.99*100, (19.99*100) = 1999, 14.99*100, (14.99*100) = 1499, 1499.00 = 1499, 1999.00 = 1999;

+-----------+--------------------+-----------+--------------------+----------------+----------------+
| 19.99*100 | (19.99*100) = 1999 | 14.99*100 | (14.99*100) = 1499 | 1499.00 = 1499 | 1999.00 = 1999 |
+-----------+--------------------+-----------+--------------------+----------------+----------------+
|   1999.00 |                  0 |   1499.00 |                  1 |              1 |              1 |
+-----------+--------------------+-----------+--------------------+----------------+----------------+

select 19.98*100, (19.98*100) = 1998, 19.99*100, (19.99*100) = 1999, 20.00*100, (20.00*100) = 2000;

+-----------+--------------------+-----------+--------------------+-----------+--------------------+
| 19.98*100 | (19.98*100) = 1998 | 19.99*100 | (19.99*100) = 1999 | 20.00*100 | (20.00*100) = 2000 |
+-----------+--------------------+-----------+--------------------+-----------+--------------------+
|   1998.00 |                  1 |   1999.00 |                  0 |   2000.00 |                  1 |
+-----------+--------------------+-----------+--------------------+-----------+--------------------+

select 19.991*100, (19.991*100) = 1999.1, 19.99*100, (19.99*100) = 1999, 19.899*100, (19.899*100) = 1989.9;

+------------+-----------------------+-----------+--------------------+------------+-----------------------+
| 19.991*100 | (19.991*100) = 1999.1 | 19.99*100 | (19.99*100) = 1999 | 19.899*100 | (19.899*100) = 1989.9 |
+------------+-----------------------+-----------+--------------------+------------+-----------------------+
|   1999.100 |                     1 |   1999.00 |                  0 |   1989.900 |                     1 |
+------------+-----------------------+-----------+--------------------+------------+-----------------------+

select 19.99*100, (19.99*100) = 1999, (19.99*100) = 1999.00;

+-----------+--------------------+-----------------------+
| 19.99*100 | (19.99*100) = 1999 | (19.99*100) = 1999.00 |
+-----------+--------------------+-----------------------+
|   1999.00 |                  0 |                     0 |
+-----------+--------------------+-----------------------+

The test (19.99*100) = 1999 always fails. Although similar tests for 19.991 and 19.899 did not fail.
I am using Mysql4.1 on Suse. I've noticed the same effect on mysql 4.0 and mysql 5.0.
Thanks,
Paul

How to repeat:
select 19.99*100, (19.99*100) = 1999, 14.99*100, (14.99*100) = 1499, 1499.00 = 1499, 1999.00 = 1999;

select 19.98*100, (19.98*100) = 1998, 19.99*100, (19.99*100) = 1999, 20.00*100, (20.00*100) = 2000;

select 19.991*100, (19.991*100) = 1999.1, 19.99*100, (19.99*100) = 1999, 19.899*100, (19.899*100) = 1989.9;

select 19.99*100, (19.99*100) = 1999, (19.99*100) = 1999.00;
[16 Sep 2004 15:20] Paul Ardeleanu
See also:

select 19.99*100 between 1998.9 and 1999, 19.99*100 between 1999 and 1999.1;
[16 Sep 2004 16:33] MySQL Verification Team
Please read:

http://dev.mysql.com/doc/mysql/en/Problems_with_float.html

why sometimes float point comparison have that behavior.