Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 695 insertions, 0 deletions

diff --git a/mysql-test/suite/gcol/t/innodb_virtual_fk.test b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
new file mode 100644
index 00000000..c9925953
--- /dev/null
+++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test
@@ -0,0 +1,695 @@
+-- source include/have_innodb.inc
+
+set default_storage_engine=innodb;
+
+--echo #
+--echo # Bug#22469130: FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
+--echo #               WHEN A VIRTUAL INDEX EXISTS.
+
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT fld3 FROM t2;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
+UPDATE t1 SET fld1= 3;
+SELECT fld2, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
+UPDATE t1 SET fld1= 3;
+SELECT fld2, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= COPY;
+UPDATE t1 SET fld1= 3;
+SELECT fld2, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+ALTER TABLE t2 ADD INDEX vk(fld2), ALGORITHM= INPLACE;
+UPDATE t1 SET fld1= 3;
+SELECT fld2, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # Drop the VIRTUAL INDEX contains fk constraint column
+--echo # 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;
+ALTER TABLE t2 DROP INDEX vk, ALGORITHM= COPY;
+UPDATE t1 SET fld1= 3;
+SELECT fld3, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # Drop the VIRTUAL INDEX which contains fk constraint column
+--echo # 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;
+alter TABLE t2 DROP INDEX vk, ALGORITHM= INPLACE;
+UPDATE t1 SET fld1= 3;
+SELECT fld3, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # Add the VIRTUAL INDEX contains fk constraint column
+--echo # 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;
+alter TABLE t2 ADD INDEX vk(fld3, fld1), ALGORITHM= COPY;
+UPDATE t1 SET fld1= 3;
+SELECT fld3, fld1 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # Cascading UPDATEs and DELETEs for the multiple
+--echo # 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;
+SELECT fld2, fld1 FROM t3;
+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;
+SELECT fld3, fld1 FROM t3;
+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;
+SELECT fld2, fld1 FROM t3;
+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;
+SELECT fld3, fld1 FROM t3;
+DROP TABLE t3, t2, t1;
+
+--echo # 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;
+DROP TABLE t3, t1;
+
+--echo # 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;
+DROP TABLE t2, t1;
+
+--echo # GENERATED COLUMN COMPUTATION FAILS when SQL_MODE
+--echo # 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);
+#--error ER_DIVISION_BY_ZERO
+UPDATE IGNORE t1 SET fld1= 0 WHERE fld1= 2;
+SELECT fld2 FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+SET sql_mode = default;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+--echo # 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;
+SELECT * FROM t2;
+DROP TABLE t2, t1;
+
+# Foreign key constraint references to virtual index
+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;
+SELECT f1, f2 FROM t2;
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2(f1) VALUES(2);
+DROP TABLE t2, t1;
+
+# Update foreign key constraint references to virtual index
+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;
+SELECT f1, f2 FROM t2;
+UPDATE t1 SET f1 = 2 WHERE f1 = 1;
+EXPLAIN SELECT f1, f2 FROM t2;
+SELECT f1, f2 FROM t2;
+DROP TABLE t2, t1;
+
+# Add foreign key constraint via inplace alter references to virtual index
+
+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;
+SELECT f1, f2 FROM t2;
+DROP TABLE t2, t1;
+
+# Add foreign key constraint via copy alter references to virtual index
+
+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;
+SELECT f1, f2 FROM t2;
+DROP TABLE t2, t1;
+
+# Drop column via inplace alter which triggers to remove the FK index idx
+
+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;
+SELECT f1, f3 FROM t2;
+DROP TABLE t2, t1;
+
+# Drop column via copy alter which triggers to remove the FK index idx
+
+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;
+SELECT f1, f3 FROM t2;
+DROP TABLE t2, t1;
+
+--echo #
+--echo # MDEV-15553 Assertion failed in dict_table_get_col_name
+--echo #
+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;
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
+SET foreign_key_checks=0;
+--error ER_FK_NO_INDEX_CHILD
+ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
+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;
+ALTER TABLE t1 DROP FOREIGN KEY fk;
+--error ER_CANT_CREATE_TABLE
+ALTER TABLE t1 ADD CONSTRAINT fk FOREIGN KEY (v4) REFERENCES nosuch(col);
+SHOW CREATE TABLE t1;
+# Cleanup
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-20396 Server crashes after DELETE with SEL NULL Foreign key and a
+--echo # virtual column in index
+--echo #
+CREATE TABLE parent
+(
+  ID int unsigned NOT NULL,
+  PRIMARY KEY (ID)
+);
+
+CREATE TABLE child
+(
+  ID int unsigned NOT NULL,
+  ParentID int unsigned NULL,
+  Value int unsigned NOT NULL DEFAULT 0,
+  Flag int unsigned AS (Value) VIRTUAL,
+  PRIMARY KEY (ID),
+  KEY (ParentID, Flag),
+  FOREIGN KEY (ParentID) REFERENCES parent (ID) ON DELETE SET NULL
+                                                ON UPDATE CASCADE
+);
+
+INSERT INTO parent (ID) VALUES (100);
+INSERT INTO child (ID,ParentID,Value) VALUES (123123,100,1);
+DELETE FROM parent WHERE ID=100;
+select * from child;
+INSERT INTO parent (ID) VALUES (100);
+UPDATE child SET ParentID=100 WHERE ID=123123;
+
+# Cleanup
+DROP TABLE child, parent;
+--echo #
+--echo # MDEV-23387 dict_load_foreign() fails to load the table during alter
+--echo #
+SET FOREIGN_KEY_CHECKS=0;
+CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
+		f3 INT AS (f1) VIRTUAL,
+		INDEX(f1), INDEX(f2))ENGINE=InnoDB;
+ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f2) REFERENCES t1(f1), LOCK=NONE;
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 DROP INDEX f1;
+ALTER TABLE t1 DROP f3;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB
+--echo #
+SET FOREIGN_KEY_CHECKS=1;
+CREATE DATABASE `a-b`;
+USE `a-b`;
+CREATE TABLE emails (
+  id int,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE email_stats (
+  id int,
+  email_id int,
+  date_sent char(4),
+  generated_email_id int as (email_id),
+  #generated_sent_date DATE GENERATED ALWAYS AS (date_sent),
+  PRIMARY KEY (id),
+  KEY mautic_generated_sent_date_email_id (generated_email_id),
+  FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
+  ON UPDATE CASCADE
+) ENGINE=InnoDB;
+
+
+CREATE TABLE emails_metadata (
+  email_id int,
+  PRIMARY KEY (email_id),
+  CONSTRAINT FK FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE CASCADE
+  ON UPDATE CASCADE
+) ENGINE=InnoDB;
+
+
+INSERT INTO emails VALUES (1);
+INSERT INTO email_stats (id, email_id,  date_sent) VALUES (1,1,'Jan');
+INSERT INTO emails_metadata VALUES (1);
+
+UPDATE emails SET id=2;
+DELETE FROM emails;
+
+DROP TABLE email_stats;
+DROP TABLE emails_metadata;
+DROP TABLE emails;
+DROP DATABASE `a-b`;