diff options
Diffstat (limited to 'mysql-test/main/alter_table.result')
| -rw-r--r-- | mysql-test/main/alter_table.result | 752 |
1 files changed, 750 insertions, 2 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 3c59b83f2ce..a43d9845947 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -272,8 +272,7 @@ ERROR 42000: Incorrect table name '' drop table t1; drop table if exists t1, t2; Warnings: -Note 1051 Unknown table 'test.t1' -Note 1051 Unknown table 'test.t2' +Note 1051 Unknown table 'test.t1,test.t2' create table t1 ( a varchar(10) not null primary key ) engine=myisam; create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); flush tables; @@ -1679,6 +1678,8 @@ ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; # SET SESSION old_alter_table= 1; affected rows: 0 +Warnings: +Warning 1287 '@@old_alter_table' is deprecated and will be removed in a future release. Please use '@@alter_algorithm' instead ALTER TABLE t1 ADD INDEX i1(b); affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 @@ -1699,6 +1700,8 @@ Warnings: Note 1831 Duplicate index `i4`. This is deprecated and will be disallowed in a future release SET SESSION old_alter_table= 0; affected rows: 0 +Warnings: +Warning 1287 '@@old_alter_table' is deprecated and will be removed in a future release. Please use '@@alter_algorithm' instead ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; # # 3: Test unsupported in-place operation @@ -2627,3 +2630,748 @@ insert into t1 (id,name,enabled,domain_id) values (1,"Monty",1,"domain_id"); insert into t1 (id,name,enabled,domain_id) values (2,"Monty2",1,"domain_id2"); ALTER TABLE t1 ADD CONSTRAINT ixu_user2_name_domain_id UNIQUE (domain_id, name); DROP TABLE t1; +# +# End of 10.4 tests +# +# +# MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax +# +SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE; +CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `c` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES(1,'abcd',1.234); +CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `c` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t2 VALUES(1,'abcd',1.234); +ALTER TABLE t1 RENAME COLUMN a TO a; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `c` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN a TO m; +ALTER TABLE t1 RENAME COLUMN a TO m; +ERROR 42S22: Unknown column 'a' in 't1' +ALTER TABLE t1 RENAME COLUMN IF EXISTS a TO m; +Warnings: +Note 1054 Unknown column 'a' in 't1' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `m` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `c` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +SELECT * FROM t1; +m b c +1 abcd 1.234 +ALTER TABLE t1 RENAME COLUMN m TO x, +RENAME COLUMN b TO y, +RENAME COLUMN c TO z; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL, + `y` varchar(30) DEFAULT NULL, + `z` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +SELECT * FROM t1; +x y z +1 abcd 1.234 +ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `d` int(11) DEFAULT NULL, + `e` varchar(30) DEFAULT NULL, + `f` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t2; +d e f +1 abcd 1.234 +ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `z` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` varchar(30) DEFAULT NULL, + `c` double DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `c` varchar(30) DEFAULT NULL, + `d` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `c` varchar(30) DEFAULT NULL, + `f` float DEFAULT NULL, + `zz` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `zz` varchar(30) DEFAULT NULL, + `f` float DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` int(11) DEFAULT NULL, + `c` varchar(30) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `b` varchar(30) DEFAULT NULL, + `d` int(11) DEFAULT 5 +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT 5 +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD KEY(b); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT 5, + KEY `b` (`b`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN b TO bb; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `bb` int(11) DEFAULT 5, + KEY `b` (`bb`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +SELECT * FROM t1; +d bb +abcd 5 +CREATE TABLE t3(a int, b int, KEY(b)); +ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb); +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + KEY `b` (`b`), + CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN bb TO b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT 5, + KEY `b` (`b`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t3 RENAME COLUMN b TO c; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `b` (`c`), + CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +CREATE TABLE t4(a int); +ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE; +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `aa` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY; +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int(11) DEFAULT NULL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +DROP TABLE t4; +CREATE VIEW v1 AS SELECT d,e,f FROM t2; +CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10; +CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10); +ALTER TABLE t2 RENAME COLUMN d TO g; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `g` int(11) DEFAULT NULL, + `e` varchar(30) DEFAULT NULL, + `f` float DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `t2` koi8r koi8r_general_ci +Warnings: +Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SELECT * FROM v1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +UPDATE t2 SET f = f + 10; +ERROR 42S22: Unknown column 'd' in 'OLD' +CALL sp1(); +ERROR 42S22: Unknown column 'd' in 'field list' +DROP TRIGGER trg1; +DROP PROCEDURE sp1; +CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a))); +INSERT INTO t_gen(a) VALUES(4); +SELECT * FROM t_gen; +a b +4 2 +SHOW CREATE TABLE t_gen; +Table Create Table +t_gen CREATE TABLE `t_gen` ( + `a` int(11) DEFAULT NULL, + `b` double GENERATED ALWAYS AS (sqrt(`a`)) VIRTUAL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c)); +SELECT * FROM t_gen; +c b +4 2 +SHOW CREATE TABLE t_gen; +Table Create Table +t_gen CREATE TABLE `t_gen` ( + `c` int(11) DEFAULT NULL, + `b` double GENERATED ALWAYS AS (sqrt(`c`)) VIRTUAL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t_gen CHANGE COLUMN c x INT; +show create table t_gen; +Table Create Table +t_gen CREATE TABLE `t_gen` ( + `x` int(11) DEFAULT NULL, + `b` double GENERATED ALWAYS AS (sqrt(`x`)) VIRTUAL +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t_gen RENAME COLUMN x TO a; +DROP TABLE t_gen; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT 5, + KEY `b` (`b`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +ALTER TABLE t1 RENAME COLUMN b z; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'z' at line 1 +ALTER TABLE t1 RENAME COLUMN FROM b TO z; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM b TO z' at line 1 +ALTER TABLE t1 RENAME COLUMN b TO 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1 +ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e; +ERROR 42S22: Unknown column 'c' in 't1' +ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z; +ERROR 42S21: Duplicate column name 'z' +ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z; +ERROR 42S22: Unknown column 'b' in 't1' +ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b; +ERROR 42S22: Unknown column 'b' in 't1' +ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3; +ERROR 42000: Can't DROP COLUMN `c3`; check that it exists +ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y; +ERROR 42S22: Unknown column 'z' in 't1' +ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y; +ERROR 42S22: Unknown column 'z' in 't1' +ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`; +ERROR 42000: Incorrect column name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn' +ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int; +ERROR 42000: Identifier name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn' is too long +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` varchar(30) DEFAULT NULL, + `b` int(11) DEFAULT 5, + KEY `b` (`b`) +) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 +SELECT * FROM t1; +d b +abcd 5 +DROP VIEW v1; +DROP TABLE t3,t1,t2; +SET DEFAULT_STORAGE_ENGINE= @save_default_engine; +# +# MDEV-7318 RENAME INDEX +# +# +# 1) Tests for syntax and semantics of ALTER TABLE RENAME +# KEY/INDEX result. +# +# 1.a) Both RENAME KEY and RENAME INDEX variants should be +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `b` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename index b to c; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 1.b) It should be impossible to rename index that doesn't +# exists, dropped or added within the same ALTER TABLE. +alter table t1 rename key d to e; +ERROR 42000: Key 'd' doesn't exist in table 't1' +alter table t1 rename key if exists d to e; +Warnings: +Note 1176 Key 'd' doesn't exist in table 't1' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 drop key c, rename key c to d; +ERROR 42000: Key 'c' doesn't exist in table 't1' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add key d(j), rename key d to e; +ERROR 42000: Key 'd' doesn't exist in table 't1' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 1.c) It should be impossible to rename index to a name +# which is already used by another index, or is used +# by index which is added within the same ALTER TABLE. +alter table t1 add key d(j); +alter table t1 rename key c to d; +ERROR 42000: Duplicate key name 'd' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`), + KEY `d` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 drop key d; +alter table t1 add key d(j), rename key c to d; +ERROR 42000: Duplicate key name 'd' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 1.d) It should be possible to rename index to a name +# which belongs to index which is dropped within the +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 1.e) We disallow renaming from/to PRIMARY as it might +# lead to some other key becoming "primary" internally, +# which will be interpreted as dropping/addition of +# primary key. +alter table t1 rename key primary to d; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary to d' at line 1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# Even using 'funny' syntax. +alter table t1 rename key `primary` to d; +ERROR 42000: Incorrect index name 'primary' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key c to primary; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary' at line 1 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key c to `primary`; +ERROR 42000: Incorrect index name 'primary' +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `i` int(11) DEFAULT NULL, + `j` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `c` (`j`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# 2) More complex tests for semantics of ALTER TABLE. +# +# 2.a) Check that standalone RENAME KEY works as expected +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + UNIQUE KEY `uu` (`a`), + KEY `k` (`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key k to kk; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + UNIQUE KEY `uu` (`a`), + KEY `kk` (`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# 2.b) Check how that this clause can be mixed with other +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + UNIQUE KEY `uu` (`a`), + KEY `kkk` (`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key uu to uuu, add key c(c); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + UNIQUE KEY `uuu` (`a`), + KEY `kkk` (`b`), + KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key kkk to k, drop key uuu; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `k` (`b`), + KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename key k to kk, rename to t2; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `kk` (`b`), + KEY `c` (`c`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2; +# +# 3) Test coverage for handling of RENAME INDEX clause in +# various storage engines and using different ALTER +# algorithm. +# +# 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); +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + KEY `kk` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL, + KEY `kk` (`i`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +# So by default in-place algorithm should be chosen. +# (ALTER TABLE should report 0 rows affected). +alter table t1 rename key kk to kkk; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +alter table t2 rename key kk to kkk; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + KEY `kkk` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL, + KEY `kkk` (`i`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + KEY `kkkk` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT NULL, + KEY `kkkk` (`i`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +# When renaming is combined with other in-place operation +# 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; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT 100, + KEY `k` (`i`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `i` int(11) DEFAULT 100, + KEY `k` (`i`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +# Combining with non-inplace operation results in the whole ALTER +# becoming non-inplace. +alter table t1 algorithm=inplace, rename key k to kk, add column j int; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +alter table t2 algorithm=inplace, rename key k to kk, add column j int; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +drop table t1, t2; +# 3.b) Basic tests for InnoDB. More tests can be found in +# innodb.innodb_rename_index* +create table t1 (i int, key k(i)) engine=innodb; +insert into t1 values (1); +# Basic rename, inplace algorithm should be chosen +alter table t1 algorithm=inplace, rename key k to kk; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + KEY `kk` (`i`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +# copy algorithm should work as well. +alter table t1 algorithm=copy, rename key kk to kkk; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL, + KEY `kkk` (`i`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t1; +# +# 4) Additional coverage for complex cases in which code +# in ALTER TABLE comparing old and new table version +# got confused. +# +# Once InnoDB starts to support in-place index renaming the result +# of below statements should stay the same. Information about +# indexes returned by SHOW CREATE TABLE (from .FRM) and by +# InnoDB (from InnoDB data-dictionary) should be consistent. +# +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `x` (`b`), + KEY `i2` (`c`), + KEY `i1` (`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +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; +k c +i1 d +i2 c +PRIMARY a +x b +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `i2` (`b`), + KEY `i1` (`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +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; +k c +i1 d +i2 b +PRIMARY a +drop table t1; +# +# ALTER TABLE IF EXISTS +# +create table t1 (a int); +alter table if exists t1 add column b int; +alter table if exists t2 add column c int; +Warnings: +Error 1146 Table 'test.t2' doesn't exist +alter table if exists t9 rename t1; +Warnings: +Error 1146 Table 'test.t9' doesn't exist +alter table if exists t1 rename t2; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2; +# +# MDEV-22881 Unexpected errors, corrupt output, Valgrind / ASAN errors in Item_ident::print or append_identifier +# +create table t1 (a int check (a >= 0)); +lock tables t1 write; +alter table t1 rename column a to a; +alter table t1 rename key if exists x to xx; +Warnings: +Note 1176 Key 'x' doesn't exist in table 't1' +unlock tables; +drop table t1; +# +# MDEV-23852 alter table rename column to uppercase doesn't work +# +create table t1 (abc int); +alter table t1 rename column abc to Abc, algorithm=copy; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `Abc` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename column abc to ABc, algorithm=inplace; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ABc` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 rename column abc to ABC; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ABC` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# End of 10.5 tests +# |