MySQL Bugs: #41695: Foreign keys: non-distinct value change

Bug #41695	Foreign keys: non-distinct value change
Submitted:	23 Dec 2008 0:29	Modified:	27 Jan 2009 15:34
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	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 insert 'A' in each table.
I update the primary-key table from 'A' to 'a'.
If the foreign key is NO ACTION, I see an error.
If the foreign key is CASCADE, I see an unnecessary foreign-key change.
Changing to a non-distinct value should not cause such results.
I also see a possibly-unnecessary change if another column changes.

How to repeat:
drop table t1;
drop table t2;
create table t1 (s1 char(1) primary key) engine=falcon;
create table t2 (s1 char(1) references t1(s1)) engine=falcon;
insert into t1 values ('A');
insert into t2 values ('A');
update t1 set s1 = 'a'; /* Error '23000' */

OR

drop table t1;
drop table t2;
create table t1 (s1 char(40) character set ucs2, s2 int, primary key (s1,s2)) engine=falcon;
create table t2 (s1 char(40) character set ucs2, s2 int,
                 foreign key (s1,s2) references t1(s1,s2) on update cascade
                 ) engine=falcon;
insert into t1 values ('A',1);
insert into t2 values ('a',1);
update t1 set s2 = 2 where s1 = 'A';
select * from t2;
/* t2.s1 has become 'A' */

/* I'm not certain that the second case is a true bug, but it seems pointless. */

Thank you for the bug report. Verified as described:

mysql> insert into t2 values ('A');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set s1 = 'a'; /* Error '23000' */
ERROR 1781 (23000): Foreign key error: constraint 'fk_t2_vevjg': cannot change because foreign key refers to value 'A'
mysql>

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

2694 Dmitry Lenev	2009-01-21
      Fix for bug #41695 "Foreign keys: non-distinct value change".
      
      In --foreign-key-all-engines mode non-distinct value change
      of parent key with matching child rows led to unwarranted
      ER_FK_CHILD_VALUE_EXISTS error for foreign keys with ON UPDATE
      NO ACTION/RESTRICT.
      
      The problem was that for ON UPDATE NO ACTION/RESTRICT foreign
      keys during update on a parent table we were performing check
      for the presence of child rows and emitting the error if any
      matching rows existed even if new value of parent key was not
      'significantly' different from the old value.
      
      This fix simply adds condition that such check should be
      performed only if new value of parent key is different enough
      from the old one (and therefore it cannot be matching value for
      the child rows which matched its old value).

Hello Peter!

I don't think that behavior that you observe in the second case is a bug.
AFAIU the standard does not say that when performing CASCADE we should copy
to FK only values of those PK columns which got distinct values. On contrary
I think it says that if each column of FK should be set to new value of corresponding column of PK. Therefore I've proposed patch which fixes only
the first issue.

Please feel free to re-open this bug and provide your arguments if you disagree!

Hi Dmitri,

I do not think that the second example is a bug.
Changing the referencing column certainly does
not violate the standard.

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

2699 Dmitry Lenev	2009-01-27
      Fix for bug #41695 "Foreign keys: non-distinct value change".
      
      In --foreign-key-all-engines mode non-distinct value change
      of parent key with matching child rows led to unwarranted
      ER_FK_CHILD_VALUE_EXISTS error for foreign keys with ON UPDATE
      NO ACTION/RESTRICT.
      
      The problem was that for ON UPDATE NO ACTION/RESTRICT foreign
      keys during update on a parent table we were emitting the error 
      if any matching child rows existed even if new value of parent 
      key being changed was not 'significantly' different from the
      old value.
      
      This fix simply adds extra check ensuring that error is reported
      only if new value of parent key is different enough from the old 
      one (and therefore it cannot be matching value for the child rows 
      which matched its old value).

Fix for this bug was pushed into mysql-6.1-fk tree. Since this problem was reported against tree which is not publicly available yet I am simply closing this bug-report.