diff options
Diffstat (limited to 'mysql-test/main/alter_table.test')
| -rw-r--r-- | mysql-test/main/alter_table.test | 180 |
1 files changed, 180 insertions, 0 deletions
diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index ccd2ee953b1..19f9777d803 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -2274,5 +2274,185 @@ DROP TABLE t3,t1,t2; SET DEFAULT_STORAGE_ENGINE= @save_default_engine; --echo # +--echo # MDEV-7318 RENAME INDEX +--echo # + +--echo # +--echo # 1) Tests for syntax and semantics of ALTER TABLE RENAME +--echo # KEY/INDEX result. +--echo # +--echo # 1.a) Both RENAME KEY and RENAME INDEX variants should be +--echo # allowed and produce expected results. +create table t1 (pk int primary key, i int, j int, key a(i)); +alter table t1 rename key a to b; +show create table t1; +alter table t1 rename index b to c; +show create table t1; + +--echo # 1.b) It should be impossible to rename index that doesn't +--echo # exists, dropped or added within the same ALTER TABLE. +--error ER_KEY_DOES_NOT_EXITS +alter table t1 rename key d to e; +show create table t1; +--error ER_KEY_DOES_NOT_EXITS +alter table t1 drop key c, rename key c to d; +show create table t1; +--error ER_KEY_DOES_NOT_EXITS +alter table t1 add key d(j), rename key d to e; +show create table t1; + +--echo # 1.c) It should be impossible to rename index to a name +--echo # which is already used by another index, or is used +--echo # by index which is added within the same ALTER TABLE. +alter table t1 add key d(j); +--error ER_DUP_KEYNAME +alter table t1 rename key c to d; +show create table t1; +alter table t1 drop key d; +--error ER_DUP_KEYNAME +alter table t1 add key d(j), rename key c to d; +show create table t1; + +--echo # 1.d) It should be possible to rename index to a name +--echo # which belongs to index which is dropped within the +--echo # same ALTER TABLE. +alter table t1 add key d(j); +alter table t1 drop key c, rename key d to c; +show create table t1; + +--echo # 1.e) We disallow renaming from/to PRIMARY as it might +--echo # lead to some other key becoming "primary" internally, +--echo # which will be interpreted as dropping/addition of +--echo # primary key. +--error ER_PARSE_ERROR +alter table t1 rename key primary to d; +show create table t1; +--echo # Even using 'funny' syntax. +--error ER_WRONG_NAME_FOR_INDEX +alter table t1 rename key `primary` to d; +show create table t1; +--error ER_PARSE_ERROR +alter table t1 rename key c to primary; +show create table t1; +--error ER_WRONG_NAME_FOR_INDEX +alter table t1 rename key c to `primary`; +show create table t1; +drop table t1; + + +--echo # +--echo # 2) More complex tests for semantics of ALTER TABLE. +--echo # +--echo # 2.a) Check that standalone RENAME KEY works as expected +--echo # for unique and non-unique indexes. +create table t1 (a int, unique u(a), b int, key k(b)); +alter table t1 rename key u to uu; +show create table t1; +alter table t1 rename key k to kk; +show create table t1; + +--echo # 2.b) Check how that this clause can be mixed with other +--echo # clauses which don't affect key or its columns. +alter table t1 rename key kk to kkk, add column c int; +show create table t1; +alter table t1 rename key uu to uuu, add key c(c); +show create table t1; +alter table t1 rename key kkk to k, drop key uuu; +show create table t1; +alter table t1 rename key k to kk, rename to t2; +show create table t2; +drop table t2; + + +--echo # +--echo # 3) Test coverage for handling of RENAME INDEX clause in +--echo # various storage engines and using different ALTER +--echo # algorithm. +--echo # +--echo # 3.a) Test coverage for simple storage engines (MyISAM/Heap). +create table t1 (i int, key k(i)) engine=myisam; +insert into t1 values (1); +create table t2 (i int, key k(i)) engine=memory; +insert into t2 values (1); +--echo # MyISAM and Heap should be able to handle key renaming in-place. +alter table t1 algorithm=inplace, rename key k to kk; +alter table t2 algorithm=inplace, rename key k to kk; +show create table t1; +show create table t2; +--echo # So by default in-place algorithm should be chosen. +--echo # (ALTER TABLE should report 0 rows affected). +--enable_info +alter table t1 rename key kk to kkk; +alter table t2 rename key kk to kkk; +--disable_info +show create table t1; +show create table t2; +--echo # Copy algorithm should work as well. +alter table t1 algorithm=copy, rename key kkk to kkkk; +alter table t2 algorithm=copy, rename key kkk to kkkk; +show create table t1; +show create table t2; +--echo # When renaming is combined with other in-place operation +--echo # it still works as expected (i.e. works in-place). +alter table t1 algorithm=inplace, rename key kkkk to k, alter column i set default 100; +alter table t2 algorithm=inplace, rename key kkkk to k, alter column i set default 100; +show create table t1; +show create table t2; +--echo # Combining with non-inplace operation results in the whole ALTER +--echo # becoming non-inplace. +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t1 algorithm=inplace, rename key k to kk, add column j int; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t2 algorithm=inplace, rename key k to kk, add column j int; +drop table t1, t2; + +--echo # 3.b) Basic tests for InnoDB. More tests can be found in +--echo # innodb.innodb_rename_index* +create table t1 (i int, key k(i)) engine=innodb; +insert into t1 values (1); +--echo # Basic rename, inplace algorithm should be chosen +--enable_info +alter table t1 algorithm=inplace, rename key k to kk; +--disable_info +show create table t1; +--echo # copy algorithm should work as well. +--enable_info +alter table t1 algorithm=copy, rename key kk to kkk; +--disable_info +show create table t1; +drop table t1; + +--echo # +--echo # 4) Additional coverage for complex cases in which code +--echo # in ALTER TABLE comparing old and new table version +--echo # got confused. +--echo # +--echo # Once InnoDB starts to support in-place index renaming the result +--echo # of below statements should stay the same. Information about +--echo # indexes returned by SHOW CREATE TABLE (from .FRM) and by +--echo # InnoDB (from InnoDB data-dictionary) should be consistent. +--echo # +create table t1 ( a int, b int, c int, d int, + primary key (a), index i1 (b), index i2 (c) ) engine=innodb; +alter table t1 add index i1 (d), rename index i1 to x; +show create table t1; +select i.name as k, f.name as c from information_schema.innodb_sys_tables as t, + information_schema.innodb_sys_indexes as i, + information_schema.innodb_sys_fields as f +where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id +order by k, c; +drop table t1; +create table t1 (a int, b int, c int, d int, + primary key (a), index i1 (b), index i2 (c)) engine=innodb; +alter table t1 add index i1 (d), rename index i1 to i2, drop index i2; +show create table t1; +select i.name as k, f.name as c from information_schema.innodb_sys_tables as t, + information_schema.innodb_sys_indexes as i, + information_schema.innodb_sys_fields as f +where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id +order by k, c; +drop table t1; + +--echo # --echo # End of 10.5 tests --echo # |