diff options
Diffstat (limited to 'mysql-test/suite/gcol/r/innodb_virtual_fk.result')
| -rw-r--r-- | mysql-test/suite/gcol/r/innodb_virtual_fk.result | 1028 |
1 files changed, 1028 insertions, 0 deletions
diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk.result b/mysql-test/suite/gcol/r/innodb_virtual_fk.result new file mode 100644 index 00000000..97058bc2 --- /dev/null +++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result @@ -0,0 +1,1028 @@ +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 constraint. 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 NULL DEFAULT NULL, + `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 COLLATE=latin1_swedish_ci +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 NULL DEFAULT NULL, + `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 COLLATE=latin1_swedish_ci +DROP TABLE t1; +# +# MDEV-20396 Server crashes after DELETE with SEL NULL Foreign key and a +# virtual column in index +# +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; +ID ParentID Value Flag +123123 NULL 1 1 +INSERT INTO parent (ID) VALUES (100); +UPDATE child SET ParentID=100 WHERE ID=123123; +DROP TABLE child, parent; +# +# MDEV-23387 dict_load_foreign() fails to load the table during alter +# +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f2` int(11) NOT NULL, + `f3` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL, + KEY `f1` (`f1`), + KEY `f2` (`f2`), + CONSTRAINT `r` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +ALTER TABLE t1 DROP INDEX f1; +ALTER TABLE t1 DROP f3; +DROP TABLE t1; +# +# MDEV-24041 Generated column DELETE with FOREIGN KEY crash InnoDB +# +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; +# +# Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE +# +# 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; +id email_id date_sent generated_sent_date +1 1 2020-10-22 13:32:41 2020-10-22 +DELETE FROM emails; +DELETE FROM email_stats; +# Clean up. +DROP TABLE email_stats; +DROP TABLE emails; +# 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; +id +SELECT * FROM email_stats; +id email_id date_sent generated_sent_date +1 NULL 2020-10-22 13:32:41 2020-10-22 +UPDATE email_stats +SET email_id=2 +WHERE DATE(generated_sent_date) = '2020-10-22'; +SELECT * FROM email_stats; +id email_id date_sent generated_sent_date +1 2 2020-10-22 13:32:41 2020-10-22 +# Clean up. +DROP TABLE email_stats; +DROP TABLE emails; +# 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; +id email_id date_sent generated_sent_email +1 1 2020-10-22 13:32:41 2020-1 +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; +date_sent +2020-10-22 13:32:41 +DELETE FROM emails; +SELECT * FROM email_stats; +id email_id date_sent generated_sent_email +1 NULL 2020-10-22 13:32:41 2020-$ +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$'; +date_sent +2020-10-22 13:32:41 +# Clean up. +DROP TABLE email_stats; +DROP TABLE emails; +# 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; +id email_id date_sent generated_sent_email +1 1 2020-10-22 13:32:41 2020-1 +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; +date_sent +2020-10-22 13:32:41 +UPDATE emails SET id = 2 WHERE id = 1; +SELECT * FROM email_stats; +id email_id date_sent generated_sent_email +1 NULL 2020-10-22 13:32:41 2020-$ +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-$'; +date_sent +2020-10-22 13:32:41 +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; +Table Create Table +email_stats CREATE TABLE `email_stats` ( + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `email_id` int(10) unsigned DEFAULT NULL, + `date_sent` datetime NOT NULL, + `generated_sent_email` varchar(20) GENERATED ALWAYS AS (concat(year(`date_sent`),'-',coalesce(`email_id`,'$'))) VIRTUAL, + PRIMARY KEY (`id`), + KEY `idx_es1` (`email_id`), + KEY `mautic_generated_sent_date_email` (`generated_sent_email`,`email_id`), + CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES `emails` (`breaker`) ON DELETE SET NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +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; +id email_id date_sent generated_sent_email +1 1 2020-10-22 13:32:41 2020-1 +SELECT date_sent FROM email_stats WHERE generated_sent_email = '2020-1'; +date_sent +2020-10-22 13:32:41 +DELETE FROM emails; +SELECT * FROM email_stats; +id email_id date_sent generated_sent_email +1 NULL 2020-10-22 13:32:41 2020-$ +SELECT date_sent +FROM email_stats force index (mautic_generated_sent_date_email) +WHERE generated_sent_email = '2020-$'; +date_sent +2020-10-22 13:32:41 +SELECT date_sent +FROM email_stats force index (idx_es1) +WHERE generated_sent_email = '2020-$'; +date_sent +2020-10-22 13:32:41 +DROP TABLE email_stats; +DROP TABLE emails; |