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.

3 files changed, 38 insertions, 7 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 5a115e9ea99..28bf6e9edc3 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1269,3 +1269,11 @@ 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;
+DROP TABLE t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index ae48d5a8736..0c20fe523a4 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1001,3 +1001,18 @@ 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;
+DROP TABLE t1;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index fd0fad65bf4..a93bfb1a562 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3233,13 +3233,21 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	  }
 	}
 	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);
 	}