bzr commit into mysql-5.4.5-next-mr-bugfixing branch (magne.mahre:2895) Bug#31031

[Magne Mahre]

#At file:///data/z/mysql-next-mr-runtime/ based on revid:magne.mahre@stripped

 2895 Magne Mahre	2009-10-09
      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-12-09 13:31:22 +0000
+++ b/mysql-test/r/alter_table.result	2009-10-09 13:04:58 +0000
@@ -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;

=== modified file 'mysql-test/t/alter_table.test'
--- a/mysql-test/t/alter_table.test	2008-12-09 13:31:22 +0000
+++ b/mysql-test/t/alter_table.test	2009-10-09 13:04:58 +0000
@@ -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;

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2009-10-09 09:00:18 +0000
+++ b/sql/sql_table.cc	2009-10-09 13:04:58 +0000
@@ -3233,13 +3233,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);
 	}


Attachment: [text/bzr-bundle] bzr/[e-mail]