MySQL Bugs: #39113: INSERT into VIEW not permited when single column is aliased

Bug #39113	INSERT into VIEW not permited when single column is aliased
Submitted:	29 Aug 2008 10:02	Modified:	5 Sep 2008 17:44
Reporter:	Maciej Tomaka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC Documentation	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.66a, 5.1.26	OS:	Linux (debian-linux-gnu (x86_64))
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	insert, permited, VIEW

Description:
When VIEW contains a column alias then simple INSERT statement fails with error:
 target table ... of the INSERT is not insertable-into.

How to repeat:
CREATE table _products (product_id INT(11) NOT NULL PRIMARY KEY);

CREATE OR REPLACE VIEW productions AS SELECT _products.product_id AS id, _products.* FROM _products;

CREATE OR REPLACE VIEW products AS SELECT _products.product_id AS id, _products.* FROM _products;

INSERT INTO products (product_id) VALUES (1);
ERROR 1471 (HY000): The target table products of the INSERT is not insertable-into

INSERT INTO products (product_id,id) VALUES (1,1);
ERROR 1471 (HY000): The target table products of the INSERT is not insertable-into

When there is no alias then it is possible to insert:

CREATE OR REPLACE VIEW products AS SELECT _products.* FROM _products;

INSERT INTO products (product_id) VALUES (1);

Suggested fix:
It should be possible to insert because only one table is involved here.

Thank you for a problem report. Verified with 5.0.66a and 5.1.26 on Windows. Indeed, manual (http://dev.mysql.com/doc/refman/5.0/en/create-view.html) does not say that this view is NOT insertable. So, this is either a bug or a documentation request at least.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.