Merge with 4.0.13

BitKeeper/etc/gone:
  auto-union
BitKeeper/etc/logging_ok:
  auto-union
myisam/sort.c:
  Auto merged
mysql-test/r/group_by.result:
  Auto merged
mysql-test/r/join_outer.result:
  Auto merged
mysql-test/r/order_by.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
mysql-test/r/type_blob.result:
  Auto merged
mysql-test/r/type_ranges.result:
  Auto merged
mysql-test/r/update.result:
  Auto merged
mysql-test/t/bdb-crash.test:
  Auto merged
mysql-test/t/group_by.test:
  Auto merged
mysql-test/t/join_outer.test:
  Auto merged
mysql-test/t/order_by.test:
  Auto merged
mysql-test/t/query_cache.test:
  Auto merged
mysql-test/t/type_blob.test:
  Auto merged
mysql-test/t/type_decimal.test:
  Auto merged
mysql-test/t/type_ranges.test:
  Auto merged
mysql-test/t/update.test:
  Auto merged
sql-bench/crash-me.sh:
  Auto merged
client/mysql.cc:
  Keep old code

18 files changed, 100 insertions, 44 deletions

diff --git a/mysql-test/r/bdb-crash.result b/mysql-test/r/bdb-crash.result
index 42c826d55da..5079368ea21 100644
--- a/mysql-test/r/bdb-crash.result
+++ b/mysql-test/r/bdb-crash.result
@@ -1,6 +1,6 @@
 drop table if exists t1;
 CREATE TABLE t1 (
-ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+ChargeID int(10) unsigned NOT NULL auto_increment,
 ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
 ChargeDate date DEFAULT '0000-00-00' NOT NULL,
 ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index d13f31d6bef..337d5639056 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -13,7 +13,7 @@ INSERT INTO t1 VALUES (2,2,2,'','0000-00-00');
 INSERT INTO t1 VALUES (2,1,1,'','0000-00-00');
 INSERT INTO t1 VALUES (3,3,3,'','0000-00-00');
 CREATE TABLE t2 (
-userID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+userID int(10) unsigned NOT NULL auto_increment,
 niName char(15),
 passwd char(8),
 mail char(50),
@@ -53,7 +53,7 @@ userid	MIN(t1.score+0.0)
 2	2.0
 drop table t1,t2;
 CREATE TABLE t1 (
-PID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+PID int(10) unsigned NOT NULL auto_increment,
 payDate date DEFAULT '0000-00-00' NOT NULL,
 recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
 URID int(10) unsigned DEFAULT '0' NOT NULL,
@@ -76,7 +76,7 @@ SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000
 Can't group on 'IsNew'
 drop table t1;
 CREATE TABLE t1 (
-cid mediumint(9) DEFAULT '0' NOT NULL auto_increment,
+cid mediumint(9) NOT NULL auto_increment,
 firstname varchar(32) DEFAULT '' NOT NULL,
 surname varchar(32) DEFAULT '' NOT NULL,
 PRIMARY KEY (cid)
@@ -84,7 +84,7 @@ PRIMARY KEY (cid)
 INSERT INTO t1 VALUES (1,'That','Guy');
 INSERT INTO t1 VALUES (2,'Another','Gent');
 CREATE TABLE t2 (
-call_id mediumint(8) DEFAULT '0' NOT NULL auto_increment,
+call_id mediumint(8) NOT NULL auto_increment,
 contact_id mediumint(8) DEFAULT '0' NOT NULL,
 PRIMARY KEY (call_id),
 KEY contact_id (contact_id)
@@ -104,7 +104,7 @@ cid	CONCAT(firstname, ' ', surname)	COUNT(call_id)
 drop table t1,t2;
 unlock tables;
 CREATE TABLE t1 (
-bug_id mediumint(9) DEFAULT '0' NOT NULL auto_increment,
+bug_id mediumint(9) NOT NULL auto_increment,
 groupset bigint(20) DEFAULT '0' NOT NULL,
 assigned_to mediumint(9) DEFAULT '0' NOT NULL,
 bug_file_loc text,
@@ -570,3 +570,22 @@ a	MAX(b)	MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
 1	4	c
 10	43	a,b,d,f
 drop table t1;
+create table t1 (id int not null, qty int not null);
+insert into t1 values (1,2),(1,3),(2,4),(2,5);
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
+id	sqty	cqty
+1	5	2
+2	9	2
+select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
+id	sqty
+1	5
+2	9
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
+id	sqty	cqty
+1	5	2
+2	9	2
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
+id	sqty	cqty
+1	5	2
+2	9	2
+drop table t1;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 8abe6d517ee..9ac44bec377 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -234,7 +234,7 @@ INSERT INTO t2 VALUES (11410,11410,131,0);
 INSERT INTO t2 VALUES (11416,11416,32767,0);
 INSERT INTO t2 VALUES (11409,0,0,0);
 CREATE TABLE t3 (
-id int(11) DEFAULT '0' NOT NULL auto_increment,
+id int(11) NOT NULL auto_increment,
 dni_pasaporte char(16) DEFAULT '' NOT NULL,
 idPla int(11) DEFAULT '0' NOT NULL,
 cod_asig int(11) DEFAULT '0' NOT NULL,
@@ -247,7 +247,7 @@ UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
 );
 INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
 CREATE TABLE t4 (
-id int(11) DEFAULT '0' NOT NULL auto_increment,
+id int(11) NOT NULL auto_increment,
 papa int(11) DEFAULT '0' NOT NULL,
 fill int(11) DEFAULT '0' NOT NULL,
 idPla int(11) DEFAULT '0' NOT NULL,
@@ -284,7 +284,7 @@ fill	idPla
 10362	NULL
 drop table t1,t2,t3,test.t4;
 CREATE TABLE t1 (
-id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
+id smallint(5) unsigned NOT NULL auto_increment,
 name char(60) DEFAULT '' NOT NULL,
 PRIMARY KEY (id)
 );
@@ -292,7 +292,7 @@ INSERT INTO t1 VALUES (1,'Antonio Paz');
 INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
 INSERT INTO t1 VALUES (3,'Thimble Smith');
 CREATE TABLE t2 (
-id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
+id smallint(5) unsigned NOT NULL auto_increment,
 owner smallint(5) unsigned DEFAULT '0' NOT NULL,
 name char(60),
 PRIMARY KEY (id)
@@ -382,15 +382,15 @@ id	str
 2	NULL
 drop table t1;
 CREATE TABLE t1 (
-t1_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t1_id bigint(21) NOT NULL auto_increment,
 PRIMARY KEY (t1_id)
 );
 CREATE TABLE t2 (
-t2_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t2_id bigint(21) NOT NULL auto_increment,
 PRIMARY KEY (t2_id)
 );
 CREATE TABLE t3 (
-t3_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t3_id bigint(21) NOT NULL auto_increment,
 PRIMARY KEY (t3_id)
 );
 CREATE TABLE t4 (
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d0d7a954c99..bd5b283f26a 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -15,7 +15,7 @@ INSERT INTO t1 VALUES (2,6,'60671515','Y');
 INSERT INTO t1 VALUES (2,7,'60671569','Y');
 INSERT INTO t1 VALUES (2,3,'dd','Y');
 CREATE TABLE t2 (
-id int(6) DEFAULT '0' NOT NULL auto_increment,
+id int(6) NOT NULL auto_increment,
 description varchar(40) NOT NULL,
 idform varchar(40),
 ordre int(6) unsigned DEFAULT '0' NOT NULL,
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 00d79ec0b6e..da229571497 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -566,3 +566,15 @@ show status like "Qcache_queries_in_cache";
 Variable_name	Value
 Qcache_queries_in_cache	0
 drop table t1;
+drop table if exists t1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+select * from t1 into outfile "query_caceh.out.file";
+select * from t1 limit 1 into dumpfile "query_cache.dump.file";
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+drop table t1;
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 2126fadba58..24c0fcbac40 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -351,7 +351,7 @@ Incorrect sub part key. The used key part isn't a string, the used length is lon
 create table t1 (a text, key (a(255)));
 drop table t1;
 CREATE TABLE t1 (
-t1_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t1_id bigint(21) NOT NULL auto_increment,
 _field_72 varchar(128) DEFAULT '' NOT NULL,
 _field_95 varchar(32),
 _field_115 tinyint(4) DEFAULT '0' NOT NULL,
@@ -375,7 +375,7 @@ INSERT INTO t2 VALUES (1,1);
 INSERT INTO t2 VALUES (2,1);
 INSERT INTO t2 VALUES (2,2);
 CREATE TABLE t3 (
-t3_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t3_id bigint(21) NOT NULL auto_increment,
 _field_131 varchar(128),
 _field_133 tinyint(4) DEFAULT '0' NOT NULL,
 _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
@@ -403,7 +403,7 @@ PRIMARY KEY (seq_0_id,seq_1_id)
 INSERT INTO t4 VALUES (1,1);
 INSERT INTO t4 VALUES (2,1);
 CREATE TABLE t5 (
-t5_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t5_id bigint(21) NOT NULL auto_increment,
 _field_149 tinyint(4),
 _field_156 varchar(128) DEFAULT '' NOT NULL,
 _field_157 varchar(128) DEFAULT '' NOT NULL,
@@ -430,7 +430,7 @@ INSERT INTO t6 VALUES (1,1);
 INSERT INTO t6 VALUES (1,2);
 INSERT INTO t6 VALUES (2,2);
 CREATE TABLE t7 (
-t7_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+t7_id bigint(21) NOT NULL auto_increment,
 _field_143 tinyint(4),
 _field_165 varchar(32),
 _field_166 smallint(6) DEFAULT '0' NOT NULL,
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 0e60eefc9c7..4c326957c03 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -1,6 +1,6 @@
 DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (
-id int(11) DEFAULT '0' NOT NULL auto_increment,
+id int(11) NOT NULL auto_increment,
 datatype_id int(11) DEFAULT '0' NOT NULL,
 minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
 maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index 1b2c775509e..afc4f7c7a8b 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -1,6 +1,6 @@
 drop table if exists t1,t2,t3;
 CREATE TABLE t1 (
-auto int(5) unsigned DEFAULT 0 NOT NULL auto_increment,
+auto int(5) unsigned NOT NULL auto_increment,
 string char(10) default "hello",
 tiny tinyint(4) DEFAULT '0' NOT NULL ,
 short smallint(6) DEFAULT '1' NOT NULL ,
@@ -129,7 +129,7 @@ auto	new_field	new_blob_col	date_field
 15	new	4294967295	0000-00-00
 16	new	NULL	NULL
 CREATE TABLE t2 (
-auto int(5) unsigned NOT NULL DEFAULT 0 auto_increment,
+auto int(5) unsigned NOT NULL auto_increment,
 string char(20),
 mediumblob_col mediumblob not null,
 new_field char(2),
diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result
index 159b971440b..9978e3cb29c 100644
--- a/mysql-test/r/update.result
+++ b/mysql-test/r/update.result
@@ -96,7 +96,7 @@ KEY k4 (assignment),
 KEY ticket (ticket)
 ) TYPE=MyISAM;
 INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
-alter table t1 change lfdnr lfdnr int(10) unsigned default 0 not null auto_increment;
+alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
 update t1 set status=1 where type='Open';
 select status from t1;
 status
diff --git a/mysql-test/t/bdb-crash.test b/mysql-test/t/bdb-crash.test
index 956645b1188..d77de901a30 100644
--- a/mysql-test/t/bdb-crash.test
+++ b/mysql-test/t/bdb-crash.test
@@ -6,7 +6,7 @@
 drop table if exists t1;
 --enable_warnings
 CREATE TABLE t1 (
-  ChargeID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+  ChargeID int(10) unsigned NOT NULL auto_increment,
   ServiceID int(10) unsigned DEFAULT '0' NOT NULL,
   ChargeDate date DEFAULT '0000-00-00' NOT NULL,
   ChargeAmount decimal(20,2) DEFAULT '0.00' NOT NULL,
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 85ec680b3a2..912bed1955c 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -27,7 +27,7 @@ INSERT INTO t1 VALUES (2,1,1,'','0000-00-00');
 INSERT INTO t1 VALUES (3,3,3,'','0000-00-00');
 
 CREATE TABLE t2 (
-  userID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+  userID int(10) unsigned NOT NULL auto_increment,
   niName char(15),
   passwd char(8),
   mail char(50),
@@ -57,7 +57,7 @@ drop table t1,t2;
 #
 
 CREATE TABLE t1 (
-  PID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
+  PID int(10) unsigned NOT NULL auto_increment,
   payDate date DEFAULT '0000-00-00' NOT NULL,
   recDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   URID int(10) unsigned DEFAULT '0' NOT NULL,
@@ -89,7 +89,7 @@ drop table t1;
 #
 
 CREATE TABLE t1 (
-  cid mediumint(9) DEFAULT '0' NOT NULL auto_increment,
+  cid mediumint(9) NOT NULL auto_increment,
   firstname varchar(32) DEFAULT '' NOT NULL,
   surname varchar(32) DEFAULT '' NOT NULL,
   PRIMARY KEY (cid)
@@ -98,7 +98,7 @@ INSERT INTO t1 VALUES (1,'That','Guy');
 INSERT INTO t1 VALUES (2,'Another','Gent');
 
 CREATE TABLE t2 (
-  call_id mediumint(8) DEFAULT '0' NOT NULL auto_increment,
+  call_id mediumint(8) NOT NULL auto_increment,
   contact_id mediumint(8) DEFAULT '0' NOT NULL,
   PRIMARY KEY (call_id),
   KEY contact_id (contact_id)
@@ -124,7 +124,7 @@ unlock tables;
 #
 
 CREATE TABLE t1 (
-  bug_id mediumint(9) DEFAULT '0' NOT NULL auto_increment,
+  bug_id mediumint(9) NOT NULL auto_increment,
   groupset bigint(20) DEFAULT '0' NOT NULL,
   assigned_to mediumint(9) DEFAULT '0' NOT NULL,
   bug_file_loc text,
@@ -426,3 +426,15 @@ select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
 select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
 select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
 drop table t1;
+
+#
+# Problem with group by and alias
+#
+
+create table t1 (id int not null, qty int not null);
+insert into t1 values (1,2),(1,3),(2,4),(2,5);
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
+select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
+select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
+drop table t1;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index bfeb5bbb06b..c57fb8273fe 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -169,7 +169,7 @@ INSERT INTO t2 VALUES (11416,11416,32767,0);
 INSERT INTO t2 VALUES (11409,0,0,0);
 
 CREATE TABLE t3 (
-  id int(11) DEFAULT '0' NOT NULL auto_increment,
+  id int(11) NOT NULL auto_increment,
   dni_pasaporte char(16) DEFAULT '' NOT NULL,
   idPla int(11) DEFAULT '0' NOT NULL,
   cod_asig int(11) DEFAULT '0' NOT NULL,
@@ -184,7 +184,7 @@ CREATE TABLE t3 (
 INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
 
 CREATE TABLE t4 (
-  id int(11) DEFAULT '0' NOT NULL auto_increment,
+  id int(11) NOT NULL auto_increment,
   papa int(11) DEFAULT '0' NOT NULL,
   fill int(11) DEFAULT '0' NOT NULL,
   idPla int(11) DEFAULT '0' NOT NULL,
@@ -211,7 +211,7 @@ drop table t1,t2,t3,test.t4;
 #
 
 CREATE TABLE t1 (
-  id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
+  id smallint(5) unsigned NOT NULL auto_increment,
   name char(60) DEFAULT '' NOT NULL,
   PRIMARY KEY (id)
 );
@@ -220,7 +220,7 @@ INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
 INSERT INTO t1 VALUES (3,'Thimble Smith');
 
 CREATE TABLE t2 (
-  id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
+  id smallint(5) unsigned NOT NULL auto_increment,
   owner smallint(5) unsigned DEFAULT '0' NOT NULL,
   name char(60),
   PRIMARY KEY (id)
@@ -258,15 +258,15 @@ drop table t1;
 #
 
 CREATE TABLE t1 (
-  t1_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t1_id bigint(21) NOT NULL auto_increment,
   PRIMARY KEY (t1_id)
 );
 CREATE TABLE t2 (
-  t2_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t2_id bigint(21) NOT NULL auto_increment,
   PRIMARY KEY (t2_id)
 );
 CREATE TABLE t3 (
-  t3_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t3_id bigint(21) NOT NULL auto_increment,
   PRIMARY KEY (t3_id)
 );
 CREATE TABLE t4 (
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index f7677709ca4..8e1f304a5a5 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -25,7 +25,7 @@ INSERT INTO t1 VALUES (2,7,'60671569','Y');
 INSERT INTO t1 VALUES (2,3,'dd','Y');
 
 CREATE TABLE t2 (
-  id int(6) DEFAULT '0' NOT NULL auto_increment,
+  id int(6) NOT NULL auto_increment,
   description varchar(40) NOT NULL,
   idform varchar(40),
   ordre int(6) unsigned DEFAULT '0' NOT NULL,
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 47ea2d06d0a..243910bf64b 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -410,4 +410,17 @@ select * from t1;
 show status like "Qcache_queries_in_cache";
 load data infile '../../std_data/words.dat' into table t1;
 show status like "Qcache_queries_in_cache";
+drop table t1;
+
+#
+# INTO OUTFILE/DUMPFILE test
+#
+
+drop table if exists t1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+show status like "Qcache_queries_in_cache";
+select * from t1 into outfile "query_caceh.out.file";
+select * from t1 limit 1 into dumpfile "query_cache.dump.file";
+show status like "Qcache_queries_in_cache";
 drop table t1;
\ No newline at end of file
diff --git a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
index 002aeaed9dc..9c00abe980b 100644
--- a/mysql-test/t/type_blob.test
+++ b/mysql-test/t/type_blob.test
@@ -130,7 +130,7 @@ drop table t1;
 #
 
 CREATE TABLE t1 (
-  t1_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t1_id bigint(21) NOT NULL auto_increment,
   _field_72 varchar(128) DEFAULT '' NOT NULL,
   _field_95 varchar(32),
   _field_115 tinyint(4) DEFAULT '0' NOT NULL,
@@ -161,7 +161,7 @@ INSERT INTO t2 VALUES (2,1);
 INSERT INTO t2 VALUES (2,2);
 
 CREATE TABLE t3 (
-  t3_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t3_id bigint(21) NOT NULL auto_increment,
   _field_131 varchar(128),
   _field_133 tinyint(4) DEFAULT '0' NOT NULL,
   _field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
@@ -196,7 +196,7 @@ INSERT INTO t4 VALUES (1,1);
 INSERT INTO t4 VALUES (2,1);
 
 CREATE TABLE t5 (
-  t5_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t5_id bigint(21) NOT NULL auto_increment,
   _field_149 tinyint(4),
   _field_156 varchar(128) DEFAULT '' NOT NULL,
   _field_157 varchar(128) DEFAULT '' NOT NULL,
@@ -228,7 +228,7 @@ INSERT INTO t6 VALUES (1,2);
 INSERT INTO t6 VALUES (2,2);
 
 CREATE TABLE t7 (
-  t7_id bigint(21) DEFAULT '0' NOT NULL auto_increment,
+  t7_id bigint(21) NOT NULL auto_increment,
   _field_143 tinyint(4),
   _field_165 varchar(32),
   _field_166 smallint(6) DEFAULT '0' NOT NULL,
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 9b10e8943ad..053d0517904 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -5,7 +5,7 @@ DROP TABLE IF EXISTS t1;
 --enable_warnings
 
 CREATE TABLE t1 (
-   id int(11) DEFAULT '0' NOT NULL auto_increment,
+   id int(11) NOT NULL auto_increment,
    datatype_id int(11) DEFAULT '0' NOT NULL,
    minvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
    maxvalue decimal(20,10) DEFAULT '0.0000000000' NOT NULL,
diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test
index 767012d0b34..ea7fa7be8c1 100644
--- a/mysql-test/t/type_ranges.test
+++ b/mysql-test/t/type_ranges.test
@@ -7,7 +7,7 @@ drop table if exists t1,t2,t3;
 --enable_warnings
 
 CREATE TABLE t1 (
-  auto int(5) unsigned DEFAULT 0 NOT NULL auto_increment,
+  auto int(5) unsigned NOT NULL auto_increment,
   string char(10) default "hello",
   tiny tinyint(4) DEFAULT '0' NOT NULL ,
   short smallint(6) DEFAULT '1' NOT NULL ,
@@ -93,7 +93,7 @@ select auto,new_field,new_blob_col,date_field from t1 ;
 # check with old syntax
 #
 CREATE TABLE t2 (
-  auto int(5) unsigned NOT NULL DEFAULT 0 auto_increment,
+  auto int(5) unsigned NOT NULL auto_increment,
   string char(20),
   mediumblob_col mediumblob not null,
   new_field char(2),
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 31d22c1f850..6a7eac41a96 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -75,7 +75,7 @@ CREATE TABLE t1 (
 
 INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
 
-alter table t1 change lfdnr lfdnr int(10) unsigned default 0 not null auto_increment;
+alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
 update t1 set status=1 where type='Open';
 select status from t1;
 drop table t1;