path: root/mysql-test/suite/gcol/r/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 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 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;
#
# 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
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
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;