Bug #5991 Views with check option: confusing diagnostics

• Submitted: 8 Oct 2004 21:08
• Modified: 21 Oct 2004 14:08
• Reporter: Peter Gulutzan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[8 Oct 2004 21:08] Peter Gulutzan

Description:
Check option violation diagnostics (sqlstate, message) are bad. 
 
In the standard and in DB2 (though not in Oracle), the SQLSTATE 
for a violation of the check option should be 44000, not HY000. 
 
The message should be "View CHECK OPTION violation" and 
should include the database.view name because the violation 
might occur in an underlying view. That is: if view2 is based on 
view1, and you're updating view2, and the violation is of view1's 
check option, then either (a) you report an error in view2, which 
is misleading, or (b) you report an error in view1, which is 
metadata that the user might not have a right to know. So, 
don't say anything. 
 
 

How to repeat:
mysql> create table tk (s1 tinyint); 
Query OK, 0 rows affected (0.44 sec) 
 
mysql> create view vk as select * from tk where s1 = s1 with check option; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into tk values (null); 
Query OK, 1 row affected (0.00 sec) 
 
mysql> insert into vk values (null); 
ERROR 1369 (HY000): CHECK OPTION failed 'db11.vk'

[8 Oct 2004 22:00] MySQL Verification Team

Verified on latest BK source tree.

[21 Oct 2004 11:44] Peter Gulutzan

Whoops. I should not have said "should include the database.view name". I meant "should 
not include the database.view name". Sorry.

[21 Oct 2004 14:08] Oleksandr Byelkin

After some discussion we decide that it is more helpful to have view name in case of 
multi-update.