-- 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`;
USE test;

--echo #
--echo # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
--echo #

--echo # Test-Case 1
CREATE TABLE emails (
  id int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE email_stats (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  email_id int unsigned DEFAULT NULL,
  date_sent datetime NOT NULL,
  generated_sent_date date GENERATED ALWAYS AS
    (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
            '-', lpad(dayofmonth(date_sent), 2, '0'))),
  PRIMARY KEY (id),
  KEY IDX_ES1 (email_id),
  KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
  FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
) ENGINE = InnoDB;

INSERT INTO emails VALUES (1);
INSERT INTO email_stats (id, email_id, date_sent)
  VALUES (1, 1, '2020-10-22 13:32:41');
SELECT * FROM email_stats;

DELETE FROM emails;
DELETE FROM email_stats;

--echo # Clean up.
DROP TABLE email_stats;
DROP TABLE emails;

--echo # Test-Case 2
CREATE TABLE emails (
  id int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC;

CREATE TABLE email_stats (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  email_id int unsigned DEFAULT NULL,
  date_sent datetime NOT NULL,
  generated_sent_date date GENERATED ALWAYS AS
    (concat(year(date_sent), '-', lpad(month(date_sent), 2, '0'),
            '-', lpad(dayofmonth(date_sent), 2, '0'))),
  PRIMARY KEY (id),
  KEY IDX_ES1 (email_id),
  KEY mautic_generated_sent_date_email_id(generated_sent_date, email_id),
  FOREIGN KEY (email_id) REFERENCES emails (id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
) ENGINE = InnoDB;

INSERT INTO emails VALUES (1);
INSERT INTO email_stats (id, email_id, date_sent)
  VALUES (1, 1, '2020-10-22 13:32:41');

UPDATE emails SET id = 2 where id = 1;

SELECT id FROM email_stats WHERE generated_sent_date IS NULL;
SELECT * FROM email_stats;
UPDATE email_stats
  SET email_id=2
  WHERE DATE(generated_sent_date) = '2020-10-22';
SELECT * FROM email_stats;

--echo # Clean up.
DROP TABLE email_stats;
DROP TABLE emails;

--echo # Test-case 3
CREATE TABLE emails (
  id int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci
  ROW_FORMAT = DYNAMIC;
CREATE TABLE email_stats (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  email_id int unsigned DEFAULT NULL,
  date_sent datetime NOT NULL,
  generated_sent_email varchar(20) GENERATED ALWAYS AS
    (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
  PRIMARY KEY (id),
  KEY idx_es1 (email_id),
  KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
  FOREIGN KEY (email_id) REFERENCES emails (id) ON DELETE SET NULL
) ENGINE = INNODB;

INSERT INTO emails VALUES (1);
INSERT INTO email_stats (id, email_id, date_sent)
  VALUES (1, 1, '2020-10-22 13:32:41');
SELECT * FROM email_stats;
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';

DELETE FROM emails;

SELECT * FROM email_stats;
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';

--echo # Clean up.
DROP TABLE email_stats;
DROP TABLE emails;

--echo # Test-case 4
CREATE TABLE emails (
  id int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE email_stats (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  email_id int unsigned DEFAULT NULL,
  date_sent datetime NOT NULL,
  generated_sent_email varchar(20) GENERATED ALWAYS AS
    (CONCAT(YEAR(date_sent), '-', COALESCE(email_id, '$'))),
  PRIMARY KEY (id),
  KEY idx_es1 (email_id),
  KEY mautic_generated_sent_date_email(generated_sent_email, email_id),
  FOREIGN KEY (email_id) REFERENCES emails (id) ON UPDATE SET NULL
) ENGINE = INNODB;

INSERT INTO emails VALUES (1);
INSERT INTO email_stats (id, email_id, date_sent)
  VALUES (1, 1, '2020-10-22 13:32:41');
SELECT * FROM email_stats;
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';

UPDATE emails SET id = 2 WHERE id = 1;

SELECT * FROM email_stats;
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$';

#clean up.
DROP TABLE email_stats;
DROP TABLE emails;

CREATE TABLE emails (breaker int unsigned,
                     KEY (breaker),
                     id int unsigned NOT NULL AUTO_INCREMENT,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE email_stats (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    email_id int unsigned DEFAULT NULL,
    date_sent datetime NOT NULL,
    generated_sent_email varchar(20) GENERATED ALWAYS AS
                  (CONCAT(YEAR(date_sent),
                          '-',
                          COALESCE(email_id, '$'))),
    PRIMARY KEY (id),
    KEY idx_es1 (email_id),
    KEY mautic_generated_sent_date_email (generated_sent_email, email_id),
    FOREIGN KEY fk_ea1 (email_id) REFERENCES emails (breaker)
        ON DELETE SET NULL
) ENGINE=INNODB;

show create table email_stats;
INSERT INTO emails VALUES (1,1);
INSERT INTO email_stats(id, email_id, date_sent)
        VALUES (1, 1, '2020-10-22 13:32:41');
SELECT * FROM email_stats;
SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1';
DELETE FROM emails;
SELECT * FROM email_stats;
SELECT date_sent
    FROM email_stats force index (mautic_generated_sent_date_email)
    WHERE generated_sent_email = '2020-$';
SELECT date_sent
    FROM email_stats force index (idx_es1)
    WHERE generated_sent_email = '2020-$';

DROP TABLE email_stats;
DROP TABLE emails;