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: | |
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 |
[29 Jan 2007 22:06]
Tobias Asplund
[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.