MySQL Bugs: #8736: on duplicate key update allows/ignores updates to read table

Bug #8736	on duplicate key update allows/ignores updates to read table
Submitted:	23 Feb 2005 17:31	Modified:	23 Feb 2005 21:51
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Jorge del Conde	CPU Architecture:	Any

Description:
the following query contains an update to the table used as data source:

insert into one select b from two  on duplicate key update b=3;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

it does succeed, but nothing has been updated (and i believ table "two" wasnt supposed to get updated)
select * from one;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

select * from two;
+------+
| b    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

How to repeat:
create table one (a int unique) select 1 a;
create table two (b int ) select 1 b;
insert into one select b from two  on duplicate key update b=3;
select * from one;
select * from two;

# trying to update a constant (no error)
insert into one select b from (select 1 b) x  on duplicate key update b=3;

Suggested fix:
dont accept a query, which tries to update a read only target

just to add reference to the "from" table within the update part are ok, but only onthe right side of the equation (IMHO)

...update [any field in one] = [any field in one or two]

Jorge, did you verify it on the *latest* 4.1.11 tree ?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html