MySQL Bugs: #5505: Views: an updatable view is falsely called non-updatable

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

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

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;

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.

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.

Pushed in 5.0.26/5.1.12

Noted in 5.0.26, 5.1.12 changelogs.