changed for AUTO_VALUE_ON_ZERO

mysql-test/r/auto_increment.result:
  added test for AUTO_VALUE_ON_ZERO
mysql-test/t/auto_increment.test:
  added test for AUTO_VALUE_ON_ZERO

7 files changed, 111 insertions, 29 deletions

diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 99e5e596fd3..03cfcc50e17 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -150,28 +150,91 @@ select last_insert_id();
 last_insert_id()
 0
 drop table t1;
-drop table if exists t1;
-Warnings:
-Note	1051	Unknown table 't1'
 create table t1(a int auto_increment,b int null,primary key(a));
 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
 insert into t1(a,b)values(NULL,1);
 insert into t1(a,b)values(200,2);
 insert into t1(a,b)values(0,3);
 insert into t1(b)values(4);
-SET SQL_MODE='';
-insert into t1(a,b)values(NULL,5);
-insert into t1(a,b)values(300,6);
-insert into t1(a,b)values(0,7);
-insert into t1(b)values(8);
-select * from t1;
+insert into t1(b)values(5);
+insert into t1(b)values(6);
+insert into t1(b)values(7);
+select * from t1 order by b;
 a	b
 1	1
 200	2
 0	3
 201	4
 202	5
-300	6
-301	7
-302	8
+203	6
+204	7
+delete from t1 where a=0;
+update t1 set a=0 where b=5;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+0	5
+203	6
+204	7
+delete from t1 where a=0;
+update t1 set a=NULL where b=6;
+Warnings:
+Warning	1261	Data truncated, NULL supplied to NOT NULL column 'a' at row 4
+update t1 set a=300 where b=7;
+SET SQL_MODE='';
+insert into t1(a,b)values(NULL,8);
+insert into t1(a,b)values(400,9);
+insert into t1(a,b)values(0,10);
+insert into t1(b)values(11);
+insert into t1(b)values(12);
+insert into t1(b)values(13);
+insert into t1(b)values(14);
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+0	6
+300	7
+301	8
+400	9
+401	10
+402	11
+403	12
+404	13
+405	14
+delete from t1 where a=0;
+update t1 set a=0 where b=12;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+300	7
+301	8
+400	9
+401	10
+402	11
+0	12
+404	13
+405	14
+delete from t1 where a=0;
+update t1 set a=NULL where b=13;
+Warnings:
+Warning	1261	Data truncated, NULL supplied to NOT NULL column 'a' at row 9
+update t1 set a=500 where b=14;
+select * from t1 order by b;
+a	b
+1	1
+200	2
+201	4
+300	7
+301	8
+400	9
+401	10
+402	11
+0	13
+500	14
 drop table t1;
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index e187d42625e..b46993ec6dd 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -106,17 +106,36 @@ select last_insert_id();
 
 drop table t1;
 
-drop table if exists t1;
 create table t1(a int auto_increment,b int null,primary key(a));
 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
 insert into t1(a,b)values(NULL,1);
 insert into t1(a,b)values(200,2);
 insert into t1(a,b)values(0,3);
 insert into t1(b)values(4);
+insert into t1(b)values(5);
+insert into t1(b)values(6);
+insert into t1(b)values(7);
+select * from t1 order by b;
+delete from t1 where a=0;
+update t1 set a=0 where b=5;
+select * from t1 order by b;
+delete from t1 where a=0;
+update t1 set a=NULL where b=6;
+update t1 set a=300 where b=7;
 SET SQL_MODE='';
-insert into t1(a,b)values(NULL,5);
-insert into t1(a,b)values(300,6);
-insert into t1(a,b)values(0,7);
-insert into t1(b)values(8);
-select * from t1;
+insert into t1(a,b)values(NULL,8);
+insert into t1(a,b)values(400,9);
+insert into t1(a,b)values(0,10);
+insert into t1(b)values(11);
+insert into t1(b)values(12);
+insert into t1(b)values(13);
+insert into t1(b)values(14);
+select * from t1 order by b;
+delete from t1 where a=0;
+update t1 set a=0 where b=12;
+select * from t1 order by b;
+delete from t1 where a=0;
+update t1 set a=NULL where b=13;
+update t1 set a=500 where b=14;
+select * from t1 order by b;
 drop table t1;
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index 40e423a5aca..44e30afb880 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -173,7 +173,7 @@ set_field_to_null_with_conversions(Field *field, bool no_conversions)
   field->reset();
   if (field == field->table->next_number_field)
   {
-    field->table->auto_increment_field_is_null= true;
+    field->table->auto_increment_field_not_null= false;
     return 0;					// field is set in handler.cc
   }
   if (current_thd->count_cuted_fields)
diff --git a/sql/handler.cc b/sql/handler.cc
index 7f4b63e7551..90fd754a4c5 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -697,14 +697,15 @@ void handler::update_auto_increment()
   longlong nr;
   THD *thd;
   DBUG_ENTER("update_auto_increment");
-  if (table->auto_increment_field_is_null)
-    table->auto_increment_field_is_null= false;
-  else if (table->next_number_field->val_int() != 0 ||
-	   current_thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
+  if (table->next_number_field->val_int() != 0 ||
+      table->auto_increment_field_not_null &&
+      current_thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
   {
+    table->auto_increment_field_not_null= false;
     auto_increment_column_changed=0;
     DBUG_VOID_RETURN;
   }
+  table->auto_increment_field_not_null= false;
   thd=current_thd;
   if ((nr=thd->next_insert_id))
     thd->next_insert_id=0;			// Clear after use
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a7798588d59..dbb29cf53e3 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2247,8 +2247,8 @@ fill_record(List<Item> &fields,List<Item> &values, bool ignore_errors)
     value=v++;
     Field *rfield= field->field;
     TABLE *table= rfield->table;
-    if (rfield==table->next_number_field)
-      table->auto_increment_field_is_null= false;
+    if (rfield == table->next_number_field)
+      table->auto_increment_field_not_null= true;
     if (value->save_in_field(rfield, 0) > 0 && !ignore_errors)
       DBUG_RETURN(1);
   }
@@ -2268,8 +2268,8 @@ fill_record(Field **ptr,List<Item> &values, bool ignore_errors)
   {
     value=v++;
     TABLE *table= field->table;
-    if (field==table->next_number_field)
-      table->auto_increment_field_is_null= false;
+    if (field == table->next_number_field)
+      table->auto_increment_field_not_null= true;
     if (value->save_in_field(field, 0) == 1 && !ignore_errors)
       DBUG_RETURN(1);
   }
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index bddaffd3f4f..947205949f1 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -253,7 +253,6 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list,
   else
     bulk_insert=0;
 
-  table->auto_increment_field_is_null= true;
   while ((values= its++))
   {
     if (fields.elements || !value_count)
diff --git a/sql/table.h b/sql/table.h
index 8137f47aa1f..1831c834de6 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -116,7 +116,7 @@ struct st_table {
   my_bool crashed;
   my_bool is_view;
   my_bool no_keyread;
-  my_bool auto_increment_field_is_null;     
+  my_bool auto_increment_field_not_null;     
   Field *next_number_field,		/* Set if next_number is activated */
 	*found_next_number_field,	/* Set on open */
         *rowid_field;