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:Mac OS X (Mac OS X, linux)
Assigned to: Igor Babaev CPU Architecture:Any

[29 Jan 2007 22:06] Tobias Asplund
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)
[30 Jan 2007 7:58] Sveta Smirnova
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.
[31 Jan 2007 20:20] Tobias Asplund
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 :)
[31 Jan 2007 22:19] Sveta Smirnova
Thank you for the additional information.

Verified as described on Linux using last BK sources. All versions are affected.
[31 Jan 2007 22:19] Sveta Smirnova
test case

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

[6 Feb 2007 20:30] 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/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.
[6 Feb 2007 20:56] 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/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.
[7 Feb 2007 20:17] 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/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.
[7 Feb 2007 22:38] 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/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.
[14 Feb 2007 10:32] Igor Babaev
The fix has been pushed to 5.0.36 and 5.1.16-beta.
[26 Feb 2007 20:57] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.