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

[17 Dec 2006 19:17] Martin Friebe
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
[18 Dec 2006 22:26] Valeriy Kravchuk
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.
[19 Dec 2006 14:22] Martin Friebe
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.
[27 Feb 2007 20:03] 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/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.
[2 Mar 2007 12:10] 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/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.
[12 Mar 2007 5:10] Igor Babaev
Pushed to 5.0.38, 5.1.17
[16 Mar 2007 16:35] Paul Dubois
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.