MySQL Bugs: #9010: Bug or made-by-design

Bug #9010	Bug or made-by-design
Submitted:	7 Mar 2005 8:10	Modified:	9 Mar 2005 12:21
Reporter:	Lars Annerstedt	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:	Windows (Win2000)
Assigned to:		CPU Architecture:	Any

Description:
A table contains 5 different entries with an Id and a Counter value.
If the Id=3 and Counter=3 for some of the other entries all entries will be updated to Counter=4, when Counter is incremented, if Id is omitted in the select clause.

(Server 4.1.9, MyODBC 3.51.11-2, VB6)

How to repeat:
CREATE TABLE `test` (
  `Id` smallint(6) NOT NULL default '0',
  `Counter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test`
 (`Id`,`Counter`) 
VALUES 
 (1,3),
 (2,3),
 (3,0),
 (4,3),
 (5,3);

*** Following code snippet will then make all entries having Counter = 4!
queryS = "SELECT Counter FROM TEST WHERE Id = 3"
Set queryRS = New ADODB.Recordset
With queryRS
   .Open queryS, SQLConnection, adOpenStatic, adLockOptimistic
   !Counter = !Counter + 1
   .Update
   .Close
End With

Suggested fix:
Bug or made-by-design?
(Works fine with MS SQL).

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

duplicate of #9009