MySQL Bugs: #14027: updatable views cannot be updated in certain cases

Bug #14027	updatable views cannot be updated in certain cases
Submitted:	14 Oct 2005 13:45	Modified:	1 Nov 2005 19:26
Reporter:	Eric Uhres	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-nt, 5.0.15-rc BK	OS:	Linux (Linux, Windows XP)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
When i try to update a field in a view defined by an inner join with the clause 'USING' i get the following error message:

ERROR 1143 (42000): SELECT command denied to user 'root'@'localhost' for column 'id' in table 't1'

(i logged in as root with all privileges granted to eliminate this error source)

If the view is defined without the 'USING' clause, the update works.

How to repeat:
CREATE TABLE t1 (id int, a1 int);
CREATE TABLE t2 (id int, a2 int);
INSERT INTO t1 VALUES (1,15),(2,12);
INSERT INTO t2 VALUES (1,8),(2,7);
CREATE VIEW v1 AS SELECT t1.id,a1,a2 FROM t1 INNER JOIN t2 WHERE t1.id=t2.id;
CREATE VIEW v2 AS SELECT t1.id,a1,a2 FROM t1 INNER JOIN t2 USING (id);

UPDATE v1 SET a1=31 WHERE id=1;  -- this should work fine
UPDATE v2 SET a1=32 WHERE id=1;  -- this doesn't work

Thank you for a bug report. Verified as described on 5.0.15-BK (ChangeSet@1.2024.1.20, 2005-10-12 22:44:42-07:00, patg@krsna.patg.net) on Linux too:

mysql> CREATE TABLE t1 (id int, a1 int);
Query OK, 0 rows affected (0,03 sec)

mysql> CREATE TABLE t2 (id int, a2 int);
Query OK, 0 rows affected (0,06 sec)

mysql> INSERT INTO t1 VALUES (1,15),(2,12);
Query OK, 2 rows affected (0,05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1,8),(2,7);
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT t1.id,a1,a2 FROM t1 INNER JOIN t2 WHERE t1.id=t2.id;
Query OK, 0 rows affected (0,07 sec)

mysql> CREATE VIEW v2 AS SELECT t1.id,a1,a2 FROM t1 INNER JOIN t2 USING (id);
Query OK, 0 rows affected (0,00 sec)

mysql> UPDATE v1 SET a1=31 WHERE id=1;
Query OK, 1 row affected (0,02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE v2 SET a1=32 WHERE id=1;
ERROR 1143 (42000): SELECT command denied to user 'root'@'localhost' for column 'id' in table 't1'

Why? Plain SELECT works:

mysql> SELECT t1.id,a1,a2 FROM t1 INNER JOIN t2 USING (id);
+------+------+------+
| id   | a1   | a2   |
+------+------+------+
|    1 |   31 |    8 |
|    2 |   12 |    7 |
+------+------+------+
2 rows in set (0,00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0,01 sec)

The following views fail to be updated for the same reason:

CREATE VIEW v3 AS
  SELECT t1.id,a1,a2 FROM t1 JOIN t2 ON t1.id=t2.id;
CREATE VIEW v4 AS
  SELECT t1.id,a1,a2 FROM t1 NATURAL JOIN t2;

Generally the problem manifestates itself when there is a nested
join in the FROM clause of the 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/internals/31688

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The bug has been fixed in the latest BK tree of 5.0.16 by WL#2787.

Noted in 5.0.16 changelog.