#At file:///home/dlenev/src/bzr/mysql-6.1-bg39932/ based on revid:dlenev@stripped
2714 Dmitry Lenev 2009-04-20
Fix for bug #39932 "create table fails if column for FK is in different
case than in corr index".
In both --foreign-key-all-engines=0 and 1 modes server was unable to
find existing or explicitly created supporting index for foreign key
if corresponding statement clause used field names in case different
than one used in key specification and created yet another supporting
index. In cases when name of constraint (and thus name of generated
index) was the same as name of existing/explicitly created index this
led to duplicate key name error.
Also in --foreign-key-all-engines=1 mode in similar situation server
was unable to find primary/unique key in the parent table.
The problem was that unlike all other code Key_part_spec::operator==()
compared field names in case sensitive fashion. As result routines
responsible for getting rid of redundant generated supporting indexes
for foreign key and for finding unique index in parent table were
not working properly for versions of field names using different
cases.
This fix simply makes field name comparison in this operator case-
insensitive, like it is in the rest of the server.
QQ: Does it makes sense to fix this problem in some earlier version
of server ?
@ mysql-test/r/foreign_key_all_engines.result
Added test case for bug #39932 "create table fails if column for FK
is in different case than in corr index".
@ mysql-test/t/foreign_key_all_engines.test
Added test case for bug #39932 "create table fails if column for FK
is in different case than in corr index".
@ sql/sql_class.cc
Make field name comparison case-insensitive like it is
in the rest of server.
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/sql_class.cc
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2009-03-27 19:19:55 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2009-04-20 17:55:23 +0000
@@ -1742,6 +1742,27 @@ unique_constraint_name
j
drop table t1;
#
+# Test for bug #39932 "create table fails if column for FK is in different
+# case than in corr index".
+#
+drop tables if exists t1, t2;
+create table t1 (pk int primary key);
+# Even although the below statement uses uppercased field names in
+# foreign key definition it still should be able to find explicitly
+# created supporting index and primary key on parent column. So it
+# should succeed and should not create any additional supporting
+# indexes.
+create table t2 (fk int, key x (fk),
+constraint x foreign key (FK) references t1 (PK));
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `fk` int(11) DEFAULT NULL,
+ KEY `x` (`fk`),
+ CONSTRAINT `x` FOREIGN KEY (`FK`) REFERENCES `t1` (`PK`)
+) ENGINE=<engine_type> DEFAULT CHARSET=latin1
+drop tables t1, t2;
+#
# Test for bug #41687 "Foreign keys: crash if varbinary or varchar or
# datetime or decimal".
#
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2009-03-27 19:19:55 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2009-04-20 17:55:23 +0000
@@ -1602,6 +1602,26 @@ drop table t1;
--echo #
+--echo # Test for bug #39932 "create table fails if column for FK is in different
+--echo # case than in corr index".
+--echo #
+--disable_warnings
+drop tables if exists t1, t2;
+--enable_warnings
+create table t1 (pk int primary key);
+--echo # Even although the below statement uses uppercased field names in
+--echo # foreign key definition it still should be able to find explicitly
+--echo # created supporting index and primary key on parent column. So it
+--echo # should succeed and should not create any additional supporting
+--echo # indexes.
+create table t2 (fk int, key x (fk),
+ constraint x foreign key (FK) references t1 (PK));
+--replace_result $engine_type <engine_type>
+show create table t2;
+drop tables t1, t2;
+
+
+--echo #
--echo # Test for bug #41687 "Foreign keys: crash if varbinary or varchar or
--echo # datetime or decimal".
--echo #
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2009-02-16 13:48:15 +0000
+++ b/sql/sql_class.cc 2009-04-20 17:55:23 +0000
@@ -112,7 +112,8 @@ bool Key_part_spec::operator==(const Key
{
return length == other.length &&
field_name.length == other.field_name.length &&
- !strcmp(field_name.str, other.field_name.str);
+ !my_strcasecmp(system_charset_info, field_name.str,
+ other.field_name.str);
}
/**
Attachment: [text/bzr-bundle] bzr/dlenev@mysql.com-20090420175523-2w8z923fvta442hs.bundle
