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
• Status: Won't fix
• 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] MySQL Verification Team

Assigned to Sanja.

[11 Oct 2003 4:41] Oleksandr Byelkin

This fixed in 5.0 by Sinisa