MySQL Bugs: #5995: Views with check option: Confusing warning on INSERT IGNORE

Bug #5995	Views with check option: Confusing warning on INSERT IGNORE
Submitted:	8 Oct 2004 21:25	Modified:	25 Oct 2004 19:20
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Documentation Team	CPU Architecture:	Any

Description:
If I use INSERT IGNORE on a view that has a (violated) 
check option, I get a warning. If I use INSERT without 
IGNORE, I get an error. I should get an error both times. 
 

How to repeat:
mysql> create table ts (s1 int); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create view vs as select * from ts where s1 < 5 with check option; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert ignore into vs values (6); 
Query OK, 0 rows affected, 1 warning (0.01 sec) 
 
mysql> show warnings; 
+-------+------+-------------------------------+ 
| Level | Code | Message                       | 
+-------+------+-------------------------------+ 
| Error | 1369 | CHECK OPTION failed 'db11.vs' | 
+-------+------+-------------------------------+ 
1 row in set (0.00 sec) 
 
mysql> insert into vs values (6); 
ERROR 1369 (HY000): CHECK OPTION failed 'db11.vs'

Verified against latest BK source.

It is expected behaviour. Task of IGNORE clause is suppress all errors message issued 
during INSERT/UPDATE execution and let query finish with all rows which it can update. 
this feature made specially for nontransactionsl engines.

The "expected behaviour" is what the MySQL Reference Manual says: 
 
"If you specify the IGNORE keyword in an INSERT with many rows, any rows that 
duplicate an existing UNIQUE index or PRIMARY KEY value in the table are ignored and 
are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row 
that duplicates an existing key value." ... This has nothing to do with WITH CHECK 
OPTION. Either the manual is wrong, or the server is wrong.

So manual is incomplete. 
UPDATE/INSERT with IGNORE clause also ignore all errors issued by expressions 
(subqueries, for example returning more then 1 row for scalar) and return them as 
warning.

it is lack of documentation of IGNORE option 
(see internal info)

I made 'WITH CHECK OPTION' behaviour same as for other INSERT problem, for single 
value in alwais return eror, and ignore them only for several values: 
 
ChangeSet 
  1.1667 04/10/21 14:32:10 bell@sanja.is.com.ua +3 -0 
  inserting single value with check option failed always get error (part of BUG#5995)

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).