Bug #8934 DOUBLE and DECIMAL comparison fails on Windows

• Submitted: 3 Mar 2005 22:58
• Modified: 4 Mar 2005 4:20
• Reporter: Bicho Verde
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.10-nt
• OS: Windows (Windows XP and 2003)
• CPU Architecture: Any

[3 Mar 2005 22:58] Bicho Verde

Description:
MySQL for Windows doesn't compare DECIMAL and DOUBLE values correctly. On Linux it works fine.

How to repeat:
DROP TABLE IF EXISTS testtable;

CREATE TABLE testtable (
id INT NOT NULL AUTO_INCREMENT ,
col1 DOUBLE(20,2) NOT NULL ,
col2 DECIMAL(20,2) NOT NULL ,
PRIMARY KEY ( id )
);

INSERT INTO testtable (col1, col2) VALUES ('7.27', '7.27');

SELECT * FROM testtable WHERE col1 <> col2;

Expected:
Empty set (0.00 sec)

Actual result on Windows XP and 2003:
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 | 7.27 | 7.27 |
+----+------+------+
1 row in set (0.00 sec)

Actual result on Linux:
Empty set (0.00 sec)

It also return wrong values for (on Windows only):

SELECT * FROM testtable WHERE col1 = col2;

SELECT * FROM testtable HAVING col1 < col2;

[3 Mar 2005 23:01] Bicho Verde

The workaround would be using ROUND():

SELECT * FROM testtable WHERE ROUND(col1,2) <> ROUND(col2,2);

[4 Mar 2005 4:20] MySQL Verification Team

This behavior can be expected when envolving floating-point numbers.
Please see:

http://dev.mysql.com/doc/mysql/en/problems-with-float.html