Bug #78162 Inconsistent processing order for altering commands in the same ALTER TABLE sql

• Submitted: 21 Aug 2015 9:49
• Modified: 3 Sep 2015 14:05
• Reporter: Su Dylan
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.6.22
• OS: Any
• CPU Architecture: Any

[21 Aug 2015 9:49] Su Dylan

Description:

Result:
===========
mysql> alter table t1 drop index idx, add index idx(c1);
ERROR 1091 (42000): Can't DROP 'idx'; check that column/key exists
mysql>
mysql> drop table if exists t1; create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 add index idx(c1), drop index idx;
ERROR 1091 (42000): Can't DROP 'idx'; check that column/key exists
mysql>
mysql> drop table if exists t1; create table t1(c1 int, index idx(c1));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 drop index idx, add index idx(c1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Problems:
=============
1. alter table t1 add index idx(c1), drop index idx;
2. alter table t1 drop index idx, add index idx(c1);

For the above two sqls, #1 fails while #2 succeeds.
Why is that?
In #2, the "add index" can see the result of "drop index".
But in #1, "drop index" can't see the index added in "add index".

How to repeat:
drop table if exists t1; create table t1(c1 int);
alter table t1 add index idx(c1), drop index idx;

drop table if exists t1; create table t1(c1 int, index idx(c1));
alter table t1 drop index idx, add index idx(c1);

Suggested fix:
For the alter commands in the same SQL, there should be a consistent processing order.

[26 Aug 2015 6:44] Su Dylan

Hi,

Is there any progress on analysis of this issue?  Thanks.

[3 Sep 2015 14:05] MySQL Verification Team

Thank you for your bug report. I have managed to repeat it fully with your test case provided !!!!!

This is a verified bug, but one that already exists, is verified and has passed initial stage of diagnosis.

Duplicate of the bug # 61768.