Bug #5504 Views: a theoretically-updatable view can't be updated

• Submitted: 10 Sep 2004 2:19
• Modified: 8 Oct 2004 9:17
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[10 Sep 2004 2:19] Peter Gulutzan

Description:
A view is not updatable if the SELECT has a subquery. 
It should be updatable, unless the subquery refers to the table being updated. 
 

How to repeat:
mysql> set sql_updatable_view_key='NO'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (s1 int); 
Query OK, 0 rows affected (0.27 sec) 
 
mysql> create table t2 (s1 int); 
Query OK, 0 rows affected (0.30 sec) 
 
mysql> create view v2 as select * from t2 where s1 in (select s1 from t1); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into v2 values (5); 
ERROR 1288 (HY000): The target table v2 of the INSERT is not updatable 
mysql> update v2 set s1 = 0; 
ERROR 1288 (HY000): The target table v2 of the UPDATE is not updatable

[10 Sep 2004 19:07] Peter Gulutzan

Changed from feature request to non-critical bug

[12 Sep 2004 21:10] MySQL Verification Team

Verified against latest 5.0 BK source.

[7 Oct 2004 19:55] Oleksandr Byelkin

ChangeSet 
  1.1638 04/10/07 22:54:31 [e-mail] +5 -0 
  allow merging views with subqueries in WHERE clause (BUG#5504)

[8 Oct 2004 9:17] Oleksandr Byelkin

Thank you for bugreport! Bug is fixed, patch is pushed into our source repository.