Bug #1482 UPDATE allows column from target table in subquery
Submitted: 4 Oct 2003 17:33 Modified: 11 Oct 2003 4:41
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:MySQL 4.1.1 OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[4 Oct 2003 17:33] Peter Gulutzan
Description:
update t1 set s1 = (select sum(t1.s1) from t1);   -- fails 
update t1 set s1 = (select sum(t1.s1) from t2);   -- succeeds 
Either both these statements should fail, or both should succeed. 
 
Currently, allowing the second statement leads to an odd 
result. If t2 has more than one row, the effect is the same as 
if we said: 
update t1 set s1 = s1 * 2; 
 

How to repeat:
mysql> create table t1 (s1 int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t1 values (1); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into t1 values (2); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> create table t2 (s1 int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into t2 values (1); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into t2 values (2); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> update t1 set s1 = (select sum(t1.s1) from t2); 
Query OK, 2 rows affected (0.00 sec) 
Rows matched: 2  Changed: 2  Warnings: 0 
 
mysql> select * from t1; 
+------+ 
| s1   | 
+------+ 
|    2 | 
|    4 | 
+------+ 
2 rows in set (0.00 sec) 

Suggested fix:
Disallow use of "t1.s1" in subquery if table "t1" is target of UPDATE.
[10 Oct 2003 20:46] Miguel Solorzano
Assigned to Sanja.
[11 Oct 2003 4:41] Oleksandr Byelkin
This fixed in 5.0 by Sinisa