MySQL Bugs: #17098: Drop column should drop any indexes of which that column is a part

Bug #17098	Drop column should drop any indexes of which that column is a part
Submitted:	3 Feb 2006 12:55	Modified:	28 Feb 2007 22:25
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5	OS:	Any (*)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
At the moment drop column drops that column from multi-part (unique) indexes. This alters the constraints of the table in a narrowing way.
It should simply drop such indexes instead.

How to repeat:
CREATE TABLE xbt_files_users
(
  hid int NOT NULL,
  uid int NOT NULL,
  UNIQUE KEY (hid, uid),
);
ALTER TABLE xbt_files_users DROP hid;

create table bug17098 ( i int not null, j int not null, unique key (i,j));
insert into bug17098 values (1,1);
insert into bug17098 values (2,1);
alter table bug17098 drop column j;

ERROR 1062 (23000): Duplicate entry '1' for key 1

I tried to repeat the statements, but didn't get an error. The table is redefined with a unique key over 'i'. This matches the original description. The error from the verification statements happens if I drop 'i' instead of 'j' though.

Anyway, I tried also with ENGINE=InnoDB and found the same problem. The same applies for ENGINE=Memory. So I dare to claim it is an storage-engine independent behaviour. If it is a bug or a feature, I cannot decide.

The manual says:

If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. (http://dev.mysql.com/doc/refman/5.0/en/alter-table.html)

Indexes of which parts are dropped are unlikely to make sense so I think those indexes should be dropped completely instead.

Just going through some older bugs... noticed this one.  As far as I can tell, the server functions exactly as described in the documentation:

mysql> create table bug17098 ( i int not null, j int not null, unique key (i,j));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bug17098 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug17098 values (2,1);
Query OK, 1 row affected (0.00 sec)

mysql> alter table bug17098 drop column j;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table bug17098\G
*************************** 1. row ***************************
       Table: bug17098
Create Table: CREATE TABLE `bug17098` (
  `i` int(11) NOT NULL,
  UNIQUE KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table bug17098 add column j int not null;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table bug17098\G
*************************** 1. row ***************************
       Table: bug17098
Create Table: CREATE TABLE `bug17098` (
  `i` int(11) NOT NULL,
  `j` int(11) NOT NULL,
  UNIQUE KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table bug17098 drop column i;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table bug17098\G
*************************** 1. row ***************************
       Table: bug17098
Create Table: CREATE TABLE `bug17098` (
  `j` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

As you can see, the index is dropped when no columns in the index are in the table, and is reduced by the column being dropped as long as one or more columns in the index is still in the table.  This is the behaviour described in the documentation.  Recommend setting to "Not a Bug" status.

> This is the behaviour described in the documentation.  Recommend setting to "Not a Bug" status.

Why? I didn't say the code doesn't match the documentation. I said the design (and code and documentation) are wrong.

IMHO this is just plain wrong now, in 5.5.29:

mysql> create table bug17098 ( i int not null, j int not null, unique key (i,j))
;
Query OK, 0 rows affected (4.93 sec)

mysql> insert into bug17098 values (1,1);
Query OK, 1 row affected (0.16 sec)

mysql> insert into bug17098 values (2,1);
Query OK, 1 row affected (0.08 sec)

mysql> alter table bug17098 drop column j;
Query OK, 2 rows affected (1.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table bug17098\G
*************************** 1. row ***************************
       Table: bug17098
Create Table: CREATE TABLE `bug17098` (
  `i` int(11) NOT NULL,
  UNIQUE KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from bug17098;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.13 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.03 sec)

So, no errors, column is dropped and we have non-unique data with unique index in place.

My original test case was wrong somehow, to get the duplicate key error column 'i' needs to be dropped, not 'j', and this still works the same way in 5.5.29 as before ...