Bug #4602 Views: View not updatable if math operation is included in view definition

• Submitted: 18 Jul 2004 22:14
• Modified: 21 Jul 2004 14:19
• Reporter: Trudy Pelzer
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.1-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[18 Jul 2004 22:14] Trudy Pelzer

Description:
If one creates a view (e.g. vt1) based upon a single underlying table (e.g. t1), but includes a simple math operation on one underlying column in the view definition, the view cannot be updated, although there is no theoretical reason not to allow the update. At UPDATE, MySQL returns this error:
ERROR 1288 (HY000): The target table vt1 of the UPDATE is not updatable

How to repeat:
create table t1 (col1 int,col2 char(22));
insert into t1 values(5,'Hello, world of views');
create view vt1 as select col1+1 as col1,col2 from t1;
update vt1 set col2='Hello, views';

[21 Jul 2004 14:17] Oleksandr Byelkin

Thank you for bugreport, but it is expected behaviour. 
MySQL has special protection against changing view which do not contain key of 
underlaying table(s) or all fields of that table(s). If we add primary key (for example to 
that script it will work: 
+ create table t1 (col1 int,col2 char(22), primary key (col2)); 
+ insert into t1 values(5,'Hello, world of views'); 
+ create view vt1 as select col1+1 as col1,col2 from t1; 
+ update vt1 set col2='Hello, views'; 
+ select * from t1; 
+ col1  col2 
+ 5     Hello, views 
 
Also this check can be switched off using sql_updatable_view_key variable (name of 
variable is not so good => it can be changed): 
 
+ create table t1 (col1 int,col2 char(22)); 
+ insert into t1 values(5,'Hello, world of views'); 
+ create view vt1 as select col1+1 as col1,col2 from t1; 
+ set sql_updatable_view_key=NO; 
+ update vt1 set col2='Hello, views'; 
+ Warnings: 
+ Note  1353    View being update does not have complete key of underlying table in it 
+ select * from t1; 
+ col1  col2 
+ 5     Hello, views

[21 Jul 2004 14:19] Oleksandr Byelkin

I forgot to add that this protection was made to protect data against GUI tool accident 
corruption. 
 
Varianle have 3 value 
YES (default) issue error on attempt to update such view 
NO - do not issue error (only warning) 
LIMIT1 - issue error only if update statement have LIMIT 1 clause