Merge 10.2 into 10.3

7 files changed, 436 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;
diff --git a/mysql-test/suite/mariabackup/partition_partial.test b/mysql-test/suite/mariabackup/partition_partial.test
index 8e288ec997f..7ccc42c036c 100644
--- a/mysql-test/suite/mariabackup/partition_partial.test
+++ b/mysql-test/suite/mariabackup/partition_partial.test
@@ -1,4 +1,5 @@
 #--source include/innodb_page_size.inc
+--source include/have_partition.inc
 
 # import partitioned table from  table from partial backup
 
diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result
index 0f63b2600e3..7f88297a121 100644
--- a/mysql-test/suite/plugins/r/server_audit.result
+++ b/mysql-test/suite/plugins/r/server_audit.result
@@ -265,6 +265,9 @@ A	B	C	D
 A	B	C	D
 set global server_audit_query_log_limit= 1024;
 drop database sa_db;
+select length('01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');
+length('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456
+2750
 set global server_audit_file_path='.';
 show status like 'server_audit_current_log';
 Variable_name	Value
@@ -489,6 +492,7 @@ TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,proc,
 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proc,
 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,event,
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop database sa_db',0
+TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select length(\'012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0
 TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0
diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test
index 2c5acfad7ca..e7ca54cffb2 100644
--- a/mysql-test/suite/plugins/t/server_audit.test
+++ b/mysql-test/suite/plugins/t/server_audit.test
@@ -216,6 +216,8 @@ select 'A', 'B', 'C', 'D';
 set global server_audit_query_log_limit= 1024;
 drop database sa_db;
 
+select length('01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789');
+
 set global server_audit_file_path='.';
 --replace_regex /\.[\\\/]/HOME_DIR\//
 show status like 'server_audit_current_log';
diff --git a/mysql-test/suite/sys_vars/r/insert_id_func.result b/mysql-test/suite/sys_vars/r/insert_id_func.result
index 6a5746e130d..e4486cd5acf 100644
--- a/mysql-test/suite/sys_vars/r/insert_id_func.result
+++ b/mysql-test/suite/sys_vars/r/insert_id_func.result
@@ -69,3 +69,28 @@ id	name
 drop table t1;
 disconnect test_con1;
 disconnect test_con2;
+connection default;
+CREATE TABLE t1(id int primary key auto_increment);
+SET SESSION insert_id=123;
+SET SESSION insert_id=0;
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=default;
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=-1;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '-1'
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=-10;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '-10'
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+id
+1
+2
+3
+4
+DROP TABLE t1;
diff --git a/mysql-test/suite/sys_vars/t/insert_id_func.test b/mysql-test/suite/sys_vars/t/insert_id_func.test
index a710afbb0cc..feb510c091b 100644
--- a/mysql-test/suite/sys_vars/t/insert_id_func.test
+++ b/mysql-test/suite/sys_vars/t/insert_id_func.test
@@ -103,3 +103,23 @@ drop table t1;
 
 disconnect test_con1;
 disconnect test_con2;
+
+# MDEV-22711 Assertion `nr != 0' failed in handler::update_auto_increment.
+#
+connection default;
+CREATE TABLE t1(id int primary key auto_increment);
+SET SESSION insert_id=123;
+SET SESSION insert_id=0;
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=default;
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=-1;
+INSERT INTO t1 VALUES ();
+SET SESSION insert_id=123;
+SET SESSION insert_id=-10;
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+DROP TABLE t1;
+