diff options
Diffstat (limited to 'mysql-test/main/alter_table_combinations.test')
| -rw-r--r-- | mysql-test/main/alter_table_combinations.test | 263 |
1 files changed, 263 insertions, 0 deletions
diff --git a/mysql-test/main/alter_table_combinations.test b/mysql-test/main/alter_table_combinations.test new file mode 100644 index 00000000000..7c8d7f42096 --- /dev/null +++ b/mysql-test/main/alter_table_combinations.test @@ -0,0 +1,263 @@ +set @save_default_engine= @@default_storage_engine; +--disable_query_log +if ($MTR_COMBINATION_INNODB) +{ +set default_storage_engine= innodb; +} +if ($MTR_COMBINATION_ARIA) +{ +set default_storage_engine= aria; +} +if ($MTR_COMBINATION_HEAP) +{ +set default_storage_engine= memory; +} +--enable_query_log +let $default_engine= `select @@default_storage_engine`; + +--echo # +--echo # MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed +--echo # + +if (!$MTR_COMBINATION_INNODB) +{ + --disable_query_log + --disable_result_log + # There is no inplace ADD INDEX for MyISAM/Aria: + create or replace table t1 (x int); + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add unique (x), algorithm=inplace; + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add primary key(x), algorithm=inplace; + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add index(x), algorithm=inplace; + --enable_query_log + --enable_result_log +} + +create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam; +alter table t1 change x xx int, algorithm=inplace; +check table t1; +create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)); +alter table t1 change x xx int, algorithm=inplace; +check table t1; +drop table t1; + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax +--echo # + +CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT); +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES(1,'abcd',1.234); +CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam; +SHOW CREATE TABLE t2; +INSERT INTO t2 VALUES(1,'abcd',1.234); + +# Rename one column +ALTER TABLE t1 RENAME COLUMN a TO a; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 RENAME COLUMN a TO m; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 RENAME COLUMN a TO m; +ALTER TABLE t1 RENAME COLUMN IF EXISTS a TO m; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +# Rename multiple column +ALTER TABLE t1 RENAME COLUMN m TO x, + RENAME COLUMN b TO y, + RENAME COLUMN c TO z; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +# Rename multiple columns with MyIsam Engine +ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f; +SHOW CREATE TABLE t2; +SELECT * FROM t2; + +# Mix different ALTER operations with RENAME COLUMN +ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; + +#Cyclic Rename +ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; + +# Rename with Indexes +ALTER TABLE t1 ADD KEY(b); +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t1 RENAME COLUMN b TO bb; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +# Rename with Foreign keys. +CREATE TABLE t3(a int, b int, KEY(b)); +ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb); +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t3; +ALTER TABLE t1 RENAME COLUMN bb TO b; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +ALTER TABLE t3 RENAME COLUMN b TO c; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t3; + +# Different Algorithm +CREATE TABLE t4(a int); +ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t4; +ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t4; +DROP TABLE t4; + +# View, Trigger and SP +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; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t2; +SHOW CREATE VIEW v1; +--error ER_VIEW_INVALID +SELECT * FROM v1; +--error ER_BAD_FIELD_ERROR +UPDATE t2 SET f = f + 10; +--error ER_BAD_FIELD_ERROR +CALL sp1(); +DROP TRIGGER trg1; +DROP PROCEDURE sp1; + +# Generated Columns +if (!$MTR_COMBINATION_HEAP) +{ +CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a))); +INSERT INTO t_gen(a) VALUES(4); +SELECT * FROM t_gen; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t_gen; +ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c)); +SELECT * FROM t_gen; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t_gen; +#--error ER_DEPENDENT_BY_GENERATED_COLUMN +ALTER TABLE t_gen CHANGE COLUMN c x INT; +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +show create table t_gen; +#--error ER_DEPENDENT_BY_GENERATED_COLUMN +ALTER TABLE t_gen RENAME COLUMN x TO a; +DROP TABLE t_gen; +} + +# +# Negative tests +# +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; + +# Invalid Syntax +--error ER_PARSE_ERROR +ALTER TABLE t1 RENAME COLUMN b z; +--error ER_PARSE_ERROR +ALTER TABLE t1 RENAME COLUMN FROM b TO z; +--error ER_PARSE_ERROR +ALTER TABLE t1 RENAME COLUMN b TO 1; + +# Duplicate column name +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e; +--error ER_DUP_FIELDNAME +ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z; + +# Multiple operation on same column +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b; +--error ER_CANT_DROP_FIELD_OR_KEY +ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y; + +# Invalid column name while renaming +--error ER_WRONG_COLUMN_NAME +ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`; +# This error is different compared to ALTER TABLE ... CHANGE command +--error ER_TOO_LONG_IDENT +ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int; + +--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +# Cleanup +DROP VIEW v1; +DROP TABLE t3,t1,t2; + +--echo # +--echo # MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed +--echo # + +if (!$MTR_COMBINATION_INNODB) +{ + --disable_query_log + --disable_result_log + # There is no inplace ADD INDEX for MyISAM/Aria: + create or replace table t1 (x int); + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add unique (x), algorithm=inplace; + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add primary key(x), algorithm=inplace; + --error ER_ALTER_OPERATION_NOT_SUPPORTED + alter table t1 add index(x), algorithm=inplace; + --enable_query_log + --enable_result_log +} + +create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam; +alter table t1 change x xx int, algorithm=inplace; +check table t1; +create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)); +alter table t1 change x xx int, algorithm=inplace; +check table t1; +drop table t1; + +--echo # +--echo # End of 10.5 tests +--echo # + +set @@default_storage_engine= @save_default_engine; |