MDEV-22711 Assertion `nr != 0' failed in handler::update_auto_increment.bb-10.2-mdev-22711-hf

DBUG_ASSERT removed as the AUTO INCREMENT can actually be 0 when the
SET insert_id= 0; was done.

3 files changed, 57 insertions, 6 deletions

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;
+
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index f231f49a667..bd4b2fbb062 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -4064,12 +4064,16 @@ static Sys_var_session_special Sys_identity(
 */
 static bool update_insert_id(THD *thd, set_var *var)
 {
-  if (!var->value)
-  {
-    my_error(ER_NO_DEFAULT, MYF(0), var->var->name.str);
-    return true;
-  }
-  thd->force_one_auto_inc_interval(var->save_result.ulonglong_value);
+  /*
+    If we set the insert_id to the DEFAULT or 0
+    it means we 'reset' it so it's value doesn't
+    affect the INSERT.
+  */
+  if (!var->value ||
+      var->save_result.ulonglong_value == 0)
+    thd->auto_inc_intervals_forced.empty();
+  else
+    thd->force_one_auto_inc_interval(var->save_result.ulonglong_value);
   return false;
 }
 
@@ -4077,6 +4081,8 @@ static ulonglong read_insert_id(THD *thd)
 {
   return thd->auto_inc_intervals_forced.minimum();
 }
+
+
 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",