MySQL Bugs: #41702: Foreign keys: error with wrong SQLSTATE

Bug #41702	Foreign keys: error with wrong SQLSTATE
Submitted:	23 Dec 2008 0:44	Modified:	11 Jan 2009 11:16
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha-debug	OS:	Linux (SUSE 10.0 / 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I create a primary-key table and a foreign-key table.
I do an insert with whould cause '44000' view check option violation.
I get '23000' foreign-key violation.

How to repeat:
SET @@storage_engine=falcon;
DROP TABLE IF EXISTS t2,t1;
DROP VIEW IF EXISTS v2;
CREATE TABLE t1 (s1 INT PRIMARY KEY);
INSERT INTO t1 VALUES (6);
CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1));
CREATE VIEW v2 AS SELECT * FROM t2 WHERE s1 > 5 WITH CHECK OPTION;
INSERT INTO t2 VALUES (0);        /* causes a '23000' */
DROP TABLE IF EXISTS t2,t1;
DROP VIEW IF EXISTS v2;

Thank you for the bug report. Verified as described:

mysql> INSERT INTO t2 VALUES (0);        /* causes a '23000' */
ERROR 1780 (23000): Foreign key error: constraint 'fk_t2_85n2d': no matching key for value '0', it is not in parent table

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

2694 Dmitry Lenev	2009-01-11
      Test inspired by bug #41702 "Foreign keys: error with wrong SQLSTATE".
      
      Added basic test coverage for handling of views with check option on
      tables with new foreign keys (i.e --foreign-key-all-engines mode).

The above test case contains an error. In it we are inserting into t2 not
through a view but directly, so error emitted in this case is expected one.

After replacing "INSERT INTO t2 VALUES (0)" with statement which performs insertion through the view - "INSERT INTO v2 VALUES (0)" one will start
getting an error which is expected by Peter -- "ERROR HY000: CHECK OPTION
failed 'test.v2'".

The fact that SQLSTATE in this case is still 'HY000' and not '44000' was
discussed in bug#5991 "Views with check option: confusing diagnostics" and
was accepted as normal behavior.

Therefore I am closing this report as "Not a bug".

Also I have added basic test coverage for handling of views with check option
on tables with foreign keys in --foreign-key-all-engines mode to our test suite.