MySQL Bugs: #20977: confusing results after setting 'updatable_views_with

Bug #20977	confusing results after setting 'updatable_views_with_limit' to 'NO'
Submitted:	11 Jul 2006 22:07	Modified:	10 Aug 2006 16:01
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.1.12	OS:	Linux (Fedora core 5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
This may just be a documention bug.  http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
States: "updatable_views_with_limit" 
This variable controls whether updates can be made using a view that does 'NOT' contain a primary key in the underlying table, if the update contains a LIMIT clause. (Such updates often are generated by GUI tools.) An update is an UPDATE or DELETE statement. Primary key here means a PRIMARY KEY, or a UNIQUE index in which no column can contain NULL.

The code below creates a table with a primary key (It is a composite key, but the manual makes no distinction there).  This in and of itself would seem to indicate that this variable will have no effect on views created against this underlying table, however as the below code demonstrates, it does.  If set to 'NO', then error 1288 is thrown.  If set to default, then the statement is exectuted with a warning but no error.

It appears from the output that the fact that the key is composite, and both columns are NOT included in the view is the root of the problem because when the variable is set to default, and the statement executes this warning is thrown:
"Note 1355 View being updated does not have complete key of underlying table in it"

Should the manual state something more like this?
"This variable controls whether updates can be made using a view that does 'NOT' reference all columns of a primary key that 'IS' defined in the underlying table."

How to repeat:
DROP DATABASE IF EXISTS foo;
CREATE DATABASE foo;
USE foo;
CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b));
INSERT INTO t1 VALUES (10,2,-1), (20,3,-2),
                      (30,4,-3), (40,5,-4);

CREATE VIEW v1 (x,y) AS SELECT a, c FROM t1;

SET UPDATABLE_VIEWS_WITH_LIMIT=NO;
-- error 1288
UPDATE v1 SET x=x+1 LIMIT 1;

SET UPDATABLE_VIEWS_WITH_LIMIT=DEFAULT;
UPDATE v1 SET x=x+1 LIMIT 1;

DROP VIEW v1;
DROP TABLE t1;
DROP DATABASE foo;

It is a poor documentation piece indeed. 
Here (http://dev.mysql.com/tech-resources/articles/mysql-views.pdf) is a great white paper by Trudy Pelzer about views in general. She talks about 'updatable_views_with_limit' in great detail and explains all possible cases with examples starting at page 20.

Trudy says:

I suggest the wording be changed to something
like:
"This variable controls whether updates to a view can
be made when the view does 'NOT' contain all columns
of the primary key defined in the underlying table, if
the update statement contains a LIMIT clause."

Note, I say "contains" and not "references" as Eric
suggests. This is because a view can refer to an
underlying column (e.g. in a WHERE clause) without
that column actually being part of the view. For the
purposes of this variable, the column(s) must actually
be part of the view.

I will update the documentation with Trudy's suggested
wording.