MySQL Bugs: #25122: insertability of views not consistent

Bug #25122	insertability of views not consistent
Submitted:	17 Dec 2006 19:17	Modified:	16 Mar 2007 16:35
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 5.0.30	OS:	Linux (Linux, freebsd)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	insert, VIEW

Description:
with reference to the documentation issue in bug #25121 , the below could be either a bug, or an undocumented issue.

It is a bug, IF the following documentation is correct
http://dev.mysql.com/doc/refman/5.0/en/faqs-views.html#qandaitem-26-6-6
It is possible, provided that your INSERT  statement has a column list that makes it clear there's only one table involved.

See how to repeat. View with join are insertable (to single table), as long as the view is not a self-join. (But even in a self-join, the insert affects only one table. and only under one alias)

The only differnce between the 2 views is, tthat (in the insert-able version) the unuised columns come from a differnt table.
In the (non-insert-able) the unused columns come from the same table (under a diff alias) 

insert into v1 (a1) values (4);
Query OK, 1 row affected (0.00 sec)

insert into v2 (a1) values (4);
ERROR 1471 (HY000): The target table v2 of the INSERT is not insertable-into

How to repeat:
drop table if exists b1; drop table if exists b2;
drop view if exists v1;  drop view if exists v2;

create table b1 (a int, b int); insert into b1 values (1,11),(2,22);
create table b2 (a int, b int); insert into b1 values (1,10),(3,30);

create view v1 as
 select b1.a a1, b1.b b1, b2.a a2, b2.b b2 from b1, b2 where b1.a = b2.a;

insert into v1 (a1) values (4);
select * from b1;

create view v2 as
 select b1.a a1, b1.b b1, x.a a2, x.b b2 from b1, b1 x where b1.a = x.a;

insert into v2 (a1) values (4);
select * from b1;

Suggested fix:
This may be an documentation issue; it may be a bug too

Thank you for a problem report. Verified just as described with latest 5.0.34-BK on Linux. As you already noted, it is either a bug (I think so), or a request for explicit and proper documentation of this behaviour.

If it is a bug, then still some documentation may be needed, because  the self join will not work with the "with check option". (unless the view does not have join conditions)

because otherwise the check has to read the same table as the table that is inserted into.

Without the "with check option", there should be no issue allowing this.

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/20686

ChangeSet@1.2419, 2007-02-27 23:02:28+03:00, evgen@moonbone.local +9 -0
  Bug#25122: Views based on a self-joined table aren't insertable.
  
  When INSERT is done over a view the table being inserted into is 
  checked to be unique among all views tables. But if the view contains
  self-joined table an error will be thrown even if all tables are used under
  different aliases.
  
  The unique_table() function now also checks tables' aliases when needed.

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/21011

ChangeSet@1.2419, 2007-03-02 00:09:22+03:00, evgen@moonbone.local +7 -0
  Bug#25122: Views based on a self-joined table aren't insertable.
  
  When INSERT is done over a view the table being inserted into is 
  checked to be unique among all views tables. But if the view contains
  self-joined table an error will be thrown even if all tables are used under
  different aliases.
  
  The unique_table() function now also checks tables' aliases when needed.

Pushed to 5.0.38, 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.

A view on a join is insertable for INSERT statements that store
values into only one table of the join. However, inserts were being
rejected if the inserted-into table was used in a self-join because
MySQL incorrectly was considering the insert to modify multiple
tables of the view.