| Bug #27827 | Multi-table VIEW treats CHECK OPTION inconsistently on UPDATE | ||
|---|---|---|---|
| Submitted: | 14 Apr 2007 19:14 | Modified: | 18 Jun 2007 19:51 |
| Reporter: | Erik Sundkvist | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0.38, 5.1, 6.0 | OS: | Linux |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[14 Apr 2007 19:14]
Erik Sundkvist
[14 Apr 2007 19:15]
Erik Sundkvist
Script demonstrating the described problem
Attachment: mysql_view_bug.sql (application/octet-stream, text), 1.28 KiB.
[15 Apr 2007 7:23]
Erik Sundkvist
If the contents of table t1 in the attached script is modified to include three rows instead of two (the third row can contain any value), then the last UPDATE in the script actually checks the condition and prevents the UPDATE.
[16 Apr 2007 9:02]
Sveta Smirnova
Thank you for the report. Verified as described.
[16 Apr 2007 9:02]
Sveta Smirnova
Can be related to Bug #25931
[18 May 2007 0:45]
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/26935 ChangeSet@1.2491, 2007-05-18 05:54:56+05:00, gshchepa@gleb.loc +8 -0 Fixed bug #27827. ON expressions was not included into CHECK OPTION checks for UPDATE and INSERT statements. CHECK OPTION was checked too late for non-driving table UPDATE calls. The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. The save_on_expr variable was used for saving of ON expressions between EXECUTE calls. The view_check_option() check has been moved from multi_update::do_updates (too late to check here) to multi_update::send_data().
[23 May 2007 14:45]
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/27219 ChangeSet@1.2491, 2007-05-23 19:57:37+05:00, gshchepa@gleb.loc +9 -0 Fixed bug #27827. Actually there are 2 different bugs and bugfixes: 1. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. 2. CHECK OPTION expression was checked over expired record buffers (with arbitrary data in the fields). The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. The save_on_expr variable was used for saving of ON expressions for next executions of the query. Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation.
[24 May 2007 19:59]
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/27309 ChangeSet@1.2491, 2007-05-25 01:12:12+05:00, gshchepa@gleb.loc +8 -0 Fixed bug #27827. Actually there are 2 different bugs and bugfixes: 1. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. 2. CHECK OPTION expression was checked over expired record buffers (with arbitrary data in the fields). The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. Also it was optimized to prevent unnecessary rebuilding of check_option expression on every EXECUTE statement. Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation.
[25 May 2007 18:07]
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/27356 ChangeSet@1.2501, 2007-05-25 23:19:08+05:00, gshchepa@gleb.loc +6 -0 Fixed bug #27827. Actually there are 2 different bugs and bugfixes: 1. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. 2. CHECK OPTION expression was checked over expired record buffers (with arbitrary data in the fields). The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. Also it was optimized to prevent unnecessary rebuilding of check_option expression on every EXECUTE statement. Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation.
[27 May 2007 22:16]
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/27423 ChangeSet@1.2501, 2007-05-28 03:31:51+05:00, gshchepa@gleb.loc +6 -0 Fixed bug #27827. Actually there are 2 different bugs and bugfixes: 1. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. 2. CHECK OPTION expression was checked over expired record buffers (with arbitrary data in the fields). The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. Also it was optimized to prevent unnecessary rebuilding of check_option expression on every EXECUTE statement. Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation.
[28 May 2007 20:12]
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/27510 ChangeSet@1.2501, 2007-05-29 01:05:46+05:00, gshchepa@gleb.loc +4 -0 Fixed bug #27827. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. Also it was optimized to prevent unnecessary rebuilding of check_option expression on every EXECUTE statement.
[28 May 2007 22:13]
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/27518 ChangeSet@1.2501, 2007-05-29 03:29:01+05:00, gshchepa@gleb.loc +4 -0 Fixed bug #27827. ON expressions was never included into CHECK OPTION checks for UPDATE and INSERT statements. The st_table_list::prep_check_option() function has been improved to include ON expression into check_option. Also it was optimized to prevent unnecessary rebuilding of check_option expression on every EXECUTE statement.
[31 May 2007 19:50]
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/27842 ChangeSet@1.2501, 2007-06-01 01:07:18+05:00, gshchepa@gleb.loc +4 -0 Fixed bug #27827. ON conditions from JOIN expression were ignored at CHECK OPTION check when updating a multi-table view with CHECK OPTION. The st_table_list::prep_check_option() function has been modified to to take into account ON condition at CHECK OPTION check It was also changed to build the check option conditions only once for any update used in PS/SP.
[31 May 2007 20:58]
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/27848 ChangeSet@1.2501, 2007-06-01 02:15:40+05:00, gshchepa@gleb.loc +4 -0 Fixed bug #27827. ON conditions from JOIN expression were ignored at CHECK OPTION check when updating a multi-table view with CHECK OPTION. The st_table_list::prep_check_option function has been modified to to take into account ON conditions at CHECK OPTION check It was also changed to build the check option condition only once for any update used in PS/SP.
[4 Jun 2007 21:20]
Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:22]
Bugs System
Pushed into 5.0.44
[18 Jun 2007 19:51]
Paul DuBois
Noted in 5.0.44, 5.1.20 changelogs.
