Bug #10662 A column length can be reduced than the index length of the index on it.
Submitted: 16 May 2005 11:10 Modified: 18 May 2005 17:14
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.5 Beta OS:Windows (Windows 2000 Server)
Assigned to: Paul DuBois CPU Architecture:Any

[16 May 2005 11:10] Disha
Description:
The length of a column having index on it can be reduced less than the length specified for the index using the alter table.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to // and use the TEST database.
3. Create a table and create an index on it.
   create table t1(f1 char(100))//
   create index index1 on t1(f1(50))//

4. Now alter the table and reduce the length of f1 column less than the index length as follows:
   alter table t1 modify f1 char(30)//

Expected Results: 
1. Error should be displayed and the operation should not be allowed. 
2. In the section "13.2.2 Alter Table" it is mentioned as follows:
"If you use CHANGE or MODIFY to shorten a column for which an index exists on part of the column (for example, if you have an index on the first 10 characters of a VARCHAR column), you cannot make the column shorter than the number of characters that are indexed."
Link: "http://dev.mysql.com/doc/mysql/en/alter-table.html"

Actual Results: 
1. The operation is completed successfully without error.
[16 May 2005 17:19] MySQL Verification Team
Please see below how the index is created (sub_part) with alter table:

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.6-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create table t1(f1 char(100))//
Query OK, 0 rows affected (0.01 sec)

mysql>    create index index1 on t1(f1(50))//
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1\G
*************************** 1. row ***************************
       Table: t1
  Non_unique: 1
    Key_name: index1
Seq_in_index: 1
 Column_name: f1
   Collation: A
 Cardinality: NULL
    Sub_part: 50
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
1 row in set (0.01 sec)

mysql> alter table t1 modify f1 char(30)//
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1\G
*************************** 1. row ***************************
       Table: t1
  Non_unique: 1
    Key_name: index1
Seq_in_index: 1
 Column_name: f1
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
1 row in set (0.01 sec)

mysql>
[17 May 2005 18:40] Sergei Golubchik
Miguel was right - it's not a bug, MySQL always behaved this way (shortening the key automatically).

It's manual that is confusing (actually it's ambiguous here) and it should be fixed.
[18 May 2005 17:14] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).