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:
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
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
  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.