diff options
Diffstat (limited to 'mysql-test/suite/gcol/t/innodb_virtual_fk.test')
| -rw-r--r-- | mysql-test/suite/gcol/t/innodb_virtual_fk.test | 184 |
1 files changed, 184 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 index c99259531b3..0f0406b5dd4 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_fk.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_fk.test @@ -693,3 +693,187 @@ 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; |