MySQL Bugs: #16813: WITH CHECK OPTION doesn't work with UPDATE command

Bug #16813	WITH CHECK OPTION doesn't work with UPDATE command
Submitted:	26 Jan 2006 18:09	Modified:	26 Oct 2006 3:26
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18, 5.0bk, 5.1bk	OS:	Windows (Windows, linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
If a view is used to emulate a check constraint on a table, it's (under special circumstances?) possible to update fields to invalid values.

How to repeat:
mysql> CREATE TABLE checktest (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   val INT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v_checktest AS
    ->   SELECT id, val FROM checktest
    ->   WHERE val >= 1 AND val <= 5
    ->   WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v_checktest (val) VALUES (2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO v_checktest (val) VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v_checktest (val) VALUES (6);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v_checktest'

mysql> SELECT * FROM v_checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   4 |
+----+-----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   4 |
+----+-----+
2 rows in set (0.00 sec)

... up to here, it works fine ...

mysql> UPDATE v_checktest SET val=6 WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM v_checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
+----+-----+
1 row in set (0.00 sec)

mysql> SELECT * FROM checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   6 |
+----+-----+
2 rows in set (0.00 sec)

Now here's the invalid value of 6.

Suggested fix:
It should be impossible to change the value in a view to an invalid value, if the WITH CHECK OPTION is set.

verified, UPDATEs are not checked although the documentation says so:

  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.

  ( http://dev.mysql.com/doc/refman/5.0/en/create-view.html )

Forgotten?

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/9446

The patch "bk commit into 5.0 tree (holyfoot:1.2226) BUG#16813"
looks ok to push.

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/12808

ChangeSet@1.2273, 2006-09-29 12:16:07+05:00, holyfoot@mysql.com +3 -0
  bug #16813 (WITH CHECK OPTION fails with UPDATE)
  
  We use the condition from CHECK OPTION twice handling UPDATE command.
  First we construnct 'update_cond' AND 'option_cond'
  condition to select records to be updated, then we check the
  'option_cond' for the updated row.
  The problem is that first 'AND' condition is optimized during the 'select'
  which can break 'option_cond' structure, so it will be unusable for
  the sectond use - to check the updated row.
  Possible soultion is either use copy of the condition in the first
  use or to make optimization less traumatic for the operands.
  I picked the first one.

Pushed in 5.0.27/5.1.13-beta

Noted in 5.0.30, 5.1.13 changelogs.

The WITH CHECK OPTION for a view failed to prevent storing invalid
column values for UPDATE statements.