Bug #19780 DOUBLE PRIMARY KEY NOT DELETING WHEN ROUND PROBLEM!!!!
Submitted: 12 May 2006 18:00 Modified: 29 Mar 2011 18:38
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.1.5 - 5.1.9 OS:Linux (LINUX)
Assigned to: CPU Architecture:Any

[12 May 2006 18:00] Roberto Spadim 
Description:
CREATE A TABLE WITH A DOUBLE FIELD AS PRIMARY KEY
INSERT A VALUE WITH ROUND PROBLEM (4.1,3.1)
TRY TO DELETE TABLE WHERE KEY=4.1
SELECT * FROM TABLE
THE ROW STILL THERE!!!

How to repeat:
CREATE A TABLE WITH A DOUBLE FIELD AS PRIMARY KEY
INSERT A VALUE WITH ROUND PROBLEM (4.1,3.1)
TRY TO DELETE TABLE WHERE KEY=4.1
SELECT * FROM TABLE
THE ROW STILL THERE!!!

Suggested fix:
MAY BE WHEN SEEKING KEY=4.1, SERVER DON'T CHANGE 4.1 AS DOUBLE, WHEN FINDING ON TABLE IT READ 4.099999999999999999!=4.1 AND DON'T DELETE THE ROW
[12 May 2006 18:42] Hartmut Holzgraefe 
first rule of FLOAT: never compare floats for equality

if you need the precision you have to use the DECIMAL type
[13 May 2006 1:59] Roberto Spadim 
version 4.1 work with double primary keys, version 5.1 have the problem of 0.1!=0.1 (0.0999999)
[13 May 2006 5:33] Paul DuBois 
Reliance on exact-value float comparisons is dangerous.
See:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html
[29 Mar 2011 18:38] Roberto Spadim 
ok changed to decimal, thanks