Bug #3249 Can not change or delete records with float field and no key
Submitted: 21 Mar 2004 18:34 Modified: 1 Jun 2004 0:41
Reporter: Paul Wolstenholme Email Updates:
Status: Closed Impact on me:
Category:MySQLCC Severity:S2 (Serious)
Version:0.9.4-beta OS:W2000 Professional
Assigned to: Bugs System CPU Architecture:Any

[21 Mar 2004 18:34] Paul Wolstenholme
I know it is not normal practice to use tables without a sensible unique key - but I do find they are useful during development of an interface to a database.  This is exactly the time that I need a tool like MySQLCC to help me out. When I've got the industrial automation software talking to the database properly I'll start using sensibly keyed tables - for now I want to eliminate causes of records not getting written.

The problem arises when a table is created with no unique key that includes one or more float fields.  Using MySQLCC to edit or delete a record with an 'irrational' value in the float field fails.  Although the query MySQLCC generates does allow for some tolerance in the float value, the tolerance is not wide enough in almost all cases.

How to repeat:
CREATE TABLE `test3` (
  `Field1` varchar(100) NOT NULL default '',
  `Field2` float NOT NULL default '0'

 (`Field1`, `Field2`)
VALUES ('-2.718281828', -2.718281828)

Now double click on the table in MySQLCC to open a Query Window showing the record created (or you can enter records directly here).
Attempt to edit or delete the record.  The action appears to happen BUT the messages window shows '0 rows affected' and re-querying (!) confirms no change happened.

Suggested fix:
The history window shows MYSQLCC generated a query such as

WHERE `Field1`='-2.718281828' AND ABS(`Field2` - -2.71828) < 1e-031

Clearly the 1e-031 is not correct.  Perhaps a value similar to
max(1e-031, abs(<TargetValue>) * 1e-06) would be more appropriate?
In this example that equates to 2.71828e-6.
[29 Apr 2004 17:25] MySQL Verification Team
Thank you for the bug report I was able to repeat.
[1 Jun 2004 0:41] Jorge del Conde

MySQLCC has been discontinued and will not be developed nor maintained anymore with the exception of Critical bugs.

In the near future we will release the substitute of MySQLCC called MySQL Query Browser.