diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-alter.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-alter.result | 140 |
1 files changed, 133 insertions, 7 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index afdeac1c22a..e9f827c004e 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -726,6 +726,7 @@ t2 CREATE TABLE `t2` ( CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t1 CHANGE COLUMN c1 C1 INT; +ALTER TABLE t2 CHANGE COLUMN c2 C2 INT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -735,24 +736,149 @@ t1 CREATE TABLE `t1` ( SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `c2` int(11) NOT NULL, - KEY `c2` (`c2`), + `C2` int(11) DEFAULT NULL, + KEY `c2` (`C2`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1 CHANGE COLUMN C1 c5 INT; +ALTER TABLE t2 CHANGE COLUMN C2 c6 INT; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `C1` int(11) NOT NULL, - PRIMARY KEY (`C1`) + `c5` int(11) NOT NULL, + PRIMARY KEY (`c5`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `c2` int(11) NOT NULL, - KEY `c2` (`c2`), - CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`) + `c6` int(11) DEFAULT NULL, + KEY `c2` (`c6`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c6`) REFERENCES `t1` (`c5`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID +WHERE T.NAME='test/t1'; +NAME +c5 +SELECT F.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS F INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_INDEXES I ON F.INDEX_ID=I.INDEX_ID INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON I.TABLE_ID=T.TABLE_ID +WHERE T.NAME='test/t1' AND I.NAME='PRIMARY'; +NAME +c5 +SELECT C.REF_COL_NAME, C.FOR_COL_NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS C INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_FOREIGN F ON C.ID=F.ID +WHERE F.FOR_NAME='test/t2'; +REF_COL_NAME FOR_COL_NAME +c5 c6 DROP TABLE t2, t1; +# virtual columns case too +CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB; +ALTER TABLE t1 CHANGE COLUMN a A INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `A` int(11) DEFAULT NULL, + `b` int(11) GENERATED ALWAYS AS (`A`) VIRTUAL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID +WHERE T.NAME='test/t1'; +NAME +a +b +DROP TABLE t1; +# different FOREIGN KEY cases +CREATE TABLE t1 ( +a INT UNIQUE KEY, +b INT UNIQUE KEY, +c INT UNIQUE KEY, +d INT UNIQUE KEY +) ENGINE=INNODB; +CREATE TABLE t2 ( +aa INT, +bb INT, +cc INT, +dd INT +) ENGINE=INNODB; +INSERT INTO t1 VALUES (1, 1, 1, 1); +INSERT INTO t2 VALUES (1, 1, 1, 1); +ALTER TABLE t1 CHANGE a A INT, ALGORITHM=INPLACE; +ALTER TABLE t1 CHANGE c C INT, ALGORITHM=INPLACE; +ALTER TABLE t2 CHANGE cc CC INT, ALGORITHM=INPLACE; +ALTER TABLE t2 CHANGE dd DD INT, ALGORITHM=INPLACE; +SET foreign_key_checks=0; +ALTER TABLE t2 +ADD FOREIGN KEY(aa) REFERENCES t1(a), +ADD FOREIGN KEY(bb) REFERENCES t1(b), +ADD FOREIGN KEY(cc) REFERENCES t1(c), +ADD FOREIGN KEY(dd) REFERENCES t1(d), +ALGORITHM=INPLACE; +ALTER TABLE t1 CHANGE b B INT, ALGORITHM=INPLACE; +ALTER TABLE t2 CHANGE aa AA INT, ALGORITHM=INPLACE; +ALTER TABLE t1 CHANGE d D INT, ALGORITHM=INPLACE; +ALTER TABLE t2 CHANGE bb BB INT, ALGORITHM=INPLACE; +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, + `D` int(11) DEFAULT NULL, + UNIQUE KEY `a` (`A`), + UNIQUE KEY `b` (`B`), + UNIQUE KEY `c` (`C`), + UNIQUE KEY `d` (`D`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `AA` int(11) DEFAULT NULL, + `BB` int(11) DEFAULT NULL, + `CC` int(11) DEFAULT NULL, + `DD` int(11) DEFAULT NULL, + KEY `aa` (`AA`), + KEY `bb` (`BB`), + KEY `CC` (`CC`), + KEY `DD` (`DD`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`), + CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`bb`) REFERENCES `t1` (`b`), + CONSTRAINT `t2_ibfk_3` FOREIGN KEY (`cc`) REFERENCES `t1` (`c`), + CONSTRAINT `t2_ibfk_4` FOREIGN KEY (`dd`) REFERENCES `t1` (`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DELETE FROM t1 WHERE a=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE A=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE b=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE B=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE c=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE C=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE d=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DELETE FROM t1 WHERE D=1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`)) +DROP TABLE t2, t1; +# virtual columns case too +CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB; +ALTER TABLE t1 CHANGE COLUMN a A INT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `A` int(11) DEFAULT NULL, + `b` int(11) GENERATED ALWAYS AS (`A`) VIRTUAL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN +INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID +WHERE T.NAME='test/t1'; +NAME +a +b +DROP TABLE t1; # # BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN # DICT_MEM_TABLE_COL_RENAME_LOW |