Bug #28716 CHECK OPTION expression is evaluated over expired record buffers
Submitted: 28 May 2007 7:10 Modified: 19 Jun 2007 1:08
Reporter: Gleb Shchepa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S2 (Serious)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any

[28 May 2007 7:10] Gleb Shchepa
Description:
CHECK OPTION expression has to be executed twice: once when SELECTing data for update, and once over updated data.
However, when a VIEW is updatable via temporary tables, at the time of second calculation of CHECK OPTION expression record buffers of some joined tables may be expired, and expression may be calculated with wrong result over those buffers.

How to repeat:
mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.42-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a INT);
Query OK, 0 rows affected (0.08 sec)

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

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (b) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE VIEW v AS SELECT t2.b,t2.c FROM t1, t2 WHERE t1.a=t2.b AND t2.b <
3 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE v SET c=1 WHERE b=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM v;
+------+------+
| b    | c    |
+------+------+
|    1 |    1 | 
+------+------+
1 row in set (0.01 sec)

mysql> INSERT INTO t1 (a) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (b) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE v SET c=2 WHERE b=1;
ERROR 1369 (HY000): CHECK OPTION failed 'test.v'

Same for 5.1-bk.
[28 May 2007 21: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/27516

ChangeSet@1.2501, 2007-05-29 02:28:54+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #28716.
  CHECK OPTION expression was evaluated over expired record
  buffers (with arbitrary data in the fields).
  
  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.
[30 May 2007 7:05] 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/27638

ChangeSet@1.2501, 2007-05-30 12:21:39+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #28716.
  The result of the CHECK OPTION condition evaluation over an
  updated record and records of merged tables was arbitrary and
  dependant on the order of records in the merged tables during
  the execution of SELECT statement.
  
  The CHECK OPTION expression was evaluated over expired record
  buffers (with arbitrary data in the fields).
  
  Rowids of tables used in the CHECK OPTION expression were
  added to temporary table rows. The multi_update::do_updates()
  method was modified to restore necessary record buffers
  before evaluation of the CHECK OPTION condition.
[30 May 2007 11:22] 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/27672

ChangeSet@1.2507, 2007-05-30 16:39:25+05:00, gshchepa@gleb.loc +1 -0
  sql_update.cc:
    Fixed bug #28716: additional patch to eliminate compilation
    error under Windows.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:22] Bugs System
Pushed into 5.0.44
[19 Jun 2007 1:08] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.