Bug #5505 Views: an updatable view is falsely called non-updatable
Submitted: 10 Sep 2004 2:25 Modified: 6 Oct 2006 2:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.2-alpha OS:Linux (SuSE 8.2)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[10 Sep 2004 2:25] Peter Gulutzan
Description:
There are some views which are updatable (in the sense that they can be used in an 
UPDATE statement), although they are not "insertable into" (i.e. they cannot be used in an 
INSERT statement). When an INSERT statement fails for such a view -- as it should -- the 
error message falsely says that the view is not "updatable". The message should say that 
it's not "insertable-into". 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.27 sec) 
 
mysql> create view v as select s1 as a, s1 as b from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into v values (1); 
ERROR 1288 (HY000): The target table v of the INSERT is not updatable 
mysql> update v set a = 5; 
Query OK, 0 rows affected (0.00 sec) 
Rows matched: 0  Changed: 0  Warnings: 0
[19 Aug 2005 10:19] Victoria Reznichenko
verified with  5.0.12

Modified test case (there is an error in the initial test case):

create table t (s1 int); 
create view v as select s1 as a, s1 as b from t; 
insert into v values (1,1); 
update v set a = 5;
[19 Sep 2006 18:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12227

ChangeSet@1.2273, 2006-09-19 21:55:31+04:00, evgen@moonbone.local +5 -0
  Fixed bug#5505: Wrong error message on INSERT into a view
  
  On an INSERT into an updatable but non-insertable view an error message was
  issued stating the view being not updatable. This can lead to a confusion of a
  user.
  
  A new error message is introduced. Is is showed when a user tries to insert
  into a non-insertable view.
[28 Sep 2006 21:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12783

ChangeSet@1.2273, 2006-09-29 01:00:18+04:00, evgen@moonbone.local +6 -0
  Fixed bug#5505: Wrong error message on INSERT into a view
  
  On an INSERT into an updatable but non-insertable view an error message was
  issued stating the view being not updatable. This can lead to a confusion of a
  user.
  
  A new error message is introduced. Is is showed when a user tries to insert
  into a non-insertable view.
[1 Oct 2006 8:58] Georgi Kodinov
Pushed in 5.0.26/5.1.12
[6 Oct 2006 2:40] Paul Dubois
Noted in 5.0.26, 5.1.12 changelogs.