MySQL Bugs: #25931: VIEW ignores CHECK OPTION when updated against a JOIN clause

Bug #25931	VIEW ignores CHECK OPTION when updated against a JOIN clause
Submitted:	29 Jan 2007 22:06	Modified:	26 Feb 2007 20:57
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.33	OS:	MacOS (Mac OS X, linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
CHECK OPTION only seems to check the WHERE clause, not the ON clause of a JOIN.
It's possible to update a column in the ON clause so that the row(s) disappear from the VIEW despite the CHECK OPTION.

How to repeat:
mysql> CREATE TABLE t1 ( a INT );
Query OK, 0 rows affected (0.26 sec)

mysql> CREATE TABLE t2 ( b INT );
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> CREATE VIEW v1 AS SELECT t2.b FROM t1 JOIN t2 ON t1.a = t2.b WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v1;
+------+
| b    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> UPDATE v1 SET b = 3 ;     
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM v1;
Empty set (0.00 sec)

According to http://dev.mysql.com/doc/refman/5.0/en/create-view.html it is expected behaviour:

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

ok, let's rewrite the view...

mysql> CREATE VIEW v1 AS SELECT t2.b FROM t1 JOIN t2 WHERE t1.a = t2.b WITH CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM v1;
+------+
| b    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> update v1 set b=3;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The view is now created with the comparison in the WHERE :)

Thank you for the additional information.

Verified as described on Linux using last BK sources. All versions are affected.

test case

Attachment: bug25931.test (application/octet-stream, text), 474 bytes.

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

ChangeSet@1.2402, 2007-02-06 12:34:10-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25931.
  View check option clauses were ignored for updates of multi-table
  views when the updates could not be performed on fly and the rows
  to update had to be put into temporary tables first.

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

ChangeSet@1.2402, 2007-02-06 12:29:45-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25931.
  View check option clauses were ignored for updates of multi-table
  views when the updates could not be performed on fly and the rows
  to update had to be put into temporary tables first.

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

ChangeSet@1.2402, 2007-02-07 12:21:22-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25931.
  View check option clauses were ignored for updates of multi-table
  views when the updates could not be performed on fly and the rows
  to update had to be put into temporary tables first.

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

ChangeSet@1.2405, 2007-02-07 14:41:57-08:00, igor@olga.mysql.com +3 -0
  Fixed bug #25931.
  View check option clauses were ignored for updates of multi-table
  views when the updates could not be performed on fly and the rows
  to update had to be put into temporary tables first.

The fix has been pushed to 5.0.36 and 5.1.16-beta.

Noted in 5.0.36, 5.1.16 changelogs.