no_auto_value_on_zero + alter table bug

3 files changed, 29 insertions, 13 deletions

diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result
index 79bcff06f68..f5ec5f1f852 100644
--- a/mysql-test/r/auto_increment.result
+++ b/mysql-test/r/auto_increment.result
@@ -206,18 +206,25 @@ select * from t1 order by b;
 a	b
 1	1
 200	2
-205	3
+0	3
 201	4
 202	5
 203	6
 204	7
+create table t2 (a int);
+insert t2 values (1),(2);
+alter table t2 add b int auto_increment primary key;
+select * from t2;
+a	b
+1	1
+2	2
+drop table t2;
 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
-205	3
 201	4
 0	5
 203	6
@@ -225,7 +232,7 @@ a	b
 delete from t1 where a=0;
 update t1 set a=NULL where b=6;
 Warnings:
-Warning	1263	Data truncated; NULL supplied to NOT NULL column 'a' at row 5
+Warning	1263	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);
@@ -239,7 +246,6 @@ select * from t1 order by b;
 a	b
 1	1
 200	2
-205	3
 201	4
 0	6
 300	7
@@ -256,7 +262,6 @@ select * from t1 order by b;
 a	b
 1	1
 200	2
-205	3
 201	4
 300	7
 301	8
@@ -269,13 +274,12 @@ a	b
 delete from t1 where a=0;
 update t1 set a=NULL where b=13;
 Warnings:
-Warning	1263	Data truncated; NULL supplied to NOT NULL column 'a' at row 10
+Warning	1263	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
-205	3
 201	4
 300	7
 301	8
diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test
index 65770f32476..e5986e6755d 100644
--- a/mysql-test/t/auto_increment.test
+++ b/mysql-test/t/auto_increment.test
@@ -140,6 +140,11 @@ insert into t1(b)values(7);
 select * from t1 order by b;
 alter table t1 modify b mediumint;
 select * from t1 order by b;
+create table t2 (a int);
+insert t2 values (1),(2);
+alter table t2 add b int auto_increment primary key;
+select * from t2;
+drop table t2;
 delete from t1 where a=0;
 update t1 set a=0 where b=5;
 select * from t1 order by b;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8d82ca44951..7afbe6d0b87 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3271,13 +3271,12 @@ copy_data_between_tables(TABLE *from,TABLE *to,
 			 ha_rows *deleted)
 {
   int error;
-  Copy_field *copy,*copy_end;
+  Copy_field *copy,*copy_end, *next_field;
   ulong found_count,delete_count;
   THD *thd= current_thd;
   uint length;
   SORT_FIELD *sortorder;
   READ_RECORD info;
-  Field *next_field;
   TABLE_LIST   tables;
   List<Item>   fields;
   List<Item>   all_fields;
@@ -3298,7 +3297,12 @@ copy_data_between_tables(TABLE *from,TABLE *to,
   {
     def=it++;
     if (def->field)
+    {
+      if (*ptr == to->next_number_field)
+        next_field= copy_end;
       (copy_end++)->set(*ptr,def->field,0);
+    }
+
   }
 
   found_count=delete_count=0;
@@ -3334,7 +3338,7 @@ copy_data_between_tables(TABLE *from,TABLE *to,
     error= 1;
     goto err;
   }
-  
+
   /* Handler must be told explicitly to retrieve all columns, because
      this function does not set field->query_id in the columns to the
      current query id */
@@ -3343,7 +3347,6 @@ copy_data_between_tables(TABLE *from,TABLE *to,
   if (handle_duplicates == DUP_IGNORE ||
       handle_duplicates == DUP_REPLACE)
     to->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
-  next_field=to->next_number_field;
   thd->row_count= 0;
   while (!(error=info.read_record(&info)))
   {
@@ -3354,10 +3357,14 @@ copy_data_between_tables(TABLE *from,TABLE *to,
       break;
     }
     thd->row_count++;
-    if (next_field)
-      next_field->reset();
+    if (to->next_number_field)
+      to->next_number_field->reset();
     for (Copy_field *copy_ptr=copy ; copy_ptr != copy_end ; copy_ptr++)
+    {
+      if (copy_ptr == next_field)
+        to->auto_increment_field_not_null= TRUE;
       copy_ptr->do_copy(copy_ptr);
+    }
     if ((error=to->file->write_row((byte*) to->record[0])))
     {
       if ((handle_duplicates != DUP_IGNORE &&