Bug #8955 strange update behavior
Submitted: 4 Mar 2005 20:35 Modified: 8 Aug 2005 14:32
Reporter: Corin Langosch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.02 OS:Linux (linux debian)
Assigned to: Ingo Strüwing CPU Architecture:Any

[4 Mar 2005 20:35] Corin Langosch 
Description:
strange update behavior using multitable update and self-reference row-updates

How to repeat:
CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL
) ENGINE=MyISAM;

INSERT INTO `a` VALUES (1);
INSERT INTO `a` VALUES (2);
INSERT INTO `a` VALUES (3);

CREATE TABLE `b` (
  `num` int(10) unsigned NOT NULL
) ENGINE=MyISAM;

INSERT INTO `b` VALUES (3);

update a,b set b.num=b.num-1;

select num from b;

shows 2 but it should be 0, because the row was interated three times!
[4 Mar 2005 20:36] Corin Langosch 
set @n:=0;
select @n:=@n+1 from a,b;

shows 3, as expected!
[15 Mar 2005 8:44] Corin Langosch 
verified with MySQL 4.1.10-standard
[8 Aug 2005 14:32] Ingo Strüwing 
Multi-table update is not a standard SQL feature. Why do you think it should behave in the way that you described? This is a MySQL extension. Its behaviour is not documented in the way which you described. Hence, I cannot accept this as a bug.

The behaviour of multi-table update is similar to:
create v1 as select * from t1, t2;
update v1 set t2_num=t2_num-1;

As you will see, this updates every row once only.