MySQL Bugs: #40125: Serious error while updating a record from a table! You will over write all!

Bug #40125	Serious error while updating a record from a table! You will over write all!
Submitted:	18 Oct 2008 11:51	Modified:	20 Oct 2008 10:38
Reporter:	Alexandru Ciobanu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.51, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dear Sirs,
I have to project a database using MySql, and I have the following
problem: while updating a table of database, a strang thing is updating
all the records of the table. But this is happening randomize.
I have found from the log file the command who is doing this:
180229 Query       UPDATE planul_de_realizare SET
etapa_nr=3.00000000000000000e+000 ,
activitate_nr=1.00000000000000000e+000
,faza_nr=0.00000000000000000e+000 ,
descriere='Realizare model experimental' ,rol='P3' ,rezultate='model
experimental ' ,
finantare=1.05600000000000000e+005
,cofinantare=1.#INF0000000000000e+000 ,
categoria=4.90000000000000000e+001 ,datatrn='2008-10-14 14:58:21' ,
statia='Ver.3 EMS # Administrator IP:192.168.3.32        '  WHERE
id_plan=46261

The system is using Microsoft VisualFoxPro 9, as front end.
You can see the WHERE clause, BUT,updating a field with
1.#INF0000000000000e+000 (user can obtain this only using COPY-PASTE
from another file, because the textbox is protected when operating),
the result is that MySql does not see the clause WHERE and is Updating
all the records.
The field "cofinantare" is Integer, the table is InnoDb.
Do you know about this bug?
For more details, please contact me (alexandru_ciobanu_2@yahoo.com)

Alexandru Ciobanu

How to repeat:
You can obtain the bug running a command UPDATE over any Integer field of an InnoDB table, with the 1.#INF0000000000000e+000 value!!!

Suggested fix:
I think there is a problem with the SQL interpreter!

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67. In case of the same roblem, please, send the results of:

SHOW CREATE TABLE planul_de_realizare;

I can not get the result like that with my InnoDB tables on a newer version using mysql command line client.

Is it possible that this is an error (or incompatibility) in the SQL?

If the given query is all in ONE single line (assuming the new lines are inserted in the bug-report only) then 
  ,cofinantare=1.#INF0000000000000e+000 , ....
would be treated as 
  ,cofinantare=1.
followed by a comment. (# makes the rest of the line a comment).
This would move the "where" clause into the comment, and hence update the whole table

CREATE TABLE `planul_de_realizare` (
  `id_plan` int(10) unsigned NOT NULL auto_increment,
  `id_ln` int(10) unsigned zerofill NOT NULL COMMENT 'ne relationam cu contractul',
  `anul` varchar(4) NOT NULL,
  `etapa_nr` tinyint(2) unsigned zerofill NOT NULL,
  `activitate_nr` tinyint(2) unsigned zerofill NOT NULL,
  `faza_nr` tinyint(2) unsigned zerofill NOT NULL,
  `descriere` text NOT NULL,
  `categoria` int(10) unsigned zerofill NOT NULL,
  `rol` varchar(2) NOT NULL,
  `termen` date NOT NULL,
  `rezultate` text NOT NULL,
  `finantare` int(10) unsigned zerofill NOT NULL,
  `cofinantare` int(10) unsigned zerofill NOT NULL,
  `data_in` date NOT NULL,
  `data_out` date NOT NULL,
  `avans` int(10) unsigned zerofill NOT NULL COMMENT 'daca s-a platit un avans',
  `doc2` varchar(20) NOT NULL COMMENT 'numarul documentului cu care s-a platit un avans',
  `data_doc2` date NOT NULL COMMENT 'data la care s-a platit un avans',
  `data_docum` date NOT NULL COMMENT 'data documentului (pentru documentele aditionale, se pot modifica aici campurile cu sume sau termenele)',
  `datatrn` datetime NOT NULL,
  `id_oper` int(10) unsigned NOT NULL,
  `sters` tinyint(1) unsigned NOT NULL,
  `statia` varchar(60) NOT NULL,
  `dataserver` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id_plan`),
  KEY `Index_2` (`id_plan`,`id_ln`)
) ENGINE=InnoDB AUTO_INCREMENT=61504 DEFAULT CHARSET=latin1

The line I found in the log is:
180229 Query       UPDATE planul_de_realizare SET
etapa_nr=3.00000000000000000e+000 ,
activitate_nr=1.00000000000000000e+000
,faza_nr=0.00000000000000000e+000 ,
descriere='Realizare model experimental' ,rol='P3' ,rezultate='model
experimental ' ,
finantare=1.05600000000000000e+005
,cofinantare=1.#INF0000000000000e+000 ,
categoria=4.90000000000000000e+001 ,datatrn='2008-10-14 14:58:21' ,
statia='Ver.3 EMS # Administrator IP:192.168.3.32        '  WHERE
id_plan=46261

This command will overwrite all the records in the table!
The SQL interpreter will not be able to see the WHERE clause, beacuse of the value 1.#INF0000000000000e+000 of "cofinantare" field !!!

Thank you for the report.

Verified as described.

Test case:

create table t1(f1 int, f2 int);

insert into t1 values(1,2), (3,4), (5,6);

update t1 set f1=7, f2=1.#INF0000000000000e+000 where f1=1;

select * from t1;

Result:

create table t1(f1 int, f2 int);
insert into t1 values(1,2), (3,4), (5,6);
update t1 set f1=7, f2=1.#INF0000000000000e+000 where f1=1;
select * from t1;
f1      f2
7       1
7       1
7       1

Not a bug, see the explanation given by Martin.