MDEV-22118: handler::update_auto_increment(): Assertion `nr != 0' failed. | SIGABRT in handler::update_auto_incrementbb-10.2-MDEV-22118

Changed insert_id default and range to exclude 0 (because it is special value)

13 files changed, 125 insertions, 95 deletions

diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index bd61d73f08c..c16f8212125 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -687,5 +687,15 @@ id	name
 2	cat
 DROP PROCEDURE autoinc_mdev15353_one;
 #
+# MDEV-22118: handler::update_auto_increment(): Assertion `nr != 0'
+# failed. | SIGABRT in handler::update_auto_increment
+#
+CREATE TABLE t (c INT AUTO_INCREMENT KEY);
+SET @@SESSION.insert_id=-0;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '0'
+INSERT INTO t VALUES(0);
+drop table t;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc
index 4392d04db8a..cf2a3421f66 100644
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc
@@ -115,21 +115,21 @@ eval CREATE TABLE t1
  UNIQUE KEY (a))
 ENGINE=$engine;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 }
 
 -- echo # Simple test with NULL
@@ -845,17 +845,17 @@ eval CREATE TABLE t1
 ENGINE=$engine
 PARTITION BY KEY(a) PARTITIONS 2;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 SELECT * FROM t1;
 DROP TABLE t1;
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
index 0276385dc29..5632fa8d457 100644
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
@@ -125,33 +125,33 @@ CREATE TABLE t1
 UNIQUE KEY (a))
 ENGINE='Blackhole';
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 # Simple test with NULL
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -165,7 +165,7 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`c1`)
-) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+) ENGINE=BLACKHOLE AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  PARTITION BY HASH (`c1`)
 PARTITIONS 2
 SELECT * FROM t1;
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result
index 76f1ddfceae..1e9652ce465 100644
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result
@@ -141,29 +141,29 @@ CREATE TABLE t1
 UNIQUE KEY (a))
 ENGINE='InnoDB';
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
@@ -171,7 +171,7 @@ SELECT * FROM t1;
 a
 0
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 # Simple test with NULL
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -185,12 +185,12 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  PARTITION BY HASH (`c1`)
 PARTITIONS 2
 SELECT * FROM t1;
 c1
-1
+2
 DROP TABLE t1;
 # Test with sql_mode and first insert as 0
 CREATE TABLE t1 (
@@ -1071,29 +1071,29 @@ UNIQUE KEY (a))
 ENGINE='InnoDB'
 PARTITION BY KEY(a) PARTITIONS 2;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_maria.result b/mysql-test/suite/parts/r/partition_auto_increment_maria.result
index 5a3902475a9..641cb64deaf 100644
--- a/mysql-test/suite/parts/r/partition_auto_increment_maria.result
+++ b/mysql-test/suite/parts/r/partition_auto_increment_maria.result
@@ -141,29 +141,29 @@ CREATE TABLE t1
 UNIQUE KEY (a))
 ENGINE='Aria';
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
@@ -171,7 +171,7 @@ SELECT * FROM t1;
 a
 0
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 # Simple test with NULL
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -185,12 +185,12 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`c1`)
-) ENGINE=Aria AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+) ENGINE=Aria AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  PARTITION BY HASH (`c1`)
 PARTITIONS 2
 SELECT * FROM t1;
 c1
-1
+2
 DROP TABLE t1;
 # Test with sql_mode and first insert as 0
 CREATE TABLE t1 (
@@ -1118,29 +1118,29 @@ UNIQUE KEY (a))
 ENGINE='Aria'
 PARTITION BY KEY(a) PARTITIONS 2;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_memory.result b/mysql-test/suite/parts/r/partition_auto_increment_memory.result
index c395f8ed0c9..0ca34e3168f 100644
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result
@@ -141,29 +141,29 @@ CREATE TABLE t1
 UNIQUE KEY (a))
 ENGINE='Memory';
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
@@ -171,7 +171,7 @@ SELECT * FROM t1;
 a
 0
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 # Simple test with NULL
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -185,12 +185,12 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`c1`)
-) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  PARTITION BY HASH (`c1`)
 PARTITIONS 2
 SELECT * FROM t1;
 c1
-1
+2
 DROP TABLE t1;
 # Test with sql_mode and first insert as 0
 CREATE TABLE t1 (
@@ -1099,29 +1099,29 @@ UNIQUE KEY (a))
 ENGINE='Memory'
 PARTITION BY KEY(a) PARTITIONS 2;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
diff --git a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result
index 792423096b5..c0599b58e28 100644
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result
@@ -141,29 +141,29 @@ CREATE TABLE t1
 UNIQUE KEY (a))
 ENGINE='MyISAM';
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
@@ -171,7 +171,7 @@ SELECT * FROM t1;
 a
 0
 DROP TABLE t1;
-SET INSERT_ID = 1;
+SET INSERT_ID = 2;
 # Simple test with NULL
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -185,12 +185,12 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
  PARTITION BY HASH (`c1`)
 PARTITIONS 2
 SELECT * FROM t1;
 c1
