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:
None 
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
Description:
Multi table VIEWs with CHECK OPTION and '<' or '>' in the WHERE clause fails to check the conditions when a row is UPDATEd.

How to repeat:
See attached SQL script.
[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.