Merge 10.2 into 10.3

2 files changed, 384 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
index 367ed1223f7..de61c16f739 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_fk.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_fk.result
@@ -826,3 +826,203 @@ 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;
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;