Bug #8934 DOUBLE and DECIMAL comparison fails on Windows

Submitted: 3 Mar 2005 22:58 Modified: 4 Mar 2005 4:20
Reporter: Bicho Verde Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.10-nt OS:Windows (Windows XP and 2003)
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.