#456 float fields causing error in updating record

[Anonymous]

Originally created by: ansgar.b...@wolterskluwer.de
Originally owned by: a...@anse.de

Reported as bug #1749344 by philipz_ca@sf on 2007-07-06 13:07

In order to get this message across i have given a full example. First the
sql.

CREATE TABLE `cities` (
  `country` char(2) default NULL,
  `city` char(50) default NULL,
  `accent_city` char(50) default NULL,
  `region` int(10) unsigned default NULL,
  `latitude` float default NULL,
  `longitude` float default NULL
) TYPE=MyISAM;

INSERT INTO `cities` (`country`, `city`, `accent_city`, `region`,
`latitude`, `longitude`) VALUES ('AE',' al lusayli',' Al
Lusayli','3','24.9314','55.4739');

After sql is in the database, go into the datagrid and remove the first
space in the 'city' field of the record, which executes the following sql.

UPDATE `cities` SET `city`='al lusayli' WHERE `country`='AE' AND `city`='
al lusayli' AND `accent_city`=' Al Lusayli' AND `region`=3 AND
`latitude`=24.9314 AND `longitude`=55.4739

it results in

/* Affected rows: 0 */

and the error dialog

/* Warning: No row was affected by the last update.  This is most likely
caused by entering data which the MySQL-server has converted silently. For
example when you enter a date like "0000-01-01" (applies only to newer
MySQL-versions). */

the problem is due to the floats as the following sql updated it

UPDATE `cities` SET `city`='al lusayli' WHERE `country`='AE' AND `city`='
al lusayli' AND `accent_city`=' Al Lusayli' AND `region`=3

[Anonymous]

Originally posted by: rosenfie...@gmail.com

(No comment was entered for this change.)

Labels: -priority-default Severity-Default

[Anonymous]

Originally posted by: a...@anse.de

(No comment was entered for this change.)

Labels: -migrated

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Seems to cause an access violation now.

After creating the table and inserting a record with the SQL given here, but before
editing data in the data tab, create a primary key spanning all columns:

ALTER TABLE `cities` ADD PRIMARY KEY (`country`, `city`, `accent_city`, `region`,
`latitude`, `longitude`)

Labels: -Type-Enhancement -Severity-Default Type-Defect Severity-Broken

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Fixed in [r1850] as far as HeidiSQL goes.

The server still seems to fail to find the row to update (v5.0.67) when using the
float values as a PK.  Must be a server bug.

Status: Upstream