Bug #21346 Incorrect results are displayed when querying records what have float of >= x.29

• Submitted: 30 Jul 2006 2:22
• Modified: 30 Jul 2006 10:09
• Reporter: E Lau
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: MySQL 5.0.18-nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[30 Jul 2006 2:22] E Lau

Description:
When trying to query records that are >= float value of 'x.29' (ie 0.29, 1.29, 2.29, 3.29...etc), MySQL displays zero results. 

How to repeat:
1) Create a table with field 'item_price'.
-  CREATE TABLE PRODUCTS (item_price float NOT NULL);

2) Insert a few records into table PRODUCTS.
-  INSERT INTO PRODUCTS (item_price) VALUES (0.80);
-  INSERT INTO PRODUCTS (item_price) VALUES (3.75);
-  INSERT INTO PRODUCTS (item_price) VALUES (1.29);

3) Select records where item_price is >= 1.29.
-  SELECT * FROM PRODUCTS WHERE item_price >= 1.29;

ACTUAL: Only 1 record where 'item_price' equal to 3.75 is displayed.

EXPECTED: 2 records for 'item_price' equal to 1.29 and 3.75 are displayed.

Suggested fix:
Please see why record where item_price equal to 1.29 is not displayed in the results.

Not sure if there are any other numbers where this might happen.

[30 Jul 2006 2:32] E Lau

In 'Description' of bug, I meant "incorrect results", not "zero results".

[30 Jul 2006 10:09] Hartmut Holzgraefe

That's how FLOAT works. FLOAT rule number one is "never compare floats for equality" as they are stored in binary internally, and for most decimal fractions threre is no exact representation in binary, just as there is no exact decimal representation of 1/3 (= 0.3333333...) for example.

When dealing with monetary values like in your example DECIMAL is the type of choice, FLOAT is ment for more scientific values. It is a simple tradeof between precision and performance ...