MySQL Bugs: #8048: SQL Failed: Query Match For Float Data Type

Bug #8048	SQL Failed: Query Match For Float Data Type
Submitted:	20 Jan 2005 15:37	Modified:	20 Jan 2005 16:51
Reporter:	Zhen Ou	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.21	OS:	Unix/Window
Assigned to:		CPU Architecture:	Any

Description:
Unable to do an exact match for float data type when the value is a decimal point.  It works when the value doesn't contains any decimal points.  Sorry for the weak description.  Hope the "How to repeat" makes more sense.

How to repeat:
-- 
-- Table structure for table `test`
-- 

CREATE TABLE `test` (
  `t1` int(11) NOT NULL default '0',
  `f1` float NOT NULL default '0',
  `d1` double NOT NULL default '0'
) TYPE=MyISAM;

-- 
-- Dumping data for table `test`
-- 

INSERT INTO `test` (`t1`, `f1`, `d1`) VALUES (0, 0.886, 2.343);
INSERT INTO `test` (`t1`, `f1`, `d1`) VALUES (1, 2, 3);
INSERT INTO `test` (`t1`, `f1`, `d1`) VALUES (12, 13.453, 4);

Failed Query: (Zero Hits)
SELECT * FROM `test` WHERE `f1` = 13.453 LIMIT 0 , 30

Successful Query: (1 Hits)
SELECT * FROM `test` WHERE `f1` = 2 LIMIT 0 , 30

Successful Query: (1 Hits)
SELECT * FROM `test` WHERE `f1` = 2.0 LIMIT 0 , 30

Successful Query: (1 Hits)
SELECT * FROM `test` WHERE `f1` >= 13.453 LIMIT 0 , 30

Successful Query: (1 Hits)
SELECT * FROM `test` WHERE `d1` = 2.343 LIMIT 0 , 30

No error.
It's just the binary Version of float. :-)

float is 4 Bytes. so your range is from -3.40E+38 to 1.17E-38 
double is 8 Bytes (E+308- E-308)
In 4 Bytes you can just do this:
2^-1 =0,5
2^-2 =0,25
2^-3 =0,125
2^-4 =0,0625
...
When you have your floats, the CPU tries to compute a Binary for these Floats. This is not possible for many floats.
When you make a query , your inputs like f1=0.2341 then the 0.2341 is interpreted as double ! :-)
this is the reason, why cou can compare the double with the same double input :-)

I think I have a better understanding now.  Thanks.

How would you change the sql to make it work?
(SELECT * FROM `test` WHERE `f1` = 13.453)

thanks.

This sql statement would work but then how you you know f1 contains 3 decimal point to begin with.

Works, but not accurate:
SELECT * FROM `test` WHERE format(`f1`, 2) = format(13.45, 2)

Works:
SELECT * FROM `test` WHERE format(`f1`, 3) = format(13.45, 3)

thx

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, take a look at:
http://dev.mysql.com/doc/mysql/en/Problems_with_float.html