MySQL Bugs: #5653: 'WHERE"-statement fails on certain values

Bug #5653	'WHERE"-statement fails on certain values
Submitted:	19 Sep 2004 15:37	Modified:	7 Oct 2004 3:13
Reporter:	Jan Schroeder	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.x	OS:	Any (All)
Assigned to:	Lachlan Mulcahy	CPU Architecture:	Any

Description:
After migrating to MySQL 4.1.4 gamma (Windows), the "WHERE"-statement fails to give result on certain values. I tested on several machines (Windows XP Prof. & 2000 Server).

How to repeat:
/* SQL-statements for repeating */
/* create table with certain values */
CREATE TABLE `table1` (`val1` double(10,2) NOT NULL default '0.00') ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `table1` VALUES (571.23);
INSERT INTO `table1` VALUES (578.84);

/* the following statement will work correctly: */
SELECT * FROM table1 WHERE val1=571.23;

/* the following statement will bring NO RESULT, the record is simply ignored: */
SELECT * FROM table1 WHERE val1=578.84;

Suggested fix:
Currenctly, i can only give the hint that the rows can be accessed using the "LIKE"-statement:

SELECT * FROM table1 WHERE val1 LIKE '%578.84%'

MySQL 4.0.21 is not affected. Thus, backporting to this release will help.

I have confirmed this bug on current dev tree on MacOSX and appended the mysql-tests as 
follows to demonstrate in my own private tree. I will leave it to the developer to add these when 
the fix is done:

-- mysql-test/t/type_decimal.test --

# Bug 5653 - Some decimals are not matched correctly by WHEREs
CREATE TABLE t1 (val1 double(10,2) NOT NULL default '0.00') ENGINE=MyISAM;
INSERT INTO t1 VALUES (571.23);
INSERT INTO t1 VALUES (578.84);
SELECT * FROM t1 WHERE val1=571.23;
SELECT * FROM t1 WHERE val1=578.84;
DROP TABLE t1;

-- r/type_decimal.result --
CREATE TABLE t1 (val1 double(10,2) NOT NULL default '0.00') ENGINE=MyISAM;
INSERT INTO t1 VALUES (571.23);
INSERT INTO t1 VALUES (578.84);
SELECT * FROM t1 WHERE val1=571.23;
val1
571.23
SELECT * FROM t1 WHERE val1=578.84;
val1
578.84
DROP TABLE t1;

This is actually an inherent problem with floating point math, rather than a bug in MySQL. For 
these kinds of fields you should use DECIMAL type for accuracy. You can read more here:
http://dev.mysql.com/doc/mysql/en/Problems_with_float.html