MySQL Bugs: #41720: Foreign keys: wrong error message for geometry reference

Bug #41720	Foreign keys: wrong error message for geometry reference
Submitted:	23 Dec 2008 21:01	Modified:	3 Feb 2009 6:43
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 try to create a table with a foreign-key reference
to a geometry column. I get an error. Okay, it should fail.
But the error message is 1174 "Foreign key error: Type of parent column ...
is not same as type of child column ...", which is wrong.
The data type is the same. A more truthful error message 
would be 1765 "Foreign key error: Constraint ... data
type of ... column is illegal for foreign key".

How to repeat:
mysql> create table t1 (s1 geometry not null);
Query OK, 0 rows affected (0.07 sec)

mysql> create table t2 (s1 geometry references t1 (s1));
ERROR 1774 (42000): Foreign key error: Constraint 'fk_t2_i3g87': Type of parent column 's1' is not same as type of child column 's1'

Thank you for the bug report. Verified as described:

mysql> create table t1 (s1 geometry not null);
Query OK, 0 rows affected (0.38 sec)

mysql> create table t2 (s1 geometry references t1 (s1));
ERROR 1774 (42000): Foreign key error: Constraint 'fk_t2_igtk2': Type of parent column 's1' is not same as type of child column 's1'
mysql>

Investigation shows that columns of various BLOB types are also affected by this bug.

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

2703 Dmitry Lenev	2009-01-30
      Fix for bug #41720 "Foreign keys: wrong error message for
      geometry reference" and similar problem for BLOB columns.
      
      In --foreign-key-all-engines mode attempt to create foreign
      key on column of one of GEOMETRY or BLOB types led to wrong
      error message about different types of child and parent
      columns (even altough these columns had the same type).
      
      The problem occured due to discrepancy between char_length
      attribute of Create_field describing GEOMETRY/BLOB columns
      in the child table, which come from parser, and describing
      columns in parent table, which are generated from Field
      objects. The former have char_length equal to 0 in the most
      cases while the latter has char_length set to the maximum
      size of data which can be stored in column of this type.
      
      Which fix tries to avoid intrusive changes and instead of
      trying to unify handling of Create_field for BLOB/GEOMETRY 
      columns changes Create_field::is_fk_compatible() to ignore 
      length attribute for BLOB/GEOMETRY columns. It is safe to
      do so as length for such columns is fully defined by their
      type code anyway.
      
      Note that this fix only improves situation with error message.
      It is still impossible to create foreign key containing BLOB/
      GEOMETRY columns as it is impossible to create PRIMARY/UNIQUE
      KEY with such columns.

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

2704 Dmitry Lenev	2009-02-03
      Fix for bug #41720 "Foreign keys: wrong error message for
      geometry reference" and similar problem for BLOB columns.
      
      In --foreign-key-all-engines mode attempt to create foreign
      key on column of one of GEOMETRY or BLOB types led to wrong
      error message about different types of child and parent
      columns (even altough these columns had the same type).
      
      The problem occured due to discrepancy between char_length
      attribute of Create_field describing GEOMETRY/BLOB columns
      in the child table, which come from parser, and describing
      columns in parent table, which are generated from Field
      objects. The former have char_length equal to 0 in the most
      cases while the latter has char_length set to the maximum
      size of data which can be stored in column of this type.
      
      This fix tries to avoid intrusive changes and instead of
      trying to unify handling of Create_field for BLOB/GEOMETRY
      columns changes Create_field::is_fk_compatible() to ignore
      length attribute for BLOB/GEOMETRY columns. It is safe to
      do so as length for such columns is fully defined by their
      type code anyway.
      
      Note that this fix only improves situation with error message.
      It is still impossible to create foreign key containing BLOB/
      GEOMETRY columns as it is impossible to create PRIMARY/UNIQUE
      KEY with such columns.

Hello!

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

Note that after this fix the following error message is emitted in the above scenario: "Foreign key error: Constraint '...': Parent columns don't correspond
to a PRIMARY KEY or an UNIQUE constraint". This error message is equally truthful as one proposed in bug-report and explains the reason because of which foreign keys involving BLOB/GEOMETRY columns are not supported. Please feel free to reopen this bug-report if you think that this message is not acceptable!