#At file:///export/home/tmp/z/mysql-6.0-runtime-fix31031/ based on revid:alik@stripped
2766 Magne Mahre 2008-12-12
Bug #31031 ALTER TABLE regression in 5.0
An ALTER TABLE statement which added a column and added
a non-partial index on it failed with:
"ERROR 1089 (HY000): Incorrect sub part key; the used
key part isn't a string, the used length is longer than
the key part, or the storage engine doesn't support unique
sub keys"
In a check introduced to fix an earlier bug (no. 26794),
to allow for indices on spatial type columns, the
test expression was flawed (a logical OR was used instead
of a logical AND), which led to this regression.
The code in question does a sanity check on the key, and
the flawed code mistakenly classified any index created
in the way specified above as a partial index. Since
many data types does not allow partial indices, the
statement would fail.
modified:
mysql-test/r/alter_table.result
mysql-test/t/alter_table.test
sql/sql_table.cc
=== modified file 'mysql-test/r/alter_table.result'
--- a/mysql-test/r/alter_table.result 2008-11-06 18:39:27 +0000
+++ b/mysql-test/r/alter_table.result 2008-12-12 09:46:03 +0000
@@ -1253,3 +1253,10 @@ a b
5 a
DROP TABLE t1;
End of 5.1 tests
+CREATE TABLE t1(c CHAR(10),
+i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES('a',2),('b',4),('c',6);
+ALTER TABLE t1
+DROP i,
+ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
+AUTO_INCREMENT = 1;
=== modified file 'mysql-test/t/alter_table.test'
--- a/mysql-test/t/alter_table.test 2008-11-06 18:39:27 +0000
+++ b/mysql-test/t/alter_table.test 2008-12-12 09:46:03 +0000
@@ -988,3 +988,17 @@ SELECT * FROM t1;
DROP TABLE t1;
--echo End of 5.1 tests
+
+#
+# Bug #31031 ALTER TABLE regression in 5.0
+#
+# The ALTER TABLE operation failed with
+# ERROR 1089 (HY000): Incorrect sub part key; ...
+#
+CREATE TABLE t1(c CHAR(10),
+ i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
+INSERT INTO t1 VALUES('a',2),('b',4),('c',6);
+ALTER TABLE t1
+ DROP i,
+ ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
+ AUTO_INCREMENT = 1;
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2008-12-04 16:50:07 +0000
+++ b/sql/sql_table.cc 2008-12-12 09:46:03 +0000
@@ -2972,13 +2972,21 @@ mysql_prepare_create_table(THD *thd, HA_
}
}
else if (!f_is_geom(sql_field->pack_flag) &&
- (column->length > length ||
- !Field::type_can_have_key_part (sql_field->sql_type) ||
- ((f_is_packed(sql_field->pack_flag) ||
- ((file->ha_table_flags() & HA_NO_PREFIX_CHAR_KEYS) &&
- (key_info->flags & HA_NOSAME))) &&
- column->length != length)))
- {
+ ((column->length > length &&
+ !Field::type_can_have_key_part (sql_field->sql_type)) ||
+ ((f_is_packed(sql_field->pack_flag) ||
+ ((file->ha_table_flags() & HA_NO_PREFIX_CHAR_KEYS) &&
+ (key_info->flags & HA_NOSAME))) &&
+ column->length != length)))
+ {
+ /* Catch invalid uses of partial keys.
+ A key is identified as 'partial' if column->length != length.
+ A partial key is invalid if they data type does
+ not allow it, or the field is packed (as in MyISAM),
+ or the storage engine doesn't allow prefixed search and
+ the key is primary key.
+ */
+
my_message(ER_WRONG_SUB_KEY, ER(ER_WRONG_SUB_KEY), MYF(0));
DBUG_RETURN(TRUE);
}