-1
+2
 DROP TABLE t1;
 # Test with sql_mode and first insert as 0
 CREATE TABLE t1 (
@@ -1118,29 +1118,29 @@ UNIQUE KEY (a))
 ENGINE='MyISAM'
 PARTITION BY KEY(a) PARTITIONS 2;
 SET LAST_INSERT_ID = 999;
-SET INSERT_ID = 0;
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SET INSERT_ID = 1;
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
-1
-INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+2
+INSERT INTO t1 SET a = 2 ON DUPLICATE KEY UPDATE a = NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = 1 WHERE a IS NULL;
+UPDATE t1 SET a = 2 WHERE a IS NULL;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
 SELECT * FROM t1;
 a
 0
-UPDATE t1 SET a = NULL WHERE a = 1;
+UPDATE t1 SET a = NULL WHERE a = 2;
 SELECT LAST_INSERT_ID();
 LAST_INSERT_ID()
 999
diff --git a/mysql-test/suite/sys_vars/r/insert_id_basic.result b/mysql-test/suite/sys_vars/r/insert_id_basic.result
index 318563f7e42..43b2c6576b4 100644
--- a/mysql-test/suite/sys_vars/r/insert_id_basic.result
+++ b/mysql-test/suite/sys_vars/r/insert_id_basic.result
@@ -10,14 +10,18 @@ SELECT @@session.insert_id;
 99
 '#---------------------FN_DYNVARS_051_02-------------------------#'
 SET @@session.insert_id = @start_value;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '0'
 SELECT @@session.insert_id = 0;
 @@session.insert_id = 0
-1
+0
 '#--------------------FN_DYNVARS_051_03------------------------#'
 SET @@session.insert_id = 0;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '0'
 SELECT @@session.insert_id;
 @@session.insert_id
-0
+1
 SET @@session.insert_id = 1099;
 SELECT @@session.insert_id;
 @@session.insert_id
@@ -36,7 +40,7 @@ Warnings:
 Warning	1292	Truncated incorrect insert_id value: '-1'
 SELECT @@session.insert_id;
 @@session.insert_id
-0
+1
 SET @@session.insert_id = 100000000000;
 SELECT @@session.insert_id;
 @@session.insert_id
@@ -51,7 +55,7 @@ Warnings:
 Warning	1292	Truncated incorrect insert_id value: '-1024'
 SELECT @@session.insert_id;
 @@session.insert_id
-0
+1
 SET @@session.insert_id = 42949672950;
 SELECT @@session.insert_id;
 @@session.insert_id
@@ -92,9 +96,11 @@ SELECT @@session.insert_id;
 @@session.insert_id
 1
 SET @@session.insert_id = FALSE;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '0'
 SELECT @@session.insert_id;
 @@session.insert_id
-0
+1
 '#---------------------FN_DYNVARS_051_09----------------------#'
 SET @@session.insert_id = 1;
 SELECT @@insert_id = @@session.insert_id;
@@ -119,6 +125,8 @@ ERROR 42S02: Unknown table 'session' in field list
 SELECT insert_id = @@session.insert_id;
 ERROR 42S22: Unknown column 'insert_id' in 'field list'
 SET @@session.insert_id = @start_value;
+Warnings:
+Warning	1292	Truncated incorrect insert_id value: '0'
 SELECT @@session.insert_id;
 @@session.insert_id
-0
+1
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 42c236bf54a..09d0fc15fcd 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -1256,7 +1256,7 @@ VARIABLE_NAME	INSERT_ID
 VARIABLE_SCOPE	SESSION ONLY
 VARIABLE_TYPE	BIGINT UNSIGNED
 VARIABLE_COMMENT	The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value
-NUMERIC_MIN_VALUE	0
+NUMERIC_MIN_VALUE	1
 NUMERIC_MAX_VALUE	18446744073709551615
 NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 1c359ec5268..c75fff54bf6 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -430,6 +430,16 @@ SET @engine='MyISAM';
 --source include/autoinc_mdev15353.inc
 
 --echo #
+--echo # MDEV-22118: handler::update_auto_increment(): Assertion `nr != 0'
+--echo # failed. | SIGABRT in handler::update_auto_increment
+--echo #
+
+CREATE TABLE t (c INT AUTO_INCREMENT KEY);
+SET @@SESSION.insert_id=-0;  # Or -1, -2 etc.
+INSERT INTO t VALUES(0);
+drop table t;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/sql/handler.cc b/sql/handler.cc
index 87592beb5d3..d8ccee8847e 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -3231,6 +3231,7 @@ int handler::update_auto_increment()
   if (unlikely(nr == ULONGLONG_MAX))
       DBUG_RETURN(HA_ERR_AUTOINC_ERANGE);
 
