Bug #7778 Views: updates ignore check option

• Submitted: 10 Jan 2005 16:54
• Modified: 1 Jul 2005 5:24
• Reporter: Peter Gulutzan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3-alpha-debug
• OS: Linux (SUSE 9.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[10 Jan 2005 16:54] Peter Gulutzan

Description:
View CHECK OPTION clauses no longer work with UPDATE statements. 
They do work with INSERT statements. 
 
 

How to repeat:
mysql> create table tab2 (s1 char); 
Query OK, 0 rows affected (0.02 sec) 
 
mysql> create view vtab2 as select s1 from tab2 where s1 = 'A' with check option; 
Query OK, 0 rows affected (0.07 sec) 
 
mysql> insert into tab2 values (''); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> update vtab2 set s1 = 'B'; 
Query OK, 0 rows affected (0.00 sec) 
Rows matched: 0  Changed: 0  Warnings: 0

[1 Jul 2005 5:24] Oleksandr Byelkin

It is not bug, check option was not checked, because no records satisfy view where condition. if change script like following:
------------------------------------
create table t1 (s1 char);
create view v1 as select s1 from t1 where s1 = 'A' with check option;
insert into v1 values ('A');
update v1 set s1 = 'B';
------------------------------------
it will fail:
At line 4: query 'update v1 set s1 = 'B'' failed: 1369: CHECK OPTION failed 'test.v1'