Bug #5157 Views: Updating with illegal subqueries
Submitted: 23 Aug 2004 1:37 Modified: 10 Sep 2004 11:03
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[23 Aug 2004 1:37] Peter Gulutzan 
Description:
MySQL is trying to prevent updates with subqueries that refer 
to the table being updated (it's dangerous in MySQL). But it 
it is possible to bypass this prevention, by updating a view 
of a view, and referring to a view. 

How to repeat:
mysql> create table tk (col1 int); 
Query OK, 0 rows affected (0.59 sec) 
 
mysql> update tk set col1 = (select max(col1) from tk); -- fails 
ERROR 1093 (HY000): You can't specify target table 'tk' for update in FROM clause 
mysql> create view vk1 as select * from tk; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create view vk2 as select * from vk1; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> update vk2 set col1 = (select max(col1) from vk1); -- succeeds 
Query OK, 0 rows affected (0.00 sec) 
Rows matched: 0  Changed: 0  Warnings: 0
[8 Sep 2004 7:38] Oleksandr Byelkin 
ChangeSet 
  1.1753 04/09/08 10:18:04 bell@sanja.is.com.ua +12 -0 
  test of updating and fetching from the same table check (BUG##5157)
[10 Sep 2004 11:03] Oleksandr Byelkin 
Thnak you for bug report! Bug was fixed and patch is pushed into our source repositiory.