Bug #5182 Problem with Floating Values comparision
Submitted: 24 Aug 2004 14:30 Modified: 24 Aug 2004 16:20
Reporter: Sivan Narayana Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.23 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[24 Aug 2004 14:30] Sivan Narayana 
Description:
We are getting odd results while comparing floating point values.
The database schema is like this..

Table Name : test
Field Name:Value
Values
0.24
0.28
0.29
1.04
1.09
1.10

Our query is

select * from test where Value<0.29
it should return all the values which are lower than 0.29..but it is giving values including 0.29
we tried for 
select * from test where Value<"0.29"
select * from test where Value<.29
Bue we are getting same results.

How to repeat:
We are getting odd results while comparing floating point values.
The database schema is like this..

Table Name : test
Field Name:Value
Values
0.24
0.28
0.29
1.04
1.09
1.10

Our query is

select * from test where Value<0.29
it should return all the values which are lower than 0.29..but it is giving values including 0.29
we tried for 
select * from test where Value<"0.29"
select * from test where Value<.29
Bue we are getting same results.
[24 Aug 2004 16:20] MySQL Verification Team 
It is not a bug. The above behavior is expected for floating-point comparison.
You can use NUMERIC or DECIMAL column types to avoid this behavior.
More info about floating-point comparison you can find in the MySQL manual at:
http://dev.mysql.com/doc/mysql/en/Problems_with_float.html