Description:
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'
) TYPE=MyISAM;
INSERT INTO `test3`
(`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
DELETE FROM `test3`
WHERE `Field1`='-2.718281828' AND ABS(`Field2` - -2.71828) < 1e-031
LIMIT 1
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.
Description: 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' ) TYPE=MyISAM; INSERT INTO `test3` (`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 DELETE FROM `test3` WHERE `Field1`='-2.718281828' AND ABS(`Field2` - -2.71828) < 1e-031 LIMIT 1 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.