+  // 0 (and NULL) is special value meaning put next autoincrement value
   DBUG_ASSERT(nr != 0);
   DBUG_PRINT("info",("auto_increment: %llu  nb_reserved_values: %llu",
                      nr, append ? nb_reserved_values : 0));
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index e4de3d8d0aa..013ab421c84 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -4029,7 +4029,7 @@ static ulonglong read_last_insert_id(THD *thd)
 static Sys_var_session_special Sys_last_insert_id(
        "last_insert_id", "The value to be returned from LAST_INSERT_ID()",
        sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1),
+       VALID_RANGE(0, ULONGLONG_MAX), DEFAULT(0), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_last_insert_id), ON_READ(read_last_insert_id));
 
@@ -4037,7 +4037,7 @@ static Sys_var_session_special Sys_last_insert_id(
 static Sys_var_session_special Sys_identity(
        "identity", "Synonym for the last_insert_id variable",
        sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1),
+       VALID_RANGE(0, ULONGLONG_MAX), DEFAULT(0), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_last_insert_id), ON_READ(read_last_insert_id));
 
@@ -4079,7 +4079,7 @@ static Sys_var_session_special Sys_insert_id(
        "insert_id", "The value to be used by the following INSERT "
        "or ALTER TABLE statement when inserting an AUTO_INCREMENT value",
        sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1),
+       VALID_RANGE(1, ULONGLONG_MAX), DEFAULT(1), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_insert_id), ON_READ(read_insert_id));
 
@@ -4101,7 +4101,7 @@ static Sys_var_session_special Sys_rand_seed1(
        "rand_seed1", "Sets the internal state of the RAND() "
        "generator for replication purposes",
        sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONG_MAX), BLOCK_SIZE(1),
+       VALID_RANGE(0, ULONG_MAX), DEFAULT(0), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_rand_seed1), ON_READ(read_rand_seed1));
 
@@ -4123,7 +4123,7 @@ static Sys_var_session_special Sys_rand_seed2(
        "rand_seed2", "Sets the internal state of the RAND() "
        "generator for replication purposes",
        sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONG_MAX), BLOCK_SIZE(1),
+       VALID_RANGE(0, ULONG_MAX), DEFAULT(0), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_rand_seed2), ON_READ(read_rand_seed2));
 
@@ -4136,7 +4136,7 @@ static Sys_var_session_special Sys_error_count(
        "error_count", "The number of errors that resulted from the "
        "last statement that generated messages",
        READ_ONLY sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1), NO_MUTEX_GUARD,
+       VALID_RANGE(0, ULONGLONG_MAX), DEFAULT(0), BLOCK_SIZE(1), NO_MUTEX_GUARD,
        NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(0), ON_READ(read_error_count));
 
 static ulonglong read_warning_count(THD *thd)
@@ -4148,7 +4148,7 @@ static Sys_var_session_special Sys_warning_count(
        "warning_count", "The number of errors, warnings, and notes "
        "that resulted from the last statement that generated messages",
        READ_ONLY sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1), NO_MUTEX_GUARD,
+       VALID_RANGE(0, ULONGLONG_MAX), DEFAULT(0), BLOCK_SIZE(1), NO_MUTEX_GUARD,
        NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(0), ON_READ(read_warning_count));
 
 static Sys_var_ulong Sys_default_week_format(
@@ -5577,7 +5577,7 @@ static ulonglong in_transaction(THD *thd)
 static Sys_var_session_special Sys_in_transaction(
        "in_transaction", "Whether there is an active transaction",
        READ_ONLY sys_var::ONLY_SESSION, NO_CMD_LINE,
-       VALID_RANGE(0, 1), BLOCK_SIZE(1), NO_MUTEX_GUARD,
+       VALID_RANGE(0, 1), DEFAULT(0), BLOCK_SIZE(1), NO_MUTEX_GUARD,
        NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(0), ON_READ(in_transaction));
 
 #ifndef DBUG_OFF
diff --git a/sql/sys_vars.ic b/sql/sys_vars.ic
index 3c3cfb8c8a6..c3dade26cd1 100644
--- a/sql/sys_vars.ic
+++ b/sql/sys_vars.ic
@@ -1757,7 +1757,8 @@ public:
   Sys_var_session_special(const char *name_arg,
                const char *comment, int flag_args,
                CMD_LINE getopt,
-               ulonglong min_val, ulonglong max_val, uint block_size,
+               ulonglong min_val, ulonglong max_val, ulonglong def,
+               uint block_size,
                PolyLock *lock, enum binlog_status_enum binlog_status_arg,
                on_check_function on_check_func,
                session_special_update_function update_func_arg,
@@ -1765,7 +1766,7 @@ public:
                const char *substitute=0)
     : Sys_var_ulonglong(name_arg, comment, flag_args, 0,
               sizeof(ulonglong), getopt, min_val,
-              max_val, 0, block_size, lock, binlog_status_arg, on_check_func, 0,
+              max_val, def, block_size, lock, binlog_status_arg, on_check_func, 0,
               substitute),
       read_func(read_func_arg), update_func(update_func_arg)
   {