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

1 files changed, 75 insertions, 12 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;