MySQL Bugs: #852: problem of comparaison with FLOAT data type

Bug #852	problem of comparaison with FLOAT data type
Submitted:	16 Jul 2003 5:13	Modified:	16 Jul 2003 7:15
Reporter:	Christian Bonnet	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13 max log	OS:	Solaris (solaris)
Assigned to:		CPU Architecture:	Any

Description:
When using the type FLOAT for numeric data, the function "=" in a where clause doesn't work if the data has decimal number.
There is another problem with the FLOAT data type and the value returned by the "min" function.
NB : we do the test under windows NT and the version 4.0.11 and with oder table type (MyISAM) and we obtain the same results

How to repeat:
create table test (ID int AUTO_INCREMENT default'0' ,NB float default -8888, primary key (ID))Type = Innodb  ;

insert into test (NB) values (1),(2.11),(2.11),(2.1),(-999.9),(-99),(-999.9) ;

mysql> select * from test ;
+----+--------+
| ID | NB     |
+----+--------+
|  1 |      1 |
|  2 |   2.11 |
|  3 |   2.11 |
|  4 |    2.1 |
|  5 | -999.9 |
|  6 |    -99 |
|  7 | -999.9 |
+----+--------+
7 rows in set (0.00 sec)

mysql> select * from test where NB = -999.9 ;
Empty set (0.00 sec)

We should obtain the records 5 and 7 !

mysql> select * from test where NB = 2.11 ;
Empty set (0.00 sec)

We should obtain the records 2 and 3 !

mysql> select min(NB), max(NB) from test ;
+------------------+-----------------+
| min(NB)          | max(NB)         |
+------------------+-----------------+
| -999.90002441406 | 2.1099998950958 |
+------------------+-----------------+
1 row in set (0.00 sec)

we should obtain -999.9 and 2.11 !!!

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

This is docummented behaviour of FLOAT type in MySQL.

Please check:

http://www.mysql.com/doc/en/Problems_with_float.html
and 
http://www.mysql.com/doc/en/Numeric_types.html