EVOLUTION-MANAGER

Edit File: innodb_virtual_fk.result

set default_storage_engine=innodb;
#
# Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
#               WHEN A VIRTUAL INDEX EXISTS.
# UPDATE CASCADE
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# UPDATE SET NULL
CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY,
KEY(fld1));
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE SET NULL);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
NULL
SELECT * FROM t2;
fld1	fld2
NULL	NULL
DROP TABLE t2, t1;
# DELETE CASCADE
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t2 VALUES(1, DEFAULT);
INSERT INTO t2 VALUES(2, DEFAULT);
DELETE FROM t1 WHERE fld1= 1;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# DELETE SET NULL
CREATE TABLE t1(fld1 INT NOT NULL, fld2 INT NOT NULL PRIMARY KEY, KEY(fld1));
CREATE TABLE t2(fld1 INT, fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE SET NULL);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 2);
INSERT INTO t2 VALUES(1, DEFAULT);
INSERT INTO t2 VALUES(2, DEFAULT);
DELETE FROM t1 WHERE fld1= 1;
SELECT fld2 FROM t2;
fld2
NULL
2
SELECT * FROM t2;
fld1	fld2
NULL	NULL
2	2
DROP TABLE t2, t1;
# VIRTUAL INDEX CONTAINS FK CONSTRAINT COLUMN
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT, fld3 INT AS (fld2) VIRTUAL,
KEY(fld3, fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1, fld2) VALUES(1, 3);
UPDATE t1 SET fld1= 2;
SELECT fld3, fld1 FROM t2;
fld3	fld1
3	2
SELECT * FROM t2;
fld1	fld2	fld3
2	3	3
DROP TABLE t2, t1;
# Multiple level of VIRTUAL columns.
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
fld3 INT AS (fld2) VIRTUAL, KEY(fld3), KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1) VALUES(1);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT fld3 FROM t2;
fld3
2
SELECT * FROM t2;
fld1	fld2	fld3
2	2	2
DROP TABLE t2, t1;
# Drop the VIRTUAL INDEX using alter copy ALGORITHM
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL, KEY vk(fld2),
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1) VALUES(1);
UPDATE t1 SET fld1= 2;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
UPDATE t1 SET fld1= 3;
SELECT fld2, fld1 FROM t2;
fld2	fld1
3	3
DROP TABLE t2, t1;
# Drop the VIRTUAL INDEX using INPLACE alter ALGORITHM
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY vk(fld2), KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1) VALUES(1);
UPDATE t1 SET fld1= 2;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
UPDATE t1 SET fld1= 3;
SELECT fld2, fld1 FROM t2;
fld2	fld1
3	3
DROP TABLE t2, t1;
# Add the VIRTUAL INDEX using COPY alter ALGORITHM
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1) VALUES(1);
UPDATE t1 SET fld1= 2;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY;
UPDATE t1 SET fld1= 3;
SELECT fld2, fld1 FROM t2;
fld2	fld1
3	3
DROP TABLE t2, t1;
# Add the VIRTUAL INDEX using INPLACE alter ALGORITHM
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,fld2 INT AS (fld1) VIRTUAL,
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1) VALUES(1);
UPDATE t1 SET fld1= 2;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE;
UPDATE t1 SET fld1= 3;
SELECT fld2, fld1 FROM t2;
fld2	fld1
3	3
DROP TABLE t2, t1;
# Drop the VIRTUAL INDEX contains fk constraint column
# using alter copy ALGORITHM
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
UPDATE t1 SET fld1= 2;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	2
ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
UPDATE t1 SET fld1= 3;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	3
DROP TABLE t2, t1;
# Drop the VIRTUAL INDEX which contains fk constraint column
# using INPLACE alter operation
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY vk(fld3, fld1),
KEY(fld1), FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
UPDATE t1 SET fld1= 2;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	2
alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE;
UPDATE t1 SET fld1= 3;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	3
DROP TABLE t2, t1;
# Add the VIRTUAL INDEX contains fk constraint column
# using copy alter operatiON
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY(fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
INSERT INTO t1(fld1) VALUES(1);
INSERT INTO t2(fld1, fld2) VALUES(1, 2);
UPDATE t1 SET fld1= 2;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	2
alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY;
UPDATE t1 SET fld1= 3;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	3
DROP TABLE t2, t1;
# Cascading UPDATEs and DELETEs for the multiple
# fk dependent TABLEs
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY(fld1), KEY(fld2, fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY(fld2, fld1),
FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2(fld1) VALUES(1), (2);
INSERT INTO t3(fld1) VALUES(1), (2);
UPDATE t1 SET fld1= 4 WHERE fld1= 1;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
4	4
SELECT fld2, fld1 FROM t3;
fld2	fld1
2	2
4	4
DROP TABLE t3, t2, t1;
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1), KEY(fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
FOREIGN KEY(fld1) REFERENCES t2(fld1) ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
UPDATE t1 SET fld1= 4 WHERE fld1= 1;
SELECT fld3, fld1 FROM t2;
fld3	fld1
1	4
2	2
SELECT fld3, fld1 FROM t3;
fld3	fld1
1	4
2	2
DROP TABLE t3, t2, t1;
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY(fld1), KEY(fld2, fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1) ON DELETE CASCADE);
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,
KEY(fld2, fld1), FOREIGN KEY(fld1) REFERENCES t2(fld1)
ON DELETE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2(fld1) VALUES(1), (2);
INSERT INTO t3(fld1) VALUES(1), (2);
DELETE FROM t1 WHERE fld1= 1;
SELECT fld2, fld1 FROM t2;
fld2	fld1
2	2
SELECT fld2, fld1 FROM t3;
fld2	fld1
2	2
DROP TABLE t3, t2, t1;
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL,
KEY(fld3, fld1), KEY(fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON DELETE CASCADE);
CREATE TABLE t3(fld1 INT NOT NULL, fld2 INT NOT NULL,
fld3 INT AS (fld2) VIRTUAL, KEY(fld3, fld1),
FOREIGN KEY(fld1) REFERENCES t2(fld1)
ON DELETE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, 1, DEFAULT), (2, 2, default);
INSERT INTO t3 VALUES(1, 1, DEFAULT), (2, 2, default);
DELETE FROM t1 WHERE fld1= 1;
SELECT fld3, fld1 FROM t2;
fld3	fld1
2	2
SELECT fld3, fld1 FROM t3;
fld3	fld1
2	2
DROP TABLE t3, t2, t1;
# RENAME TABLE
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
KEY(fld2, fld1),
FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON DELETE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
RENAME TABLE t2 to t3;
DELETE FROM t1 WHERE fld1= 1;
SELECT fld2, fld1 FROM t3;
fld2	fld1
2	2
DROP TABLE t3, t1;
# FOREIGN_KEY_CHECKS disabled DURING INPLACE ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
SET foreign_key_checks = 0;
ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM=INPLACE;
SET foreign_key_checks = 1;
UPDATE t1 SET fld1= 3 WHERE fld1= 2;
SELECT fld2 FROM t2;
fld2
1
3
DROP TABLE t2, t1;
# GENERATED COLUMN COMPUTATION FAILS when SQL_MODE
# is set to ERROR_FOR_DIVISION_BY_ZERO
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (100/fld1) VIRTUAL,
KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
Warnings:
Warning	1365	Division by 0
SELECT fld2 FROM t2;
fld2
NULL
100
DROP TABLE t2, t1;
# CHANGE SQL_MODE and try the ERROR_FOR_DIVISION_BY_ZERO
SET sql_mode = STRICT_ALL_TABLES;
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (100/fld1) VIRTUAL,
KEY(fld2),
FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1), (2);
INSERT INTO t2 VALUES(1, DEFAULT), (2, default);
UPDATE t1 SET fld1= 0 WHERE fld1= 2;
SELECT fld2 FROM t2;
fld2
NULL
100
SELECT * FROM t2;
fld1	fld2
1	100
0	NULL
DROP TABLE t2, t1;
SET sql_mode = default;
# ADD FOREIGN CONSTRAINT USING COPY
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
ALTER TABLE t2 ADD FOREIGN KEY (fld1)
REFERENCES t1(fld1) ON UPDATE CASCADE,
ALGORITHM=copy;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# ADD FOREIGN CONSTRAINT USING INPLACE
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL, KEY(fld2));
SET foreign_key_checks = 0;
ALTER TABLE t2 ADD FOREIGN KEY (fld1)
REFERENCES t1(fld1) ON UPDATE CASCADE,
ALGORITHM=inplace;
SET foreign_key_checks = 1;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# DROP FOREIGN CONSTRAINT USING COPY
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=COPY;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
# DROP FOREIGN CONSTRAINT USING INPLACE
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL, KEY(fld2),
CONSTRAINT fidx FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
SET foreign_key_checks = 0;
ALTER TABLE t2 DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
SET foreign_key_checks = 1;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
# ADD VC INDEX and ADD FK IN SAME COPY ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE, ALGORITHM=copy;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# ADD VC INDEX and ADD FK IN SAME INPLACE ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
SET foreign_key_checks = 0;
ALTER TABLE t2 ADD INDEX(fld2), ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE, ALGORITHM=inplace;
SET foreign_key_checks = 1;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# ADD VC INDEX and DROP FK IN SAME COPY ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=copy;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
# ADD VC INDEX and DROP FK IN SAME INPLACE ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
SET foreign_key_checks = 0;
ALTER TABLE t2 ADD INDEX(fld2), DROP FOREIGN KEY fidx, ALGORITHM=inplace;
SET foreign_key_checks = 1;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
# DROP VC INDEX and ADD FK IN SAME COPY ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
KEY idx(fld2));
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE, ALGORITHM=COPY;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# DROP VC INDEX and ADD FK IN SAME INPLACE ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
KEY idx(fld2));
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
SET foreign_key_checks = 0;
ALTER TABLE t2 DROP INDEX idx, ADD FOREIGN KEY (fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE, ALGORITHM=INPLACE;
SET foreign_key_checks = 1;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
2
SELECT * FROM t2;
fld1	fld2
2	2
DROP TABLE t2, t1;
# DROP VC INDEX and DROP FK IN SAME COPY ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
KEY idx(fld2),
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=COPY;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
# DROP VC INDEX and DROP FK IN SAME INPLACE ALTER
CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
CREATE TABLE t2(fld1 INT NOT NULL,
fld2 INT AS (fld1) VIRTUAL,
KEY idx(fld2),
CONSTRAINT fidx FOREIGN KEY(fld1) REFERENCES t1(fld1)
ON UPDATE CASCADE);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1, DEFAULT);
SET foreign_key_checks = 0;
ALTER TABLE t2 DROP KEY idx, DROP FOREIGN KEY fidx, ALGORITHM=INPLACE;
SET foreign_key_checks = 1;
UPDATE t1 SET fld1= 2;
SELECT fld2 FROM t2;
fld2
1
SELECT * FROM t2;
fld1	fld2
1	1
DROP TABLE t2, t1;
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
EXPLAIN SELECT f1, f2 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	f1	9	NULL	1	Using index
SELECT f1, f2 FROM t2;
f1	f2
1	1
INSERT INTO t2(f1) VALUES(2);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`))
DROP TABLE t2, t1;
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
KEY (f1, f2), FOREIGN KEY(f1) REFERENCES t1(f1)
ON UPDATE CASCADE)ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
EXPLAIN SELECT f1, f2 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	f1	9	NULL	1	Using index
SELECT f1, f2 FROM t2;
f1	f2
1	1
UPDATE t1 SET f1 = 2 WHERE f1 = 1;
EXPLAIN SELECT f1, f2 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	f1	9	NULL	1	Using index
SELECT f1, f2 FROM t2;
f1	f2
2	2
DROP TABLE t2, t1;
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
KEY (f1, f2))ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
ON UPDATE CASCADE, ALGORITHM=INPLACE;
SET FOREIGN_KEY_CHECKS = 1;
UPDATE t1 SET f1 = 3;
EXPLAIN SELECT f1, f2 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	f1	9	NULL	1	Using index
SELECT f1, f2 FROM t2;
f1	f2
3	3
DROP TABLE t2, t1;
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY)ENGINE=INNODB;
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT AS (f1) VIRTUAL,
KEY (f1, f2))ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
ALTER TABLE t2 ADD FOREIGN KEY (f1) REFERENCES t1(f1)
ON UPDATE CASCADE, ALGORITHM=COPY;
UPDATE t1 SET f1 = 3;
EXPLAIN SELECT f1, f2 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	f1	9	NULL	1	Using index
SELECT f1, f2 FROM t2;
f1	f2
3	3
DROP TABLE t2, t1;
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
f3 INT AS (2) VIRTUAL,
FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
KEY idx1 (f2, f1, f3))ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=INPLACE;
UPDATE t1 SET f1 = 3;
EXPLAIN SELECT f1, f3 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx1	9	NULL	1	Using index
SELECT f1, f3 FROM t2;
f1	f3
3	2
DROP TABLE t2, t1;
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
CREATE TABLE t2(f1 INT NOT NULL, f2 INT AS (1) VIRTUAL,
f3 INT AS (2) VIRTUAL,
FOREIGN KEY idx (f1) REFERENCES t1(f1) ON UPDATE CASCADE,
KEY idx1 (f2, f1, f3))ENGINE=INNODB;
INSERT INTO t1 VALUES(1);
INSERT INTO t2(f1) VALUES(1);
ALTER TABLE t2 DROP COLUMN f2, ALGORITHM=COPY;
UPDATE t1 SET f1 = 3;
EXPLAIN SELECT f1, f3 FROM t2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	idx1	9	NULL	1	Using index
SELECT f1, f3 FROM t2;
f1	f3
3	2
DROP TABLE t2, t1;
#
# MDEV-15553 Assertion failed in dict_table_get_col_name
#
CREATE TABLE t1 (
c1 TIMESTAMP,
c2 YEAR,
c3 TIME,
c4 CHAR(10),
v1 TIMESTAMP AS (c1) VIRTUAL,
v2 YEAR AS (c2) VIRTUAL,
v3 TIME AS (c3) VIRTUAL,
v4 CHAR(10) AS (c4) VIRTUAL
) ENGINE=InnoDB;
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
SET foreign_key_checks=0;
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk' in the foreign table 't1'
ALTER TABLE t1 ADD INDEX(v4);
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
SET foreign_key_checks=1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `c2` year(4) DEFAULT NULL,
  `c3` time DEFAULT NULL,
  `c4` char(10) DEFAULT NULL,
  `v1` timestamp GENERATED ALWAYS AS (`c1`) VIRTUAL,
  `v2` year(4) GENERATED ALWAYS AS (`c2`) VIRTUAL,
  `v3` time GENERATED ALWAYS AS (`c3`) VIRTUAL,
  `v4` char(10) GENERATED ALWAYS AS (`c4`) VIRTUAL,
  KEY `v4` (`v4`),
  CONSTRAINT `fk` FOREIGN KEY (`v4`) REFERENCES `nosuch` (`col`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
ALTER TABLE t1 DROP FOREIGN KEY fk;
ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `c2` year(4) DEFAULT NULL,
  `c3` time DEFAULT NULL,
  `c4` char(10) DEFAULT NULL,
  `v1` timestamp GENERATED ALWAYS AS (`c1`) VIRTUAL,
  `v2` year(4) GENERATED ALWAYS AS (`c2`) VIRTUAL,
  `v3` time GENERATED ALWAYS AS (`c3`) VIRTUAL,
  `v4` char(10) GENERATED ALWAYS AS (`c4`) VIRTUAL,
  KEY `v4` (`v4`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t1;